How to Upgrade a PostgreSQL Database in a Docker Environment

Upgrading a PostgreSQL database within a Docker environment requires careful planning to ensure data integrity and system stability. Here’s a structured approach to performing a major version upgrade:

Prerequisites

1. Docker Environment: Ensure your PostgreSQL database is running within a Docker container managed by Docker Compose.
2. Service Naming: Your Docker Compose configuration should define a service (e.g., db) utilizing the official PostgreSQL image.
3. Data Persistence: Verify that your database data is stored in a Docker volume or a host directory to persist data across container restarts.

Step 1: Backup Existing Data

• Stop Application Services: To prevent data inconsistencies, halt any services that interact with the database.
• Export Data: Initiate the database container and execute a comprehensive backup using pg_dumpall:

 docker-compose up -d db docker exec -it <container_name> pg_dumpall -U postgres > ~/db_backup.sql

Replace <container_name> with your actual PostgreSQL container name.

• Verify Backup: Inspect the backup file to ensure it contains the expected SQL statements.

Step 2: Prepare for Upgrade

• Modify Docker Compose Configuration: Update your docker-compose.yml to reference the new PostgreSQL version and designate a new data directory:

services:
  db:
    image: postgres:<new_version>
    ports:
      - "5432:5432"
    volumes:
      - ./postgres_data_new:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=your_password
      - POSTGRES_DB=your_database
      - POSTGRES_USER=your_user
    restart: always

Ensure you create the new data directory (./postgres_data_new) on your host system.

• Deploy New Database Container: Launch the updated PostgreSQL container:

docker-compose up -d db

• Initialize Database: The new container will set up an empty database using the specified environment variables.

Step 3: Import Data into New Version

• Import Backup: Transfer the previously exported data into the new PostgreSQL instance:

cat ~/db_backup.sql | docker exec -i <container_name> psql -U postgres

• Restart Application Services: After the data import, restart your application services to resume normal operations.

Additional Considerations

• Configuration Files: If you have custom configurations (postgresql.conf, pg_hba.conf), ensure they are migrated to the new data directory.
• Testing: Before deploying to production, test the upgrade process in a staging environment to identify and resolve potential issues.

By following this methodical approach, you can effectively upgrade your PostgreSQL database within a Docker setup, minimizing downtime and preserving data integrity.