From: | Peter Adlersburg <peter(dot)adlersburg(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema= |
Date: | 2024-07-08 16:16:41 |
Message-ID: | CAD1Uk1qjrjq3Y7PHjrO0ycAv3kiVBKpS9bUCKDYM=5Oag7RxPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hello,
postgres: 15.6
os: rhel8
I've been tasked to create a workflow to copy various schemas of a
database "db" from a remote cluster to a local cluster.
database: db
consists of
schema a -> should be included in the dump
schema b -> should be included in the dump
schema c -> not needed
schema d -> not needed
schema e -> consists of a bunch of foreign tables / data for this tables
should *not* be included in dump (only CREATE of the foreign tables is
required)
schema f -> should be included in the dump
There also exists a foreign server fsrv in the remote database.
The role transfer_role has read access to all tables in schematas a, b and
f
--------------------------------------------------------------------------------------------
variant a:
--------------------------------------------------------------------------------------------
pg_dump --host=remote-host --user=transfer_role --exclude-schema=c
--exclude-schema=d --schema-only --create db > db.ddl
the generated ddl includes:
...
CREATE SERVER fsrv FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( ... ) ;
CREATE USER MAPPING FOR <role> SERVER fsrv;
...
CREATE SCHEMA e ;
...
CREATE FOREIGN TABLE e.<table> ... SERVER fsrv ;
...
--------------------------------------------------------------------------------------------
variant b:
--------------------------------------------------------------------------------------------
pg_dump --host=remote-host --user=transfer_user --schema=a --schema=b
--schema=e --schema=f --schema-only --create db > db.ddl
the generated ddl includes:
...
CREATE SCHEMA e ;
...
CREATE FOREIGN TABLE e.<table> ... SERVER fsrv ;
...
*NOT* included are
CREATE SERVER ...
CREATE USER MAPPING FOR <role> ...
--------------------------------------------------------------------------------------------
Question:
--------------------------------------------------------------------------------------------
What's the trick to use --schema= with pg_dump *AND* also have the foreign
server definition and the user mappings copied?
(I also included --schema=public but that changed nothing)
Thanks for any input & KR
p.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-07-08 16:32:26 | Re: Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema= |
Previous Message | Keith Fiske | 2024-07-08 13:15:35 | Re: Detach partition concurrently from pg cron |