Solidblocks > RDS PostgreSQL

RDS PostgreSQL

A containerized PostgreSQL database with all batteries included backup solution powered by pgBackRest

Configuration

RDS PostgreSQL aims at being easy to use while keeping data a safe as possible. Based on the conventions of the official PostgreSQL docker image it can be configured by tuning different environment variables.

Global

configuration	type	description
`DB_INSTANCE_NAME`	environment	unique name of this database instance
`DB_ADMIN_PASSWORD`	environment	Password for the db superuser, if not set a random password will be assigned. Username for the superuser is `rds`
`DB_POSTGRES_EXTRA_CONFIG`	environment	Extra postgres configurations options for the `postgresql.conf`
/some/data/dir:/storage/data	mount	Container volume mount for the PostgreSQL data directory. The docker image uses a user with `uid` 10000, which needs to be reflected in the directory permissions
/some/backup/dir:/storage/backup	mount	Container volume mount for the pgBackRest backup repository directory. The docker image uses a group with `gid` 10000, which needs to be reflected in the directory permissions

Local Backup

Based on the functionality of pgBackRest two types of backup repositories are supported. Local filesystem (local), or an S3 compatible object storage (s3). Those can be configured individually, but at least one type has to be configured.

configuration	type	description
`DB_BACKUP_LOCAL`	environment	Flag to enable local filesystem as backup repository
`DB_BACKUP_LOCAL_RETENTION_FULL_TYPE`	environment	Retention type for full backups, see retention type documentation
`DB_BACKUP_LOCAL_RETENTION_FULL`	environment	Retention for full backups, see retention full documentation
`DB_BACKUP_LOCAL_RETENTION_DIFF`	environment	Retention for diff backups, see retention diff documentation

S3 Backup

configuration	type	description
`DB_BACKUP_S3`	environment	Flag to enable S3 object storage as backup repository
`DB_BACKUP_S3_HOST`	environment	Hostname of the S3 object storage service
`DB_BACKUP_S3_BUCKET`	environment	Bucket for the backup repository
`DB_BACKUP_S3_ACCESS_KEY`	environment	Access key for the backup bucket
`DB_BACKUP_S3_SECRET_KEY`	environment	Secret key for the backup bucket
`DB_BACKUP_S3_CA_PUBLIC_KEY`	environment	Public key for the CA that issued the certificates for the `DB_BACKUP_S3_HOST`. Useful when a non SaaS solution like minIO is used.
`DB_BACKUP_S3_RETENTION_FULL_TYPE`	environment	Retention type for full backups, see retention type documentation
`DB_BACKUP_S3_RETENTION_FULL`	environment	Retention for full backups, see retention full documentation
`DB_BACKUP_S3_RETENTION_DIFF`	environment	Retention for diff backups, see retention diff documentation

Databases

