Protecting PostgreSQL using pgbackrest 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 pgbackrest 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 /backup
NODENAME spfs
NODEPWDFILE /etc/spfs/TSM.PWD
OPTIONFILE /etc/spfs/dsm.opt

6. mounting the SPFS file system

# mkdir /backup
# mount.spfs /backup

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

Performing backup using pgbackrest

https://pgbackrest.org/

The configuration of PgBackRest is very easy, it consists of a configuration pgbackrest.conf file that must be edited. In my case the file is located in /etc. As specified, we will use a very basic configuration file.
Below the contents of my configuration file
A complete list can be found here

[root@pgserver etc]# cat pgbackrest.conf 
[global] 
repo-path=/var/lib/pgbackrest 
[clustpgserver] 
db-path=/var/lib/pgsql/10/data 
retention-full=2 
[root@pgserver etc]#

In the file above,
• repo-path is where backup will be stored,
• clusterpgserver is the name of my cluster stanza (free to take what you want as name). A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc.
• db-path is the path of my database files
• retention-full : configure retention to 2 full backups
A complete list can be found here

[root@pgserver etc]# cat pgbackrest.conf 
[global] 
repo-path=/var/lib/pgbackrest 
[demo] 
db-path=/var/lib/pgsql/10/data 
retention-full=2 
[root@pgserver etc]#

Create the stanza

$ pgbackrest — stanza=demo — log-level-console=info stanza-create

Check configuration

$ pgbackrest --stanza=demo --log-level-console=info check

Example of backing up the instance

$ pgbackrest — stanza=demo — log-level-console=info backup

Restoring from a backup

$  pgbackrest --stanza=demo--log-level-console=info restore

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