Infrastructure at your Service

Daniel Westermann

PostgreSQL 15: read some options for pg_dump from a file

PostgreSQL 15 will probably come with a new feature for pg_dump: Instead of passing the arguments/options directly to pg_dump when it is executed, you may also use a file to describe what you want to export. In the Oracle world it was (maybe still is, but I don’t know) very popular to do that with traditional exp/imp and the expdp/impdp utilities. pg_dump did not know that feature but this will probably change for PostgreSQL 15. Currently the patch is “ready for committer” so we don’t really know if it finally will be committed, but I believe chances are not too bad. Let’s have a look.

For being able to dump, we need some data:

postgres=# create schema s1;
CREATE SCHEMA
postgres=# create schema s2;
CREATE SCHEMA
postgres=# create table s1.t1 as select i as a, i::text as b, now() as c from generate_series(1,100) i;
SELECT 100
postgres=# create table s2.t2 as select i as a, i::text as b, now() as c from generate_series(1,100) i;
SELECT 100
postgres=# create view s1.v1 as select a from s1.t1;
CREATE VIEW
postgres=# create view s2.v2 as select b from s2.t2;
CREATE VIEW

The new option for pg_dump is called:

  --filter=FILENAME            dump objects and data based on the filter expressions
                               in specified file

Currently the following types of objects are supported:

  • tables
  • schemas
  • foreign_data
  • data

The first two can be either “included” or “excluded”, foreign_data can only be “included” and data can only be excluded. If, for example, we want to dump the “s1” schema but we do not want to have the “s2” schema included we can do it like this:

[email protected]:/home/postgres/ [pgdev] echo "include schema s1" > filter.txt
[email protected]:/home/postgres/ [pgdev] echo "exclude schema s2" >> filter.txt
[email protected]:/home/postgres/ [pgdev] pg_dump --filter=filter.txt > dmp.sql
[email protected]:/home/postgres/ [pgdev] grep s2 dmp.sql 

Actually, for this requirement this would already be enough and the result is the same:

[email protected]:/home/postgres/ [pgdev] echo "include schema s1" > filter.txt
[email protected]:/home/postgres/ [pgdev] pg_dump --filter=filter.txt > dmp.sql
[email protected]:/home/postgres/ [pgdev] grep s2 dmp.sql 

For tables it works exactly the same:

[email protected]:/home/postgres/ [pgdev] echo "include table s1.t1" > filter.txt
[email protected]:/home/postgres/ [pgdev] pg_dump --filter=filter.txt > dmp.sql

Getting only the meta data is possible by excluding the data from the dump:

[email protected]:/home/postgres/ [pgdev] cat filter.txt 
include schema s1
exclude data s1.t1
[email protected]:/home/postgres/ [pgdev] pg_dump --filter=filter.txt > dmp.sql

As I do not have a foreign data wrapper configured right now, I’ll skip this one, should be clear what it does. This for sure is not a killer feature but personally I always liked to work with options coming from files, and this is a good starting point.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure