PostgreSQL 13 update

:warning: 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’ve just landed changes to upgrade our Docker image to PostgreSQL 13. Any site admins rebuilding Discourse from the command line will be upgraded to PostgreSQL 13 from the previous PostgreSQL 12. Note that if you held back from upgrading when the PostgreSQL 12 update happened back in May, you can skip that upgrade and go straight to PostgreSQL 13.

If you had held back the upgrade previously, change the PostgreSQL template in app.yml from templates/postgres.10.template.yml to templates/postgres.template.yml.

As with any upgrade, it is strongly advised to take a backup before doing anything.

Updating

Official Install Guide (single container)

On you next rebuild, you will see this message at the end:

-------------------------------------------------------------------------------------
UPGRADE OF POSTGRES COMPLETE

Old 12 database is stored at /shared/postgres_data_old

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.

Running a 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 [36] LOG:  received smart shutdown request
2020-05-13 18:33:33.464 UTC [36] LOG:  worker process: logical replication launcher (PID 52) exited with exit code 1
2020-05-13 18:33:33.465 UTC [47] LOG:  shutting down
2020-05-13 18:33:33.479 UTC [36] LOG:  database system is shut down

Doing a manual update / space constrained environments

:warning::warning::warning:
YOU MUST BACKUP THE POSTGRES_DATA BEFORE TRYING THIS
:warning::warning::warning:

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/12/data \
	-v /var/discourse/shared/standalone/postgres_data_new:/var/lib/postgresql/13/data \
	tianon/postgres-upgrade:12-to-13
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 "templates/postgres.template.yml" to "templates/postgres.12.template.yml".

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

Or this one-line version of the above:

/var/discourse/launcher run app "echo 'vacuum verbose analyze;' | su postgres -c 'psql discourse'"

Re-creating the indexes

This upgrade main feature is great file savings in our largest table in every instance, the post_timings table and it’s indexes. After doing a successful update you will need to run a command to re-build the indexes and reap the benefits.

cd /var/discourse
./launcher enter app
su postgres
psql
\connect discourse
REINDEX SCHEMA CONCURRENTLY public;
\q
exit
exit

If you can check the post_timings size before and after the REINDEX that would be a cool stat to share here!

You can use the below query to check the 20 largest data objects, run it before the reindex and after:

WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
    (select inhrelid, inhparent
    FROM pg_inherits
    UNION
    SELECT child.inhrelid, parent.inhparent
    FROM pg_inherit child, pg_inherits parent
    WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
    , TABLE_NAME
    , row_estimate
    , pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
    FROM (
         SELECT c.oid
              , nspname AS table_schema
              , relname AS TABLE_NAME
              , SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
              , SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
              , SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
              , SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
              , parent
          FROM (
                SELECT pg_class.oid
                    , reltuples
                    , relname
                    , relnamespace
                    , pg_class.reltoastrelid
                    , COALESCE(inhparent, pg_class.oid) parent
                FROM pg_class
                    LEFT JOIN pg_inherit_short ON inhrelid = oid
                WHERE relkind IN ('r', 'p')
             ) c
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  ) a
  WHERE oid = parent
) a
ORDER BY total_bytes DESC LIMIT 20;

Cleaning up old data

For a standard install, you can delete the old data in PG12 format with the following command:

cd /var/discourse
./launcher cleanup

If you have a separate data container, you’ll need to remove the backup copy like this:

rm -fr /var/discourse/shared/data/postgres_data_old/

FAQ

The source cluster was not shut down cleanly

If you get a upgrade failed with the above message, you can try a simpler approach to get it back into a better state.

Restart the old container with ./launcher start app. Wait a few minutes until it is back up.

Now shut it down again with ./launcher stop app. After that tail the logs to see if it was a clean one:

tail -f shared/data/log/var-log/postgres/current
2020-05-13 18:33:33.457 UTC [36] LOG:  received smart shutdown request
2020-05-13 18:33:33.464 UTC [36] LOG:  worker process: logical replication launcher (PID 52) exited with exit code 1
2020-05-13 18:33:33.465 UTC [47] LOG:  shutting down
2020-05-13 18:33:33.479 UTC [36] LOG:  database system is shut down

If the logs look like above, you can now try to upgrade again using ./launcher rebuild app.

lc_collate values for database “postgres” do not match

