Postgres:Upgrading

FreeBSD

• Upgrade the clients utilities from the ports in /usr/ports/databases/postgresql-version-client
• Shutdown the server and restart it.

pg_ctl -m fast stop && pg_ctl start

You might have to do a

postmaster -D /usr/local/pgsql/data

if there's a problem with the new client utilities when you're starting the server.

• Do a dump of all databases as the pgsql user:

pg_dumpall > pg.dump

• Backup all the configuration files. If you are the pgsql user:

cp $HOME/data/*.{conf,key,crt} $HOME

• Upgrade the server from the port in /usr/ports/databases/postgresql-version-server
• Delete the old database directory:

rm -fr $HOME/data

• Remake it:

initdb && cp $HOME/*.{conf,crt,key} $HOME/data

• Start the server as the pgsql user:

pg_ctl start

• Start the restore as the pgsql user:

createdb pgsql && psql < pg.dump

Upgrading PostgreSQL Version 9.6 to 14.3

Last updated August 2022

PostgreSQL, also known as Postgres, is an open-source relational database system that provides tables and frames for organizing data. Originally Bowie was using PostgreSQL 9.6, which was released in 2016. At Earlham, some of the data from the Iceland Epic program, computer science department, math department, and some other science majors use PostgreSQL on Bowie as a way to manage and structure data. Most clients who used PostgreSQL on Bowie recently faced PostgreSQL version incompatibility. Therefore, PostgreSQL version 9.6 was replaced with the latest version of PostgreSQL 14.3 to bring compatibility for new database commands, data management, and keeping things up to date. The following steps were taken to remove the old version of Postgres on Bowie and transfer all the data from the old version to the newly installed version;

1. In the beginning, there were two cluster versions of PostgreSQL. Cluster one was PostgreSQL 9.6 and cluster two was PostgreSQL 14.3. As a first step, all databases on PostgreSQL 9.6 were dumped into a file in /postgres/backup/dumpall_backup/all_databases.sql using pg_dumpall -u postgres > all_databases.sql command.
2. After making sure all the data were backed up properly, then deleted Postgres cluster 9.6.
3. Then created a PostGIS extension in PostgreSQL 14.3 to create geographic and location capabilities for data.
4. Finally restored the /postgres/backup/dumpall_backup/all_databases.sql file into the newly installed Postgres version of 14.3.
5. To ensure data have been properly restored, some commands were tried to check accuracy. E.g., from Hopper server: psql -h bowie.cs.earlham.edu -d field_science -U fieldsci then select * from fieldday_trip. If the command returns a few lines of relations and databases, it assures us that the backed-up data is restored properly.