Physical PostgreSQL Backup using pg_basebackup

June 21, 2021

Physical PostgreSQL Backup using pg_basebackup

pg_basebackup is used to take base backups of a running PostgreSQL database cluster.
It Can be used both for point-in-time recovery and as the starting point for log shipping or streaming replication standby servers.
pg_basebackup makes a binary copy of the database cluster files while making sure the system is put in and out of backup mode automatically.
Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.
The backup is made over a regular PostgreSQL connection and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions.

For this demonstration, we have already created a sample database click here to read more.

Help Command: Use option –help to get full available options for pg_basebackup

[root@test-machine02 backup]# pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.

Usage:
  pg_basebackup [OPTION]...

Options controlling the output:
  -D, --pgdata=DIRECTORY receive base backup into directory

  -F, --format=p|t       output format (plain (default), tar) - piping to for example gzip. This is only possible if the cluster has no additional tablespaces and WAL streaming is not used.

  -r, --max-rate=RATE    maximum transfer rate to transfer data directory
                         (in kB/s, or use suffix "k" or "M")

Plain Format Full Cluster Backup: Before you take backup make sure the target directory is empty. Use option -Fp for plain backup. Backup command will work fine if you have only default tablespaces i.e. pg_defualt and pg_global. But if you have added tablespace in your cluster you need to use option -T, –tablespace-mapping=OLDDIR=NEWDIR. The plain format will take binary backup of PG_DATA directory and non-default tablespace folder into the target folder.

-bash-4.2$ pg_basebackup -h localhost -p 5432 -U postgres -D /u01/backup/bkp1 -Fp
Password:
pg_basebackup: error: directory "/u01/backup/bkp1" exists but is not empty
-bash-4.2$

-bash-4.2$ pg_basebackup -h localhost -p 5432 -U postgres -D /u01/backup/bkp1 -Fp
Password:
pg_basebackup: error: directory "/u01/psql_tbls" exists but is not empty
pg_basebackup: removing contents of data directory "/u01/backup/bkp1"
-bash-4.2$
-bash-4.2$

postgres=# \db+
                                      List of tablespaces
    Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description
------------+----------+-----------------+-------------------+---------+---------+-------------
 myts01     | postgres | /u01/psql_tbls  |                   |         | 8173 kB |
 myts02     | postgres | /u01/psql2_tbls |                   |         | 15 MB   |
 pg_default | postgres |                 |                   |         | 24 MB   |
 pg_global  | postgres |                 |                   |         | 367 kB  |
(4 rows)

-bash-4.2$ pg_basebackup -h localhost -p 5432 -U postgres -D /u01/backup/bkp1 -Fp -T /u01/psql_tbls=/u01/backup/psql_tbls -T /u01/psql2_tbls=/u01/backup/psql2_tbls -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_38440"
48888/48888 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/1D000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$

-bash-4.2$ pwd
/u01/backup
-bash-4.2$
-bash-4.2$ ls -ltr psql_tbls
total 0
drwx------. 4 postgres postgres 32 Jun 19 15:25 PG_13_202007201
-bash-4.2$
-bash-4.2$ ls -ltr psql2_tbls
total 0
drwx------. 3 postgres postgres 19 Jun 19 15:25 PG_13_202007201
-bash-4.2$
-bash-4.2$ ls -ltr bkp1
total 312
drwx------. 3 postgres postgres     60 Jun 19 15:25 pg_wal
-rw-------. 1 postgres postgres    226 Jun 19 15:25 backup_label
drwx------. 2 postgres postgres      6 Jun 19 15:25 pg_commit_ts
drwx------. 2 postgres postgres      6 Jun 19 15:25 pg_twophase
drwx------. 2 postgres postgres      6 Jun 19 15:25 pg_subtrans
drwx------. 2 postgres postgres      6 Jun 19 15:25 pg_snapshots
drwx------. 2 postgres postgres      6 Jun 19 15:25 pg_serial
-bash-4.2$

Plain Format Full Cluster Backup with –waldir: By default, pg_basebackup command will take backup of WAL segments unless you explicitly define –wal-method=none or -Xn. If you wish to take WAL backup in a separate folder along with pg_wal folder you can specify the parameter –waldir.

-bash-4.2$
-bash-4.2$ pg_basebackup -h localhost -p 5432 -U postgres -D /u01/backup/bkp1 -Fp -T /u01/psql_tbls=/u01/backup/psql_tbls -T /u01/psql2_tbls=/u01/backup/psql2_tbls --waldir=/u01/backup/wal_bkp -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1F000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_39171"
48888/48888 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/1F000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$

