Upgrading postgresql database from 11 to use Postgresql 15

ProServ · Aug 1, 2024

Hi, when this FreeBSD Server was upgraded from 11 to 14.0, when one must run portmaster -af it deleted postgresql 11 and now 11 will not install.

Installed Postreql server/client 15 and of course postgres will not run because the DB is from 11.

Have searched to find how to upgrade this DB with pg_upgrade. In all cases, instructions say to run pg_upgrade with --old-datadir and --old-bindir etc. etc.

There is ONLY one dir /var/db/postgres/data11/ and as for bin dir, I am unsure where that is. But in any event, there is ONLY:


pkg info postgresql\*
postgresql-odbc-16.00.0000
postgresql15-client-15.7
postgresql15-server-15.7

Would love to get help to get this DB upgraded if possible.

Thanks in advance.

Alain De Vos · Aug 1, 2024

find /var/db | grep postgres

sko · Aug 1, 2024

pg_upgrade only works if you have both versions installed - which does not work on FreeBSD (at least not from standard ports/packages) or any other OS that respects hier(7) and installs binaries into the correct path, so there are no distinct 'bin dirs' for different versions...
The only sane way is to use gp_dumpall and restore from that; however, you need to run pg_dumpall *before* you remove the old PostgreSQL version.

I always found it massively annoying that PostgreSQL has no way of importing/migrating databases from older versions, but OTOH PostgreSQL is the only database I can halfway tolerate/get along if I absolutely need some database. Otherwise I avoid them like the plague they are...

BjarneB · Aug 1, 2024

Take a look in /usr/ports/UPDATING, 20240723. I just did that same procedure from PostgreSQL 15 to 16 .
you might have the old pkg in /var/cache/pkg and extract it into /tmp/pg-upgrade

VladiBG · Aug 1, 2024

Here's my old notes about the upgrading:

----
pg_upgrade
---
Both postgre13 and postgre15 must be build with the same version of ICU

service postgresql stop
pkg create postgresql13-server postgresql13-client
rm -dR /tmp/pg-upgrade
mkdir /tmp/pg-upgrade
tar xf postgresql10-server-13.12.txz -C /tmp/pg-upgrade/
tar xf postgresql10-contrib-13.12.txz -C /tmp/pg-upgrade
tar xf /var/cache/pkg/icu-73.2,1.pkg -C /tmp/pg-upgrade

pkg delete postgresql13-client
------ v10 -> v11
su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 "
su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data10/ -B /usr/local/bin/ -D /var/db/postgres/data11/ "
su -l postgres -c "/var/db/postgres/analyze_new_cluster.sh"
#### WARNING ### this will delete the old data10 folder.
su -l postgres -c "/var/db/postgres/delete_old_cluster.sh"

/usr/local/etc/rc.d/postgresql initdb
----------- v13 -> v15

su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data13/ -B /usr/local/bin/ -D /var/db/postgres/data15/ "
su -l postgres -c "/var/db/postgres/analyze_new_cluster.sh"
#### WARNING ### this will delete the old data13 folder.
su -l postgres -c "/var/db/postgres/delete_old_cluster.sh"

pg_hba.conf

www.postgresql.org/docs/current/upgrading.html

ProServ · Aug 1, 2024

sko said:
BjarneB said:

Take a look in /usr/ports/UPDATING, 20240723. I just did that same procedure from PostgreSQL 15 to 16 .
you might have the old pkg in /var/cache/pkg and extract it into /tmp/pg-upgrade

Click to expand...

BjarneB said:

Take a look in /usr/ports/UPDATING, 20240723. I just did that same procedure from PostgreSQL 15 to 16 .
you might have the old pkg in /var/cache/pkg and extract it into /tmp/pg-upgrade

Click to expand...

pg_upgrade only works if you have both versions installed - which does not work on FreeBSD (at least not from standard ports/packages) or any other OS that respects hier(7) and installs binaries into the correct path, so there are no distinct 'bin dirs' for different versions...
The only sane way is to use gp_dumpall and restore from that; however, you need to run pg_dumpall *before* you remove the old PostgreSQL version.

