Postgres SQL 11 to 16 upgrade tool

[gadgetusaf]

Hey everyone, Had a bit of fun getting the database upgraded when I upgraded my conatiner. This will properly upgrade your database. you may need to tweak for a differnt version but this worked great for me.

Youll need to replace your-password in both the upgrade.sh script and the Dockerfile.

1. Ensure that Docker and docker-compose are installed on your system.

2. Determine the path to your existing PostgreSQL 11 data directory. This is typically /var/lib/postgresql/11/data.

3. Choose a new path where you want the upgraded PostgreSQL 16 data to be saved. For example, /path/to/new/folder.

4. Change the ownership of the new path to the user ID of the process that will run the upgraded database. You can do this by running the following command:

   sudo chown 70:70 /path/to/new/folder
   

5. Create a new empty folder and navigate to it in your terminal.

6. In this folder, create two files: upgrade.sh and Dockerfile. Copy the contents of the provided upgrade.sh and Dockerfile into their respective files.

7. Make the upgrade.sh script executable by running the following command:

   chmod +x upgrade.sh
   

8. Build the Docker image by running the following command:

   sudo docker build -t postgres-upgrade .
   

9. Ensure that all related containers are stopped. If you used docker-compose to start your containers, make sure to stop them using the following command:

   docker-compose down
   

10. Run the upgrade process using the following command, replacing /psql11/path with the path to your existing PostgreSQL 11 data directory and /psql16/path with the path where you want the upgraded data to be saved:

    sudo docker run -v /psql11/path:/var/lib/postgresql/11/data -v /psql16/path:/var/lib/postgresql/16/data postgres-upgrade
    

11. After the upgrade process is complete, modify the path in your application's configuration to point to the new PostgreSQL 16 data location (/psql16/path).

Note: Make sure to replace your-password in the upgrade.sh script and Dockerfile with your actual PostgreSQL password.

upgrade.sh

#!/bin/bash

set -e

# Set the path for the old PostgreSQL 11 data directory
OLD_DB_PATH=/var/lib/postgresql/11/data

# Check if the old database directory exists
if [ ! -d "$OLD_DB_PATH/base" ]; then
  echo "Old database directory not found: $OLD_DB_PATH/base"
  exit 1
fi

# Start the old PostgreSQL 11 server
echo "Starting old PostgreSQL 11 server..."
/usr/lib/postgresql/11/bin/pg_ctl -D "$OLD_DB_PATH" -o "-c listen_addresses='localhost'" start

# Wait for the server to start
sleep 5

# Dump the djangodb database
echo "Dumping the djangodb database..."
/usr/lib/postgresql/11/bin/pg_dump -h localhost -p 5432 -U djangouser -d djangodb -Fc > /tmp/djangodb_dump.sql

# Stop the old PostgreSQL 11 server
echo "Stopping old PostgreSQL 11 server..."
/usr/lib/postgresql/11/bin/pg_ctl -D "$OLD_DB_PATH" stop

# Change ownership and permissions of the new PostgreSQL 16 data directory
echo "Changing ownership and permissions of the new PostgreSQL 16 data directory..."
chown -R postgres:postgres /var/lib/postgresql/16/data
chmod -R 700 /var/lib/postgresql/16/data

# Initialize a new PostgreSQL 16 database cluster
echo "Initializing a new PostgreSQL 16 database cluster..."
/usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/data

# Start the PostgreSQL 16 server
echo "Starting PostgreSQL 16 server..."
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/data -o "-c listen_addresses='localhost'" start

# Wait for the server to start
sleep 5

# Create the djangouser role in the new PostgreSQL 16 server
echo "Creating the djangouser role..."
/usr/lib/postgresql/16/bin/psql -h localhost -p 5432 -U postgres -c "CREATE ROLE djangouser LOGIN PASSWORD 'your-password';"

# Grant the djangouser role the necessary privileges to create databases
echo "Granting privileges to the djangouser role..."
/usr/lib/postgresql/16/bin/psql -h localhost -p 5432 -U postgres -c "ALTER ROLE djangouser CREATEDB;"

# Create the djangodb database in the new PostgreSQL 16 server
echo "Creating the djangodb database..."
/usr/lib/postgresql/16/bin/createdb -h localhost -p 5432 -U djangouser djangodb

# Restore the djangodb dump into the new PostgreSQL 16 server
echo "Restoring the djangodb dump into the new PostgreSQL 16 server..."
/usr/lib/postgresql/16/bin/pg_restore -h localhost -p 5432 -U djangouser -d djangodb /tmp/djangodb_dump.sql

# Modify pg_hba.conf to allow connections from the Django application
echo "Modifying pg_hba.conf..."
echo "host    djangodb    djangouser    172.19.0.0/16    md5" >> /var/lib/postgresql/16/data/pg_hba.conf

# Reload the PostgreSQL configuration
echo "Reloading PostgreSQL configuration..."
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/data reload

# Analyze the upgraded database
echo "Analyzing the upgraded database..."
/usr/lib/postgresql/16/bin/vacuumdb -h localhost -p 5432 -U djangouser --analyze djangodb

echo "Upgrade process completed."

# Stop the PostgreSQL 16 service
echo "Stopping PostgreSQL 16 service..."
/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/data stop

# Exit the script
exit 0

dockerfile

FROM postgres:16

# Set environment variables
ENV POSTGRES_USER=djangouser
ENV POSTGRES_PASSWORD=your-password

# Install necessary packages
RUN apt-get update && apt-get install -y \
    postgresql-11 \
    postgresql-client-11 \
    sudo \
    && rm -rf /var/lib/apt/lists/*

# Change the UID of the existing postgres user to 70
RUN usermod -u 70 postgres

# Create the new data directory for PostgreSQL 16
RUN mkdir -p /var/lib/postgresql/16/data

# Copy the upgrade script
COPY upgrade.sh /usr/local/bin/

# Set execute permission on the script
RUN chmod +x /usr/local/bin/upgrade.sh

# Change ownership and permissions of the /var/lib/postgresql directory as root
RUN sudo chown -R postgres:postgres /var/lib/postgresql && \
    sudo chmod -R 700 /var/lib/postgresql

# Switch to the postgres user
USER postgres

# Set the entrypoint
ENTRYPOINT ["/usr/local/bin/upgrade.sh"]