PostgreSQL

Last modified by Sebastian Marsching on 2023/03/22 23:25

Listing Privileges

Use \l to list databases with privileges. Use \z to list tables with privileges. The output format is explained in the PostgreSQL manual.

Upgrading PostgreSQL running inside Docker

When running PostgreSQL inside a Docker container the usual upgrade method provided by Debian (pg_upgradecluster) does not work. Instead, one has to do the dump and restore manually.

One can either dump and restore the whole cluster or just a specific database. Which of the two methods is better depends on different circumstances (e.g. whether the application in question uses more than one database or whether there are any modifications to the default database). When there is only one database and there are no modifications to the postgresql, template0, or template1 databases, dumping and restoring the single application database should be sufficient.

In the following, we assume that the name of the volume that stores the PostgreSQL data (and is mounted at /var/lib/postgresql/data inside the container) is postgresql-data and the name of the container running PostgreSQL is postgresql.

First, we stop the PostgreSQL container:

docker container stop postgresql

Next, we create a volume for the old data and move the existing data there:

docker volume create postgresql-data-old
docker run --rm -it -v postgresql-data:/data -v postgresql-data-old:/data-old alpine sh -c "mv /data/* /data-old/"

Now, we replace the PostgreSQL container with a new container using the image for the new PostgreSQL version. How this is done strongly depends on the setup (e.g. when using Salt, this might be as easy as changing the version number in the SLS and applying it).

Once this has been done, the container is running again, but now with the new version and an empty database. We can now start a second container with the old data in order to dump and restore it:

docker container run --rm -it -v postgresql-data-old:/var/lib/postgresql/data --name postgresql-old postgres:x.x-alpine

In this example, x.x has to be replaced by the version number of the old PostgreSQL version.

Now, we can transfer the data. If we only want to transfer a single database, we can use the following command:

docker exec postgresql-old pg_dump -U postgres mydatabase | docker exec -i postgresql psql -U postgres mydatabase

If we want to dump and restore the whole cluster, we can use the following command:

docker exec postgresql-old pg_dumpall -U postgres | docker exec -i postgresql psql -U postgres

After this, we can stop the postgresql-old container. Once we are satified that the migration was successful, we can delete the volume holding the old data:

docker container stop postgresql-old
docker volume rm postgresql-data-old