PostgreSQL Upgrade

These instructions assume you are upgrading PostgreSQL version 9.4 to PostgreSQL 13 on CentOS. If these assumptions aren't correct, adjust these instructions to fit your case.

Login as root user on the box to be updated. If you are log-in as non-root such as nanadmin then switch into root with sudo.

sudo -i

Validation steps

Validate what version of PostgreSQL you are running

psql -U nanitor -c "select version();"

If that doesn't work see if the following directory is valid and what the highest number is in that directory.

ls -al /var/lib/pgsql/

If that isn't valid then you need to figure out where your PostgreSQL is installed and what the version is before proceeding.

These instructions assume the output you got started with "PostgreSQL 9.4", if it is something else then replace 9.4 with that number.

Validate that the following directory is valid, has a bunch of sub-directories and few conf files such as pg_hba.conf

ls -al /var/lib/pgsql/9.4/data

If not, you need to figure out what this path is on your box and adjust these instructions accordingly.

Validate that the following directory is valid, and has a bunch of executable binaries such as pg_dump, pg_restore, psql, etc.

ls -al /usr/pgsql-9.4/bin

If not, you need to figure out what this path is on your box and adjust these instructions accordingly.

Implementation

Make sure /var/lib/pgsql/9.4/data/pg_hba.conf looks like this, ignore the replicator lines and the comments (lines that start with #). Edit if necessary.

local all       all                         trust
host  all       all       127.0.0.1/32      trust
host  all       all       ::1/128           trust

Restart PostgreSQL if you made changes.

systemctl restart postgresql-9.4

Then execute the following commands to back up the existing databases. Change "backup-filename-date" to something more sensible like: "nanitor-backup-Example-27jul22-1645" for the backup done on July 27 2022 at 16:45 for customer example.com.

Start with the base nanitor database

pg_dump -U nanitor nanitor | gzip -c > backup-filename-date.sql.gz

Then if you have a collector on the same box, do that next.

pg_dump -U nancollector nancollector | gzip -c > collector-backup-filename-date.sql.gz

Run the following line to reset /etc/yum.repos.d/CentOS-Base.repo

/usr/lib/nanitor-server/bin/nanitor-server-ctl set_yum_repositories

Then add a new section

echo '[postgresql13]
name=CentOS-$releasever - Updates
baseurl=https://hub.nanitor.com/centos/7/postgresql-13/x86_64/
gpgcheck=1' >>/etc/yum.repos.d/CentOS-Base.repo

We will be installing the new version of Postgres, then shutting down the old one, running an upgrade procedure then starting a new one.

Install Postgres 13

yum clean metadata
yum install postgresql13-server postgresql13-contrib -y

Set up the database

/usr/pgsql-13/bin/postgresql-13-setup initdb Then make the same edit to the new pg_hba.conf as you did before by editing /var/lib/pgsql/13/data/pg_hba.conf and make it look like this, ignore the replicator lines and the comments (lines that start with #)

local all       all                         trust
host  all       all       127.0.0.1/32      trust
host  all       all       ::1/128           trust

Shutdown the nanitor server

/usr/lib/nanitor-server/bin/nanitor-server-ctl systemctl_stop

Shut down and disable the old database

systemctl disable postgresql-9.4 
systemctl stop postgresql-9.4

Switch to the postgres user

sudo -u postgres -H bash

cd ~

Run an upgrade check

/usr/pgsql-13/bin/pg_upgrade \
--old-datadir=/var/lib/pgsql/9.4/data \
--new-datadir=/var/lib/pgsql/13/data \
--old-bindir=/usr/pgsql-9.4/bin \
--new-bindir=/usr/pgsql-13/bin \
--old-options '-c config_file=/var/lib/pgsql/9.4/data/postgresql.conf' \
--new-options '-c config_file=/var/lib/pgsql/13/data/postgresql.conf' \
--check

Fix or troubleshoot any issues this check called out, if any, and rerun the check until you get all OK.

Then run the upgrade

/usr/pgsql-13/bin/pg_upgrade \
--old-datadir=/var/lib/pgsql/9.4/data \
--new-datadir=/var/lib/pgsql/13/data \
--old-bindir=/usr/pgsql-9.4/bin \
--new-bindir=/usr/pgsql-13/bin \
--old-options '-c config_file=/var/lib/pgsql/9.4/data/postgresql.conf' \
--new-options '-c config_file=/var/lib/pgsql/13/data/postgresql.conf' \

Exit back to root and edit /var/lib/pgsql/13/data/postgresql.conf and set the following options as a minimum. Use a higher number if the box can handle it. Make sure the lines aren't commented out with # as the first character. Use PGTune to help guide you what your configuration should look like.

shared_buffers = 1GB
work_mem = 128MB

Then enable the new version and start it

systemctl enable postgresql-13 
systemctl start postgresql-13

Start the nanitor server

/usr/lib/nanitor-server/bin/nanitor-server-ctl systemctl_start

upgrade the nanitor server to make sure you are on the latest version here

/usr/lib/nanitor-server/bin/nanitor-server-ctl upgrade

Post-implementation validation

Check the status of the server and make sure all is good.

[root@demo ~]# /usr/lib/nanitor-server/bin/nanitor-server-ctl systemctl_status
● nanitor-nsq.service - Nanitor NSQ
   Loaded: loaded (/usr/lib/systemd/system/nanitor-nsq.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16419 (nsqd)
   CGroup: /system.slice/nanitor-nsq.service
           └─16419 /usr/lib/nanitor-server/bin/nsqd -tcp-address=127.0.0.1:81...

● nanitor-api.service - Nanitor API
   Loaded: loaded (/usr/lib/systemd/system/nanitor-api.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16420 (nanitor-api)
   CGroup: /system.slice/nanitor-api.service
           └─16420 /usr/lib/nanitor-server/bin/nanitor-api start

● nanitor-manager.service - Nanitor Manager
   Loaded: loaded (/usr/lib/systemd/system/nanitor-manager.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16421 (nanitor-manager)
   CGroup: /system.slice/nanitor-manager.service
           └─16421 /usr/lib/nanitor-server/bin/nanitor-manager start

● nanitor-ui-api.service - Nanitor UI API
   Loaded: loaded (/usr/lib/systemd/system/nanitor-ui-api.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16422 (nanitor-ui-api)
   CGroup: /system.slice/nanitor-ui-api.service
           └─16422 /usr/lib/nanitor-server/bin/nanitor-ui-api start

● nanitor-system-api.service - Nanitor System API
   Loaded: loaded (/usr/lib/systemd/system/nanitor-system-api.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16423 (nanitor-system-)
   CGroup: /system.slice/nanitor-system-api.service
           └─16423 /usr/lib/nanitor-server/bin/nanitor-system-api start

● nanitor-ui-runner.service - Nanitor UI Runner
   Loaded: loaded (/usr/lib/systemd/system/nanitor-ui-runner.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16424 (nanitor-ui-runn)
   CGroup: /system.slice/nanitor-ui-runner.service
           └─16424 /usr/lib/nanitor-server/bin/nanitor-ui-runner start /usr/l...

● nanitor-collector-socket.service - Nanitor Collector Socket
   Loaded: loaded (/usr/lib/systemd/system/nanitor-collector-socket.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 11:40:28 GMT; 12min ago
 Main PID: 16428 (nanitor-collect)
   CGroup: /system.slice/nanitor-collector-socket.service
           └─16428 /usr/lib/nanitor-server/bin/nanitor-collector-socket start...

Then log into nanitor and validate all looks good and you are good to go.