I always found it massively annoying that PostgreSQL has no way of importing/migrating databases from older versions, but OTOH PostgreSQL is the only database I can halfway tolerate/get along if I absolutely need some database. Otherwise I avoid them like the plague they are...

Hi, looking at /var/cache/pkg it has the client but not the server for some reason....

postgresql11-client-11.5-80ba5ff49c.txz
postgresql11-client-11.7-847740cad2.txz
postgresql11-client-11.7-8f92af07db.txz
postgresql11-client-11.7-e713ab4d5c.txz
postgresql11-client-11.7.txz
postgresql12-client-12.19.pkg
postgresql12-client-12.19~05f3101350.pkg
postgresql12-server-12.19.pkg
postgresql12-server-12.19~6a08ee7335.pkg
postgresql15-client-15.7.pkg
postgresql15-client-15.7~abf652832c.pkg
postgresql15-server-15.7.pkg
postgresql15-server-15.7~3018069269.pkg
postgresql15-server-15.7~5f3163a45e.pkg
postgresql95-client-9.5.19-1beb3c248d.txz

Have to find out if I can restore the VM from it's backups while the running FreeBSD VM with the postgres DB issue has same ID. If we can, then I can take current dump and/or dumpall with postgresql11. I am not sure if postgresql 15 is available on a 11.3 FreeBSD server. And perhaps in /var/cache/pkg there is the postgresql11-server file or pkg.

Thanks.

rootbert · Aug 1, 2024

postgresql11 is end of life since December 2023. You could just create a full dump on your postgres11 and restore it according to https://www.postgresql.org/docs/16/backup-dump.html#BACKUP-DUMP-RESTORE

edit: you could just work around your issue by creating a FreeBSD11 jail on your FreeBSD14 host, and install the postgresql11 packages that you have cached, then dump the DB server and import that dump on your postgresql15 on the host

T-Aoki · Aug 1, 2024

BjarneB said:
Take a look in /usr/ports/UPDATING, 20240723.

Intentionally link to the commit on main (aka latest) branch.
With this procedure, you can upgrade your databases even if you already and intentionally deleted pkg for old version, as it includes the procedure to create pkgs from installed version, extract them into temporary (non-standard) place, then delete old version.
You can specify your actual old and new versions instead of 15 and 16, if postgresql support upgrading databases from your older version to the upgraded version.

Note that, /tmp/pg-upgrade directory SHALL be allowed to execute programs in it. If /tmp is mounted with execution prohibited, you MUST specify any other non-default directory with execution is allowed. Otherwise, the procedure is promised to be fail, if I understand correctly.

ProServ · Aug 4, 2024

The output of # ls below is from the now 14.1 FreeBSD Server. The dumpall and dump of the single DB has date of 2017. And I know there has not been much or any updates of the content of those two DB's. The thinking is the postgresql version was either 11 or 12

/usr/ports/distfiles/postgresql # ls
Pg-2.1.1.tar.gz postgresql-15.7.tar.bz2 psqlodbc-09.06.0310.tar.gz psqlodbc-16.00.0000.tar.gz
postgresql-11.8.tar.bz2 postgresql-9.5.20.tar.bz2 psqlodbc-09.06.0500.tar.gz
postgresql-12.19.tar.bz2 postgresql-9.5.21.tar.bz2 psqlodbc-10.02.0000.tar.gz
postgresql-13.15.tar.bz2 postgresql-9.5.22.tar.bz2 psqlodbc-10.03.0000.tar.gz

# vim pgdumpall-latest.sql doesn't show a version number. Also the dump of pg database needed doesn't have a version number in the file. What is recommended to do?

Thanks.

ProServ · Aug 4, 2024

T-Aoki said:
Intentionally link to the commit on main (aka latest) branch.
With this procedure, you can upgrade your databases even if you already and intentionally deleted pkg for old version, as it includes the procedure to create pkgs from installed version, extract them into temporary (non-standard) place, then delete old version.
You can specify your actual old and new versions instead of 15 and 16, if postgresql support upgrading databases from your older version to the upgraded version.

