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:

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

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

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

For tables it works exactly the same:

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

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

postgres@debian11pg:/home/postgres/ [pgdev] cat filter.txt 
include schema s1
exclude data s1.t1
postgres@debian11pg:/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.