Multiple databases can automatically be provisioned by providing configurations for multiple distinct unique `${database_id}

per database configuration	type	description
`DB_DATABASE_${database_id}`	environment	name of the database that implicitly will be crated when the PostgreSQL is initialized
`DB_USERNAME_${database_id}`	environment	name ot the user who will be granted full access to `DB_DATABASE_${database_id}`
`DB_PASSWORD_${database_id}`	environment	password for the database user

If any of those settings are missing or invalid, the container will complain with a log message and exit with an error code:

$ docker run \
    -e DB_INSTANCE_NAME=instance1 \
    -e DB_DATABASE_db1=database1 \
    -e DB_USERNAME_db1=user1 \
    -e DB_PASSWORD_db1=password1 \
    pellepelster/solidblocks-rds-postgresql:v0.0.78 

[solidblocks-rds-postgresql] either 'DB_BACKUP_S3' or 'DB_BACKUP_LOCAL' has to be activated

analogous if you try to start the container with the right configuration but with missing mounts it will result in similar messages:

$ docker run \
    -e DB_INSTANCE_NAME=instance1 \
    -e DB_DATABASE_db1=database1 \
    -e DB_USERNAME_db1=user1 \
    -e DB_PASSWORD_db1=password1 \
    -e DB_BACKUP_LOCAL=1 \
    -v "$(pwd)/postgres_data:/storage/data" \
    pellepelster/solidblocks-rds-postgresql:v0.0.78

[solidblocks-rds-postgresql] local backup dir '/storage/backup' not mounted


$ docker run \
    -e DB_INSTANCE_NAME=instance1 \
    -e DB_DATABASE_db1=database1 \
    -e DB_USERNAME_db1=user1 \
    -e DB_PASSWORD_db1=password1 \
    -e DB_BACKUP_LOCAL=1 \
    -v "$(pwd)/postgres_backup:/storage/backup" \
    pellepelster/solidblocks-rds-postgresql:v0.0.78

[solidblocks-rds-postgresql] data dir '/storage/data' not mounted

Those safety checks are aimed at ensuring that there is always a working backup solution, and data is not accidentally stored inside an ephemeral container.

On the first start RDS PostgreSQL will initialize the database according to the provided credentials, and create an initial backup to validate the backup repositories are working as expected.

The database inside the container runs with a non-root user with a uid and gid of 10000 so for the mounts the correct permissions need to be ensured

$ mkdir postgres_{data,backup} && sudo chown 10000:10000 postgres_{data,backup}

$ docker run \
    --name instance1 \
    -e DB_INSTANCE_NAME=instance1 \
    -e DB_DATABASE_db1=database1 \
    -e DB_USERNAME_db1=user1 \
    -e DB_PASSWORD_db1=password1 \
    -e DB_BACKUP_LOCAL=1 \
    -v "$(pwd)/postgres_backup:/storage/backup" \
    -v "$(pwd)/postgres_data:/storage/data" \
    pellepelster/solidblocks-rds-postgresql:v0.0.78

[solidblocks-rds-postgresql] data dir is empty
[solidblocks-rds-postgresql] initializing database instance
The files belonging to this database system will be owned by user "rds".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /storage/data/instance1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

[...]

2022-10-02 18:11:07.671 P00   INFO: stanza-create command begin 2.36: --config=/rds/config/pgbackrest.conf --exec-id=66-4099fe2e --log-level-console=info --log-path=/rds/log --pg1-path=/storage/data/instance1 --pg1-socket-path=/rds/socket --repo2-path=/storage/backup --repo2-type=posix --stanza=instance1
2022-10-02 18:11:08.312 P00   INFO: stanza-create for stanza 'instance1' on repo2
2022-10-02 18:11:08.363 P00   INFO: stanza-create command end: completed successfully (694ms)
ensuring database 'database1' with user 'user1'
[solidblocks-rds-postgresql] creating database 'database1'
[solidblocks-rds-postgresql] creating user 'user1'
[solidblocks-rds-postgresql] granting all privileges for 'user1' on 'database1'
[solidblocks-rds-postgresql] setting permissions for 'user1'
[solidblocks-rds-postgresql] executing initial backup
2022-10-02 18:11:08.562 P00   INFO: backup command begin 2.36: --config=/rds/config/pgbackrest.conf --exec-id=88-ebcdde69 --log-level-console=info --log-path=/rds/log --pg1-path=/storage/data/instance1 --pg1-socket-path=/rds/socket --repo2-path=/storage/backup --repo2-retention-diff=7 --repo2-retention-full=7 --repo2-retention-full-type=count --repo2-type=posix --stanza=instance1 --type=full
2022-10-02 18:11:09.273 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2022-10-02 18:11:09.458 P00   INFO: archive-push command begin 2.36: [pg_wal/000000010000000000000001] --config=/rds/config/pgbackrest.conf --exec-id=90-a91e3518 --log-level-console=info --log-path=/rds/log --pg1-path=/storage/data/instance1 --repo2-path=/storage/backup --repo2-type=posix --stanza=instance1

[...]

2022-10-02 18:11:18.492 P00   INFO: check archive for segment(s) 000000010000000000000002:000000010000000000000002
2022-10-02 18:11:18.545 P00   INFO: pushed WAL file '000000010000000000000002' to the archive
2022-10-02 18:11:18.545 P00   INFO: archive-push command end: completed successfully (179ms)
2022-10-02 18:11:18.552 P00   INFO: archive-push command begin 2.36: [pg_wal/000000010000000000000002.00000028.backup] --config=/rds/config/pgbackrest.conf --exec-id=93-6e4c74bc --log-level-console=info --log-path=/rds/log --pg1-path=/storage/data/instance1 --repo2-path=/storage/backup --repo2-type=posix --stanza=instance1
2022-10-02 18:11:18.561 P00   INFO: pushed WAL file '000000010000000000000002.00000028.backup' to the archive
2022-10-02 18:11:18.561 P00   INFO: archive-push command end: completed successfully (10ms)
2022-10-02 18:11:18.617 P00   INFO: new backup label = 20221002-181109F
2022-10-02 18:11:18.721 P00   INFO: full backup size = 33.9MB, file total = 1244
2022-10-02 18:11:18.721 P00   INFO: backup command end: completed successfully (10160ms)
2022-10-02 18:11:18.722 P00   INFO: expire command begin 2.36: --config=/rds/config/pgbackrest.conf --exec-id=88-ebcdde69 --log-level-console=info

[...]

2022-10-02 18:11:18.882 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-10-02 18:11:18.882 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2022-10-02 18:11:18.889 GMT [1] LOG:  listening on Unix socket "/rds/socket/.s.PGSQL.5432"
2022-10-02 18:11:18.898 GMT [97] LOG:  database system was shut down at 2022-10-02 18:11:18 GMT
2022-10-02 18:11:18.903 GMT [1] LOG:  database system is ready to accept connections

Backups

Backups can be triggered via

$ docker exec instance1 /rds/bin/backup-[full|incr|diff].sh

where full, incr and diff refer to the available backup types of the pgBackRest backup command

Information about currently available backups can be retrieved with:

$ docker exec instance1 /rds/bin/backup-info.sh

stanza: instance1
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/000000010000000000000008

        full backup: 20221002-181109F
            timestamp start/stop: 2022-10-02 18:11:09 / 2022-10-02 18:11:18
            wal start/stop: 000000010000000000000002 / 000000010000000000000002
            database size: 33.9MB, database backup size: 33.9MB
            repo2: backup set size: 4.2MB, backup size: 4.2MB

        full backup: 20221002-182001F
            timestamp start/stop: 2022-10-02 18:20:01 / 2022-10-02 18:20:10
            wal start/stop: 000000010000000000000008 / 000000010000000000000008
            database size: 33.9MB, database backup size: 33.9MB
            repo2: backup set size: 4.2MB, backup size: 4.2MB

to restore a backup, just stop the container:

$ docker rm --force instance1

remove the data dir

$ sudo rm -rf postgres_data
$ mkdir postgres_data && sudo chown 10000:10000 postgres_data

and just restart the container again. It will automatically detect the empty data dir and restore the latest backup from the backup repository.

$ docker run \
    --name instance1 \
    -e DB_INSTANCE_NAME=instance1 \
    -e DB_DATABASE_db1=database1 \
    -e DB_USERNAME_db1=user1 \
    -e DB_PASSWORD_db1=password1 \
    -e DB_BACKUP_LOCAL=1 \
    -v "$(pwd)/postgres_backup:/storage/backup" \
    -v "$(pwd)/postgres_data:/storage/data" \
    pellepelster/solidblocks-rds-postgresql:v0.0.78