Moving a small PostgreSQL database without downtime
A client needed to move a 40 GB PostgreSQL database to a new provider. A dump and restore would have meant two hours of downtime, which their customers would have noticed.
How we did it
We set up the new server, copied the schema, and started logical replication from the old one. Once the new copy caught up, we watched replication lag for a day to make sure it stayed close to zero.
CREATE PUBLICATION move_all FOR ALL TABLES;
-- on the new server:
CREATE SUBSCRIPTION move_all CONNECTION 'host=old-db dbname=app' PUBLICATION move_all;The switch
The application was paused for about forty seconds: stop writes, wait for the last changes, update sequences, point the connection string at the new server. We kept the old server running for a week as the rollback plan and never needed it.