Postgres Local Backup Restore using Docker

#pg-backup-restore

restore your pgbackrest backup into a local docker container

README.md

PostgreSQL 16 Restore Docker Image for Local Development

This Docker image is built on top of the official PostgreSQL 16 image and includes pgBackRest for restoring a local development database from a backup.

Usage

Building the Image

To build the Docker image, run the following command in the directory containing the Dockerfile:

docker build -t pg16-restore .

Running the Container

To run the container and restore a database from a pgBackRest backup, use the following command:

docker run -d \
  --name pg16-restore \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_USER=your_username \
  -v /path/to/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf \
  pg16-restore your_stanza_name

Replace the following:

• your_password: Set a password for the PostgreSQL user
• your_username: Set the username for the PostgreSQL user (optional, defaults to 'postgres')
• /path/to/pgbackrest.conf: Path to your pgBackRest configuration file
• your_stanza_name: Name of the stanza to restore

Point-in-Time Recovery

To perform a point-in-time recovery, add the target datetime as the second argument:

docker run -d \
  --name pg16-restore \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_USER=your_username \
  -v /path/to/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf \
  pg16-restore your_stanza_name "2024-10-01 12:00:00"

Accessing the Restored Database

After the restore process is complete, you can connect to the PostgreSQL server using the following command:

docker exec -it pg16-restore psql -U your_username your_db

Important Notes

1. The POSTGRES_PASSWORD environment variable is required.
2. The POSTGRES_USER environment variable is optional and defaults to 'postgres'.
3. The pgBackRest configuration file must be mounted at /etc/pgbackrest/pgbackrest.conf.
4. This image is intended for restoring a local development database and does not include backup functionality.
5. The restored database allows connections from all hosts for testing purposes.
6. pgBackRest is removed after the restore process to ensure not accidentally overwrite backups.
7. Once a container has started using this image, you cannot move the location of the pgbackrest config file, as it has been mounted. However, it's fine to empty the file after the restore process, as it contains sensitive information.

Customization

You can customize the PostgreSQL configuration by modifying the postgresql.conf and pg_hba.conf files in the data directory after the restore process.

Troubleshooting

If you encounter any issues, check the Docker logs for detailed information about the restore process and any errors:

docker logs pg16-restore

For more information on pgBackRest configuration and usage, refer to the official pgBackRest documentation.

Dockerfile

FROM postgres:16

# Install pgBackRest and ca-certificates
RUN apt-get update && \
    apt-get install -y --no-install-recommends pgbackrest ca-certificates

# Create a directory for pgBackRest configuration
RUN mkdir -p /etc/pgbackrest

# Create a script to handle restore and cleanup
COPY entrypoint.sh /entrypoint.sh
RUN chmod +x /entrypoint.sh

# Update the CA certificate store
RUN update-ca-certificates

# Set the entrypoint
ENTRYPOINT ["/entrypoint.sh"]

entrypoint.sh

#!/bin/bash

# Check if a stanza argument is provided
if [ -z "$1" ]; then
  echo "Error: Stanza name is required as the first argument."
  exit 1
fi

# Ensure postgres password has been provided (exit with error if not provided)
if [ -z "$POSTGRES_PASSWORD" ]; then
  echo "Error: POSTGRES_PASSWORD environment variable is not set."
  exit 1
fi

# Check if the pgbackrest.conf file exists
if [ ! -f /etc/pgbackrest/pgbackrest.conf ]; then
  echo "Error: pgbackrest.conf not found at /etc/pgbackrest/pgbackrest.conf. Please mount your configuration file."
  exit 1
fi

# Check if the restore has already been performed
if [ ! -f /var/lib/postgresql/data/.restored ]; then
    STANZA="$1"

    # Remove any existing files in the data directory
    rm -rf /var/lib/postgresql/data/*

    # Check if a datetime argument is provided
    if [ -n "$2" ]; then
    echo "Restoring stanza '$STANZA' to datetime: $2"
    pgbackrest --stanza="$STANZA" --log-level-console=detail --pg1-path=/var/lib/postgresql/data restore --type=time --target="$2"
    else
    echo "Performing full restore of stanza '$STANZA'"
    pgbackrest --stanza="$STANZA" --log-level-console=detail --pg1-path=/var/lib/postgresql/data restore
    fi

    # Initialize the PostgreSQL data directory
    su postgres -c "initdb -D /tmp/pgdata"

    # Copy the configuration files to the actual data directory
    cp /tmp/pgdata/postgresql.conf /var/lib/postgresql/data/
    cp /tmp/pgdata/pg_hba.conf /var/lib/postgresql/data/

    # Remove the temporary data directory
    rm -rf /tmp/pgdata

    # Change ownership of the data directory to the postgres user
    chown -R postgres:postgres /var/lib/postgresql/data

    # Allow connections from all hosts for testing purposes
    echo "host all all 0.0.0.0/0 md5" >> /var/lib/postgresql/data/pg_hba.conf

    # Start PostgreSQL in the background
    su postgres -c "postgres -D /var/lib/postgresql/data &"

    # Wait for PostgreSQL to start
    sleep 5

    # Promote the database after point-in-time recovery (if needed)
    su postgres -c "pg_ctl promote -D /var/lib/postgresql/data"

    # Set the username (default to 'postgres' if not provided)
    USERNAME=${POSTGRES_USER:-postgres}

    # Check if the user already exists
    if su postgres -c "psql -tAc \"SELECT 1 FROM pg_roles WHERE rolname='$USERNAME'\" | grep -q 1"; then
    echo "User '$USERNAME' already exists. Updating password..."
    su postgres -c "psql -c \"ALTER USER $USERNAME WITH PASSWORD '$POSTGRES_PASSWORD';\""
    else
    echo "Creating user '$USERNAME'..."
    su postgres -c "psql -c \"CREATE USER $USERNAME WITH SUPERUSER PASSWORD '$POSTGRES_PASSWORD';\""
    fi

    # Remove pgBackRest after restore (moved after PostgreSQL starts)
    rm -rf /var/log/pgbackrest
    apt-get purge -y pgbackrest

    # Create the flag file to indicate that the restore has been performed
    touch /var/lib/postgresql/data/.restored
    # Keep the container running
    tail -f /dev/null
else
  # Just start PostgreSQL
  echo "Starting PostgreSQL..."
  exec su postgres -c "postgres -D /var/lib/postgresql/data"
fi