As a native backup utility, pg_basebackup was introduced in Postgres version 9.1, which is used to take a base backup of the database cluster. This utility helps to take a physical backup of the up and running database along with the WAL files that are needed for point-in-time recovery.
Primarily, pg_basebackup helps to take a full backup of the database or to setup replica/ standby databases using the primary database backup.
In the recent release of Fujitsu Enterprise Postgres version 15, pg_basebackup has been enhanced with important functionalities.
New option --target
One of the important enhancements is to allow backup of the database with the server as the backup target. Up to Fujitsu Enterprise Postgres 14, the backup target was always the machine from which pg_basebackup was initiated, and this could not be altered.
With version 15, we can explicitly specify the option to use the server and backup path using the syntax below.
pg_basebackup [OPTION]...
-t, --target=TARGET[:DETAIL]
backup target (if other than client)
The default target is client, so when the option --target is omitted, the backup files are created on the machine from which pg_basebackup was initiated.
If the --target option is set to server:/backups/adhoc/, the backup will be stored on the database server at the specified path.
To use the --target option, there are two pre-requisites that need to be fulfilled.
- The WAL method (--wal-method or -X) option must be specified as either 'none' or 'fetch'. This is because it does not accept 'stream' which is the default, as WAL streaming is implemented by pg_basebackup. If this option is omitted, the command fails, as shown below.
[postgres@FEP-Client: pg_basebackup -h 40.126.246.181 -p 65010 -U bkpuser -t server:/database/bkup
pg_basebackup: error: WAL cannot be streamed when a backup target is specified
pg_basebackup: hint: Try "pg_basebackup --help" for more information.
[postgres@FEP-Client: ~] $
- It is important to grant the role pg_write_server_files to the replication user used to execute the pg_basebackup or run as superuser.
[postgres@FEP-Client: --]$ pg_basebackup -h 40.126.246.181 -p 65010 -U bkpuser -t server:/database/bkup -X none
Password:
pg_basebackup: error: could not initiate base backup: ERROR: must be superuser or a role with privileges of
the pg write server files role to create backup stored on server
[postgres@FEP-Client: -]$
Grant the role pg_write_server_files to the user.
postgres=# GRANT pg write server files TO bkpuser;
GRANT ROLE
postgres=#
With the --wal-method and role granted, the pg_basebackup with --target can be successfully executed using the below command.
[postgres@FEP-Client: -]$ pg_basebackup -h 40.126.246.181 -p 65010 -U bkpuser -t server:/database/bkup -X fetch -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: write-ahead log end point: 0/21000100
pg_basebackup: base backup completed
[postgres@FEP-Client: -]$
We can see that the backup files are generated on the database server, as below.
[postgres@FEP15-lab: /home/postgres]$ is -1 /database/bkup/
total 48912
-rw-------. 1 postgres postgres 183293 Jan 23 12:08 backup_manifest
-rw-------. 1 postgres postgres 49898496 Jan 23 12:08 base.tar
[postgres@FEP15-lab: /home/postgres] $
Backup compression option --compress
The new compression option introduced in version 15 helps define when and where compression should happen. This can be achieved using the below syntax.
-Z, --compress=[{client|server}-]METHOD[:DETAIL]
compress on client or server as specified
-Z, --compress=none do not compress tar output
When --compress is set to 'server', the compression is carried out by the database server before the backup file is copied to the client. This is a better choice when network bandwidth is limited, as the compressed backup will be copied over.
[postgres@FEP-Client: pg_basebackup -h 40.126.246.181 -p 65010 -U bkpuser -F t -D /u01/fep15 --compress=server-gzip:9 -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2F000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_8469"
pg_basebackup: write-ahead log end point: 0/2F000100
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
[postgres@FEP-Client: ~]$
[postgres@FEP-Client: ~]$ ls -l /u01/fep15/
total 20748
-rw-------. 1 postgres postgres 183115 Jan 23 04:45 backup_manifest
-rw-------. 1 postgres postgres 4279319 Jan 23 04:45 base.tar.gz
-rw-------. 1 postgres postgres 16778752 Jan 23 04:45 pg_wal.tar
[postgres@FEP-Client: ~]$
Alternatively, when compress is set to client, then compression is carried out on the client side, from where pg_basebackup is executed. In this case, transferring the uncompressed backup file to the client demands relatively higher bandwidth. Also, compression, which is a CPU-intensive process, is offloaded to the client machine, thus sparing database server resources.
[postgres@FEP-Client: -]$ pg_basebackup -h 40.126.246.181 -p65010 -U bkpuser -Ft -D /u01/fep15 -X fetch -Z client-1z4:7 -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3C000028 on timeline 1
pg_basebackup: write-ahead log end point: 0/3C000138
pg_basebackup: syncing data to disk ...
pg basebackup: renaming backup manifest.tmp to backup manifest
pg:basebackup: base backup completed
[postgres@FEP-Client: ~]$
[postgres@FEP-Client: ~]$ ls -1 /u01/fep15/
total 5268
-rw-------. 1 postgres postgres 183467 Jan 23 06:17 backup_manifest
-rw-------. 1 postgres postgres 5209066 Jan 23 06:17 base.tar.1z4
[postgres@FEP-Client: ~]$
The compression on the client side is the default when 'server' or 'client' is not specified for the --compress option. An exception to the default behavior is when --target is used, as backup happens on the server only the server compression is allowed.
To summarize, when compression is set to server, it increases CPU utilization on the database server but reduces the network bandwidth required to transfer the backup. Whereas when compression is set to client, CPU-intensive compression is executed on the client system but demands more bandwidth for transfer. DBAs can prudently choose where the compression should happen based on the above factors.
Before we go
These enhancements to pg_basebackup help to back up databases on the server, and the --compress option allows us to direct the CPU-intensive backup compression to be offloaded to the client or to the server when network bandwidth is a constraint.
Fujitsu Enterprise Postgres
provides to harness your data.
