HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

postgres pg_upgrade 9.2 to 9.5 problem

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
problempg_upgradepostgres

Problem

I'm trying to upgrade from 9.2 to 9.5 using the pg_upgrade tool.

I'm using CentOS 7.0

current postgres bin folder: /opt/rh/postgresql92/root/usr/bin
new postgres bin folder: /opt/rh/rh-postgresql95/root/usr/bin
current data folder: /mnt/postgres/data
new data folder: /mnt/postgres/data95


I've set the LD_LIBRARY_PATH=/opt/rh/rh-postgresql95/root/usr/lib64

When I run

/opt/rh/rh-postgresql95/root/usr/bin/pg_upgrade --link --check --old-bindir=/opt/rh/postgresql92/root/usr/bin --new-bindir=/opt/rh/rh-postgresql95/root/usr/bin --old-datadir=/mnt/postgres/data --new-datadir=/mnt/postgres/data95


I get this message :

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   /opt/rh/postgresql92/root/usr/bin/pg_ctl: error while loading shared libraries: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory

Could not get pg_ctl version data using "/opt/rh/postgresql92/root/usr/bin/pg_ctl" --version: No such file or directory
Failure, exiting


I'm not sure why it is trying to use the 9.2 version of pg_ctl

which pg_ctl


returns

/opt/rh/rh-postgresql95/root/usr/bin/pg_ctl


What have I done wrong?

Solution

The issue repeated for me on a legacy PostgreSQL 9.2 to 11 upgrade. The pg_upgrade would fail with: FATAL: unrecognized configuration parameter "unix_socket_directory"

Blog entry at https://altinukshini.wordpress.com/2018/05/03/upgrade-postgres-9-2-to-9-6-in-centos-7-bold-lines/ gives the solution, ditto:

mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >>     
   /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl


Run now the pg_upgrade (as postgres user), the unix_socket_directories error should be gone.

Lastly, (as root) revert:

mv -f /usr/bin/pg_ctl{-orig,}

Code Snippets

mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >>     
   /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Context

StackExchange Database Administrators Q#197698, answer score: 4

Revisions (0)

No revisions yet.