Postgres Pro
Downloads
Home   >   mailing lists

Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema= - Mailing list pgsql-admin

From Peter Adlersburg
Subject Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema=
Date
Msg-id CAD1Uk1qjrjq3Y7PHjrO0ycAv3kiVBKpS9bUCKDYM=5Oag7RxPA@mail.gmail.com
Whole thread Raw
List pgsql-admin
Tree view
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.

pgsql-admin by date:

Previous
From: Keith Fiske
Date:
Subject: Re: Detach partition concurrently from pg cron
Next
From: "David G. Johnston"
Date:
Subject: Re: Retrieve "CREATE FOREIGN SERVER" with pg_dump ... --schema=