I tried to make an automatic backup with pleasanter + PostgreSQL + SSL + docker

Overview

-Previous article is a continuation. ――If you think about using the system in earnest, you need to think about backup properly. ――This time, we have systematized it so that it can be backed up automatically. --The backup types that can be supported are "Full backup" and "PITR (point-in-time recovery)". --By entering the AWS S3 settings, you can also synchronize the backup data with S3.

Container configuration

A container has been added for backup work. The added container is shown in the table below. postgres-db and cron-backup share a DB area and PITR is ready for use.

Backup script

The script used for the automatic backup mechanism is as follows. These scripts are deployed in the pcron-backup container.

Backup settings

Directory structure

The directory structure seen from the pcron-backup container is shown in the table below. Paths that are shared with 〇 are shared with the postgres-db container.

The backup result data is designed to be collected in "/ var / db_backup". Therefore, if you connect this directory outside the container and make a backup, you will be able to make a backup from outside the required container.

Acquisition frequency

Back operation management is controlled by cron. The corresponding crom settings are in "cron-backup \ config \ crontab". In the initial state, it is specified as follows. Please switch the target time if necessary.

Start backup

These backup scripts will start automatically when you start Docker Compose. No special action is required to get started.

On the contrary, if you do not want to make a backup, do not start the corresponding container

Backup setting optional function S3 synchronization

Overview

With only the backup function so far, there is a risk of data loss when the container flies due to something. The backup should be kept somewhere else. In modern times, there is a convenient one called S3, so it is made to automatically synchronize there.

activation

It is enabled when the AWS CLI settings exist. The pass that is the target of the activation judgment is as follows.

「/root/.aws/config」

It's easier to put this config file outside the container and mount it than to copy it as a config inside the container. (The part marked with ★ below.)

docker-compose.yml

  cron-backup:
    build:
      context: cron-backup/.
    volumes:
      - *APP_DB_data
      - *APP_DB_arclog
      #The result of automatic backup is "/var/db_It is stored in "backup".
      #If you access from outside the container, place this path where you can see it from the outside.
      - db-backup:/var/db_backup
      #When backing up automatically with S3
      #Set aws cli in the following path.
      #If it does not exist, it will not be backed up
      - ./cron-backup/config/aws-cli:/root/.aws/ ←　★

Configuration

If the host is in a Windows environment, the ** configuration file may be acquired by CRLF ** depending on the GIT settings. If you bring the configuration file into the container in that state, ** the line feed code will not match and it will not work properly **, so be careful when handling the line feed code in the configuration file.

First, you need to configure the AWS CLI. This is a general setting, so get the information you need from your account.

cron-backup\config\aws-cli\credentials

[default]
aws_access_key_id =access key
aws_secret_access_key =Secret key

Next, set the backup target. This is a shell-style setting.

cron-backup\config\aws-cli\S3Config.sh

#!/bin/bash

# ---S3 sync settings

#S3 bucket name to sync
export S3_TARGET_BUCKET_NAME={Bucket name}

#S3 directory name to be synchronized
export S3_TARGET_DIRECTORY_NAME={Directory name}

Execution timing

Synchronization to S3 is the timing when the backup process is executed. If the settings are correct, no special settings are required to get started.

Safe mode

Syncing to S3 is done with the delete option of the aws cli "rsync" command. When this command is used, ** the synchronization destination directory will be the same as the synchronization source directory **. Here, the same means ** "Files that are not in the synchronization source but in the same destination are deleted" **. If you make a mistake in the settings immediately after creating a new environment and this backup works, the necessary data will be lost and it will be a serious problem.

Therefore, the backup process of S3 has a safe mode. In safe mode, if data different from the backup system that the existing container has is confirmed in the target directory of the target bucket of S3, create a directory called "directory name-safe" without overwriting and back up to that side. It will be a process to do. To check the identity of the backup system, refer to the file "BackupFiles.sha256.7z" generated at the time of backup.

Basically, users don't have to worry too much, but ** when migrating the environment, etc. ** Be careful not to damage the existing backup. Also, after performing the first backup, check if a directory such as "directory name -safe" has been created.

restore

Regarding restore, The method of returning depends on the method of acquiring backup data.

When acquired by all backups

