I was doing some testing recently with parallel backup feature along with backup manifest feature which was one of the last feature that got committed to PostgreSQL 13. Parallel backup is not committed to PG yet, it is currently under discussion in the hackers community. I was trying to take parallel backup of a database that contained multiple tablespaces and ran into some issues on how to pass multiple tablespaces to pg_basebackup. So decided to write this small blog on how to do that. This write-up might not be worthy of a blog but it will help someone who is facing the same issue. 🙂

So I start with creating two directories for the tablespaces :

mkdir ~/pgbench_tab_1
mkdir ~/pgbench_tab_2 

The test database would be created with pgbench hence the name of the tablespace directories are named as such. When initialising the database with pgbench, you can pass tablesapce to —index_tablespace and –tablespace switch.

Next I went ahead and created the tablespaces in psql :

postgres=# create tablespace pgbench_tab1 location '/home/postgres/pgbench_tab_1';

postgres=# create tablespace pgbench_tab2 location '/home/postgres/pgbench_tab_2';

Although it is not related to this topic but the following is the pgbench command used to initialise the database with tablespaces.

./pgbench -i -s 100 --tablespace=pgbench_tab1 --index-tablespace=pgbench_tab2 postgres

In the link below to pg_basebackup documentation, you will see the switch -T or —tablespace-mapping used for passing tablespace information to pg_basebackup. The “OLDIR” is the source tablespace directory i.e. /home/postgres/pgbench_tab_1 in case of this example. The “NEWDIR”is the new location of the tablespace for the backup, we will create the new directories below.

Here is link to pg_basebackup documentation :

https://www.postgresql.org/docs/12/app-pgbasebackup.html

Create the new directories where the backup tablespaces will be kept :

mkdir ~/pgbench_tab_3
mkdir ~/pgbench_tab_4 

Once the directories are created we are ready to pass these to pg_basebackup :

./pg_basebackup -D ../pgsql_backup -v -F p --tablespace-mapping=/home/postgres/pgbench_tab_1=/home/postgres/pgbench_tab_3  --tablespace-mapping=/home/postgres/pgbench_tab_2=/home/postgres/pgbench_tab_4

As you can see I have used the —-tablespace-mapping switch twice to pass information for multiple tablespaces, you can pass more of these for backing up database containing more tablespaces. One can also use -T instead of –tablespace-mapping for doing the same thing.

Ahsan Hadi

Ahsan Hadi