PostgreSQL

For members

PostgreSQL is available as a service for members on caffeine. Just run ceo postgresql create to create a new database for your account. As of this writing, club reps cannot create PostgreSQL databases for their clubs via ceo, so they will need to send an email to syscom instead.

For syscom

We are also running a Postgres database on coffee, which is not available to members. Any software installed by syscom should use this database instead of the one on caffeine.

Creating a database manually on caffeine

See how ceo does it.

Upgrades

Upgrading Postgres is more difficult than upgrading MySQL; when you upgrade the Debian version on a machine, a newer version of Postgres will be installed but the old version will remain and the data will not be migrated. You are responsible for manually upgrading the database yourself on all machines where Postgres is installed (currently, just coffee and caffeine).

Here's the Debian-specific way to do it (steps adapted from here). In the example below, we will assume that we are upgrading from Postgres 13 to 15.

1. First, take a full backup of the database. DO NOT SKIP THIS STEP.

   pg_dumpall | xz -T0 > dump.sql.xz
   

2. Drop the new database, which should be empty at this point. Make sure that you are not dropping the old database instead! You can run pg_lsclusters to see which database versions are present.

   # Make sure that this is the NEW version, not the old version!
   pg_dropcluster --stop 15 main
   

3. Upgrade the cluster:

   pg_upgradecluster -v 15 13 main
   

4. Run psql and make sure that the databases are present:

   su - postgres -c psql
   \l
   \q
   

5. Once we are sure that everything is working fine, drop the old database:

   # Make sure that this is the OLD version, not the new version!
   pg_dropcluster --stop 13