How to Rollback a Database Upgrade

This article is about how to revert a database upgrade, and roll back to a previous version of Talkyard — also if the upgrade script did changes to the database, e.g. added new tables or columns.

Currently this throws away all data between the upgrade and the downgrade (because you'll overwrite the database with a backup).

You might first want to read: How to Rollback a Software-Only Upgrade.

Ok. To rollback the upgrade, log in to the Talkyard server and:

1. Disable automatic upgrades

See How to Rollback a Software-Only Upgrade, step 1.

2. Find the backup to restore

Just before upgrading to a newer version, Talkyard takes a backup of the database. We want to restore to that backup, but which one is it? Let's find out what version of Talkyard is currently running:

cd /opt/talkyard
grep -C3 VERSION .env

That'll print something like: VERSION_TAG=v0.2022.20-5c06cee44 (and some lines above and below).

This means you're currently using version v0.2022.20. Let's find the backup for the version you were running before that (which is not necessarily the previous version. Maybe you were running an even older version). List the database backups, sorted by time, recent first:

ls -t1 /opt/talkyard-backups/archives/*postgres.sql* | head -n11

In the result, ignore the -daily- backups, and instead find the backup with the highest v0.YYYY.MM version number but lower than your current version. Let's say ls -halt prints:

root@server:/opt/talkyard# ls -t1 /opt/talkyard-backups/archives/*postgres.sql* | head -n6
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-20T2049Z-v0.2022.18-d89a31486-postgres.sql.gz
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-20T1908Z-v0.2022.17-4481d9163-postgres.sql.gz
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-20T1807Z-v0.2022.16-f7f76378e-postgres.sql.gz
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-20T0210Z-daily-postgres.sql.gz   <—— ignore
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-19T0210Z-daily-postgres.sql.gz   <—— ignore
/opt/talkyard-backups/archives/tyw-gcen1aal19a-2022-11-18T1151Z-v0.2022.15-05084b4d6-postgres.sql.gz

Then, v0.2022.18-d89a31486 is the most recent backup, lower than v0.2022.20.

(The last part, -d89a31486 in this example, is a Git revision. You need to include it as well, below.)

3. Restore the backup

Briefly: You'll shut down the app server (it's the wrong version, and also, PostgreSQL refuses to restore the database, if the app server is still connected to it). And you'll take an extra backup. And then restore — this throws away anything that happened after the backup you're restoring.

Ok, here we go:

sudo -i # become root
cd /opt/talkyard
RESTORE_VERSION='FILL IN THIS'  # e.g. ='v0.2022.10.1-b13ba1601'
BACKUP_FILE_PATH="$( echo /opt/talkyard-backups/archives/*$RESTORE_VERSION-postgres.sql.gz )"

# Valid path? This should print exactly one line, namely the path to the PostgreSQL
# backup file, without any asterisk '*':
ls $BACKUP_FILE_PATH

echo "$(date -I): Downgrading, restoring backup: $BACKUP_FILE_PATH ..." | tee -a talkyard-maint.log

# Stop the app server; start the database.
docker-compose stop app
docker-compose up -d rdb

# Take an extra backup.
./scripts/backup.sh before_rollback

# Restore. WARNING: Overwrites any existing database.
zcat $BACKUP_FILE_PATH \
    | docker exec -i $(docker-compose ps -q rdb) psql postgres postgres \
    | tee -a talkyard-maint.log

4. Downgrade the App server

It'd be annoying if the app server upgraded the database again? — It'll do, unless we downgrade it. Edit the .env file:

vi /opt/talkyard/.env

Talkyard version:

Set VERSION_TAG to the version (including Git revision) you just restored, e.g.:

VERSION_TAG=v0.2022.10.1-b13ba1601

Release branch:

If the previous version is in another release branch, then change RELEASE_CHANNEL back to that other branch. E.g. to revert to branch tyse-v0.2022.10:

RELEASE_CHANNEL=tyse-v0.2022.10

By default, though, everyone uses tyse-v0-regular, and if you didn't change this, you can leave RELEASE_CHANNEL as is. (RELEASE_CHANNEL should be renamed to RELEASE_BRANCH, some day. [ty_v1])

5. Restart

Start everything and check the logs:   (don't use ./scripts/upgrade-if-needed.sh — we aren't upgrading)

docker-compose up -d
docker-compose logs -f --tail 333

6. What went wrong?

What was the reason you had to rollback? Talk with us here in the forum.