Is there any way to pg_upgrade PostgreSQL 12 to 15 over network?

Question

My situation is a little bit complicated.

I have an older version of PostgreSQL 12.9 installed on an older Centos Stream 8 machine. There are about five custom databases created under the roof, say database1, ozssc, database5, owned by different roles to handle different business applications.

As our business application has been updated recently, we plan to update the database as well.

There is another newer machine, CentOS Stream 9 was set up, and PostgreSQL 15.2 was installed on the newer machine.

First, we tried to dump all database schema and data by using the newer version (15.2) command pg_dump from the newer machine:

pg_dump -h 10.0.1.105 -p 5433  -U postgres -v -n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog' $DB -Ft -f ${bkp_path}/${DB}_schema_bkp_${date}.tar

Which will dump 5 XXX.tar files

Then I tried to restore it (by using the same version (15.2) of pg_restore) to my newer machine (Centos Stream 9)

pg_restore -h 10.0.1.103 -p 5433 -U postgres -w -d $db_name $db_tar_file

Run those commands, system response error as:

pg_restore: error: could not execute query: ERROR: unacceptable schema name "pg_temp_1" DETAIL: The prefix "pg_" is reserved for system schemas. Command was: CREATE SCHEMA pg_temp_1; As result, there quite lot of database setting is incorrect as well:

pg_restore could not set the correct database owner from the dumped sql statement. For example the SQL statement in database ozssc as:

CREATE DATABASE ozssc WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_AU.UTF-8';
 ALTER DATABASE ozssc OWNER TO tomcat;

Those statement will change database ozssc owner to tomcat.

After restoration, I check the database ozssc's owner; it is still PostgreSQL instead of tomcat.

Another significant error is that it seems pg_dump does not dump any extensions. For example, there are about other three extensions, such as cutest, cube, and earth distance in the original database ozssc, but I don't find anything in dumped sql statement.

As this operation (pg_dump/restore) failed, I tried to look for pg_upgrade, but unfortunately. I found pg_upgrade only support some host upgrade by different installed directory.

I tried to go a work around:

I logged in to the newer version of host (Centos Stream 9, installed PostgreSQL 15.2), and mount remote file system (Centos Stream 8, PostgreSQL 12.9 installed) by using fuse and fuse-sshfs:

sshfs [email protected]:/var/lib/pgsql/data /mntsshfs/pgsql/12/data
sshfs [email protected]:/usr/bin /mntsshfs/pgsql/12/bin 

Then i run following commend on my newer host

pg_upgrade --old-datadir=/mntsshfs/pgsql/12/data --new-datadir=/usr/local/pgsql/data \
            --old-bindir=/mntsshfs/pgsql/12/bin --new-bindir=/usr/local/pgsql/bin \
            --old-options '-c config_file=/mntsshfs/pgsql/12/data/postgresql.conf' --new-options '-c config_file=/usr/local/pgsql/data/postgresql.conf' --check

response as:

/mntsshfs/pgsql/12/bin/postgres: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory

Seems mntsshfs/pgsql/12/bin/postgres (remote file system command) is trying to find libssl.so.1.1 that seating on local mounted machine? But there is no such version of libssl installed on newer host.

I believe this is quite common use case when company migrate different versions of PostgreSQL server over network on different hosts. Such older version of PostgreSQL and newer PostgreSQL seating on different machines.

Could anyone advise: How we can use pg_upgrade to migrate PostgreSQL 12 to 15 over the network instead of on the same host?

Answer 1

If you dump a database without specifying any of the -n or -N options, it will dump all the user schemas of the database. By using -n '*', you told PostgreSQL to dump system schemas, which it usually wouldn't do. These give you trouble during the restore.

Don't forget to additionally use pg_dumpall -r to dump the users.