This errors happens if you are using non-default locales for your database. It was reported that you need 3 variables for it to succeed. Ensure that the env: section of you app.yml file has the 3 lines:

  LC_ALL: en_US.UTF-8
  LANG: en_US.UTF-8
  LANGUAGE: en_US.UTF-8

Changing en_US.UTF-8 to your locale.

Every rebuild does the upgrade again aka upgrade loop

When this happens your upgrade logs will contain

mv: cannot move '/shared/postgres_data' to '/shared/postgres_data_old/postgres_data': Directory not empty
mv: cannot move '/shared/postgres_data_new' to '/shared/postgres_data/postgres_data_new': Directory not empty

This means that there are still files from the last upgrade lingering around. Move those elsewhere before continuing.

Upgrade Complete suggestion scripts - do I need to do anything?

Once the upgrade completes, you’ll see output from the pg_upgrade message saying:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

You can safely ignore this message.

I did skip the PostgreSQL 12 update, what to do now?

You can follow the standard instructions at the top of this guide and they will upgrade from your version to 13 without issues.

If you are following the space constrained instructions, adapt the version numbers accordingly.

27 Likes

Hi and thanks for developing Discourse.

I’m running an instance with two containers, one for web and one for data. I had previously skipped upgrading to postgresql 12 and I’m now trying to get from 10 to 13 with ./launcher rebuild data. My data.yml does include the correct template "templates/postgres.template.yml".

The postgresql upgrade fails with

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

The source cluster was not shut down cleanly.
Failure, exiting
-------------------------------------------------------------------------------------
UPGRADE OF POSTGRES FAILED

I also tried first stopping the container then rebuilding it, but this failed as well.

Here’s the full console output: Debian Pastezone

I noticed that the rebuild command procedure only brings postgresql-10 and postgresql-client-10, according to discourse_docker/postgres.template.yml at master · discourse/discourse_docker · GitHub .

The following additional packages will be installed:
  postgresql-client-10
Suggested packages:
  postgresql-doc-10
The following NEW packages will be installed:
  postgresql-10 postgresql-client-10
0 upgraded, 2 newly installed, 0 to remove and 4 not upgraded.

Doesn’t this mean we’re missing version 13 packages?

1 Like

That is covered in the OP FAQ session, the gist of it is that you need to cleanly stop the PostgreSQL server.

  1. Restart your database and site ./launcher start data and ./launcher start web_only
  2. Wait for the site to be up and running.
  3. Stop the app container with ./launcher stop web_only
  4. Stop the data container with ./launcher stop data
  5. Try a rebuild of data now ./launcher rebuild data.
  6. After the two rebuilds, restart the web_only ./launcher start web_only
5 Likes

You’re absolutely right, I did miss the fact that both containers should be stopped. Rebuild worked just fine after following the instructions. Thanks for your prompt response. :slightly_smiling_face:

4 Likes

The pg_upgrade command requires the old version binaries to be installed as a part of the upgrade process. The new version packages are already installed as a part of the base image.

4 Likes

I ran into this problem when upgrading:


The following additional packages will be installed:
  postgresql-client-12
Suggested packages:
  postgresql-doc-12
The following NEW packages will be installed:
  postgresql-12 postgresql-client-12
