ELMA365 On-Premises > Other / Backup and recover databases

Backup and recover databases

ELMA365 backup must be done for:

  • PostgreSQL. The database stores main system data: users, app items, settings of apps and workspaces, processes, pages, widgtes, tasks, events and other configuration settings;
  • MongoDB. The database stores unstructured system settings, data from chats and activity stream;
  • S3 Object Storage. It stores the uploaded files as well as files created in ELMA365 of any type and size, such as documents, photos, audio and video files.

To access the database inside a cluster, forward the port of corresponding services:

kubectl port-forward postgres-0 5432:5432 --address 0.0.0.0
kubectl port-forward mongo-0 27017:27017 --address 0.0.0.0
kubectl port-forward minio-<name-pod> 9000:9000 --address 0.0.0.0

To obtain information about connection strings for databases, run the following command. For the command to be executed, you need to install the jq package.

To install ELMA365 with charts to an existing Kubernetes cluster:

kubectl get secrets/elma365-db-connections -o json | jq '.data | map_values(@base64d)'

To install ELMA365 to MicroK8s:

microk8s kubectl get secrets/elma365-db-connections -o json | jq '.data | map_values(@base64d)'

To install ELMA365 to Kubernetes-in-Docker:

docker exec elma365 kubectl get secrets/elma365-db-connections -o json | jq '.data | map_values(@base64d)'

Backup

PostgreSQL backup

There are several ways to create backup copies of PostgreSQL databases.

Option 1: Physical backup copy

The pg_basebackup utility helps make a backup copy of database files. It can be used for point-in-time recovery (PITR) which guarantees that data is copied from tables continuously. The copies are created for the entire cluster, individual databases cannot be backed up.

начало внимание

The backup copy is created over a regular PostgreSQL connection that uses the replication protocol. The connection must be made by a user with REPLICATION permissions or by a superuser.

конец внимание

Use the following command to create a complete backup copy:

pg_basebackup -h <postgresql-server-address> -p 5432 -U postgres -D /backup/<backup-postgresql-folder-name> -Ft -z -Xs -P

To run pg_basebackup from a remote server (for example, from 192.168.1.10), set it up to accept the connection. For that, add the following line to the pg_hba.conf file:

host replication all 192.168.1.10/32 md5

After making changes in the pg_hba.conf file, restart PostgreSQL to apply them.

For continious archiving of PostgreSQL, enable WAL. To do that, configure the following parameters in /etc/postgresql/10/main/postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backup/<backup-postgresql-folder-name>/postgresql_archives/%f && cp %p /backup/<backup-postgresql-folder-name>/postgresql_archives/%f'
archive_timeout = 300

In the same file, configure the value of the data_directory parameter if it is not defined. If it is not configured, the backup module will not be able to determine the locations of database files, for example, for PostgreSQL 10:

data_directory = '/var/lib/postgresql/10/main' 

After making the changes, run the following command to restart PostgreSQL:

service postgresql restart

The value of the archive_command parameter must contain the catalog in the PostgreSQL server to which WAL records will be archived.

Option 2: Logical copy

The pg_dump utilty dumps a single database, and does not include information about roles and tablespaces. When pg_dump is running, the only operations locked are those requiring exclusive locks. This means that any changes made to the database during the dump will not be added to the output archive file.

To create a backup copy, run the following command:

pg_dump <postgresqlURL> -C -c --if-exists -F directory -f /backup/<backup-postgresql-folder-name>

MongoDB backup

The mongodump utility is a basic tool for logical backups. It is included in MongoDB and creates a binary export of a database's contents or a collection.

To create a backup copy, run the following command:

mongodump --uri <mongodbURL> --gzip --quiet --out /backup/<backup-mongodb-folder-name>

Enable --oplog for backing up replica sets. The output data will contain the oplog.bson file with the oplog entries with data that occur during the mongodump operation

The oplog.bson file is created as part of the mongodump output and provides an effective point-in-time snapshot of the state of the mongodb instance. This option only works for nodes that maintain an oplog, this includes all members of a replica set.

