HOAB

Problem :

When migrating a database, pg_upgrade failed with “could not connect, role <XXX> is unknown”.

My database was created with another user (postgresql)

Then transfered to another user (let's call it john)

The login authentication was password only until upgrade, where I put local to trust for upgrade.

The new destination database was created by john.

Solution :

The pg_upgrade can not use : 

• an old database with super role postgres
• a new database with super role john

The option “-U <username>” is applied to the both, so there is always one which is wrong.

So rename the old database super role to john (thanks to internet).

1. start the old database, pg_upgrade give the command in the log but !!

• Be careful ! there is a hidden option “-b” which prevent any modification :) remove it and all is ok

"/home/myuser/postgresql-9.6.2/bin/pg_ctl" -w -D "/home/myuser/database-introscope-9.6" -o "-p 50432  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/home/myuser'" start

Connect with : 

/home/myuser/postgresql-9.6.2/bin/psql -p 50432 -h /home/myuser -U postgres -d postgres

Check the super user role name : 

SELECT rolname FROM pg_roles WHERE oid = 10;

Create another super user

CREATE ROLE spiderman SUPERUSER LOGIN PASSWORD 'moreSecurePass';

Quit, connect with spideman, still on postgres database

/home/myuser/postgresql-9.6.2/bin/psql -p 50432 -h /home/myuser -U spideman -d postgres

Rename the original role :

alter role postgres rename to john;

Check the connection :

/home/myuser/postgresql-9.6.2/bin/psql -p 50432 -h /home/myuser -U john -d postgres

Drop spiderman role : 

DROP ROLE spiderman;

I learn to side tricks here : 

• -h <path> : give the path to the unix socket connection
• -b option : use for binary upgrade mode on pg_ctl (didn't see the option in the documentation)
• Owner role has value OID=10

Problem :

I need a relocatable / portable version of postgresql.

Disclaimer : if possible, use the OS distribution, it will be more up-to-date and sure.

That thing said : 

• I downloaded the source
• Compile it on my computer (Debian / Trixie / Sid)
• Package and run

Fail ! Obviously but here is why : 

1. Shared library are related to an absolute path
2. Glibc version on my computer is more recent than target server

Solution :

Two problems here and a few useful commands :

• ldd <program name>
  • Will give you if the program is static or dynamic
  • If dynamic, the list of libraries and the searched path, example (libpq is a library from postgres)

 libpq.so.5 => /lib/x86_64-linux-gnu/libpq.so.5 (0x00007f29f9786000)

• readelf -d <program name> : information on execution

0x0000000000000001 (NEEDED)             Shared library : [libpq.so.5]
0x0000000000000001 (NEEDED)             Shared library : [libm.so.6]
0x0000000000000001 (NEEDED)             Shared library : [libc.so.6]
0x000000000000001d (RUNPATH)            Library runpath :[/home/myuser/projects/packaging/postgresql/output/lib]
0x000000000000000c (INIT)               0x6000
0x000000000000000d (FINI)               0x162a0

• objdump -T <program name> | grep GLIBC : list dependencies on GLIBC

Once I had this information : 

• I compile postgresql on the target system : GLIBC 2.34, 2.33 were not available so postgresql compile without linking it
• I change the runpath of the executables with these commands :

# extract postgres sources
./configure --prefix=/home/myuser/postgresql/output --without-icu --without-readline --without-zlib --disable-rpath
export LD_RUN_PATH='$ORIGIN/../lib'
make
make install

It generates a postgresql version in  /home/myuser/postgresql/output

The readelf commands returns : 

 0x000000000000000f (RPATH)              Library rpath: [$ORIGIN/../lib]

So I was now able to package it.