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