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?
-n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog'
. It doesn't do what you think it does, and probably breaks something. Just delete that part, and let pg_dump do what needs to be done for the specified database./usr/local/pgsql/data
as $PGDATA./var/lib/pgsql/15/data
is the standard location.