Migrating PostgreSQL to AnubizHost - Operational Deep Dive

Three options, ranked by complexity and downtime:

1. pg_dump + restore: simplest, works for any source PostgreSQL 9.x+, requires write-pause during cutover. Best for databases under 50GB.
2. Logical replication: PostgreSQL 10+, near-zero downtime, replicates data changes continuously. Best for databases 50GB-1TB.
3. Base backup + streaming replication: largest databases, requires same major version, gives binary-identical replica.

Install matching PostgreSQL major version: apt install postgresql-15 postgresql-15-contrib. Tune postgresql.conf: shared_buffers = 25% of RAM, effective_cache_size = 75% of RAM, maintenance_work_mem = 1GB, checkpoint_completion_target = 0.9, wal_buffers = 16MB, random_page_cost = 1.1 (NVMe SSD).

For logical or streaming replication, also set wal_level=logical, max_replication_slots=4, max_wal_senders=4. Restart and create the target database.

pg_dump -Fc -h source-host -U user dbname > db.dump
pg_restore -d dbname -h localhost -U user --jobs=4 db.dump

Use -Fc (custom format) for compression and parallelism. --jobs=4 on restore parallelizes index creation. Plan write-pause during the dump+restore window. For 50GB databases on modern hardware, plan 30-60 minutes.

On source: CREATE PUBLICATION migration FOR ALL TABLES;. On target (with schema already created): CREATE SUBSCRIPTION migration CONNECTION 'host=source user=repuser password=xxx dbname=dbname' PUBLICATION migration;. Monitor with SELECT * FROM pg_stat_subscription;. Cut over at zero lag with a brief write-pause for application restart.

Run ANALYZE on all tables. Set up nightly base backups to AnubizHost offshore storage. Install pg_stat_statements for query monitoring. Configure WAL archiving for point-in-time recovery. Set up streaming replica to a second AnubizHost VPS for HA. Related reading: database hosting, RDS migration, DO Managed DB migration.