Oct 24, 2021
Protecting PostgreSQL using pg_basebackup with IBM Spectrum Protect
This time we are going to explain how to protect PostgreSQL databases using a tool called pg_basebackup, that comes together with the PostgreSQL packages.
- 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 /backup
NODENAME spfs
NODEPWDFILE /etc/spfs/TSM.PWD
OPTIONFILE /etc/spfs/dsm.opt
MOUNTPOINT /archive
NODENAME spfs
NODEPWDFILE /etc/spfs/TSM.PWD
OPTIONFILE /etc/spfs/dsm.opt
DATATYPE archive
6. mounting the SPFS file system
# mkdir /backup
# mkdir /archive
# mount.spfs /backup
# mount.spfs /archive
You are now ready to start using Spectrum Protect as a file system, storing and retrieving backup data of PostgreSQL
7. setting up WAL — continuous archiving
https://www.postgresql.org/docs/9.4/continuous-archiving.html
set wal_level to archive or higher, archive_mode to on, and set up an archive_command that performs archiving.
archive_command = cp %p /archive/%f’
Restart of PostgreSQL is required in order to enable the WAL archiving.
Performing snapshot consistent backups
$ pg_basebackup -h mydbserver -D /backup/latest_backup \
— checkpoint=fast -Xf \
— format=tar
Restoring from backup
Stop the PostgreSQL server instance
$ pg_ctl -D $PGDATA stop -mf
Restore data from backup
$ tar xzf /backup/latest_backup/base.tar.gz -C /pgdata
Create a new recovery.conf file with the required parameters for PITR.
# recovery.conf file always exists in the Data Directory of Slave
recovery_target_time = ‘2021–10–23 10:27:34 EDT’
restore_command = ‘cp /archive/%p %f’
recovery_target_action = ‘pause’
recovery_target_inclusive = ‘false’
Start up the PostgreSQL server instance, so that an automatic recovery can be initiated
$ pg_ctl -D $PGDATA start