Note that, /tmp/pg-upgrade directory SHALL be allowed to execute programs in it. If /tmp is mounted with execution prohibited, you MUST specify any other non-default directory with execution is allowed. Otherwise, the procedure is promised to be fail, if I understand correctly.

Hi, I read the link you placed above. Does it mean with postgresql15 currently installed just run the following:


# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"
# su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

So, rsync /usr/ports/distfiles/postgresql/postgresql-11.8.tar.bz2 /tmp/pg-upgrade and then run the commands above?

Thanks for your help.

VladiBG · Aug 4, 2024

Both version of the SQL must be build against the same libs. If you try to start pg_upgrade it will give you error as it will miss the old libs. The easy way in your case it to boot the backup of the VM and make a dump of the db then restore it on the new VM.

freebuser · Aug 4, 2024

ProServ said:
Hi, I read the link you placed above. Does it mean with postgresql15 currently installed just run the following:

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres" # su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

So, rsync /usr/ports/distfiles/postgresql/postgresql-11.8.tar.bz2 /tmp/pg-upgrade and then run the commands above?

Thanks for your help.

I just copy and paste each commands from the link and it works perfectly.

ProServ · Aug 5, 2024

Hi, in the current LIVE FreeBSD VM, installed is:

Code:

# pkg info |grep postgres\*
postgresql-odbc-16.00.0000
postgresql15-client-15.7
postgresql15-server-15.7

Code:

:/var/db/postgres/data11 # ls
PG_VERSION              pg_hba.conf             pg_replslot             pg_subtrans             postgresql.auto.conf
base                    pg_ident.conf           pg_serial               pg_tblspc               postgresql.conf
global                  pg_logical              pg_snapshots            pg_twophase
pg_commit_ts            pg_multixact            pg_stat                 pg_wal
pg_dynshmem             pg_notify               pg_stat_tmp             pg_xact

There is NO /var/db/postgres/data15

The weird part is, created (mkdir) /tmp/pg_upgrade and rsync'd /usr/ports/distfiles/postgresql-11.8.tar.bz2 /tmp/pg_upgrade

While writing this, /tmp/pg_upgrade magically contains....

 

:/tmp/pg-upgrade # ls

Code:

+COMPACT_MANIFEST       +MANIFEST               postgresql-11.8.tar.bz2 usr

I am the only one able to access this server. I did not run bunzip on postgresql-11.8.tar.bz2
How tthe other files and usr DIR is there is beyond me.

In any event will try running the codes from the link posted by T-Aoki above

 

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"

# su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

ProServ · Aug 5, 2024

Tried running the first command and it failed

Code:

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
  provider:    libc
  LC_COLLATE:  C
  LC_CTYPE:    C.UTF-8
  LC_MESSAGES: C.UTF-8
  LC_MONETARY: C.UTF-8
  LC_NUMERIC:  C.UTF-8
  LC_TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

initdb: error: directory "/var/db/postgres/data11" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/db/postgres/data11" or run initdb with an argument other than "/var/db/postgres/data11".

SirDice · Aug 5, 2024

Initdb needs to be run with the new database directory, /var/db/postgres/data15 in this case.

freebuser · Aug 5, 2024

Just posting the code just in case.
Please change the postgresql version as appropriate.


 # service postgresql stop
# pkg create postgresql11-server postgresql11-contrib
# mkdir /tmp/pg-upgrade
# tar xf postgresql11-server-11.pkg -C /tmp/pg-upgrade

# tar xf postgresql11-contrib-11.pkg -C /tmp/pg-upgrade

# pkg delete -f databases/postgresql11-server databases/postgresql11-contrib databases/postgresql11-client

# pkg install databases/postgresql15-server databases/postgresql15-contrib

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data15 -U postgres"
# su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data16/ -U postgres "
# service postgresql start

SirDice · Aug 5, 2024

freebuser said:
# pkg create postgresql15-server postgresql15-contrib

This might be problematic if you upgraded the packages after you created them. You can use pkg fetch postgresql15-server postgresql15-contrib instead, and extract the files from /var/cache/pkg/

