PostgreSQL version upgrade

  1. Stop old postgres server (PG 12 in this example) and set it to manual start in services
  2. Go into postgres.conf and change the port to something different than the original (say 5433 instead of default 5432)
  3. Install new postgres server (PG 13 in this example) using original port number.
  4. Stop new postgres server in services.
  5. Edit pg_hba.conf in the old postgres data and set local connections (127.0.0.1/32 and ::1/128) to trust on the old server and then copy the file to the new server data directory.
  6. Change postgresql.conf for the new postgres to be set to the original postgres port (if needed, default is 5432).
  7. If using windows run a command like this to migrate data: (adjust paths as needed) "C:\Program Files\PostgreSQL\13\bin\pg_upgrade.exe" --old-bindir "C:\Program Files\PostgreSQL\12\bin" --new-bindir "C:\Program Files\PostgreSQL\13\bin" --old-datadir "C:\Program Files\PostgreSQL\12\data" --new-datadir "C:\Program Files\PostgreSQL\13\data" -U postgres -p 51432 -P 52432 
  8. In pg_hba.conf change from trust back to md5 or the sha256 etc (can do for old as well if you want to keep it for a bit to make sure everything worked OK)
  9. Start new postgres service
  10. Can now check that things are working and can uninstall/delete the old postgres service as necessary.