Protecting PostgreSQL using pg_probackup with IBM Spectrum Protect

There are many different methods that one can use for protecting PostgreSQL. This time we are going to introduce you to the pg_probackup utility.

1. Install the IBM Spectrum Protect Client API

# rpm -ivh gsk*.rpm
# rpm –ivh *api*.rpm

# dpkg -i gsk*.deb
# dpkg –i *api*.deb

2. Install the SPFS software

# rpm -ivh spfs*.rpm
# rpm –ivh spictera*.rpm

# dpkg -i spfs*.deb
# dpkg –i spictera*.deb

3. Configure the Spectrum Protect client

# vi /opt/Tivoli/tsm/client/api/bin64/dsm.sys
SERVERNAME spfs
TCPSERVERADDRESS tsm.backupserver.com
ERRORLOGNAME /var/log/dsmerror_spfs.log

4. Register the node on the backup server

TSM> REGISTER NODE SPFS secret DOMAIN=SPFS

5. Configure the SPFS file system

5.1 Create option file

# echo SERVERNAME spfs > /etc/spfs/dsm.opt

5.2 set initial password

# setpassword /etc/spfs/TSM.PWD
secret

5.3 Create spfs configuration

# vi /etc/spfs/spfs.opt
MOUNTPOINT /archive
NODENAME spfs
NODEPWDFILE /etc/spfs/TSM.PWD
OPTIONFILE /etc/spfs/dsm.opt
DATATYPE archive

6. mounting the SPFS file system

# mkdir /archive
# mount.spfs /archive

You are now ready to start using Spectrum Protect as a file system, storing and retrieving backup data of PostgreSQL

Performing backup using pg_probackup

https://postgrespro.github.io/pg_probackup/

Initializing backup catalog

$ pg_probackup init -B /archive/pgbackup

Adding a new backup instance

$ pg_probackup add-instance -B /archive/pgbackup -D /var/lib/pgsql/12/data --instance test1

Example of performing a full backup of the instance

$ pg_probackup backup -B /archive/pgbackup --instance test1 -b FULL -j 8 --no-validate

Sample output:

INFO: Backup start, pg_probackup version: 2.4.10, instance: test1, backup ID: QRGSKA, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘ — data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run backup or checkdb as superuser.
INFO: Wait for WAL segment /archive/pgbackup/wal/test1/000000020000000000000038 to be archived
INFO: PGDATA size: 24MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 3m:7s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Wait for LSN 0/39000128 in archived WAL segment /archive/pgbackup/wal/test1/000000020000000000000039
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 2m:33s
INFO: Backup QRGSKA resident size: 24MB
INFO: Backup QRGSKA completed

Restoring from a backup

$  pg_probackup restore -B /archive/pgbackup --instance test

Sample output

INFO: Validating backup QRGPCX
INFO: Backup QRGPCX data files are valid
INFO: Backup QRGPCX WAL segments are valid
INFO: Backup QRGPCX is valid.
INFO: Restoring the database from backup at 2021–04–12 18:46:09+01
INFO: Start restoring backup files. PGDATA size: 24MB
INFO: Backup files are restored. Transfered bytes: 24MB, time elapsed: 6m:13s
INFO: Restore incremental ratio (less is better): 100% (24MB/24MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 0
INFO: Restore of backup QRGPCX completed.
$

Follow us on Twitter: http://twitter.com/spictera
Follow us on LinkedIn: http://LinkedIn.com/company/spictera