How to manually upgrade PostgreSQL

Manual upgrade steps

The following instructions show how to upgrade your PostgreSQL service instances manually without downtime. These steps must be applied to each environment where the upgrade is required. Please carefully review the "Risks" section below before proceeding!

info

The example below demonstrates an upgrade from PostgreSQL 9.6 to PostgreSQL 13. These steps apply to any PostgreSQL version. Be sure to replace 9.6 with your current/old version and 13 with your target/new version.

1. Check the prefix of the current installation. This will be DEFAULT if it has not been manually changed, but can be anything. In this example, we will refer to it as DEFAULT.

2. Add the PostgreSQL service for the new version to your environment. Assign this the default prefix NEW.

3. Provision the new PosgreSQL service.

4. Repeat steps 1-3 for each environment.

At this point, the environment is still running on the old version of the database, but the service for the new version is deployed and ready to receive data. The following steps will copy the data from the existing database to the new version.

info

When a service shows as Pending attachment, it means it is functional but not yet connected to the application. Only services in the Attached and Pending detachment states are visible to the app and thus usable. The new version will be used (Attached) after you deploy your changes in step 11.

For more information on how and when services are provisioned and attached, please see the Services documentation.

5. Create a backup for the DEFAULT database service.
6. (Optional) Prepare a backup download and back this up somewhere. This step is not required from a migration perspective but may be needed to comply with local policies or just useful as a pre-migration snapshot later.
7. Once the backup is complete, restore it to the NEW service.

8. Rename the prefix for the old database version from DEFAULT to OLD.
9. Detach the OLD PostgreSQL service. It should now be in Pending detachment state.

10. Rename the prefix for the new database version from NEW to DEFAULT (or whatever prefix your original PostgreSQL service had before the upgrade).
11. Deploy the environment. Upon success, you should see the new service as Attached and the old service as Detached. Your application is now using the new database.
12. Delete the OLD PostgreSQL service.

13. Repeat steps 5-11 for each environment.

Your environment is now using the new PostgreSQL service for the upgraded version.

To run the application locally with the upgraded database, a few extra steps are required:

14. Edit your docker-compose.yml and change the tag of the PostgreSQL image. For example, from postgres:13.5-alpine to postgres:15.7-alpine`. See the official DockerHub postgres image for a list of available tags.
15. Run docker compose down -v to stop the containers and remove the volumes.
16. Run divio app pull db <environment> to pull the updated database from the environment you want locally.
17. Run docker compose up to bring everything up again.

Risks

As with all database migrations, there is a small risk here. Any data written to the database in the short time between step 5 (creating the backup of the old database service), and step 11 (deploying the changes) will be lost.

It is your responsibility to assess this risk and act accordingly. If your application rarely writes to the database and is mostly reading it, the risk is low. If the only database writes are controlled by you as a Divio user (e.g. a CMS application), then this process is safe. If, on the other hand, the users of your application are continually creating new records in the database, this process carries a higher risk.

To mitigate this risk, ensure that your application (or any components that write to the database) is inaccessible while these operations are being performed.

Upgrading PostgreSQL 13 to PostgreSQL 15

Known incompatibility: libpq < 10

PostgreSQL 14 changed the default password encryption server parameter from md5 to scram-sha-256. This new authentication encryption is supported in libpq version 10 and higher. Postgres drivers such as psycopg2 (Python), pg (Ruby), and pdo_pgsql (PHP PDO extension) are known to rely on libpq behind the scenes.

info

All divio base images (divio/base:<tag>) have been updated to use a recent libpq binary. If your application uses a divio/base image (check the FROM instruction in your Dockerfile), simply rebuilding your application is enough to guarantee SCRAM support.

Checking your version of libpq

To check your version of libpq:

1. Go to your Application’s Environment view

2. Copy the SSH command and execute it in a terminal to connect to your application

3. Run the following command inside the shell:

   find / -name "libpq.so.5.*" 2>/dev/null

   The output should look like:

   /usr/lib/libpq.so.5.9

   The number X in libpq.so.5.X is the actual version of libpq (9 in the above example). If there is no output, this means you are not using libpq. You can thus safely proceed with the migration.

Upgrading libpq

If your version of libpq is below 10, you need to take action. In general, updating your base image (FROM instruction in your Dockerfile) to a more recent tag and/or installing a newer postgreSQL client should fix the issue.

tip

On linux, libpq is often installed through the postgresql client:

• Using apt → apt install postgresql-client-15
• Using apk → apk add postgresql15-client

If you need support, don’t hesitate to reach us.

Hack: temporarily patch an image

danger

The following outlines a highly temporary and unconventional workaround. Only proceed if no other options are available and you fully understand each step in this procedure!

In some cases, the base image is too old to provide a newer libpq from the default package manager, and updating the base image requires too many changes that you cannot handle right now. As a last resort and temporary measure, a possibility is to copy the libpq binary from a more recent base image inside your image. The only requirement is for the two images to share the same OS (e.g. linux) and architecture (e.g. x86_64).

1. Find the path P_OLD to the libpq binary inside your image (see “check your version” above). Note down the directory D_OLD in which the binary resides.

2. Find the path P_NEW to the libpq binary inside a more recent image. Note down the filename F_NEW of the binary. It should match libpq.so.5.XX, with XXthe new version.

3. Add the following to your Dockerfile:

   FROM some-image:<old-libpq-tag>
   # ...
   
   ## Copy the new binary inside the image
   # some-image:new-tag should contain a more recent libpq version
   COPY --from some-image:<new-libpq-tag> <P_NEW> <D_OLD>/<F_NEW>
   # Update the link so it points to the new version
   RUN ln -sf <D_OLD>/<F_NEW> $(find / -name libpq.so.5 | head -1)

   The COPY adds the new binary to the image, and the RUN updates the symbolic link to the library, thus activating the new version in place of the old one.

Other incompatibilities

For more information about possible incompatibilities, see:

• https://www.postgresql.org/docs/release/14.0/
• https://www.postgresql.org/docs/release/15.0/