3

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?

5
  • 1
    Don't do this: -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.
    – RonJohn
    Commented Mar 2, 2023 at 23:27
  • Also, I really would not use /usr/local/pgsql/data as $PGDATA. /var/lib/pgsql/15/data is the standard location.
    – RonJohn
    Commented Mar 2, 2023 at 23:32
  • Thank you for your advise, I installed PostgreSQL from source, so /usr/local/pgsql/data was used as $PGDATA
    – cidy.long
    Commented Mar 3, 2023 at 0:04
  • Out of curiosity, why from source instead of a distro package?
    – RonJohn
    Commented Mar 3, 2023 at 0:16
  • Need compile with other application under development environment.
    – cidy.long
    Commented Mar 3, 2023 at 1:08

1 Answer 1

0

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.

2
  • Tried to remove "-n '*'" section from pg_dump command, re-run it. pg_restore can restore extensions properly back, but database owner alter is still not work. Database owner is still "postgres" instead of "tomcat". I checked dumped tar file, there is an alter database owner to statement, but seems pg_restore not run that statement. Any idea?
    – cidy.long
    Commented Mar 4, 2023 at 6:09
  • That's a long-standing shortcoming of pg_dump (in my opinion): it never dumps database ownership or permissions unless you use the --create option. Commented Mar 6, 2023 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.