Steps to Backup the Whole PostgreSQL from a Docker container

question

Now you need to back up the entire PostgreSQL from the Docker container, and then use the backup file to recover it in another pg docker container.

step

Backup PG in old containers

# Log in to the old PG containerdocker exec -it postgres bash
# Backup the databasepg_dumpall -c -U postgres &gt; dump_`date +%Y-%m-%d"_"%H_%M_%S`.sql
# Exit the containerexit
# After exiting the container, copy the backup file to the hostdocker cp postgres:/dump_2024-10-26_05_58_04.sql dump_2024-10-26_05_58_04.sql

This is mainly usedpg_dumpallBack up the entire pg instance, the specific commands are explained as follows:

-c: The backup SQL file contains DROP statements;
-U: Database username.

Create a new PG container

After you have prepared the backup file, then, stop the old PG container and create a new PG container.

# Stop old PG containerdocker stop postgres
# Create a new PG containerdocker run --name postgres2 -e POSTGRES_PASSWORD=&lt;password&gt; -p 5432:5432 -d postgres -c max_connections=800

Recover data

# Copy backup data files into a new PG containerdocker cp dump_2024-10-26_05_58_04.sql postgres2:/
# Log in to the new PG containerdocker exec -it postgres2 bash
# Restore the backup databasepsql -f dump_2024-10-26_05_58_04.sql -U postgres postgres

ending

After the new pg container is fine, you can delete the old pg container and then change the name of the new pg container.

# Delete the old pg containerdocker rm postgres
# Change the name of the old pg containerdocker rename postgres2 postgres

Expand: Timely backup of PostgreSQL databases deployed by Docker

Note: Docker and docker-compose must be installed on the system

1. PostgreSQL backup script

1. Backup script

# vi /opt/postgresql/conf/backup/pg_backup.sh
#!/bin/bash
# Set database connection parametersDB_HOST="localhost"
DB_PORT="5432"
DB_NAME="PGDB"
DB_USER="root"
DB_PASS="Admin@123"
# Set up the backup directoryBACKUP_DIR="/mnt"
# Get the current time stampTIMESTAMP=$(date +"%Y%m%d%H%M%S")
# Set the backup file nameBACKUP_FILE="$BACKUP_DIR/backup_$DB_NAME_$"
# Use the pg_dump command to back up the databasepg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -Fc -b -v -f "$BACKUP_FILE"
# Check whether the backup is successful and export the backup logif [ $? -eq 0 ]; then
    echo "$TIMESTAMP Backup completed successfully: $BACKUP_FILE" &gt;&gt; /mnt/pg_backup.log
else
    echo "$TIMESTAMP Backup failed." &gt;&gt; /mnt/pg_backuo.log
fi
# Check and delete backup files ten days agofind "$BACKUP_DIR" -type f -name "$DB_NAME_*.sql" -mtime +10 -exec rm {} \;  
echo "$TIMESTAMP Backup completed and old backups cleaned up." &gt;&gt; /mnt/pg_backup.log

2. Add execution permissions

chmod +x  /opt/postgresql/conf/backup/pg_backup.sh

2. Deploy PostgreSQL

1. Deploy PG with docker-compose

# vi /opt/postgresql
version: "3"
services:
  postgresql:
    image: postgres:16.3-alpine
    restart: always
    privileged: true
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: Admin@123
      POSTGRES_DB: PGDB
    ports:
      - 5432:5432
    volumes:
      - ./conf/postgres/data:/var/lib/postgresql/data
      - ./conf/backup:/mnt			#Backup scripts and backup files storage directory

2. Start PostgreSQL

# cd /opt/ postgresql
# docker-compose up -d

3. Automatic backup of scheduled tasks

1. Set up scheduled tasks

# Automatically execute database backup scripts at 1:00 a.m. every day# crontab -l
0 1 * * *  docker exec postgresql   /bin/bash -c "/mnt/pg_backup.sh"

Summarize

It is quite easy to migrate pg from docker here. If there is a missing PG container here, it should be mapped to the persistent disk. Don’t learn from me about this. I will talk about it later if I have the chance.

This is the article about the operation steps of backing up the entire PostgreSQL from a Docker container. For more related content on Docker backup PostgreSQL, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!