0 upgraded, 2 newly installed, 0 to remove and 5 not upgraded.
Need to get 16.1 MB of archives.
After this operation, 54.0 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-client-12 amd64 12.5-1.pgdg100+1 [1,422 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-12 amd64 12.5-1.pgdg100+1 [14.7 MB]
Fetched 16.1 MB in 14s (1,152 kB/s)
Selecting previously unselected package postgresql-client-12.
(Reading database ... 43899 files and directories currently installed.)
Preparing to unpack .../postgresql-client-12_12.5-1.pgdg100+1_amd64.deb ...
Unpacking postgresql-client-12 (12.5-1.pgdg100+1) ...
Selecting previously unselected package postgresql-12.
Preparing to unpack .../postgresql-12_12.5-1.pgdg100+1_amd64.deb ...
Unpacking postgresql-12 (12.5-1.pgdg100+1) ...
Setting up postgresql-client-12 (12.5-1.pgdg100+1) ...
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/13/man/man1/psql.1.gz because link group psql.1.gz is broken
Setting up postgresql-12 (12.5-1.pgdg100+1) ...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Ver Cluster Port Status Owner    Data directory              Log file
12  main    5433 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/13/man/man1/postmaster.1.gz because link group postmaster.1.gz is broken
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.
Processing triggers for postgresql-common (223.pgdg100+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Stopping PostgreSQL 12 database server: main.
Stopping PostgreSQL 13 database server: main.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
  discourse
  postgres
  template1
                                                            ok

lc_collate values for database "postgres" do not match:  old "en_US.UTF-8", new "en_GB.UTF-8"
Failure, exiting
-------------------------------------------------------------------------------------
UPGRADE OF POSTGRES FAILED

Please visit https://meta.discourse.org/t/postgresql-13-update/172563 for support.

You can run ./launcher start app to restart your app in the meanwhile




FAILED
--------------------
Pups::ExecError: /root/upgrade_postgres failed with return #<Process::Status: pid 46 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn'
exec failed with the params "/root/upgrade_postgres"
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

I have this in containers/app.yml:

env:
  LC_ALL: en_GB.UTF-8
  LANG: en_GB.UTF-8
  LANGUAGE: en_GB.UTF-8
1 Like

@chrisc

Take a look at this:

Locale support is automatically initialized when a database cluster is created using initdb . initdb will initialize the database cluster with the locale setting of its execution environment by default, so if your system is already set to use the locale that you want in your database cluster then there is nothing else you need to do. If you want to use a different locale (or you are not sure which locale your system is set to), you can instruct initdb exactly which locale to use by specifying the --locale option.

3 Likes

Thanks @DBHacker, on another server I set the upgrade off via ./launcher rebuild app and while the Docker images were downloading I edited all the templates/postgres*yml files to add --locale=en_GB.UTF-8 to all initdb commands and this appears to have done the trick.

2 Likes

However editing the PostgreSQL templates hasn’t fixed the issue with the original server as reported in the post above, I have edited al the PostgreSQL templates to add the locale but when rebuilding the app I get:

I, [2021-01-06T16:28:50.773835 #1]  INFO -- : Upgrading PostgreSQL from version 12 to 13                                                                  
The files belonging to this database system will be owned by user "postgres".                                                                             
This user must also own the server process.                                                                                                               

The database cluster will be initialized with locale "en_GB.UTF-8".                                                                                       
The default database encoding has accordingly been set to "UTF8".                                                                                         
The default text search configuration will be set to "english".                                                                                           

Data page checksums are disabled.                                                                                                                         

fixing permissions on existing directory /shared/postgres_data_new ... ok                                                                                 
creating subdirectories ... ok                                                                                                                            
selecting dynamic shared memory implementation ... posix                                                                                                  
selecting default max_connections ... 100                                                                                                                 
selecting default shared_buffers ... 128MB                                                                                                                
selecting default time zone ... Etc/UTC                                                                                                                   
creating configuration files ... ok                                                                                                                       
running bootstrap script ... ok                                                                                                                           
performing post-bootstrap initialization ... ok                                                                                                           
syncing data to disk ... ok                                                                                                                               


Success. You can now start the database server using:                                                                                                     

    /usr/lib/postgresql/13/bin/pg_ctl -D /shared/postgres_data_new -l logfile start                                                                       

Get:1 http://security.debian.org/debian-security buster/updates InRelease [65.4 kB]                                                                       
Hit:2 http://deb.debian.org/debian buster InRelease                                                                                                       
Get:3 http://deb.debian.org/debian buster-updates InRelease [51.9 kB]                                                                                     
Get:4 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease [104 kB]                                                                              
Get:5 https://deb.nodesource.com/node_10.x buster InRelease [4,584 B]                                                                                     
Get:6 http://deb.debian.org/debian buster-updates/main amd64 Packages.diff/Index [4,672 B]                                                                
Get:7 http://deb.debian.org/debian buster-updates/main amd64 Packages 2020-12-24-1401.30.pdiff [286 B]                                                    
Get:7 http://deb.debian.org/debian buster-updates/main amd64 Packages 2020-12-24-1401.30.pdiff [286 B]                                                    
Get:8 http://security.debian.org/debian-security buster/updates/main amd64 Packages [260 kB]                                                              
Get:9 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 Packages [216 kB]                                                                    
Get:10 https://deb.nodesource.com/node_10.x buster/main amd64 Packages [764 B]                                                                            
Fetched 707 kB in 3s (249 kB/s)                                                                                                                           
Reading package lists...                                                                                                                                  
Reading package lists...                                                                                                                                  
Building dependency tree...                                                                                                                               
Reading state information...                                                                                                                              
The following additional packages will be installed:                                                                                                      
  postgresql-client-12                                                                                                                                    
Suggested packages:                                                                                                                                       
  postgresql-doc-12                                                                                                                                       
The following NEW packages will be installed:                                                                                                             
  postgresql-12 postgresql-client-12                                                                                                                      
0 upgraded, 2 newly installed, 0 to remove and 5 not upgraded.                                                                                            
Need to get 16.1 MB of archives.                                                                                                                          
After this operation, 54.0 MB of additional disk space will be used.                                                                                      
Get:1 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-client-12 amd64 12.5-1.pgdg100+1 [1,422 kB]                               
Get:2 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-12 amd64 12.5-1.pgdg100+1 [14.7 MB]                                       
Fetched 16.1 MB in 58s (280 kB/s)                                                                                                                         
Selecting previously unselected package postgresql-client-12.                                                                                             
(Reading database ... 43899 files and directories currently installed.)                                                                                   
Preparing to unpack .../postgresql-client-12_12.5-1.pgdg100+1_amd64.deb ...                                                                               
Unpacking postgresql-client-12 (12.5-1.pgdg100+1) ...                                                                                                     
Selecting previously unselected package postgresql-12.                                                                                                    
Preparing to unpack .../postgresql-12_12.5-1.pgdg100+1_amd64.deb ...                                                                                      
Unpacking postgresql-12 (12.5-1.pgdg100+1) ...                                                                                                            
Setting up postgresql-client-12 (12.5-1.pgdg100+1) ...                                                                                                    
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/13/man/man1/psql.1.gz because link group psql.1.gz is broken
Setting up postgresql-12 (12.5-1.pgdg100+1) ...                                                                                                           
Creating new PostgreSQL cluster 12/main ...                                                                                                               
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5                                                        
The files belonging to this database system will be owned by user "postgres".                                                                             
This user must also own the server process.                                                                                                               

The database cluster will be initialized with locale "C.UTF-8".                                                                                           
The default database encoding has accordingly been set to "UTF8".                                                                                         
The default text search configuration will be set to "english".                                                                                           

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 12 main start

Warning: The selected stats_temp_directory /var/run/postgresql/12-main.pg_stat_tmp
is not writable for the cluster owner. Not adding this setting in
postgresql.conf.
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5433 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/13/man/man1/postmaster.1.gz because link group postmaster.1.gz is broken
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.
Processing triggers for postgresql-common (223.pgdg100+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Stopping PostgreSQL 12 database server: main.
Stopping PostgreSQL 13 database server: main.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

The source cluster was not shut down cleanly.
Failure, exiting
-------------------------------------------------------------------------------------
UPGRADE OF POSTGRES FAILED

Please visit https://meta.discourse.org/t/postgresql-13-update/172563 for support.

You can run ./launcher start app to restart your app in the meanwhile




FAILED
--------------------
Pups::ExecError: /root/upgrade_postgres failed with return #<Process::Status: pid 47 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn'
exec failed with the params "/root/upgrade_postgres"
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

Does anyone have any suggestions regarding how best to fix the problem with the PostgreSQL upgrade I appear to have?

1 Like

When this happens you need to follow the FAQ entry in the OP. Restart the old container, wait a while, stop it, and try again.

4 Likes

Thanks, all sorted now.

For others that have issues around this you can check the existing PostgreSQL locale like this:

./launcher enter app
su postgres
psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges    
-----------+----------+----------+-------------+-------------+------------------------
 discourse | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | discourse=CTc/postgres
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

exit
exit
5 Likes

Has this been going relatively smoothly for everyone? I’ve been too scared to upgrade even tho it went fine last time - upgrading PG always makes me nervous.

Is there any kind of rollback feature that we can make use of it it goes awry? (If not, wonder if the team could build something like this for future updates?)

1 Like

The first one I did worked with my existing tooling that handled the last upgrade. I had one fail due to disk space. I changed to the pg12 template and will try again now that I’ve deleted a bunch of unecessary backups plus the extra PG copy from the last upgrade.

It makes a copy of the old PG in postgres_data_old, so you can use that and the PG12 template.

4 Likes

I just followed the instructions and it worked. I had to clear some space at one point but was prompted with the command to use. I got some warnings which I asked about here but assume they are nothing to worry about because they got deleted from this topic before anyone answered and the forum still seems to work! :slight_smile:

6 Likes