Wiki source code of PostgreSQL

Last modified by Sebastian Marsching on 2023/03/22 23:25
Show last authors
1 {{toc/}}
2
3 # Listing Privileges
4
5 Use `\l` to list databases with privileges. Use `\z` to list tables with privileges. The output format is explained in the [PostgreSQL manual](http://www.postgresql.org/docs/9.3/static/sql-grant.html#SQL-GRANT-NOTES).
6
7 # Upgrading PostgreSQL running inside Docker
8
9 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.
10
11 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.
12
13 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`.
14
15 First, we stop the PostgreSQL container:
16
17 ```sh
18 docker container stop postgresql
19 ```
20
21 Next, we create a volume for the old data and move the existing data there:
22
23 ```sh
24 docker volume create postgresql-data-old
25 docker run --rm -it -v postgresql-data:/data -v postgresql-data-old:/data-old alpine sh -c "mv /data/* /data-old/"
26 ```
27
28 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).
29
30 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:
31
32 ```sh
33 docker container run --rm -it -v postgresql-data-old:/var/lib/postgresql/data --name postgresql-old postgres:x.x-alpine
34 ```
35
36 In this example, `x.x` has to be replaced by the version number of the old PostgreSQL version.
37
38 Now, we can transfer the data. If we only want to transfer a single database, we can use the following command:
39
40 ```sh
41 docker exec postgresql-old pg_dump -U postgres mydatabase | docker exec -i postgresql psql -U postgres mydatabase
42 ```
43
44 If we want to dump and restore the whole cluster, we can use the following command:
45
46 ```sh
47 docker exec postgresql-old pg_dumpall -U postgres | docker exec -i postgresql psql -U postgres
48 ```
49
50 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:
51
52 ```sh
53 docker container stop postgresql-old
54 docker volume rm postgresql-data-old
55 ```