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.