I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime. I thought I could :
- on the old Postgres, activate archive_command to copy WAL to a NFS directory mounted on both the old and new server
- pg_dumpall the old Postgres
- install a Postgres 9.6 on the new server
- import the SQL dump
- restart the new server in recovery mode, pointing to the NFS directory to get the WAL from the old server So when the old and new Postgres are in sync, I can stop the old, verify that the new is up-to-date, change applications configuration to the new Postgres, and restart the applications.
I tested this with some test databases, and it does not work: on the new postgres the latest checkpoint's redo wal file has a number which is higher than the WAL number on the old. When I import the sql dump some WAL are generated, so the WAL number get incremented. So when I restart the new in recovery mode it does not read the old Postgres WAL.
I'm not sure this method is possible at all.
Could someone advise me another method if this one is not possible?