Aller au contenu

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


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);