Upgrading postgresql database from 11 to use Postgresql 15

I just upgraded a PostgreSQL 13 server to version 15 via pg_dumpall (which as already said is IMHO the only sane way...).
You can basically follow the PostgreSQL documentation 1:1 for that:

Code: 
# su -l postgres -c "pg_dumpall > /var/db/postgres/2024-08-07_9:22.dump"
# service postgresql stop
# pkg install postgresql15-server
# service postgresql initdb
# cp -a /var/db/postgresql/data13/pg_hba.conf /var/db/postgresql/data15/
# cp -a /var/db/postgresql/data13/postgresql.conf /var/db/postgresql/data15/
# su -l postgres -c "psql -d postgres -f /var/db/postgres/2024-08-07_9:22.dump"

Downtime including the import of the 2GB dump was less than 4 minutes according to zabbix.

I could have also cloned the jail (or set up a new one) and prepared the PostgreSQL 15 server there and then used something like nc -l a.b.c.d 666| pqsl -d postgres on the 15 jail and pg_dumpall | nc a.b.c.d 666 on the old jail to directly send and import the dump into the new installation, but if I couldn't afford such a minimal downtime on that database I'd run a cluster.
 
ProServ said:
Now it cannot find libssl.so.8
Click to expand...
because the old binary was built against an ancient OpenSSL version. pg_upgrade *might* work for versions that have been released/built within a relatively short timespan (i.e. both versions are still available from ports/pkg), but otherwise you will always run into missing or outdated dependencies...
 
ProServ said:
initdb: error: directory "/var/db/postgres/data11" exists but is not empty
Click to expand...
Why are you not understanding you need to initialize the new data directory of the new version?

Whenever you copy data from an old disk to a new disk, do you format (i.e. initialize) the new disk or the old one? It's the same idea here. You initialize the new database so you can copy (migrate in this case) data from the old to the new.
 
Going to restore the original VM and start new ticket based on restored vm without any updates/upgrades
 
Have restored the original, FreeBSD 11.3 and have ran
Code: 
# freebsd-update fetch
# freebsd-update install
followed by
# freebsd-update -r 13.1-RELEASE upgrade

Now at the point where one must run # pkg-static upgrade -f

I believe that is where pkg will remove postgresql11 and I do not want it removed. Question is how to prevent the installed postgreql from removal?

Thanks,
 
PostgreSQL upgrading isn't particularly easy. The live method requires both versions to be running at the same time, which is difficult on FreeBSD, as you've noticed.

VladiBG is asking if you made a full DB backup because the pg_dumpall/initdb/load method is the most reliable, so if you've backed up, uninstalling 11 will be ok because 15 will load the dump and everything will hopefully be fine.

If you're not sure about it, run another copy of FreeBSD and do a test restore with a dump.
 
So, the original server is back, FreeBSD 11 and running PostgreSQL 11? Was PostgreSQL 15 already available then? If there is I would do the database upgrade first, then the OS upgrade. Or pick an intermediate PostgreSQL version that's available for both FreeBSD 11 and 14 (PostgreSQL 13?), then the OS upgrade, then another DB upgrade.

msplsh said:
The live method requires both versions to be running at the same time, which is difficult on FreeBSD, as you've noticed.
Click to expand...
It's not exactly "live", neither service is actually running. You just need to have access to the binaries of both versions. The only "problem" is that you need to have the proper OS version of both db server packages.

On 14 you could also install misc/compat11x, misc/compat12x and misc/compat13x as a stop gap.
 
SirDice said:
So, the original server is back, FreeBSD 11 and running PostgreSQL 11? Was PostgreSQL 15 already available then? If there is I would do the database upgrade first, then the OS upgrade. Or pick an intermediate PostgreSQL version that's available for both FreeBSD 11 and 14 (PostgreSQL 13?), then the OS upgrade, then another DB upgrade.


It's not exactly "live", neither service is actually running. You just need to have access to the binaries of both versions. The only "problem" is that you need to have the proper OS version of both db server packages.

On 14 you could also install misc/compat11x, misc/compat12x and misc/compat13x as a stop gap.
Click to expand...
The original is back however, the server has been upgraded to FreeBSD 13.3-RELEASE-p5 GENERIC amd64
as in my post above ran the following after the original FreeBSD 11.3:
Code: 
# freebsd-update fetch
# freebsd-update install
followed by
# freebsd-update -r 13.1-RELEASE upgrade

Ran
Code: 
# freebsd-update install
twice
Now according to Updating FreeBSD have to run
Code: 
# pkg-static upgrade -f
but have NOT run that command because I think it will deinstall postgresql

Log into postgres...

# psql systems
psql (9.5.22)
Type "help" for help.
\q

# psql --version
psql (PostgreSQL) 9.5.22

The data DIR shows differently:
/var/db/postgres # ls
data11
#

