PostgreSQL

This section presents the different actions related to PostgreSQL that you might be required to do while updating/upgrading OKA.

Backup

First, ensure that PostgreSQL database is running and accessible.
Backup your PostgreSQL database:
pg_dump --dbname=oka_db --host=HOSTNAME --port=PORT --username=oka --password > oka_db.backup.sql

Restore

sudo -i -u postgres
psql
      DROP DATABASE oka_db;
      CREATE DATABASE oka_db;
      GRANT ALL PRIVILEGES ON DATABASE oka_db TO oka;
      ctrl+d
psql oka_db < oka_db.backup.sql
ctrl+d
sudo service postgresql restart

Migrating to latest version

There are two ways to easily upgrade to the latest required version of PostgreSQL.

Following explanation shows how to upgrade from version 12 to 15 but process can be applied with other versions as well.

Remove and replace.

Backup existing databases:

pg_dumpall -U postgres > backup_pg12.sql

Stop both PostgreSQL services:
```
sudo systemctl stop postgresql
```

Uninstall current version:

sudo apt remove postgresql-12 postgresql-client-12
# or on RHEL/CentOS:
sudo yum remove postgresql12-server postgresql12

Install new version:

sudo apt install postgresql-15 postgresql-client-15
# or on RHEL/CentOS:
sudo yum install postgresql15-server postgresql15
sudo postgresql-15-setup initdb

Start PostgreSQL 15 service:
```
sudo systemctl start postgresql
```
Restore databases:
```
psql -U postgres -f backup_pg12.sql
```

Set database ownership and permissions:

psql -U postgres -c "GRANT ALL ON DATABASE oka_db TO oka;"
psql -U postgres -c "ALTER DATABASE oka_db OWNER TO oka;"

Verify the installation:

psql -U postgres -c "SELECT version();"

Optional: Remove old data directory if it wasn’t automatically removed:
```
sudo rm -rf /var/lib/postgresql/12/data
```

In-place upgrade with pg_upgrade.

Backup existing databases:

pg_dumpall -U postgres > backup_pg12.sql

Install PostgreSQL 15 alongside version 12:

sudo apt install postgresql-15 postgresql-client-15
# or on RHEL/CentOS:
sudo yum install postgresql15-server postgresql15
sudo postgresql-15-setup initdb

Stop both PostgreSQL services:
```
sudo systemctl stop postgresql
```

Switch to postgres user and verify paths:

sudo su - postgres
/usr/lib/postgresql/12/bin/postgres --version
/usr/lib/postgresql/15/bin/postgres --version

Run pg_upgrade check to verify compatibility:

/usr/lib/postgresql/15/bin/pg_upgrade \
      -b /usr/lib/postgresql/12/bin \
      -B /usr/lib/postgresql/15/bin \
      -d /var/lib/postgresql/12/data \
      -D /var/lib/postgresql/15/data \
      --check

Perform the actual upgrade:

/usr/lib/postgresql/15/bin/pg_upgrade \
      -b /usr/lib/postgresql/12/bin \
      -B /usr/lib/postgresql/15/bin \
      -d /var/lib/postgresql/12/data \
      -D /var/lib/postgresql/15/data

Copy configuration files:

cp /var/lib/postgresql/12/data/postgresql.conf /var/lib/postgresql/15/data/
cp /var/lib/postgresql/12/data/pg_hba.conf /var/lib/postgresql/15/data/

Update port configuration if needed (edit postgresql.conf to use default port 5432)
Start PostgreSQL 15:
```
sudo systemctl start postgresql
```
Run the analyze script (generated locally when running pg_upgrade) to update statistics:
```
./analyze_new_cluster.sh
```

Set database ownership and permissions:

psql -U postgres -c "GRANT ALL ON DATABASE oka_db TO oka;"
psql -U postgres -c "ALTER DATABASE oka_db OWNER TO oka;"

Verify the upgrade:

psql -U postgres -c "SELECT version();"

Optional: Remove old cluster after verification:

./delete_old_cluster.sh
# or manually:
sudo apt remove postgresql-12 postgresql-client-12
sudo rm -rf /var/lib/postgresql/12