notebook/IT/SQL/PostgreSQL/Upgrade.md

# 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

```bash
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

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

- launch upgrade

```bash
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

```SQL
select * from name_seq;
```

- increment sequence value

```SQL
select nextval(name_seq);
```

- set sequence value:

```SQL
select setval(name_seq,newvalue);
```