Probably not going to help the OP though, PostgreSQL 11 was removed some time ago.

ProServ · Aug 6, 2024

SirDice said:
Initdb needs to be run with the new database directory, /var/db/postgres/data15 in this case.

Hi SirDice,
Would you be so kind as to give me the correct command to initdb and what do you mean by [/file] .. what file?

adorno · Aug 6, 2024

Unless you need to do anything special (and then you'd know it), service postgresql initdb should get you going.

SirDice · Aug 6, 2024

ProServ said:
Would you be so kind as to give me the correct command to initdb and what do you mean by [/file] .. what file?

They've been posted multiple times already, you just have to change the data1* directory to match your situation.

ProServ · Aug 7, 2024

Hi,
Ran
# service postgresql initdb
# /usr/local/bin/pg_ctl -D /var/db/postgres/data15 -l logfile start
# /usr/local/etc/rc.d/postgresql start

Subsequently ran:

Code:

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data15 -U postgres"

and again it complains about data15 not empty
initdb: error: directory "/var/db/postgres/data15" exists but is not empty

 # cd /var/db/postgres

# ls

data11  data15

VladiBG · Aug 7, 2024

You are trying to create two times the data15 with initdb. This is what are you doing wrong.

ProServ · Aug 7, 2024

SirDice said:
They've been posted multiple times already, you just have to change the data1* directory to match your situation.

Hi SirDice,
Change the command to use data11?

Code:

su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"

Thanks.

ProServ · Aug 7, 2024

VladiBG said:
You are trying to create two times the data15 with initdb. This is what are you doing wrong.

Hi VladiBG,
Ran it as:

Code:

 # su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"

Result:

initdb: error: directory "/var/db/postgres/data11" exists but is not empty

It's giving the same error to both data DIRs.
Thanks.

ProServ · Aug 7, 2024

Ok try again. This time with 2nd command...

Code:

/var/db/postgres # su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

Now it cannot find libssl.so.8

Code:

no data was returned by command ""/tmp/pg-upgrade/usr/local/bin/postgres" -V"

check for "/tmp/pg-upgrade/usr/local/bin/postgres" failed: cannot execute
Failure, exiting

Going to try what SirDice wrote in https://forums.freebsd.org/threads/...-libssl-so-8-not-found-required-by-pkg.69457/ for this issue.

Code:

pkg-static install -f pkg
pkg upgrade -f

WTF
# pkg-static install -f
Usage: pkg install [-AfInFMqRUy] [-r reponame] [-Cgix] <pkg-name> ...

Ran

Code:

 # pkg-static upgrade -f

Try again with

Code:

 su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data15/ -U postgres "

JFC!
ld-elf.so.1: Shared object "libssl.so.8" not found, required by "postgres"
no data was returned by command ""/tmp/pg-upgrade/usr/local/bin/postgres" -V"

Removed postgresql15 server and client.

Code:

# pkg install postgresql16-server
# portmaster databases/postgresql16-contrib

Ran

Code:

# service postgresql initdb

Ran

Code:

# su -l postgres -c "/usr/local/bin/initdb --encoding=utf-8 --lc-collate=C -D /var/db/postgres/data11 -U postgres"

it fails with
initdb: error: directory "/var/db/postgres/data11" exists but is not empty

Also tried

Code:

# su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /var/db/postgres/data11/ -B /usr/local/bin/ -D /var/db/postgres/data16/ -U postgres "

It fails with
ld-elf.so.1: Shared object "libssl.so.8" not found, required by "postgres"
no data was returned by command ""/tmp/pg-upgrade/usr/local/bin/postgres" -V"

How can we get past this??

Thanks.

Upgrading postgresql database from 11 to use Postgresql 15

ProServ

Alain De Vos

sko

BjarneB

VladiBG

ProServ

rootbert

T-Aoki

ProServ

ProServ

VladiBG

freebuser

ProServ

ProServ

SirDice

Administrator

freebuser

SirDice

Administrator

ProServ

adorno

SirDice

Administrator

ProServ

VladiBG

ProServ

ProServ

ProServ