Physical PostgreSQL Backup using pg_basebackup

Physical PostgreSQL Backup using pg_basebackup

 

1.  pg_basebackup is used to take base backups of a running PostgreSQL database cluster.
   
   
2.  It Can be used both for point-in-time recovery and as the starting point for log shipping or streaming replication standby servers.
   
   
3.  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.
   
   
4.  Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.
   
   
5.  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.

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

1 2 3 4 5 6 7 8 9 10 11 12 13

[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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

-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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

-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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

-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“

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

-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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

-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$