Have the following in /root
postgresql93-server-9.3.25_1.txz
postgresql95-client-9.5.22.pkg
postgresql95-server-9.5.22.pkg

the two postgresql95 .pkg above were just created now with
Code: 
# pkg create postgresql95-client-9.5.22
and
# pkg create postgresql95-server-9.5.22

/var/db/pkg contains:

postgresql-odbc-09.06.0310 postgresql93-client-9.3.25_1 postgresql95-client-9.5.15_2 postgresql95-server-9.5.19
postgresql11-client-11.8 postgresql93-server-9.3.17_1 postgresql95-client-9.5.20
postgresql93-client-9.3.17_2 postgresql93-server-9.3.22 postgresql95-client-9.5.7_2
postgresql93-client-9.3.22 postgresql93-server-9.3.24 postgresql95-server-9.5.15_1

Ran
Code: 
# pkg-static upgrade -f
in order to see what will be removed. And as I thought postgresql is to be removed....
Installed packages to be REMOVED:
php74: 7.4.7
php74-bz2: 7.4.7
php74-ctype: 7.4.7
php74-dom: 7.4.7
php74-extensions: 1.0
php74-filter: 7.4.7
php74-gd: 7.4.7
php74-iconv: 7.4.7
php74-json: 7.4.7
php74-mbstring: 7.4.7
php74-mysqli: 7.4.7
php74-opcache: 7.4.7
php74-openssl: 7.4.7
php74-pdo: 7.4.7
php74-pdo_sqlite: 7.4.7
php74-pgsql: 7.4.7
php74-phar: 7.4.7
php74-posix: 7.4.7
php74-session: 7.4.7
php74-simplexml: 7.4.7
php74-sqlite3: 7.4.7
php74-tokenizer: 7.4.7
php74-xml: 7.4.7
php74-xmlreader: 7.4.7
php74-xmlwriter: 7.4.7
php74-zip: 7.4.7
php74-zlib: 7.4.7
phpMyAdmin-php74: 4.9.5
postgresql95-client: 9.5.22
postgresql95-server: 9.5.22
py27-cffi: 1.14.0
py27-cryptography: 2.6.1
py27-openssl: 19.0.0
py27-pystemmer: 2.0.0.1
py27-snowballstemmer: 1.2.1
py27-urllib3: 1.25.7,1
py36-Babel: 2.7.0
py36-Jinja2: 2.10.1
py36-MarkupSafe: 1.1.1
py36-alabaster: 0.7.6
py36-asn1crypto: 0.24.0
py36-certifi: 2019.9.11
py36-cffi: 1.12.3
py36-chardet: 3.0.4_1
py36-cryptography: 2.6.1
py36-docutils: 0.15.2
py36-idna: 2.8
py36-imagesize: 1.1.0
py36-openssl: 19.0.0
py36-pycparser: 2.19
py36-pygments: 2.4.1
py36-pysocks: 1.7.1
py36-pystemmer: 1.3.0_2
py36-pytz: 2019.3,1
py36-requests: 2.22.0
py36-setuptools: 44.0.0
py36-six: 1.12.0
py36-snowballstemmer: 1.2.1
py36-sphinx: 1.6.5_2,1
py36-sphinx_rtd_theme: 0.4.3
py36-sphinxcontrib-websupport: 1.1.2
py36-urllib3: 1.25.6,1
py37-ply: 3.11
py37-setuptools: 44.0.0
python36: 3.6.10
python37: 3.7.8_1
ruby26-bdb: 0.6.6_7

and it will install postgresql15-client: 15.7

I did not continue.

Right now thought, apache will not start and error log doesn't show anything for today. /var/log/messages shows core dump
Aug 14 02:43:22 w3 kernel: pid 73253 (httpd), jid 0, uid 0: exited on signal 11 (core dumped


So I am lost as to how to proceed forward.

Thanks
 
Upgrade your FreeBSD installation to the latest Release. Then install all pkgs and restore the backup of the database.
 
VladiBG said:
Upgrade your FreeBSD installation to the latest Release. Then install all pkgs and restore the backup of the database.
Click to expand...
Hi ValdiBG, tried that before. postgres would not update due to not finding libssl.so.x
Upgrading to 14.1-RELEASE means pkg-static upgrade -f which guarantees postgresql95 is history.

And I assume when you say upgrade, you also mean run pkg-static upgrade -f.

Thanks.
 
You didn't understand me. You do not upgrade the Postgresql you install the newer version and then restore the old backup that you have of the database.
For the test you can install a fresh VM with Postgresql and restore the backup there.

www.postgresql.org

Chapter 26. Backup and Restore

Chapter 26. Backup and Restore Table of Contents 26.1. SQL Dump 26.1.1. Restoring the Dump 26.1.2. Using pg_dumpall 26.1.3. Handling Large Databases …
www.postgresql.org www.postgresql.org
 
You must log in or register to reply here.
Back
Top