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.
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$
No comments:
Post a Comment