Infrastructure at your Service

Mouhamadou Diaw

PostgreSQL 12: New option –exclude-database for pg_dumpall

Up to PostgreSQL 11 pg_dump all was used to dump all PostgreSQL databases of a cluster into one script file. The script file then contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in the cluster.
PostgreSQL 12 is under development but we can test new features which will be implemented. Now with PostgreSQL 12 there is a new option that allows to exclude specific database with pg_dumpall. This option is called –exclude-database
This option can be useful where a database name is visible but the database is not dumpable by the user.
We can now see this new option in the help output

[[email protected] log]$ /u01/app/postgres/product/12dev/bin/pg_dumpall --help | grep exclude
--exclude-database=PATTERN   exclude databases whose name matches PATTERN
[[email protected] log]$

In this blog I am going to test this new option.

Let’s first list the databases in my cluster

postgres=# \l
                                     List of databases
      Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------------+----------+----------+-------------+-------------+-----------------------
 db1             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 db2             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 mydb            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres_sample | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
(7 rows)

postgres=#

First let’s dump all user databases

[[email protected] ~]$  /u01/app/postgres/product/12dev/bin/pg_dumpall   > with_alldb.sql   

If we do a grep in the output file, we can see that the dump contains all databases

[[email protected] ~]$ grep "CREATE DATABASE" with_alldb.sql
CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE db2 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE postgres_sample WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
[[email protected] ~]$

Now let’s dump all the user databases except db1 and db2.

[[email protected] ~]$ /u01/app/postgres/product/12dev/bin/pg_dumpall  --exclude-database=db2 --exclude-database=db1 > without_db1_db2.sql

We can effectively verify that the dump file contains instructions to create the databases mydb and postgres_sample. That’s the expected result.

[[email protected] ~]$ grep "CREATE DATABASE" without_db1_db2.sql
CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE postgres_sample WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
[[email protected] ~]$¨

What is interesting is that we can use wildcard characters. The result above can obtained like following

[[email protected] ~]$  /u01/app/postgres/product/12dev/bin/pg_dumpall  --exclude-database=db* > without_db_star.sql
[[email protected] ~]$ grep "CREATE DATABASE" without_db_star.sql
CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
CREATE DATABASE postgres_sample WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
[[email protected] ~]$

Let’s say we want to dump all user databases except those whose names contain ‘db’

[[email protected] ~]$  /u01/app/postgres/product/12dev/bin/pg_dumpall  --exclude-database=*db* > without_star_db_star.sql
[[email protected] ~]$ grep "CREATE DATABASE" without_star_db_star.sql                    
 CREATE DATABASE postgres_sample WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
[[email protected] ~]$

Conclusion:

In this blog we talked about the new option exclude-database for pg_dumpall in PostgreSQL 12.

Leave a Reply

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

Mouhamadou Diaw
Mouhamadou Diaw

Consultant