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
- init new db
initdb -D /var/lib/postgres/data --locale=C.UTF-8 --encoding=UTF8 --data-checksums
-
if use extention copy extention file from /usr/lib/postgresql/ to /opt/pgsql-PG_VERSION/lib
-
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
if need to restart old database
/opt/pgsql-16/bin/pg_ctl -w -D "/var/lib/postgres/olddata" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgres/tmp'" stop
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);