Backups

Overview

This page explains the local and remote backup methods for Scrutinizer Postgres Databases.

Important

It is critical that you understand the fundamentals of Postgres databases. If you have any questions or concerns, please contact support for assistance.

Local Backup

Local backup is created on the same server that is hosting the Scrutinizer database.

  1. Add the following line to the /var/db/big/pgsql/data/pg_hba.conf file to allow local replication connections:
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     postgres                                peer
  1. Run the command below to reload the database configuration:
psql -Uroot plixer -c "SELECT pg_reload_conf()"
  1. Use the pg_basebackup utility to back up the PostgreSQL database:
sudo su postgres -c "cd ~; pg_basebackup -D backup -Ft -z -P"

Hint

When taking a database backup, “waiting for checkpoint” is to be expected. However, you can force a checkpoint by running the following command:

psql -Uroot plixer -c "CHECKPOINT"
  1. Your backup should consist of three tarballs in the /var/lib/pgsql/backup directory. All three are critical in restoration.
ls -l /var/lib/pgsql/backup
-rw-r--r-- 1 postgres postgres     2025 Sep 24 18:20 16385.tar.gz
-rw-r--r-- 1 postgres postgres 40170653 Sep 24 18:20 base.tar.gz
-rw------- 1 postgres postgres  1453827 Sep 24 18:20 pg_wal.tar.gz

Important

It is recommended to transfer backup files to an FTP server so they will not be lost in the event of a failure.

Remote Backup

This method assumes a second “destination” host for the backup that has at least enough disk space to contain the backup and that the same version of Scrutinizer and Postgres have already been installed.

  1. On the source host, run this command to create a ROLE for remote replication:
psql -Uroot plixer -c "CREATE ROLE remote_rep WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'think_about_it'"
  1. To allow remote connections for the ROLE on the source host, add this line to the pg_hba.conf. Replace the $DESTINATION_IP with the IP of the destination server and reload the configuration.
sudo tee -a /var/db/big/pgsql/data/pg_hba.conf << EOF
host replication remote_rep $DESTINATION_IP/32 md5
EOF
PGPASSWORD=root psql -Uroot plixer -c "SELECT pg_reload_conf()"
  1. On the destination host, populate the $SOURCE_IP with the source server. Use the pg_basebackup utility to back up the PostgreSQL database. You will be prompted for the password used in Step 1.
sudo su postgres -c "cd ~; pg_basebackup -h $SOURCE_IP -U remote_rep -D backup -Ft -z -P"
  1. Your backup should consist of three tarballs in the /var/lib/pgsql/backup directory. All three are critical in restoration.
sudo ls -l /var/lib/pgsql/backup
-rw-r--r--. 1 postgres postgres        501 Sep 24 13:23 16385.tar.gz
-rw-r--r--. 1 postgres postgres 4146859412 Sep 24 13:32 base.tar.gz
-rw-------. 1 postgres postgres   12316287 Sep 24 13:32 pg_wal.tar.gz

Restoring from a Backup

The instructions below assume a fresh Scrutinizer instance was deployed on the same Scrutinizer and postgres version as the backup was taken on. Upload the backup files to the /var/lib/pgsql/backup/ directory.

Important

You MUST be restoring to the same version of PostgreSQL that was used during the backup.

  1. Stop all Scrutinizer services.
sudo service scrutinizer stop
  1. Delete the current database files.
sudo rm -Rf /var/db/big/pgsql/data/* /var/db/fast/pgsql_tmp/*
  1. Restore the base.tar.gz backup files.
sudo mv /var/lib/pgsql/backup/base.tar.gz /var/db/big/pgsql/data/
sudo tar -zxvf /var/db/big/pgsql/data/base.tar.gz -C /var/db/big/pgsql/data
  1. Restore The WAL.
sudo mv /var/lib/pgsql/backup/pg_wal.tar.gz /var/db/big/pgsql/data/pg_wal/
sudo tar -zxvf /var/db/big/pgsql/data/pg_wal/pg_wal.tar.gz -C /var/db/big/pgsql/data/pg_wal
  1. Restore The ‘fast’ TABLESPACE. Keep in mind that the number for the fast TABLESPACE will vary from system to system. In my example, it’s 16385.
sudo mv /var/lib/pgsql/backup/16385.tar.gz /var/db/fast/pgsql_tmp/
sudo tar -zxvf /var/db/fast/pgsql_tmp/16385.tar.gz -C /var/db/fast/pgsql_tmp
  1. Start The PostgreSQL service. If it doesn’t start, please contact Plixer support before continuing.
sudo service plixer_db start
  1. It’s common for database passwords to be different across a backup and restore. Run the following commands and reset the database passwords to ensure they are in sync:
scrut_util.exe
set password scrutdb
services all restart
  1. Register the collector with the new IP address. By moving the database, it is possible for the IP to change on the destination host. We need to ensure the collector service is aware of the new IP.
scrut_util.exe
set selfregister reset
  1. Check SSL Configuration. If you are not using SSL for the web interface on the source or destination servers, you can skip this step.
scrut_util.exe
set ssl off
set ssl on
  1. Apply a new license key. When changing hosts, Scrutinizer needs a new license key. In the web interface of the restoration host, navigate to Admin -> Settings -> Licensing, copy the Machine ID and contact Plixer support for a new license. Once you receive the new license key, in the web interface of the restoration host, navigate to Admin -> Settings -> Licensing and apply the new license.
  2. Before deleting the backup, it is recommended to verify the restored system is collecting new flow data and that you can report on the restored historical data.If you changed the IP, you may need to point your exporters to the IP address of the new collector.
  3. Once you’re confident the database has been restored, remove the backup tarballs from the server.
sudo rm /var/db/fast/pgsql_tmp/16385.tar.gz var/db/big/pgsql/data/pg_wal/pg_wal.tar.gz /var/db/big/pgsql/data/base.tar.gz