WARNING! If your database is very large, you will need a lot of extra disk space (2x database size) and should be very careful with this upgrade!
We just landed the long waited PostgreSQL major version upgrade. Any site admins rebuilding Discourse from the command line will be upgraded to PostgreSQL 12 from the old PostgreSQL 10.
We are running this new version for a while on Meta, and everything is working fine. PostgreSQL 12 brings lots of improvements that will be automatically leveraged by Discourse.
Official Install Guide (single container)
On you next rebuild, you will see this message at the end:
Upgrade Complete ---------------- To complete the upgrade, rebuild again using: ./launcher rebuild app -------------------------------------------------------------------------------------
That means everything went well in the upgrade! You just need to issue a new rebuild to get your site back and running.
Data Container Install
If you are running a setup with a dedicated data container based in the sample supplied in our discourse_docker repository, you want to be sure you are shutting down PostgreSQL in a safe and clean way.
Nowadays, we have background jobs running queries spanning several minutes, so shutting down the web container will help the data container be shutdown safely.
./launcher stop web_only ./launcher stop data ./launcher rebuild data ./launcher rebuild data ./launcher rebuild web_only
Before issuing the first rebuild to the data container, you can tail the PostgreSQL log to see if it was shutdown properly.
tail -f shared/data/log/var-log/postgres/current should give you the following log if it was clean:
2020-05-13 18:33:33.457 UTC  LOG: received smart shutdown request 2020-05-13 18:33:33.464 UTC  LOG: worker process: logical replication launcher (PID 52) exited with exit code 1 2020-05-13 18:33:33.465 UTC  LOG: shutting down 2020-05-13 18:33:33.479 UTC  LOG: database system is shut down
Doing a manual update / space constrained environments
YOU MUST BACKUP THE POSTGRES_DATA BEFORE TRYING THIS
If you are in a constrained space environment without any way to get more space you can try the following:
./launcher stop app #(or both web_only and data if that is your case) mkdir -p /var/discourse/shared/standalone/postgres_data_new docker run --rm \ -v /var/discourse/shared/standalone/postgres_data:/var/lib/postgresql/10/data \ -v /var/discourse/shared/standalone/postgres_data_new:/var/lib/postgresql/12/data \ tianon/postgres-upgrade:10-to-12 mv /var/discourse/shared/standalone/postgres_data /var/discourse/shared/standalone/postgres_data_old mv /var/discourse/shared/standalone/postgres_data_new /var/discourse/shared/standalone/postgres_data ./launcher rebuild app #(or first data and then web_only if that is your case)
On my tests this procedure requires less than 1x your current database size in free space.
Postponing the update
If you need to postpone the update during your next rebuild, you can swap the PostgreSQL template on your app.yml file by changing
This is not recommended, as some site admins will forget to revert the change afterwards.
Post update optional tasks
Optimizing PostgreSQL statistics
After the update, the new PostgreSQL won’t have table statistics on hand. You can generate those using:
cd /var/discourse ./launcher enter app su postgres psql \connect discourse VACUUM VERBOSE ANALYZE; \q exit exit
Re-indexing your database
One of the highlights of PostgreSQL 12 is concurrent re-indexing, which means you can do this while you site is running. We were able to reduce the Meta database size by 20% doing this.
Just keep in mind that this operation uses quite a bit of resources, so I recommend doing this on the off hours, while your traffic is low.
cd /var/discourse ./launcher enter app su postgres psql \connect discourse REINDEX SCHEMA CONCURRENTLY public; \q exit exit
Cleaning up old data
You can delete the old data in PG10 format by removing the following folder:
cd /var/discourse rm -rf shared/standalone/postgres_data_old