Data backup and system recovery

Pyrus Datacenter comes with an internal PostgreSQL DBMS for data storage. The PostgreSQL mechanisms supported for performing data backup and recovery from backups are:

• pg_dump/pg_restore;
• wal-g.

Backup and recovery via pg_dump/pg_restore

Creating a backup

To create a backup, execute the following command, replacing login and dbname with your own values:

docker exec -ti pyrus-postgresql /bin/sh -c 'pg_dump -Fc -U login dbname > /tmp/dump.bkp'

After executing the command, copy the backup file from the container to the host machine by running the command:

docker cp pyrus-postgresql:/tmp/dump.bkp dump.bkp

Once copying is complete, you should move this file to a secure file storage.

Restoring from a backup

Restoration should be performed on a clean database container. To do this, delete the old container and its volume, then start a new database container by running the command:

docker-compose up -d postgres

A new container with an empty database will be created and started.

Next, copy the backup file into the running container, which was previously taken using pg_dump:

docker cp dump.bkp pyrus-postgresql:/tmp

Once copying is complete, execute the following command, replacing login and dbname with your own values:

docker exec -ti pyrus-postgresql /bin/sh -c 'pg_restore -v -Fc -U login -d dbname /tmp/dump.bkp'

Wait for the command to finish executing. Once it is executed, the database will be restored.

Backup and recovery via wal-g

Pyrus Datacenter supports automatic backup using wal-g, allowing you to upload copies to AWS S3 or another S3-compatible storage.

Creating a backup

To use this function, you need to specify S3 parameters for the postgres service:

services:
	postgres:
		environment:
            AWS_ACCESS_KEY_ID: <ACCESSKEY>
            AWS_SECRET_ACCESS_KEY: <SECRETKEY>
            WALG_S3_PREFIX: s3://<BUCKETNAME>

When using an S3-compatible storage other than AWS S3, you also need to specify the S3 endpoint:

services:
	postgres:
		environment:
            AWS_ENDPOINT: <S3_URL>

Once the container is launched with the specified settings, archived WAL logs and full database backups are automatically sent to the storage.

Full backup is launched automatically and is performed at 01:00 daily, by default. To change the backup frequency, use the following SQL command in the PostgreSQL container:

--                    ┌───────────── min (0 - 59)
--                    │ ┌────────────── hour (0 - 23)
--                    │ │ ┌─────────────── day of month (1 - 31)
--                    │ │ │ ┌──────────────── month (1 - 12)
--                    │ │ │ │ ┌───────────────── day of week (0 - 6) (0 to 6 are Sunday to
--                    │ │ │ │ │                  Saturday, or use names; 7 is also Sunday)
--                    * * * * *
SELECT cron.schedule('0 1 * * *', $$SELECT backup_manage(RETAIN_FIND_FULL integer, WALG_DELTA_MAX_STEPS integer)$$);

The parameter RETAIN_FIND_FULL specifies the number of backups stored in S3. The default is 7 backups.

The parameter WALG_DELTA_MAX_STEPS controls the number of steps by which the delta copy can be behind the full copy. The default is 3 steps.

You can view all tasks with the SQL command:

select * from cron.job;

Restoring from a backup

To restore from a backup, you need to specify the name of the restore point in the postgres service settings. For example, for the latest point:

services:
	postgres:
		environment:
            RESTORE_NAME: LATEST

You can view the list of backups via the command:

wal-g backup-list

Restoration from a backup will only start if the ${PGDATA} directory is empty. Otherwise, the restoration process will be ignored and the system will start from the files located in that directory.

After restoring from a backup

After restoring the database from a backup and before starting the service, you need to prepare the full-text search and check the system's functionality.

Restoring full-text search

Initialize Elasticsearch search indexes and reindex them:

$ docker-compose run --rm elastic-console -iall
$ docker-compose run --rm elastic-console -rt
$ docker-compose run --rm elastic-console -wgall
$ docker-compose run --rm elastic-console -wcall
$ docker-compose run --rm elastic-console -wpall
$ docker-compose run --rm elastic-console -wnall

Start all containers with the command:

$ docker-compose up -d

Checking functionality

The checklist below is recommended to ensure the system's functionality:

• log in again, receive an email with a code;

• create a task;

• create a form;

• attach a file to the task;

• download a file from the task;

• use the search function.