By Mouhamadou Diaw

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

1
2
3
[postgres@dbi-pg-essentials log]$ /u01/app/postgres/product/12dev/bin/pg_dumpall --help | grep exclude
--exclude-database=PATTERN   exclude databases whose name matches PATTERN
[postgres@dbi-pg-essentials log]$

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

Let’s first list the databases in my cluster

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
[postgres@dbi-pg-essentials ~]$  /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

1
2
3
4
5
6
[postgres@dbi-pg-essentials ~]$ 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';
[postgres@dbi-pg-essentials ~]$

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

1
[postgres@dbi-pg-essentials ~]$ /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.

1
2
3
4
[postgres@dbi-pg-essentials ~]$ 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';
[postgres@dbi-pg-essentials ~]$¨

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

1
2
3
4
5
[postgres@dbi-pg-essentials ~]$  /u01/app/postgres/product/12dev/bin/pg_dumpall  --exclude-database=db* > without_db_star.sql
[postgres@dbi-pg-essentials ~]$ 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';
[postgres@dbi-pg-essentials ~]$

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

1
2
3
4
[postgres@dbi-pg-essentials ~]$  /u01/app/postgres/product/12dev/bin/pg_dumpall  --exclude-database=*db* > without_star_db_star.sql
[postgres@dbi-pg-essentials ~]$ 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';
[postgres@dbi-pg-essentials ~]$

Conclusion:

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

Post Views: 3,472
Thumbnail [60x60]
by
Oracle Team