PostgreSQL: Difference between revisions
m (→For syscom) |
|||
Line 41: | Line 41: | ||
</li> |
</li> |
||
<li> |
<li> |
||
Once we are sure that everything is working |
Once we are sure that everything is working, drop the old database: |
||
<pre> |
<pre> |
||
# Make sure that this is the OLD version, not the new version! |
# Make sure that this is the OLD version, not the new version! |
||
pg_dropcluster --stop 13 |
pg_dropcluster --stop 13 |
||
</pre> |
|||
</li> |
|||
<li> |
|||
It is now safe to purge the old postgres package: |
|||
<pre> |
|||
apt purge postgresql-13 |
|||
</pre> |
</pre> |
||
</li> |
</li> |
||
</ol> |
</ol> |
||
=== Backups === |
|||
We use [https://pgbackrest.org pgBackRest] for Postgres backups. |
|||
==== Installation ==== |
|||
In the example below, we will be installing pgbackrest on coffee, and using corn-syrup to store the backups (via SSH). |
|||
The pgbackrest package in bookworm is too old and doesn't support SFTP, so we're going to download the packages we need from trixie instead (starting from trixie and higher, this should no longer be necessary): |
|||
<pre> |
|||
# On coffee |
|||
wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/p/pgbackrest/pgbackrest_2.48-1_amd64.deb |
|||
wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/libz/libzstd/libzstd1_1.5.5+dfsg2-2_amd64.deb |
|||
apt install ./pgbackrest_2.48-1_amd64.deb ./libzstd1_1.5.5+dfsg2-2_amd64.deb |
|||
</pre> |
|||
Switch to the postgres user and create a new SSH key: |
|||
<pre> |
|||
su - postgres |
|||
ssh-keygen -t ed25519 |
|||
</pre> |
|||
Login to corn-syrup, switch to the syscom user, and paste the public key you created earlier into /users/syscom/.ssh/authorized_keys: |
|||
<pre> |
|||
restrict ssh-ed25519 AAAAC3Nza... postgres@coffee |
|||
</pre> |
|||
Next, on coffee, paste the following into /etc/pgbackrest.conf (adjust the pg1-path as appropriate): |
|||
<pre> |
|||
[global] |
|||
repo1-retention-full=2 |
|||
repo1-retention-diff=4 |
|||
repo1-bundle=y |
|||
repo1-type=sftp |
|||
repo1-sftp-host=corn-syrup |
|||
repo1-sftp-host-user=syscom |
|||
repo1-path=/users/syscom/backups/coffee/pgbackrest |
|||
repo1-sftp-private-key-file=/var/lib/postgresql/.ssh/id_ed25519 |
|||
repo1-sftp-public-key-file=/var/lib/postgresql/.ssh/id_ed25519.pub |
|||
repo1-sftp-host-key-hash-type=sha256 |
|||
repo1-sftp-host-key-check-type=none |
|||
start-fast=y |
|||
log-level-console=info |
|||
process-max=4 |
|||
compress-type=lz4 |
|||
[main] |
|||
pg1-path=/var/lib/postgresql/15/main |
|||
</pre> |
|||
The config above will keep two full backups and at least four differential backups. See https://pgbackrest.org/user-guide.html#retention for more details. |
|||
Next, open /etc/postgresql/15/main/postgresql.conf and add/edit the following lines: |
|||
<pre> |
|||
archive_mode = on |
|||
archive_command = 'pgbackrest --stanza=main archive-push %p' |
|||
</pre> |
|||
See https://pgbackrest.org/user-guide.html#quickstart/configure-archiving for more details. |
|||
Next, restart Postgres: |
|||
<pre> |
|||
systemctl restart postgresql@15-main |
|||
</pre> |
|||
Switch to the postgres user, create the main stanza, and run the first backup: |
|||
<pre> |
|||
su - postgres |
|||
pgbackrest --stanza=main stanza-create |
|||
pgbackrest --stanza=main check |
|||
pgbackrest --stanza=main backup --type=full |
|||
</pre> |
Revision as of 10:42, 25 November 2023
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.
-
First, take a full backup of the database. DO NOT SKIP THIS STEP.
pg_dumpall | xz -T0 > dump.sql.xz
-
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
-
Upgrade the cluster:
pg_upgradecluster -v 15 13 main
-
Run psql and make sure that the databases are present:
su - postgres -c psql \l \q
-
Once we are sure that everything is working, drop the old database:
# Make sure that this is the OLD version, not the new version! pg_dropcluster --stop 13
-
It is now safe to purge the old postgres package:
apt purge postgresql-13
Backups
We use pgBackRest for Postgres backups.
Installation
In the example below, we will be installing pgbackrest on coffee, and using corn-syrup to store the backups (via SSH).
The pgbackrest package in bookworm is too old and doesn't support SFTP, so we're going to download the packages we need from trixie instead (starting from trixie and higher, this should no longer be necessary):
# On coffee wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/p/pgbackrest/pgbackrest_2.48-1_amd64.deb wget http://mirror.csclub.uwaterloo.ca/debian/pool/main/libz/libzstd/libzstd1_1.5.5+dfsg2-2_amd64.deb apt install ./pgbackrest_2.48-1_amd64.deb ./libzstd1_1.5.5+dfsg2-2_amd64.deb
Switch to the postgres user and create a new SSH key:
su - postgres ssh-keygen -t ed25519
Login to corn-syrup, switch to the syscom user, and paste the public key you created earlier into /users/syscom/.ssh/authorized_keys:
restrict ssh-ed25519 AAAAC3Nza... postgres@coffee
Next, on coffee, paste the following into /etc/pgbackrest.conf (adjust the pg1-path as appropriate):
[global] repo1-retention-full=2 repo1-retention-diff=4 repo1-bundle=y repo1-type=sftp repo1-sftp-host=corn-syrup repo1-sftp-host-user=syscom repo1-path=/users/syscom/backups/coffee/pgbackrest repo1-sftp-private-key-file=/var/lib/postgresql/.ssh/id_ed25519 repo1-sftp-public-key-file=/var/lib/postgresql/.ssh/id_ed25519.pub repo1-sftp-host-key-hash-type=sha256 repo1-sftp-host-key-check-type=none start-fast=y log-level-console=info process-max=4 compress-type=lz4 [main] pg1-path=/var/lib/postgresql/15/main
The config above will keep two full backups and at least four differential backups. See https://pgbackrest.org/user-guide.html#retention for more details.
Next, open /etc/postgresql/15/main/postgresql.conf and add/edit the following lines:
archive_mode = on archive_command = 'pgbackrest --stanza=main archive-push %p'
See https://pgbackrest.org/user-guide.html#quickstart/configure-archiving for more details.
Next, restart Postgres:
systemctl restart postgresql@15-main
Switch to the postgres user, create the main stanza, and run the first backup:
su - postgres pgbackrest --stanza=main stanza-create pgbackrest --stanza=main check pgbackrest --stanza=main backup --type=full