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



ERRORLOGNAME /var/log/dsmerror_spfs.log

4. Register the node on the backup server


 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


5.3 Create spfs configuration

# vi /etc/spfs/spfs.opt




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

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



All Posts

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OKSubscriptions powered by Strikingly