Skip to content

Instantly share code, notes, and snippets.

@ceving
Created July 4, 2023 14:00
Show Gist options
  • Save ceving/4f6462a6ca70e35b3b39f4de0f6ab257 to your computer and use it in GitHub Desktop.
Save ceving/4f6462a6ca70e35b3b39f4de0f6ab257 to your computer and use it in GitHub Desktop.
Migrate Postgresql 13 to 15

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

postgres$ pg_dumpall -p 5432 > db.out

Import into version 15

postgres$ psql -p 5433 -f db.out postgres

Remove old packages

# apt-get 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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment