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 is a VP of Development with HighGo Software Inc. Prior to coming to HighGo Software, Ahsan had worked at EnterpriseDB as a Senior Director of Product Development, Ahsan worked with EnterpriseDB for 15 years. The flagship product of EnterpriseDB is Postgres Plus Advanced server which is based on Open source PostgreSQL. Ahsan has vast experience with Postgres and has lead the development team at EnterpriseDB for building the core compatibility of adding Oracle compatible layer to EDB’s Postgres Plus Advanced Server. Ahsan has also spent number of years working with development team for adding Horizontal scalability and sharding to Postgres. Initially, he worked with postgres-xc which is multi-master sharded cluster and later worked on managing the development of adding horizontal scalability/sharding to Postgres. Ahsan has also worked a great deal with Postgres foreign data wrapper technology and worked on developing and maintaining FDW’s for several sql and nosql databases like MongoDB, Hadoop and MySQL.
Prior to EnterpriseDB, Ahsan worked for Fusion Technologies as a Senior Project Manager. Fusion Tech was a US based consultancy company, Ahsan lead the team that developed java based job factory responsible for placing items on shelfs at big stores like Walmart. Prior to Fusion technologies, Ahsan worked at British Telecom as a Analyst/Programmer and developed web based database application for network fault monitoring.
Ahsan joined HighGo Software Inc (Canada) in April 2019 and is leading the development teams based in multiple Geo’s, the primary responsibility is community based Postgres development and also developing HighGo Postgres server.