Upgrading PostgreSQL: A Step-by-Step Guide with Docker

A crucial task that database administrators often face is upgrading databases to a newer version. PostgreSQL, being a powerful and highly scalable database system, is no exception to this. This blog post will guide you through the process of upgrading a PostgreSQL database using Docker.

Step 1: Build Docker images for both versions of PostgreSQL

Start by creating Docker images for both the old and new versions of PostgreSQL. Let’s assume you’re upgrading from PostgreSQL 9.4 to 13.0. You need to create Dockerfiles for both versions and build Docker images from them:

docker build -f Dockerfile-9-4 -t db-9-4 .
docker build -f Dockerfile-13 -t db-13 .

When you try to run the new PostgreSQL image, it will complain that the database is incompatible since it was created with an older version. This is expected and will be addressed in the steps that follow.

Step 2: Run the old PostgreSQL version and export the database

Now, you need to start a Docker container with the old PostgreSQL version and dump the database to a SQL file:

docker run --rm -it -v $PWD/volumes/db/var/lib/postgresql/data:/var/lib/postgresql/data --name db-9-4 db-9-4
docker exec db-9-4 pg_dump -U chatuser chatdb > chatdb.sql

Step 3: Create a new database directory and initialize the new database

Next, you need to create a new directory for the updated PostgreSQL database and initialize it. However, remember that the new PostgreSQL container will exit right after initialization:

mkdir -p ./volumes/db/var/lib/postgresql-13/data
docker run --rm -it -v $PWD/volumes/db/var/lib/postgresql-13/data/:/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=******* -e POSTGRES_USER=chatuser -e POSTGRES_DB=chatdb --name db-13 db-13

To actually start the new PostgreSQL container, you need to invoke the same command again:

docker run --rm -it -v $PWD/volumes/db/var/lib/postgresql-13/data/:/var/lib/postgresql/data/ -e POSTGRES_PASSWORD=******* -e POSTGRES_USER=chatuser -e POSTGRES_DB=chatdb --name db-13 db-13

Step 4: Import the old database into the new one

Now, you need to copy the database dump file you created earlier to the new database folder and import it into the new PostgreSQL instance:

cp chatdb.sql volumes/db/var/lib/postgresql-13/data/
docker exec db-13 su postgres -c "psql -U chatuser chatdb < /var/lib/postgresql/data/chatdb.sql"

Step 5: Start the Docker Compose with the new PostgreSQL version

Finally, you can start your Docker Compose application with the new PostgreSQL directory and version.

That’s it! You have successfully upgraded your PostgreSQL database using Docker. This process can be adapted for different versions of PostgreSQL and can also be automated for seamless database upgrades in production environments.