Docu review done: Mon 03 Jul 2023 16:50:59 CEST

Table of Content

• Commands and Descriptions
• Backup DBs
• Restore a DB
• Upgrade from Postgresversion X to N

Commands and Descriptions

Backup DBs

For dumping a DB you can use the pg_dump Either use dump a dedicated DB

$ /usr/bin/pg_dump -h host -p port dbname --clean > /packup/path/backupfilename.db.dump

or all DBs

$ /usr/bin/pg_dumpall -h host -p port --clean > /packup/path/backupfilename.db.dump

Restore a DB

Restores can just run a psql command with -f parameter (if it got dumped with pg_dumpall)

# backup generated like this: pg_dumpall dbname --clean > /packup/path/backupfilename.db.dump
$ psql -f /pacup/path/backupfilename.db.dump

Normal dumps are restored like with redirects:

# backup generated like this: pg_dump dbname > /packup/path/backupfilename.db.dump
$ createdb -T template0 mynewDB
$ psql mynewDB < /packup/path/backupfilename.db.dump

If you have problems while applying the dump, you can enable stop on error:

$ psql --set ON_ERROR_STOP=on myneDB < /packup/path/backupfilename.db.dump

OR a better way is to use the parameter ‘-1’ or ‘–single-transaction’

$ psql -1 ON_ERROR_STOP=on myneDB < /packup/path/backupfilename.db.dump

Upgrade from Postgresversion X to N

upgrade from e.g. 9.6 to 11

installed postgres versions 9.6, 10, 11

disable monitoring (e.g. for monit)

$ monit unmonitor postgres

stop postgres services first

$ systemctl stop postgresql.service

verify that all postgres services are down

$ ps afxj | grep postgres

drop default installed data in new DBs

$ pg_dropcluster --stop 10 main
$ pg_dropcluster --stop 11 main

start migration from 9.6 to newest installed version

$ pg_upgradecluster -m upgrade 9.6 main

output of the migration, wait till it says its done

Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Stopping old cluster...
Creating new PostgreSQL cluster 11/main ...
.
.
.
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "<DBNAME>": Generating minimal optimizer statistics (1 target)
.
.
.
vacuumdb: processing database "<DBNAME>": Generating default (full) optimizer statistics

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 9.6 main

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

after the migration yill get the message that you can now drop the old main data

$ pg_dropcluster 9.6 main

after this is done, you can safely remove the old packages

$ apt purge postgresql-9.6 postgresql-10

change configuration link in etc

$ rm /etc/postgres/main
$ ln -s /etc/postgres/11 /etc/postgres/main

stop the new cluster if running and restart it using the general postgresql service

$ pg_ctlcluster 11 main stop
$ systemctl start postgresql.service

verify that postgres is up and running

$ systemctl status postgresql@11-main.service
● postgresql@11-main.service - PostgreSQL Cluster 11-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: ena
   Active: active (running) since Thu 2018-12-13 00:03:10 CET; 12min ago
  Process: 13327 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 11-main start (code
 Main PID: 13332 (postgres)
    Tasks: 8 (limit: 4915)
   Memory: 30.8M
   CGroup: /system.slice/system-postgresql.slice/postgresql@11-main.service

Needed for older postgres versions (<15) after that, it is part of the upgrade hook

after postgres started, recreate the optimizer statistics

$ /usr/lib/postgresql/11/bin/vacuumdb --all --analyze-in-stages

enable monitoring again (e.g. for monit)

$ monit unmonitor postgres