Hereafter, it is assumed that you have worked in the cron-backup container. The backup file is in 7z format and is encrypted. Please restore as appropriate. (See the shell file "cron-backup \ shell \ pg_dumpall.sh" for the encryption key) The following commands assume that the restored file is named "backup". Restoration from all backups is completed with one command below.

 psql -h postgres-db -p 5432  -U postgres -f backup

PITR

For PITR, it's a little confusing. In the case of PITR, it cannot be restored well if postgres is running, so Start only the cron-backup container.

docker-compose up pleasanter-cron-backup

Step 0. Data copy

Perform in a pleasanter-cron-backup container. Prepare the data to be used for restoration. Copy the data used for restoration to "/ var / db_backup / PITR /".

Step 1. Stop Coulomb

Perform in a pleasanter-cron-backup container. If you start Coulomb normally, Backup Coulomb will start backing up your data. Therefore, stop the backup coulomb.

crontab -l > my_cron_backup.txt
crontab -r

Step 2. Check backed up data

Perform in a pleasanter-cron-backup container. The following command displays the list of backups that have been acquired.

/usr/lib/postgresql/12/bin/pg_rman show  -B /var/db_backup/PITR/

When you execute the command, a list of timings that can be restored as a backup is displayed as shown below.

root@885777f50d56:~/out/cron-backup/shell# /usr/lib/postgresql/12/bin/pg_rman show  -B /var/db_backup/PITR/
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2020-09-22 21:44:35  2020-09-22 21:44:38  INCR    33kB     3  OK
2020-09-22 21:43:32  2020-09-22 21:43:35  INCR    33kB     3  OK
2020-09-22 21:40:54  2020-09-22 21:40:58  INCR    33kB     3  OK
2020-09-22 21:39:32  2020-09-22 21:39:35  INCR    33kB     3  OK
2020-09-22 21:38:47  2020-09-22 21:38:51  INCR    33kB     3  OK
2020-09-22 21:32:47  2020-09-22 21:36:18  FULL    61MB     3  OK

Step 3. Restore

Perform in a pleasanter-cron-backup container. In the case of PITR, there are two patterns for returning.

Return to the final back timing

Use the following command.

/usr/lib/postgresql/12/bin/pg_rman restore  -B /var/db_backup/PITR/ -D /var/lib/postgresql/data/

Return to arbitrary timing

When returning to an arbitrary timing, it is possible to specify the time to return. The command is as follows, and specify the target time as "recovery-target-time".

/usr/lib/postgresql/12/bin/pg_rman restore  --recovery-target-time '2020-09-06 14:29:00'  -B /var/db_backup/PITR/ -D /var/lib/postgresql/data/

Step 6. Return Coulomb

Perform in a pleasanter-cron-backup container. Restore the Coulomb settings.

crontab my_cron_backup.txt
crontab -l

DB integrity check

This process is required in common regardless of whether you return from "All backup" or "PITR". In the backup process, data is extracted during system operation. For this reason, the session information that exists on the DB remains in a half-finished state.

Use the following command to clean unnecessary data on the DB once. (Run on docker host.)

docker ps | grep leasanter-web | cut -d' ' -f 1  | xargs -I {} docker exec {} cmdnetcore/codedefiner.sh

Post-processing

Post-processing is required after returning the DB.

Prompt FUll backup

Make a full backup as soon as you confirm that it works. You can get a full backup by directly hitting the following command. However, if S3 is set, synchronization to S3 will also run, so please be careful.

「/var/backup_sh/pg_dumpall.sh」

Reacquisition of base backup in PITR

After reverting from a backup, PITR may have changed the base backup from what it had previously taken. (In PITR, the difference is superimposed on the base backup, but if the base backup and the latest DB status are lost due to restoration, the subsequent backup process cannot be executed correctly.) Therefore, it is necessary to reacquire the base backup.

1. There is PITR backup data in the "/ var / db_backup / PITR /" path. Move this.
2. Use the "/var/backup_sh/pg_rman.sh" command to perform the backup again with the backup data empty. (Be careful because it synchronizes with S3)

reference

OSC Hokkaido 2014_JPUG Material Back up Mastodon's PostgreSQL using pg_rman pg_rman Try using pg_rman Easy backup and recovery of PostgreSQL by pg_rman