S3 backup

Files can be copied with any utility that is connected to S3, for example, mc.

Add host to mc configurations:

mc alias set <alias> <s3-endpoint> <access-key> <secret-key> --api <api-signature>

Sync the bucket contents with the file system catalog.

mc mirror <alias>/<bucket> /backup/<backup-s3-folder-name>

Recovery

Before recovering databases, you have to stop all the application services completely, for example, with the following commands

kubectl scale deploy --replicas=0 --all -l 'app notin (minio)' [--namespace <elma365-namespace>]

kubectl [--namespace <elma365-namespace>] patch daemonset billing -p '{"spec": {"template": {"spec": {"nodeSelector": {"non-existing": "true"}}}}}'

Recreate PostgreSQL and MongoDB databases. To learn more see Install PostgreSQL and Install MongoDB.

Then recover the databases.

PostgreSQL recovery

Recover PostgreSQL physical copy

To recover a backup copy created via pg_basebackup, or to run it in a new location, all you have to do is stop the PostgreSQL server, unpack, and replace the contents of its database directory with the backup copy and the WAL archive. For example, for PostgreSQL version 10 :

systemctl stop postgresql

mv /var/lib/postgresql/10/main /var/lib/postgresql/10/main.old

mkdir /var/lib/postgresql/10/main

tar xvf /backup/<backup-postgresql-folder-name>/base.tar.gz -C  /var/lib/postgresql/10/main

tar xvf /backup/<backup-postgresql-folder-name>/pg_wal.tar.gz -C /var/lib/postgresql/10/main/pg_wal

chown -R postgres:postgres /var/lib/postgresql 

chmod -R go-rwx /var/lib/postgresql/

systemctl start postgresql

To restore the PostgreSQL cluster to the state it was in at a certain point in time, stop the PostgreSQL server, restore the latest complete backup copy, mount the WAL archive files to the file system and create the /var/lib/postgresql/10/main/recovery.conf file with the following contents:

restore_command = 'cp /backup/<backup-postgresql-folder-name>/postgresql_archives/%f%p'
recovery_target_time = '2022-02-08 11:20:00 UTC'
recovery_target_inclusive = false

Restart the PostgreSQL server. When restarting, it will call the restore_command script multiple times to get the WAL files for restoring the database at a certain stopping point.

Recover PostgreSQL logical copy

The pg_restore utility restores the backup created with pg_dump.

pg_restore -d <postgresqlURL> -j 1 -F directory /backup/<backup-postgresql-folder-name>

Recover MongoDB

To recover the database, use the mongorestore utility. Recovery is done as follows:

mongorestore --uri <mongodbURL> --dir /backup/<backup-mongodb-folder-name> --drop --gzip --preserveUUID --excludeCollection=head.settings.view

To reciver the database up to a certain point in time, use oplogs. Copy only the the oplog.bson file that you obtained earlier to the recovery catalog using the following command:

mongodump --oplog

Run mongorestore with the --oplogReplay parameter (make sure that the folder only contains oplog.bson)

mongorestore --uri <mongodbURL> --oplogReplay --dir /backup/<backup-mongodb-olplog-folder-name>

Recover S3

Database can be recovered with any utility that is connected to S3, for example, mc.

Add host to the mc configuration:

mc alias set <alias> <s3-endpoint> <access-key> <secret-key> --api <api-signature>

Sync the bucket contents with the file system catalog.

mc mirror /backup/<backup-s3-folder-name> <alias>/<bucket>

After the backup copy has been restored, restart all the application services using the following commands:

kubectl scale deploy --replicas=1 --all [--namespace <elma365-namespace>]

kubectl [--namespace <elma365-namespace>] patch daemonset billing --type json -p='[{"op": "remove", "path": "/spec/template/spec/nodeSelector/non-existing"}]'

 

Found a typo? Highlight the text, press ctrl + enter and notify us