-bash-4.2$
-bash-4.2$ ls -ltr wal_bkp/
total 16384
drwx------. 2 postgres postgres       43 Jun 19 15:37 archive_status
-rw-------. 1 postgres postgres 16777216 Jun 19 15:37 00000001000000000000001F
-bash-4.2$
-bash-4.2$ ls -ltr wal_bkp/archive_status/
total 0
-rw-------. 1 postgres postgres 0 Jun 19 15:37 00000001000000000000001F.done
-bash-4.2$

Tar Format Full Cluster Backup: Use option -Ft for tar format backup. This option will create one tar file for PG_DATA, PG_WAL, and non-default Tablespace. You can also use -z option for Compression and compression can only be used with Tar format backup.

-bash-4.2$
-bash-4.2$ pg_basebackup -h localhost -p 5432 -U postgres -D /u01/backup/06-16-2021 -Ft -z -Xs -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/21000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_39432"
48888/48888 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/21000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$

-bash-4.2$ pwd
/u01/backup
-bash-4.2$  ls -ltr 06-16-2021
total 7036
-rw-------. 1 postgres postgres 1027472 Jun 19 15:41 23701.tar.gz
-rw-------. 1 postgres postgres 2790209 Jun 19 15:41 23704.tar.gz
-rw-------. 1 postgres postgres 3106630 Jun 19 15:41 base.tar.gz
-rw-------. 1 postgres postgres  251658 Jun 19 15:41 backup_manifest
-rw-------. 1 postgres postgres   17657 Jun 19 15:41 pg_wal.tar.gz
-bash-4.2$

Remote pg_basebackup: To execute pg_basebackup command from the Remote Server you need to update pg_hba.conf and tell PostgreSQL Server that you will get replication connection request from Remote Server. Otherwise, you will receive an error. Once changes are done in pg_hba.conf reload the changes with pg_ctl reload or SELECT pg_reload_conf(), Please note we added line “host replication all 192.168.114.0/24 scram-sha-256“

-bash-4.2$ hostname
test-machine01
-bash-4.2$  pg_basebackup -h test-machine02 -p 5432 -U postgres -D /u01/pg_backup -Ft -z -Xs -P -v
pg_basebackup: error: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.114.177", user "postgres", SSL off
-bash-4.2$
-bash-4.2$


-bash-4.2$ cd /var/lib/pgsql/13/data/
-bash-4.2$ vi pg_hba.conf
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     all             192.168.114.0/24        scram-sha-256
-bash-4.2$


-bash-4.2$ /usr/pgsql-13/bin/pg_ctl reload
server signaled
-bash-4.2$

-bash-4.2$ psql
psql (13.2)
Type "help" for help.

postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#



-bash-4.2$  pg_basebackup -h test-machine02 -p 5432 -U postgres -D /u01/pg_backup -Ft -z -Xs -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/27000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_42075"
48888/48888 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/27000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$

-bash-4.2$ ls -ltr /u01/pg_backup
total 7036
-rw-------. 1 postgres postgres 1027472 Jun 19 16:27 23701.tar.gz
-rw-------. 1 postgres postgres 2790209 Jun 19 16:27 23704.tar.gz
-rw-------. 1 postgres postgres 3106761 Jun 19 16:27 base.tar.gz
-rw-------. 1 postgres postgres  251658 Jun 19 16:27 backup_manifest
-rw-------. 1 postgres postgres   17083 Jun 19 16:27 pg_wal.tar.gz
-bash-4.2$

Piping to gzip: Piping to gzip is only possible if the cluster has no additional tablespaces and WAL streaming is not used.

-bash-4.2$ pg_basebackup -h test-machine02 -p 5432 -U postgres -Ft -X fetch -D - | gzip > /u01/backup/bkp2/db_Filebackup.tar.gz
Password:
pg_basebackup: error: can only write single tablespace to stdout, database has 3
-bash-4.2$
-bash-4.2$

-bash-4.2$ pg_basebackup -p 5432 -U postgres -Ft -Xs  -v -D - | gzip > /u01/pg_backup/db_Filebackup.tar.gz
pg_basebackup: error: cannot stream write-ahead logs in tar mode to stdout
Try "pg_basebackup --help" for more information.
-bash-4.2$


-bash-4.2$
-bash-4.2$ pg_basebackup -p 5432 -U postgres -Ft -X fetch -v -D - | gzip > /u01/pg_backup/db_Filebackup.tar.gz
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1
pg_basebackup: write-ahead log end point: 0/6000100
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$ ls -ltr /u01/pg_backup/db_Filebackup.tar.gz
-rw-r--r--. 1 postgres postgres 7034770 Jun 19 17:02 /u01/pg_backup/db_Filebackup.tar.gz
-bash-4.2$

Reference : pg_basebackup : https://www.postgresql.org/docs/10/app-pgbasebackup.html

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

BACKUP, POSTGRESQL