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


-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.

Container name Overview
cron-backup Backup coulomb container
postgres-db PostgreSQL DB
pleasanter-web Pleasanter web system

Backup script

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

Script name Overview
/var/backup_sh/pg_rman.sh Run PITR
/var/backup_sh/pg_dumpall.sh Run Full Backup
/var/backup_sh/pg_dumpall.sh Perform S3 sync

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.

Path name Overview Sharing between containers
/var/db_backup/PITR/ Backup directory for point-in-time recovery
/var/db_backup/dumpall Directory for all data backup
/var/backup_sh Backup Shell storage directory
/var/ Backup Shell storage directory
/var/lib/postgresql/data/ postgresql data area
/var/lib/postgresql/arclog postgresql WAL area

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.

Type frequency
All backups Once daily at 3 am
PITR(Point-in-time recovery) Every 30 minutes

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


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.


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


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.)


      context: cron-backup/.
      - *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/ ← ★


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.


aws_access_key_id =access key
aws_secret_access_key =Secret key

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



# ---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.


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


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 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.


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)


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

Recommended Posts

I tried to make an automatic backup with pleasanter + PostgreSQL + SSL + docker
I tried to make an introduction to PHP + MySQL with Docker
I tried to make an Android application with MVC now (Java)
I tried to verify AdoptOpenJDK 11 (11.0.2) with Docker image
I tried to make Basic authentication with Java
I tried to make a machine learning application with Dash (+ Docker) part3 ~ Practice ~
Rails6 I tried to introduce Docker to an existing application
I tried to build an environment using Docker (beginner)
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to make an application in 3 months from inexperienced
I tried to make Venn diagram an easy-to-understand GIF animation
I tried to create a padrino development environment with Docker
I tried to interact with Java
I tried to build an API server with Go (Echo) x MySQL x Docker x Clean Architecture
I tried to build the environment of PlantUML Server with Docker
I tried to build an http2 development environment with Eclipse + Tomcat
I tried to make it an arbitrary URL using routing nesting
I tried to make a group function (bulletin board) with Rails
I tried to make a machine learning application with Dash (+ Docker) part2 ~ Basic way of writing Dash ~
I want to make an ios.android app
I tried to get started with WebAssembly
I tried using Scalar DL with Docker
What is Docker? I tried to summarize
I tried to implement ModanShogi with Kinx
[Amateur remarks] I tried to automate SSL possible (self-signed certificate) with Docker-Compose
[iOS] I tried to make a processing application like Instagram with Swift
I tried to make a Web API that connects to DB with Quarkus
I tried to build a Firebase application development environment with Docker in 2020
01. I tried to build an environment with SpringBoot + IntelliJ + MySQL (MyBatis) (Windows10)
I tried to make a machine learning application with Dash (+ Docker) part1 ~ Environment construction and operation check ~
When I tried to build an environment of PHP7.4 + Apache + MySQL with Docker, I got stuck [Windows & Mac]
I tried to create a portfolio with AWS, Docker, CircleCI, Laravel [with reference link]
I tried to manage struts configuration with Coggle
I tried to manage login information with JMX
I tried to make a simple game with Javafx ① "Let's find happiness game" (unfinished)
Rails6 I want to make an array of values with a check box
[Android] I tried to make a material list screen with ListView + Bottom Sheet
I tried installing docker on an EC2 instance
I tried to develop an application in 2 languages
[Rails] How to build an environment with Docker
I tried to break a block with java (1)
I tried to make a simple game with Javafx ① "Let's find happiness game" (unfinished version ②)
I tried to read and output CSV with Outsystems
I tried to implement TCP / IP + BIO with JAVA
[Java 11] I tried to execute Java without compiling with javac
How to make an almost static page with rails
I started MySQL 5.7 with docker-compose and tried to connect
I tried to get started with Spring Data JPA
I tried to make a login function in Java
I tried to make FizzBuzz that is uselessly flexible
I tried to draw animation with Blazor + canvas API
When I bcrypt with node + docker, I got an error
I tried to implement Stalin sort with Java Collector
App development beginners tried to make an Android calculator app
How to make Laravel faster with Docker for Mac
I used to make nc (netcat) with JAVA normally
roman numerals (I tried to simplify it with hash)
I tried to make an app that allows you to post and chat by genre ~ App overview ~
Easy to make LINE BOT with Java Servlet Part 2: I tried image messages and templates
When I tried to run Azure Kinect DK with Docker, it was blocked by EULA