Postgresql upgrade and known problems

Problems with PostgreSQL upgrading

As this document covers all possible upgrade scenarios (e.g. from RHEL6 to RHEL6 Software Collections, upgrading from RHEL5 to RHEL7), please look at the part of document relevant to your case.

Table Of Contents:

• In-place upgrade problems
  • Locale changed between RHEL6 and RHEL7 (Symptoms, Workaround 1, Workaround 2)
  • Old server uses bad socket directory (Symptomps, Workaround)
• General problems
  • Plugin (module) incompatibilities, Solution

In-place upgrade

RHEL-6 to RHEL-7, locale changed

If you upgraded from RHEL-6 to RHEL-7 and your system locale (/etc/locale.conf) has changed in the meantime, the in-place upgrade will fail. This is known PostgreSQL problem for which is not good solution. Note that even if your locale changed from en_US.utf8 to en_US.utf-8, it will cause troubles.

Symptoms

# postgresql-setup upgrade
Redirecting to /bin/systemctl stop  postgresql.service
Upgrading database: failed

See /var/lib/pgsql/pgupgrade.log for details.
# tail /var/lib/pgsql/pgupgrade.log
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for large objects                                  ok
Creating catalog dump                                       ok

lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_US.utf8"
Failure, exiting

Workaround 1

Fix for This content is not included.bug #1055965 implements usage of the '$PGSETUP_INITDB_OPTIONS' variable in postgresql-setup. Using that variable you may easily affect the new database locale because options added to this variable are passed down to subsequent call of initdb. Advantages over next workaround: You may also adjust whatever option of initdb you want, e.g. maybe just collation by --lc-collate= option (when changing whole locale is not necessary). More info may be found in man initdb(1) and man postgresql-setup(1).

# PGSETUP_INITDB_OPTIONS="--locale=en_US.UTF-8" postgresql-setup upgrade
Redirecting to /bin/systemctl stop  postgresql.service
Upgrading database: OK

The configuration files was replaced by default configuration.
The previous configuration and data are stored in folder
/var/lib/pgsql/data-old.

See /var/lib/pgsql/pgupgrade.log for details.

Workaround 2

You are discouraged to do it but still, you may temporarily re-set the system locale back to the RHEL6 value (to en_US.UTF-8 from our symptoms). Once you finished the postgresql-setup upgrade run, please set back the previous value. For more info how to adjust your system locale please look at man localectl (1) (Red Hat Enterprise Linux 7).

Bad socket directory

If you are using one of the unix_socket_directory or unix_socket_directories options, you will very likely raise problems during in-place upgrade. To be more specific, you raise problems if you use non-default directory (not a /var/run/postgresql one). To work-around this, you should specify correct socket directory at least for the postgresql-setup time, see the workaround.

Symptoms:

Note that postgresql-setup upgrade silently lefts the old PostgreSQL server running in background, which blocks subsequent upgrade attempts.

[root@localhost ~]# postgresql-setup upgrade
Upgrading database: failed

See /var/lib/pgsql/pgupgrade.log for details.
[root@localhost ~]# cat /var/lib/pgsql/pgupgrade.log
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?


could not connect to old postmaster started with the command:
"/usr/lib64/pgsql/postgresql-8.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data-old" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c listen_addresses='' -c unix_socket_permissions=0700" start
Failure, exiting

Workaround:

1. Firstly, when you already tried to upgrade via postgresql-setup upgrade, please manage to stop the old server (the killall postgres should be OK now).

2. Run this command rather: PGSETUP_PGUPGRADE_OPTIONS="-o '-k /var/run/postgresql'" postgresql-setup upgrade

The old server will then be run with socket file created in proper directory.

General problems

Here are described problems for both in-place and dump&restore cases:

Third party plugin installed for older PostgreSQL server

PostgreSQL upstream same as RHEL do not guarantee plugin ABI compatibility between major PostgreSQL releases. That basically means that you can not take the same plugin compiled against older server and use it with the newer one. Worth saying here is also that even different versions of the same plugin may not be compatible with each other. So in any case, to make the upgrade as fluent as possible, you should manage to run even the newer server with the same set of plugins where all the plugins are rather of the same version.

Solution:

Should you use the third party plugin, you should either:

1. Obtain somehow or compile yourself the same plugin in the same version, or
2. remove properly plugin from the older database stack (upstream documentation),

before you start any upgrade actions. Note that safely removing plugin from database requires (except the optional plugin file removal) the plugin shall not be registered in any database in any running PostgreSQL server. For how to work with plugins look at CREATE|ALTER|DROP EXTENSION features.