pg_basebackup is a widely used PostgreSQL backup tool that allows us to take an ONLINE and CONSISTENT file system level backup. These backups can be used for point-in-time-recovery or to set up a slave/standby. You may want to refer to our previous blog posts, PostgreSQL Backup Strategy, Streaming Replication in PostgreSQL and Faster PITR in PostgreSQL where we describe how we used pg_basebackup for different purposes. In this post, I’ll demonstrate the steps to restore a backup taken using pg_basebackup when we have many tablespaces that store databases or their underlying objects.
A simple backup can be taken using the following syntax.
Tar and Compressed Format $ pg_basebackup -h localhost -p 5432 -U postgres -D /backupdir/latest_backup -Ft -z -Xs -P Plain Format $ pg_basebackup -h localhost -p 5432 -U postgres -D /backupdir/latest_backup -Fp -Xs -P
Using a tar and compressed format is advantageous when you wish to use less disk space to backup and store all tablespaces, data directory and WAL segments, with everything in just one directory (target directory for backup).
Whereas a plain format stores a copy of the data directory as is, in the target directory. When you have one or more non-default tablespaces, tablespaces may be stored in a separate directory. This is usually the same as the original location, unless you use
to modify the destination for storing the tablespaces backup.
PostgreSQL supports the concept of tablespaces. In simple words, a tablespace helps us maintain multiple locations to scatter databases or their objects. In this way, we can distribute the IO and balance the load across multiple disks.
To understand what happens when we backup a PostgreSQL cluster that contains multiple tablespaces, let’s consider the following example. We’ll take these steps:
- Create two tablespaces in an existing master-slave replication setup.
- Take a backup and see what is inside the backup directory.
- Restore the backup.
- Conclude our findings
Create 2 tablespaces and take a backup (tar format) using pg_basebackup
Step 1 :
I set up a replication cluster using PostgreSQL 11.2. You can refer to our blog post Streaming Replication in PostgreSQL to reproduce the same scenario. Here are the steps used to create two tablespaces:
$ sudo mkdir /data_pgbench $ sudo mkdir /data_pgtest $ psql -c "CREATE TABLESPACE data_pgbench LOCATION '/data_pgbench'" $ psql -c "CREATE TABLESPACE data_pgtest LOCATION '/data_pgtest'" $ psql -c "select oid, spcname, pg_tablespace_location(oid) from pg_tablespace" oid | spcname | pg_tablespace_location -------+--------------+------------------------ 1663 | pg_default | 1664 | pg_global | 16419 | data_pgbench | /data_pgbench 16420 | data_pgtest | /data_pgtest (4 rows)
Step 2 :
Now, I create two databases in two different tablespaces, using pgbench to create a few tables and load some data in them.
$ psql -c "CREATE DATABASE pgbench TABLESPACE data_pgbench" $ psql -c "CREATE DATABASE pgtest TABLESPACE data_pgtest" $ pgbench -i pgbench $ pgbench -i pgtest
In a master-slave setup built using streaming replication, you must ensure that the directories exist in the slave, before running a
"CREATE TABLESPACE ..."
on the master. This is because, the same statements used to create a tablespace are shipped/applied to the slave through WALs – this is unavoidable. The slave crashes with the following message, when these directories do not exist:
2018-12-15 12:00:56.319 UTC  LOG: consistent recovery state reached at 0/80000F8 2018-12-15 12:00:56.319 UTC  LOG: database system is ready to accept read only connections 2018-12-15 12:00:56.327 UTC  LOG: started streaming WAL from primary at 0/9000000 on timeline 1 2018-12-15 12:26:36.310 UTC  FATAL: directory "/data_pgbench" does not exist 2018-12-15 12:26:36.310 UTC  HINT: Create this directory for the tablespace before restarting the server. 2018-12-15 12:26:36.310 UTC  CONTEXT: WAL redo at 0/9000448 for Tablespace/CREATE: 16417 "/data_pgbench" 2018-12-15 12:26:36.311 UTC  LOG: startup process (PID 13121) exited with exit code 1 2018-12-15 12:26:36.311 UTC  LOG: terminating any other active server processes 2018-12-15 12:26:36.314 UTC  LOG: database system is shut down 2018-12-15 12:27:01.906 UTC  LOG: database system was interrupted while in recovery at log time 2018-12-15 12:06:13 UTC 2018-12-15 12:27:01.906 UTC  HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
Step 3 :
Let’s now use pg_basebackup to take a backup. In this example, I use a tar format backup.
$ pg_basebackup -h localhost -p 5432 -U postgres -D /backup/latest_backup -Ft -z -Xs -P 94390/94390 kB (100%), 3/3 tablespaces
In the above log, you could see that there are three tablespaces that have been backed up: one default, and two newly created tablespaces. If we go back and check how the data in the two tablespaces are distributed to appropriate directories, we see that there are symbolic links created inside the pg_tblspc directory (within the data directory) for the oid’s of both tablespaces. These links are directed to the actual location of the tablespaces, we specified in Step 1.
$ ls -l $PGDATA/pg_tblspc total 0 lrwxrwxrwx. 1 postgres postgres 5 Dec 15 12:31 16419 -> /data_pgbench lrwxrwxrwx. 1 postgres postgres 6 Dec 15 12:31 16420 -> /data_pgtest
Step 4 :
Here are the contents inside the backup directory, that was generated through the backup taken in Step 3.
$ ls -l /backup/latest_backup total 8520 -rw-------. 1 postgres postgres 1791930 Dec 15 12:54 16419.tar.gz -rw-------. 1 postgres postgres 1791953 Dec 15 12:54 16420.tar.gz -rw-------. 1 postgres postgres 5113532 Dec 15 12:54 base.tar.gz -rw-------. 1 postgres postgres 17097 Dec 15 12:54 pg_wal.tar.gz
Tar Files :
are created as a backup for the two tablespaces. These are created with the same names as the OIDs of their respective tablespaces.
Let’s now take a look how we can restore this backup to completely different locations for data and tablespaces.
Restore a backup with multiple tablespaces
Step 1 :
In order to proceed further with the restore, let’s first extract the base.tar.gz file. This file contains some important files that help us to proceed further.
$ tar xzf /backup/latest_backup/base.tar.gz -C /pgdata $ ls -larth /pgdata total 76K drwx------. 2 postgres postgres 18 Dec 14 14:15 pg_xact -rw-------. 1 postgres postgres 3 Dec 14 14:15 PG_VERSION drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_twophase drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_subtrans drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_snapshots drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_serial drwx------. 4 postgres postgres 36 Dec 14 14:15 pg_multixact -rw-------. 1 postgres postgres 1.6K Dec 14 14:15 pg_ident.conf drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_dynshmem drwx------. 2 postgres postgres 6 Dec 14 14:15 pg_commit_ts drwx------. 6 postgres postgres 54 Dec 14 14:18 base -rw-------. 1 postgres postgres 4.5K Dec 14 16:16 pg_hba.conf -rw-------. 1 postgres postgres 208 Dec 14 16:18 postgresql.auto.conf drwx------. 2 postgres postgres 6 Dec 14 16:18 pg_stat drwx------. 2 postgres postgres 58 Dec 15 00:00 log drwx------. 2 postgres postgres 6 Dec 15 12:54 pg_stat_tmp drwx------. 2 postgres postgres 6 Dec 15 12:54 pg_replslot drwx------. 4 postgres postgres 68 Dec 15 12:54 pg_logical -rw-------. 1 postgres postgres 224 Dec 15 12:54 backup_label drwx------. 3 postgres postgres 28 Dec 15 12:57 pg_wal drwx------. 2 postgres postgres 4.0K Dec 15 12:57 global drwx------. 2 postgres postgres 32 Dec 15 13:01 pg_tblspc -rw-------. 1 postgres postgres 55 Dec 15 13:01 tablespace_map -rw-------. 1 postgres postgres 24K Dec 15 13:04 postgresql.conf -rw-r--r--. 1 postgres postgres 64 Dec 15 13:07 recovery.conf -rw-------. 1 postgres postgres 44 Dec 15 13:07 postmaster.opts drwx------. 2 postgres postgres 18 Dec 15 13:07 pg_notify -rw-------. 1 postgres postgres 30 Dec 15 13:07 current_logfiles
Step 2 :
The files that we need to consider for our recovery are :
When you open the backup_label file, we see the start WAL location, backup start time, etc. These are some details that help us perform a point-in-time-recovery.
$ cat backup_label START WAL LOCATION: 0/B000028 (file 00000001000000000000000B) CHECKPOINT LOCATION: 0/B000060 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2018-12-15 12:54:10 UTC LABEL: pg_basebackup base backup START TIMELINE: 1
Now, let us see what is inside the
$ cat tablespace_map 16419 /data_pgbench 16420 /data_pgtest
In the above log, you could see that there are two entries – one for each tablespace. This is a file that maps a tablespace (oid) to its location. When you start PostgreSQL after extracting the tablespace and WAL tar files, symbolic links are created automatically by postgres – inside the pg_tblspc directory for each tablespace – to the appropriate tablespace location, using the mapping done in this files.
Step 3 :
Now, in order to restore this backup in the same postgres server from where the backup was taken, you must remove the existing data in the original tablespace directories. This allows you to extract the tar files of each tablespaces to the appropriate tablespace locations.
The actual commands for extracting tablespaces from the backup in this case were the following:
$ tar xzf 16419.tar.gz -C /data_pgbench (Original tablespace location) $ tar xzf 16420.tar.gz -C /data_pgtest (Original tablespace location)
In a scenario where you want to restore the backup to the same machine from where the backup was originally taken, we must use different locations while extracting the data directory and tablespaces from the backup. In order to achieve that, tar files for individual tablespaces may be extracted to different directories than the original directories specified in
file, upon which we can modify the
file with the new tablespace locations. The next two steps should help you to see how this works.
Step 3a :
Create two different directories and extract the tablespaces to them.
$ tar xzf 16419.tar.gz -C /pgdata_pgbench (Different location for tablespace than original) $ tar xzf 16420.tar.gz -C /pgdata_pgtest (Different location for tablespace than original)
Step 3b :
file with the new tablespace locations. Replace the original location of each tablespace with the new location, where we have extracted the tablespaces in the previous step. Here is how it appears after the edit.
$ cat tablespace_map 16419 /pgdata_pgbench 16420 /pgdata_pgtest
Step 4 :
Extract pg_wal.tar.gz from backup to pg_wal directory of the new data directory.
$ tar xzf pg_wal.tar.gz -C /pgdata/pg_wal
Step 5 :
to specify the time until when you wish to perform a point-in-time-recovery. Please refer to our previous blog post – Step 3, to understand recovery.conf for PITR in detail.
Step 6 :
Once all of the steps above are complete you can start PostgreSQL.
You should see the following files renamed after recovery.
backup_label --> backup_label.old tablespace_map --> tablespace_map.old recovery.conf --> recovery.done
To avoid the exercise of manually modifying the tablespace_map file, you can use
. This is an option that works when you use a plain format backup, but not with tar. Let’s see why you may prefer a tar format when compared to plain.
Backup of PostgreSQL cluster with tablespaces using plain format
Consider the same scenario where you have a PostgreSQL cluster with two tablespaces. You might see the following error when you do not use
$ pg_basebackup -h localhost -p 5432 -U postgres -D /backup/latest_backup -Fp -Xs -P -v pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/22000028 on timeline 1 pg_basebackup: directory "/data_pgbench" exists but is not empty pg_basebackup: removing contents of data directory "/backup/latest_backup"
What the above error means is that the pg_basebackup is trying to store the tablespaces in the same location as the original tablespace directory. Here
is the location of tablespace :
And, now, pg_basebackup is trying to store the tablespace backup in the same location. In order to overcome this error, you can apply tablespace mapping using the following syntax.
$ pg_basebackup -h localhost -p 5432 -U postgres -D /backup/latest_backup -T "/data_pgbench=/pgdata_pgbench" -T "/data_pgtest=/pgdata_pgtest" -Fp -Xs -P
is used to specify the tablespace mapping.
can be replaced by
The advantage of using -T (
) is that the tablespaces are stored separately in the mapping directories. In this example with plain format backup, you must extract all the following three directories in order to restore/recover the database using backup.
However, you do not need a
file in this scenario, as it is automatically taken care of by PostgreSQL.
If you take a backup in tar format, you see all the tar files for base, tablespaces and WAL segments stored in the same backup directory, and just this directory can be extracted for performing restore/recovery. However, you must manually extract the tablespaces and WAL segments to appropriate locations and edit the tablespace_map file, as discussed above.