How to Upgrade PostgreSQL in Docker and Kubernetes

Published: August 25, 2020 by Author's Photo Shane Rainville | Reading time: 5 minutes
Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

In this tutorial, you will learn how to safely upgrade your PostgreSQL server containers to more recent versions of the database server. These instructions apply to upgrading between major release (11.x -> 12.x) and do not apply to minor or bug releases. For minor and bug releases the base image can be upgraded without problems.

Applications like Postgres were not designed to run in a containerized world, as conventions introduced with Docker did not exist when they were originally written.

Instead, we will have to follow old, trusted conventions to safely upgrade Postgres in Docker or Kubernetes. You will need to dump your databases from the image running the older Postgres version, and then import the dump into the container running a new version of Postgres.

1. Deploy New Postgres Image

The first step is to deploy a new Postgress container using the updated image version. This container MUST NOT mount the same volume from the older Postgress container. It will need to mount a new volume for the database.

If you mount to a previous volume used by the older Postgres server, the new Postgres server will fail. Postgres requires the data to be migrated before it can load it.

2. Backup Running Container

pg_dumpall

The pg_dumpall utility is used for writing out (dumping) all of your PostgreSQL databases of a cluster. It accomplishes this by calling the pg_dump command for each database in a cluster, while also dumping global objects that are common to all databases, such as database roles and tablespaces.

The official PostgreSQL Docker image come bundled with all of the standard utilities, such as pg_dumpall, and it is what we will use in this tutorial to perform a complete backup of our database server.

In a typical PostgreSQL backup using the pg_dumpall command, you would use the -f flag to specify an output file. While, this can still be done with a containerized server the output file will be written inside of that container. An additional step would be required to copy it to your local filesystem.

Alternatively, to dump the output directly to your local filesystem you can use > to write a local file of the dump. The examples below will use this method over using the -f flag to simplify the backup process.

Docker

If your Postgres server is running as a Docker container, you will need to execute the following command.

docker exec -it [postgres-container] /usr/bin/pg_dumpall -U [username] > dumpfile

For example, if you were dumping the entire database server from a container with the ID of eddc550220ed and a PostgreSQL user named postgres, you would run the following command.

docker exec -it eddc550220ed /usr/bin/pg_dumpall -U postgres > dumpfile

Kubernetes

If your Postgres server is running as a Kubernetes Pod, you will execute the following command.

kubectl exec -it [postgres-pod] -- /usr/bin/pg_dumpall -U postgre > dumpfile

Kubernetes Port-Forward

The Kubernetes example above executes a command inside of your PostgreSQL container, and then dumps the backup to your local filesystem using an unconventional method. A different approach that can be accomplished with Kubernetes is to port-forward your pod’s exposed port onto your local system.

By port-forwarding your Postgres pod you can run native tools locally against your database server.

kubectl port-forward svc/postgres 5432 &
Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432

With your Postgres svc port-forwarded to your local machine, you can now point psql to port 5432 on your local host to perform operations.

Connections to port-forwarded PostgreSQL services will require a username and password, if set. Unlike executing commands within the PostgreSQL container, which does not require a username or password by default, even if one is set.

The example command below connects to the Postgres service running on a Kubernetes cluster with user postgres. The -W causes pg_dumpall to prompt for a password, and the -f flag sets the output file of the backup.

pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -W -f database.bkp
Password: 
Handling connection for 5432

If your connection was successful, a new dump file will be found on your local filesystem.

3. Import PostgreSQL Dump into New Container

With the new Postgres container running with a new volume mount for the data directory, you will use the psql command to import the database dump file. During the import process Postgres will migrate the databases to the latest system schema.

Docker

docker exec -it [new-postgres-container] psql < dumpfile 

Kubernetes

kubectl exec -it [new-postgres-pod] -- psql < dumpfile>

4. Verify Import

Always verify the import completed successfully and without corruption. You may want to perform a vew tests against the data to ensure everything imported correctly before moving on.

5. Stop Old Container

Once you’ve verified that the new Postgres server is operating correctly and the imported backup is fine, you will need to stop the container or Pod of the old Postgres server.

If you are running Kubernetes or Docker Swarm, you will need to delete the deployment of the Postgres service. Otherwise, both orchestrators will reschedule an older version of your Postgres server.

Further Reading

Last updated on September 7, 2020 by Shane Rainville: Add more content c61b901d3d63b9104d4127b9aa5d4332d3ad904a
Author Photo
Blogger, Developer, pipeline builder, cloud engineer, and DevSecOps specialist. I have been working in the cloud for over a decade and running containized workloads since 2012, with gigs at small startups to large financial enterprises.

How to Deploy Jekyll on Kubernetes

Publised September 15, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

How to Set PHP Options for Wordpress in Docker

Publised August 27, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

How to Solve Wordpress Redirects to Localhost 8080

Publised August 27, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

How to Deploy Postgres on Kubernetes

Publised August 25, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

Running Python Flask on Kubernetes

Publised July 6, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

Deploying React to Kubernetes

Publised June 30, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

How to Update Kubernetes Deployments

Publised September 11, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.

How to Configure Node-based apps in Kubernetes

Publised September 9, 2020 by Shane Rainville

Learn how to upgrade your Postgres servers running in Docker or Kubernetes in a safe and reliable way to ensure data integrity.