postgres installation guide for db upgrade

start docker

docker compose -f ubuntu.yaml up -d

go into container

docker exec -ti ubuntu bash

update

apt-get update

install needed packages

apt-get -y install lsb-release nano mc wget ca-certificates gnupg2 sudo

create the file repository configuration

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

import the repository signing key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

update the package lists

apt-get update

install desired versions of postgresql

apt-get -y install postgresql-11 postgresql-14

set postgres user password

passwd postgres

open sudoers file

nano /etc/sudoers

copy/paste root user to line below and change root to postgres

root    ALL=(ALL:ALL) ALL
postgres    ALL=(ALL:ALL) ALL

switch to postgres user

sudo -iu postgres

remove existing dbs

rm -rf /var/lib/postgresql/11
rm -rf /var/lib/postgresql/14

initialize dbs

/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main

stop services

/etc/init.d/postgresql stop

update port for 11 => port = 5411

nano /var/lib/postgresql/11/main/ppostgres.conf

update port for 14 => port = 5414

nano /var/lib/postgresql/14/main/ppostgres.conf

start postgresql 11

/usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main -l logfile start

import db

createdb -p 5411 curator_metadata
psql -p 5411 -U postgres curator_metadata < /dump/curator_metadata.sql

check db compatibility

/usr/lib/postgresql/14/bin/pg_upgrade -b  /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/14/bin -d /var/lib/postgresql/11/main -D /var/lib/postgresql/14/main --check

this should give you a report. good luck!

ozbeksu/guide.md