Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema=

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.

Responses

Browse pgsql-admin by date

  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