Upgrading PostgreSQL Database

when major release of postgresql is done you need to do some manual action if not done you can meet issue during request

action

need to have postgresqk-old-upgrade installed on arch

• stop service

systemctl stop postgresql.service

• rename cluster directory

mv /var/lib/postgres/data /var/lib/postgres/olddata
mkdir /var/lib/postgres/data /var/lib/postgres/tmp
chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp

• log in postgres account

sudo -iu postgres
cd /var/lib/postgres/tmp

• launch upgrade

pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

• check both pg_hba.conf in new and old cluster
• restart server sc-start postgresql

sequece manipulation

sequence are use to generate uniq identifier

• get sequence value

select * from name_seq;

• increment sequence value

select nextval(name_seq);

• set sequence value:

select setval(name_seq,newvalue);