Job vps-postgres-database-host-move-with-tested-restore · revised 11 October 2026
Move one PostgreSQL database to a new host with a tested restore
One PostgreSQL database moves to a new server with its roles and data. A restore is rehearsed first, rows and counts are checked, and the cutover has a written way back.
You might be seeing
- The database lives on the same old server as everything else
- Nobody has ever restored a copy of it to prove the backups work
- The move is planned, but nobody knows how long writes must stop
No passwords, keys, card details or admin invites needed to start.
What usually happened
A database move fails quietly when roles, extensions or ownership are missed, when a dump is taken from a newer or older version than the restore expects, when the restore is never tried, or when writes continue to the old server after the switch. A dump is consistent while the database is in use, but anything written after it is not in it. pg_dump covers one database, so roles and tablespaces are dumped separately (a default roles dump carries password hashes and needs a superuser; the option that leaves passwords out does not), and a restore can fail on ownership unless the target roles exist.
Who it’s for: A founder or developer whose app's PostgreSQL database runs on a server they want to leave, or on a host they want to replace, with a database small enough to dump and restore.
Usually starts when: A legacy server is being retired, a provider is being changed, or the database has been sharing a machine with other things for too long.
The result: One PostgreSQL database up to 50 GB moves to a new host you control. A full rehearsal restore proves the dump, the roles and the application's key queries work before the cutover day. On cutover, writes to the old host stop, the final dump is restored and the checks pass, and the old database is left untouched for the agreed time as the way back.
Check whether this job fits
Five checks that decide whether this fixed job fits. Your answers stay on this page unless you choose to email them.
Checks you can run yourself
Read the version, size and extensions
Run these in a database console connected to the source as an ordinary user. They only read.
SELECT version(); SELECT pg_size_pretty(pg_database_size(current_database())); SELECT extname, extversion FROM pg_extension;Look for: The major version, the database size and each extension name. Send these, not any data.
What you get
- The rehearsal restore log with timings, errors found and how each was resolved
- The comparison table: table counts, checksums for the agreed tables and query results, source against target
- The cutover plan and a way-back plan, and the post-cutover check results
Included
- One PostgreSQL database up to 50 GB, from a source and to a target that both run a supported PostgreSQL major version
- A dump with a format that supports selective and parallel restore, a roles dump without passwords that you run, the role and ownership handling, extensions, and a rehearsal restore into an empty database on the target
- Row-count and checksum comparisons for the largest tables and a set of key application queries run before and after
- A written cutover plan with a write freeze, the final dump and restore, the connection string change and the way back
Not included
- Changing PostgreSQL major versions as part of the move unless agreed in the quote
- Zero-downtime replication; this move uses a write freeze
- Tuning, schema redesign or application code changes
- Running the new database server for you, or ongoing backup and monitoring
- Databases over 50 GB, or more than one database in the same job
- Moving a Heroku Postgres database: that is part of the Heroku move job
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
The rehearsal is repeated until a restore into an empty database on the target, run with the option that stops at the first error, finishes with exit status 0. Every error from the earlier runs, which continue past errors so all are collected, is listed with its cause and how it was resolved.
Evidence: The restore logs with timings, the error resolution table and the exit status of the final run.
pg_restore --exit-on-error --dbname=TARGET_DB backup.dump; echo $?After the final restore, the row count of every table in the agreed list matches the source, and the checksums for the agreed tables match.
Evidence: The comparison table with source and target values side by side.
The key application queries return the same results on source and target, and the application writes and reads back a named test record on the target.
Evidence: Query results on both sides and the test record read back.
All roles the application uses exist on the target with the intended privileges, and the application connects with its own role, not a superuser.
Evidence: The role and privilege listing and a connection test.
The old database is unchanged after cutover and the way-back steps, tested in the rehearsal, restore the application to the old host.
Evidence: The way-back rehearsal result and the old database's unchanged row counts.
Sign-off. You sign off after the rehearsal and the cutover checks pass and you have the way-back plan. Payment follows sign-off.
If it fails. If the rehearsal cannot be made to pass within the scope, we stop before the cutover, you do not pay for the fixed scope and the old database is untouched. We never switch the application to a target that has failed the comparison.
When it fits, and when we stop
It fits when
- The source is a PostgreSQL server you administer yourself; for Heroku Postgres see the Heroku app and Postgres move
- You administer the source and the target and can accept a write freeze of a length we estimate from the rehearsal
- The target PostgreSQL major version is the same as or newer than the source version
- You can give a way to run key application queries on both sides, and a test record that the app can write
- The application can be pointed at the new host by changing a connection setting
We stop and tell you if
- The target would be an older PostgreSQL major version than the source
- The database depends on extensions that are not available on the target
- No write freeze of any length is acceptable and replication would be needed
- The source database is already damaged and no known-good copy exists
What could go wrong
The old database is left unchanged and read-only after cutover for the agreed period. Pointing the application back at it undoes the move, but writes made to the new database after the cutover would have to be copied back, so the plan states the point after which going back loses data.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| Writes continue to the old database after the final dump and are lost. | Stop writes before the final dump by revoking write access or stopping the application, and confirm that the old host sees no writes before the switch. |
| The restore fails or silently misses objects because roles or ownership differ on the target. | Dump and recreate roles first, restore with the same owners or a documented ownership choice, and compare object lists. |
| Query plans are slower on the new host because statistics were not restored. | Run ANALYZE after restore, because a dump does not carry planner statistics unless they were asked for, and compare the key queries before declaring success. |
An independent reviewer checks the cutover plan, the point after which a return would lose writes, the role and ownership handling, and that real data is not copied into notes.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the databases, versions, extensions, the write freeze length and the cutover window in writing
- Dump the source with a selective, parallel-capable format; you run the roles dump without passwords, and the roles are recreated on the target
- Restore into an empty database on the target as a rehearsal, record errors and fix their causes
- Compare counts, checksums for the agreed tables and key query results between source and target
- Independent review of the cutover plan, ownership and privileges, and the way back
- On cutover day, freeze writes, take the final dump, restore, repeat the checks, switch the connection setting and keep the old database untouched for the agreed time
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access, any licences and necessary permissions are in place. Hosting, platform and supplier charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
If you want the new database backed up and its restore proven on a schedule, ask about backups with a restore test. This job moves the database once.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- PostgreSQL documents pg_dump and pg_restore thoroughly, including the custom format and the version rules; a database administrator can follow them. www.postgresql.org
- Many hosted PostgreSQL services offer their own migration tool for moves into their platform; check it first if you are moving to one.
Questions
How long will writes stop?
We measure the dump and restore in the rehearsal and give you that figure. It is an estimate, and a real cutover can take longer if the data has grown.
Is this the same as setting up backups?
No. A backup job proves you can restore a copy. This job moves the live database to a new host with a rehearsal and a cutover plan.
What about my users and passwords?
Roles are dumped without their passwords, by you, and recreated on the target separately from the database. You set the passwords on the target; we do not receive them or their hashes.
Can you do it with no downtime?
Not in this scope. Replication is a different job.
Send an enquiry
Send us
- The source and target PostgreSQL versions, the database size and the main tables' sizes if you know them
- The extensions in use, and whether the app runs migrations on start
- The longest write freeze you can accept, and when
- Do not send passwords, dumps, connection strings or customer data in the first enquiry
Later, once you agree
- A rehearsal environment in your control with a copy of the database or a dump, and an empty target database to restore into. Masking of real values is done by you, in your environment, before anything is handed over; unmasked data does not leave it
- A role that can read every object in the source database (for example its owner) and one that can create roles and restore on the target, both created by you and revoked afterwards
- The roles file, produced by you with the option that leaves passwords out; you set each role's password on the target yourself
- Your sign-off for the cutover window and a person who can change the application's connection setting
- A company-controlled secure handoff agreed before access: no live passwords, keys, private code or customer records by ordinary email.
You own both servers, the database and the data. Rehearsals run on a copy in an environment you provide, with real values masked by you before it is handed over. You produce the roles file with the option that omits passwords and set the passwords on the target; we do not receive password hashes or passwords. Cutover steps are run by you or through a time-limited account that you create and revoke, agreed in writing. Customer data is never copied into notes or logs.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “vps-postgres-database-host-move-with-tested-restore” as the subject.