RDS PostgreSQL

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

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.

Architecture

Configuration and backup operations are encoded in the startup script run.sh that implements the following flow

graph TD
    startup[container startup] -->|get postgres major version| set_data_dir["set data dir to\n/${major_version}"]
    set_data_dir -->|get previous postgres major version| has_old_data{"has old data in\n/${previous_version}"}
    has_old_data -->|no| data_dir_empty{"data dir '/${major_version}'\nis empty?"}
    has_old_data -->|yes| migrate_data["migrate data from /${previous_version}\n to /${major_version}"]
    backup_exists -->|no| init_db
    data_dir_empty -->|no| init_db
    data_dir_empty -->|yes| backup_exists{backup exists?}
    backup_exists -->|yes| restore[restore from backup]
    migrate_data --> init_users
    restore --> init_users[initialize / update schemas and users]
    init_db[initialize database] --> initial_backup
    initial_backup[initial backup] --> init_users
    init_users --> start_database[start database]

Versions

The PostgreSQL version can be selected via specific docker image tags

• ghcr.io/pellepelster/solidblocks-rds-postgresql:14-v0.4.3
• ghcr.io/pellepelster/solidblocks-rds-postgresql:15-v0.4.3
• ghcr.io/pellepelster/solidblocks-rds-postgresql:16-v0.4.3
• ghcr.io/pellepelster/solidblocks-rds-postgresql:17-v0.4.3

Each docker image includes at least the previous PostgreSQL version for version migration purposes. The current version can be shown with pg_versions and the versions are stored at /usr/libexec/postgresql/${postgresql_major_version}

Upgrade

Based on the startup logic explained above and the fact that the database data is stored in a version specific directory /storage/data/${db_instance_name}/${postgresql_major_version} a version upgrade looks like this:

• execute a full backup (backup-full.sh)
• stop container with currently running version
• start new container with the same configuration but a new PostgreSQL version
  • the new version will look for any data from a previous version and start a migration using pg_upgrade

Configuration

Global

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

Local Backup

S3 Backup

The S3 backup target works with an S3 compatible service, so apart from AWS you can for example also use Hetzner Cloud Storage. In most cases you should only need to point DB_BACKUP_S3_HOST to your specific provider.

Google storage bucket backup

Restore

Point-In-Time-Recovery (PITR) is supported by providing a specific time to restore. See pgBackRest restore documentation for more details on the configuration options.

Databases

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

Extensions

The following PostgreSQL extensions are available by default