Can't drop logical replication slot
Hi all,
There is a logical replication slot I can't drop. Tried using
select pg_terminate_backend(PID) OK
select pg_drop_replication_slot('slot_name) OK
But the slot_name restarts with other active_pid. There is not subscriber
connected and no errors in database log. We are using PG14.
Any help to drop or even rename replication slot would be appreciated.
Thanks in advance.
--
Eduardo
Making Sure Primary and Secondary Keys Alligns
Fact Table:
-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_ZTBR_TransactionCode_seq"'::regclass),
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE
pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer,
"Source_Description_Secondary_Key" integer,
"Direct_Indirect_Secondary_Key" integer,
"Entity_Secondary_Key" integer,
"Region_Secondary_Key" integer,
"Staging_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY
("ZTBR_TransactionCode"),
CONSTRAINT
"IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_key" UNIQUE
("Direct_Indirect_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_key" UNIQUE
("Entity_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_key"
UNIQUE ("Master_BRACS_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_key" UNIQUE
("Region_Secondary_Key"),
CONSTRAINT
"IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_key" UNIQUE
("Source_Description_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_key"
UNIQUE ("Staging_Secondary_Key")
)and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
"CoCd" character varying(255) COLLATE pg_catalog."default",
"Sub Region" character varying(255) COLLATE pg_catalog."default",
"Region" character varying(255) COLLATE pg_catalog."default",
"BRACS Entity" character varying(255) COLLATE pg_catalog."default",
"Consul" character varying(255) COLLATE pg_catalog."default",
"Report" character varying(255) COLLATE pg_catalog."default",
"Region BRACS" character varying(255) COLLATE pg_catalog."default",
"Group" character varying(255) COLLATE pg_catalog."default",
"Group BRACS" character varying(255) COLLATE pg_catalog."default",
"J" character varying(255) COLLATE pg_catalog."default",
"K" character varying(255) COLLATE pg_catalog."default",
"L" character varying(255) COLLATE pg_catalog."default",
"M" character varying(255) COLLATE pg_catalog."default",
"N" character varying(255) COLLATE pg_catalog."default",
"Region_Primary_Key" integer NOT NULL DEFAULT
nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY
("Region_Primary_Key")
)
How do i get that all these columns that are joined are aligned, meaning if
it starts with 1 in one column it must be 1 in the other columns. Or how
would you assign unique keys in Postgres?
Re: Making Sure Primary and Secondary Keys Alligns
> On 11 Sep 2023, at 16:09, Anthony Apollis wrote: > > Fact Table: > CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" > ( (…) > ) > and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping" > ( (…) > ) > How do i get that all these columns that are joined are aligned, meaning if > it starts with 1 in one column it must be 1 in the other columns. Or how > would you assign unique keys in Postgres? Are you perhaps asking how to define FOREIGN KEY CONSTRAINTs? https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-FK Regards, Alban Hertroys -- There is always an exception to always.
Upgrade problem
Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: /mga8/usr/bin/postgres: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory no data was returned by command ""/mga8/usr/bin/postgres" -V" However: [root@bach lib64]# cd /mga8/usr/lib64 [root@bach lib64]# ls -l|grep libssl -rwxr-xr-x 1 root root 426192 Jul 5 23:07 libssl3.so* lrwxrwxrwx 1 root root 13 Jun 1 09:35 libssl.so -> libssl.so.1.1* -r-xr-xr-x 1 root root 442424 Feb 27 2021 libssl.so.1.0.0* -rwxr-xr-x 1 root root 666496 Jun 1 09:36 libssl.so.1.1* Can someone suggest my next move please? Ta Graeme Gemmill
Re: Upgrade problem
Graeme writes: > Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg > 15. I'm at the point of running pg_upgrade but have received anerror > message: > /mga8/usr/bin/postgres: error while loading shared libraries: > libssl.so.1.1: cannot open shared object file: No such file or directory > no data was returned by command ""/mga8/usr/bin/postgres" -V" You might get useful info from "ldd /mga8/usr/bin/postgres" about where that executable is looking for libssl. regards, tom lane
Re: Upgrade problem
On 9/11/23 09:04, Graeme wrote: Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: You are going to have to be more specific on the Postgres version. Prior to Postgres 10 major version changes where two digits. So for Postgres 9.X.x that meant 9.0.x --> 9.6.x /mga8/usr/bin/postgres: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory no data was returned by command ""/mga8/usr/bin/postgres" -V" Where are you running the pg_upgrade and what version of it are you using? However: [root@bach lib64]# cd /mga8/usr/lib64 [root@bach lib64]# ls -l|grep libssl -rwxr-xr-x 1 root root 426192 Jul 5 23:07 libssl3.so* lrwxrwxrwx 1 root root 13 Jun 1 09:35 libssl.so -> libssl.so.1.1* -r-xr-xr-x 1 root root 442424 Feb 27 2021 libssl.so.1.0.0* -rwxr-xr-x 1 root root 666496 Jun 1 09:36 libssl.so.1.1* Can someone suggest my next move please? Ta Graeme Gemmill -- Adrian Klaver adrian.kla...@aklaver.com
Re: Upgrade problem
On 11/09/2023 17:10, Tom Lane wrote: Graeme writes: Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: /mga8/usr/bin/postgres: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory no data was returned by command ""/mga8/usr/bin/postgres" -V" You might get useful info from "ldd /mga8/usr/bin/postgres" about where that executable is looking for libssl. regards, tom lane Tom, thanks: it's finding other so. files in the correct place libpam.so.0 => /lib64/libpam.so.0 (0x7f8d49e1e000) libssl.so.1.1 => not found libcrypto.so.1.1 => not found librt.so.1 => /lib64/librt.so.1 (0x7f8d49e16000) so there must be another problem. Graeme
Re: Upgrade problem
Graeme writes: > Tom, thanks: it's finding other so. files in the correct place > libpam.so.0 => /lib64/libpam.so.0 (0x7f8d49e1e000) > libssl.so.1.1 => not found > libcrypto.so.1.1 => not found > librt.so.1 => /lib64/librt.so.1 (0x7f8d49e16000) Note this only shows it looking in /lib64, maybe you need a symlink there? Alternatively, try ldd on the libssl.so.1.1 file itself, to see if it has unresolved dependencies. I'm not totally sure, but I think indirect unresolved dependencies might display this way. regards, tom lane
Re: Upgrade problem
On 11/09/2023 17:13, Adrian Klaver wrote: On 9/11/23 09:04, Graeme wrote: Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: You are going to have to be more specific on the Postgres version. Prior to Postgres 10 major version changes where two digits. So for Postgres 9.X.x that meant 9.0.x --> 9.6.x Don't have access to that version without re-booting; probably 9.5 /mga8/usr/bin/postgres: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory no data was returned by command ""/mga8/usr/bin/postgres" -V" Where are you running the pg_upgrade and what version of it are you using? pg_upgrade (PostgreSQL) 15.3 I specified -B /usr/bin However: [root@bach lib64]# cd /mga8/usr/lib64 [root@bach lib64]# ls -l|grep libssl -rwxr-xr-x 1 root root 426192 Jul 5 23:07 libssl3.so* lrwxrwxrwx 1 root root 13 Jun 1 09:35 libssl.so -> libssl.so.1.1* -r-xr-xr-x 1 root root 442424 Feb 27 2021 libssl.so.1.0.0* -rwxr-xr-x 1 root root 666496 Jun 1 09:36 libssl.so.1.1* Can someone suggest my next move please? Ta Graeme Gemmill
Re: Upgrade problem
On 9/11/23 09:33, Graeme wrote: On 11/09/2023 17:13, Adrian Klaver wrote: On 9/11/23 09:04, Graeme wrote: Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: You are going to have to be more specific on the Postgres version. Prior to Postgres 10 major version changes where two digits. So for Postgres 9.X.x that meant 9.0.x --> 9.6.x Don't have access to that version without re-booting; probably 9.5 In psql do: select version(); Where are you running the pg_upgrade and what version of it are you using? pg_upgrade (PostgreSQL) 15.3 I specified -B /usr/bin Your Postgres 15 is on one machine(OS version) and your Postgres 9.X is on another machine(OS version), how are reaching the Postgres installs on both? Graeme Gemmill -- Adrian Klaver adrian.kla...@aklaver.com
Re: Upgrade problem
On 11/09/2023 17:33, Graeme wrote: On 11/09/2023 17:13, Adrian Klaver wrote: On 9/11/23 09:04, Graeme wrote: Preparing to upgrade my small cluster from Mageia 8/Pg 9 to Mageia 9/Pg 15. I'm at the point of running pg_upgrade but have received anerror message: You are going to have to be more specific on the Postgres version. Prior to Postgres 10 major version changes where two digits. So for Postgres 9.X.x that meant 9.0.x --> 9.6.x Don't have access to that version without re-booting; probably 9.5 select version(); ? Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
Re: Granting privileges to a schema to a role
On 11/09/2023 20:07 CEST Johnson, Bruce E - (bjohnson) wrote: > I’ve created the database ‘webdata', successfully used ora2pg to migrate one > schema ‘trav’ to Postgres. > > The schema on the oracle side is called trav the owner is webdata, and I > created the role trav and granted all table rights in the schema to the role > > GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav. > > When I log into pgsql as trav and run \dp the privileges appear correct but > trying a simple select fails with ‘permission denied’ error: > > > psql webdata -U trav > > psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) > > Type "help" for help. > > > webdata=> \dp trav.sectors > > Access privileges > > Schema | Name | Type |Access privileges| Column privileges | > Policies > > +-+---+-+---+-- > > trav | sectors | table | webdata=arwdDxt/webdata+| | > > | | | trav=arwdDxt/webdata| | > > (1 row) > > > webdata=> select sectorname, count(worldname) from trav.sectors group by > sectorname order by sectorname; > > ERROR: permission denied for schema trav > > LINE 1: select sectorname, count(worldname) from trav.sectors group ... > > What am I missing? You must also grant USAGE on schema trav to role trav to access objects in that schema. Use \dn+ trav to check the schema privileges. -- Erik
Granting privileges to a schema to a role
(Background I’m working on migrating an existing set of Oracle schemas with a bunch of inter-schema grants to a Postgres 15 system) I’ve created the database ‘webdata', successfully used ora2pg to migrate one schema ‘trav’ to Postgres. The schema on the oracle side is called trav the owner is webdata, and I created the role trav and granted all table rights in the schema to the role GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav. When I log into pgsql as trav and run \dp the privileges appear correct but trying a simple select fails with ‘permission denied’ error: psql webdata -U trav psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) Type "help" for help. webdata=> \dp trav.sectors Access privileges Schema | Name | Type |Access privileges| Column privileges | Policies +-+---+-+---+-- trav | sectors | table | webdata=arwdDxt/webdata+| | | | | trav=arwdDxt/webdata| | (1 row) webdata=> select sectorname, count(worldname) from trav.sectors group by sectorname order by sectorname; ERROR: permission denied for schema trav LINE 1: select sectorname, count(worldname) from trav.sectors group ... What am I missing? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
