Migrate Postgresql 13 to 15

README.md

Migrate Postgresql 13 to 15

Check which cluster uses which port

# fuser -v /run/postgresql/.s.*
                     USER        PID ACCESS COMMAND
/run/postgresql/.s.PGSQL.5432:
                     postgres    653 F.... postgres
/run/postgresql/.s.PGSQL.5433:
                     postgres    652 F.... postgres
# ps -f -p 652,653
UID          PID    PPID  C STIME TTY          TIME CMD
postgres     652       1  0 12:28 ?        00:00:00 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc
postgres     653       1  0 12:28 ?        00:00:01 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc

• Version 13 has the PID 653 and uses the port 5432.
• Version 15 has the PID 652 and uses the port 5433.

Export from version 13

# sudo -u postgres pg_dumpall -p 5432 > /tmp/db.out

Import into version 15

# sudo -u postgres psql -p 5433 -f /tmp/db.out postgres

Remove old packages

# apt remove postgresql-13 postgresql-client-13

Restore the default port

# sed -i 's/^port = 5433/port = 5432/' /etc/postgresql/15/main/postgresql.conf

Restart service

# systemctl restart postgresql

Check ports

# fuser /run/postgresql/.s.*
/run/postgresql/.s.PGSQL.5432:  5447
# ss -ltnp 'sport = 5432'
State      Recv-Q     Send-Q          Local Address:Port           Peer Address:Port     Process     
LISTEN     0          244                 127.0.0.1:5432                0.0.0.0:*         users:(("postgres",pid=5447,fd=5))

Change user passwords (switching from MD5 to SCRAM-SHA-256)

Since PostgreSQL 14, user passwords are hashed as SCRAM-SHA-256 instead of MD5. This means, that the user has to retype all passwords for all users.

To list all users use \du

# sudo -u postgres psql
postgres=# \du
postgres=# \password username