This is a practical guide for setting up continuous archival of PostgreSQL to S3 (and S3-like storage).

Install WAL-G⌗

  1. Grab the binary from WAL-G release page. For example: wal-g-pg-ubuntu-20.04-amd64.

  2. Download the binary.

    wget https://github.com/wal-g/wal-g/releases/download/v2.0.1/wal-g-pg-ubuntu-20.04-amd64

  3. Move to /usr/local/bin and set permissions.

    sudo mv wal-g-pg-ubuntu-20.04-amd64 /usr/local/bin/wal-g && \
sudo chmod +x /usr/local/bin/wal-g

  4. Verify if wal-g is installed.

    wal-g --help

Connecting WAL-G to S3 (or any S3-compatible API)⌗

This setup works for archiving the PostgreSQL WAL files to AWS S3 or S3-compatible API like Backblaze B2, MinIO, or SeaweedFS.

The following steps are executed in PostgreSQL host and logged in as postgres user (i.e.: sudo -i -u postgres) with current directory pointing to /var/lib/posrgresql.

Steps:

  1. mkdir .aws

  2. touch .aws/credentials

  3. Put the valid AWS credentials into .aws/credentials. It should look like following:

    [default]
aws_access_key_id = AKIDblahblah
aws_secret_access_key = acc3ssk3yyblahblah

  4. Create test script to test connection to remote S3 server.

    File path: /var/lib/postgresql/test_wal_g.sh

    #!/bin/bash

export WALG_S3_PREFIX=s3://postgres-archive-bucket/production-db
export AWS_REGION=us-east-2
export AWS_SHARED_CREDENTIALS_FILE=/var/lib/postgresql/.aws/credentials

# Optional for AWS S3 (just comment out the code). But necessary for SeaweedFS or MinIO.
export AWS_ENDPOINT=http://192.168.1.182:8333 # SeaweedFS or MinIO server address.
export AWS_S3_FORCE_PATH_STYLE=true

wal-g st check read
wal-g st check write

  5. Set executable permission.

    chmod u+x test_wal_g.sh

  6. Execute the test script.

    ./test_wal_g.sh

    If there is no error, it means that wal-g can successfully perform the read and write operations to the S3 bucket. If you encounter problem, do consult with the S3 server manual (e.g.: Checking bucket permission, etc.).

Setting up archival scripts for PostgreSQL⌗

  • File /var/lib/postgresql/make_basebackup.sh:

    #!/bin/bash

# Connect using TCP
# export PGHOST=localhost
# export PGPORT=5432

# Connect using Unix socket
export PGHOST=/var/run/postgresql

export WALG_S3_PREFIX=s3://postgres-archive-bucket/production-db
export AWS_REGION=us-east-2
export AWS_SHARED_CREDENTIALS_FILE=/var/lib/postgresql/.aws/credentials

# Optional for AWS S3 (just comment out the code). But necessary for SeaweedFS or MinIO.
export AWS_ENDPOINT=http://192.168.1.182:8333 # SeaweedFS or MinIO server address.
export AWS_S3_FORCE_PATH_STYLE=true

wal-g backup-push $1

    Set executable permission to the script:

    chmod u+x make_basebackup.sh

  • File /var/lib/postgresql/archive_command.sh:

    #!/bin/bash

# Connect using TCP
# export PGHOST=localhost
# export PGPORT=5432

# Connect using Unix socket
export PGHOST=/var/run/postgresql

export WALG_S3_PREFIX=s3://postgres-archive-bucket/production-db
export AWS_REGION=us-east-2
export AWS_SHARED_CREDENTIALS_FILE=/var/lib/postgresql/.aws/credentials

# Optional for AWS S3 (just comment out the code). But necessary for SeaweedFS or MinIO.
export AWS_ENDPOINT=http://192.168.1.182:8333 # SeaweedFS or MinIO server address.
export AWS_S3_FORCE_PATH_STYLE=true

wal-g wal-push $1

    Set executable permission to the script:

    chmod u+x archive_command.sh

Configuring PostgreSQL and make the first base backup⌗

In this part. You need to configure the PostgreSQL to ship the WAL files to the S3 bucket using the wal-g.

Steps:

  1. Open the file /etc/postgresql/15/main/postgresql.conf and set the following settings:

    wal_level = replica
archive_mode = on
archive_command = '/var/lib/postgresql/archive_command.sh /var/lib/postgresql/15/main/%p'
archive_timeout = 300 # 5 minutes

  2. Restart PostgreSQL.

    systemctl restart postgresql

    Watch for errors in the logs. e.g.:

    tail -f /var/log/postgresql/postgresql-15-main.log

  3. Execute the make_basebackup.sh script.

    ./make_basebackup.sh /var/lib/postgresql/15/main

  4. Once successful, verify that the base backup has been stored in the S3 bucket.

Next steps⌗

This article covers the practical guide for configuring PostgreSQL to ship the WAL files to S3 bucket. Restoring from backups or setting up warm standby PostgreSQL server will be covered in separate articles.