Backup and restore PostgreSQL

This document describes how to create a backup, and restore using a backup, when using PostgreSQL as TSB's datastore. It is recommended to create a backup of your TSB datastore every 24 hours, so in case it gets corrupted, you can easily recover all the information.

Before you get started make sure:

✓ You have installed and configured TSB.
✓ You have installed and configured kubectl to access the management cluster.
✓ You have full access to the PostgreSQL system where TSB is storing the data.

Create a backup of TSB configuration

TSB requires PostgreSQL 11.1 or up. We will be using this 11.1 version for the example. You can create a backup of the database by running:

pg_dump tsb > tsb_backup.sql

Restore a backup

To restore a backup, it is recommended to scale down the tsb and iam deployments to 0, as these deployments will be doing queries to the database continuously:

kubectl scale deployment tsb iam -n tsb --replicas 0

note

Scaling tsb deployment to 0 only interrupts the ability to change configuration in a running TSB installation while restoration is in progress, but does not interfere with the data plane / running services.

At this point, you have to login into your PostgreSQL system, and execute the following actions with a privileged user. Usually, the database will have few active connections. You can check them by running this query:

SELECT *
FROM pg_stat_activity
WHERE datname = 'tsb';

The next step is to terminate these connections with this query:

SELECT  pg_terminate_backend (pid)
FROM    pg_stat_activity
WHERE   pg_stat_activity.datname = 'tsb';

And immediately remove tsb database:

DROP DATABASE tsb;

At this point, all the TSB configurations will be removed. Now you will need to create tsb database again:

CREATE DATABASE tsb;

And grant all permissions for this database to the user called tsb:

GRANT ALL PRIVILEGES ON DATABASE tsb TO tsb;

Once this is done, login with the user tsb and restore the dump created previously:

psql tsb < tsb_backup.sql

Now all data from the backup is restored and you can scale up tsb and iam deployments to 1:

kubectl scale deployment tsb iam -n tsb --replicas 1