The situation refers to postgresql version 9.3 running inside a docker container. The docker host distro is Ubuntu Server 18.04
The configuration of log shipping is optional to the system, using a script to configure this feature.
When using log shipping configuration, wal archiving is configured to force a logfile segment switch every 5 minutes to the directory /var/lib/postgresql/archive/wal/ inside the container. The directory /var/lib/postgresql/archive is mounted as a docker volume, this is shown on docker compose configuration below.
The system needs to copy the wal logs to remote host every time a new one is archived, this way if the server where postgresql container runs crash, a backup can be restored using the wal files on the remote host.
This is the docker compose configuration for postgresql container
version: '2.1'
services:
dbhost:
build:
context: ./dbhost
image: amsinstance/postgres:9.3-de_DE
restart: always
ports:
- "127.0.0.1:${POSTGRES_PORT}:5432"
expose:
- "5432"
volumes:
- db-data:/var/lib/postgresql/data
- db-archive:/var/lib/postgresql/archive
The current way to solve this problem consists on the following steps:
- Modify custom Appollo postgresql image to support remote copy of files using scp command.
- Generate ssh keys on the postgresql container and copy public key to authorized_keys on remote host.
- Create a script to be used on archive_command, inside postgresql file postgresql.conf as follows: archive_command = 'test ! -f /var/lib/postgresql/archive/wal/%f.gz && /online-backup/copy_wall_remote.sh /var/lib/postgresql/data/%p %f $remote_user $remote_ip $remote_path'
- Use scp inside the script to copy the wal file to the remote host
We want to know if there exists a more practical way to do the same, using Docker.