pg_dump and pg_restore: how to create and verify PostgreSQL backups
Published on 2026-10-26 by frfrand
Logical copy and restore in PostgreSQL with secure commands
"We have backups" doesn't mean much until someone needs to restore one. With PostgreSQL, pg_dump allows you to create logical copies and pg_restore recovers them when you used compatible formats. What matters is verifying the procedure.
The idea is to solve a data or deployment problem that appears in real applications. We will look at the concept without detours, an implementation example, and what to check before taking it to production.
What pg_dump and pg_restore do
A logical copy exports objects and data from the database, not the entire state of the PostgreSQL server nor necessarily global roles or external configurations. For very large databases or recovery to a specific point in time, physical backups and WAL/PITR may be necessary.
Simply repeating a recipe isn't enough if you don't understand who reads the data, from what process, and with what permissions. In an application with reservations, invoices, or users, a race condition or a poorly designed access policy can be more serious than a slow API.
A concrete example
# Formato custom en un entorno autorizado
pg_dump -h 127.0.0.1 -U app -F c -f /backups/app-20261026.dump app
# Inspeccionar contenido de la copia
pg_restore -l /backups/app-20261026.dump | head
# Restaurar en una base de PRUEBA ya creada
pg_restore -h 127.0.0.1 -U restaurador -d app_pruebas --no-owner /backups/app-20261026.dumpThe names, tables, and keys in this example are illustrative. Try it first in a test or staging environment and adapt permissions, indexes, and versions. When there are write operations, do not run sample queries on customer data without reviewing what they modify.
Step by step
- Choose storage destination with appropriate permissions, encryption, and retention.
- Create the copy in an appropriate format and verify that the command finished without error.
- Save engine version and restoration procedure along with the backup.
- Restore on an isolated test database and verify tables, relationships, and key data.
- Schedule recurring checks, not only the nightly file generation.
At the end, don't settle for "the SQL did not give an error." Verify the real effect from the API and with concurrency or permission tests as appropriate. In databases there are errors that only appear when two users do something at the same time.
Example work project
A rental platform has reservations and paid entries. Before a schema update, you make a coherent copy, restore it in staging, and test that the application can start with that data.
That scenario helps distinguish performance from consistency. Speeding up a query is useless if it allows creating two reservations for a single spot, nor does a huge database fix the backend allowing querying another client's data.
The typical error
Do not restore a test copy onto production by accident. For active databases, size, consistency, and recovery window may require a strategy different from the daily full dump.
It's also a good idea to have a recovery plan. Before touching schema, users, or policies, save a verifiable copy or reversible migration and verify that the application can keep running during the change.
How would I solve this with ArduMaker?
When managing PostgreSQL and VPS from ArduMaker, document where backups are stored, who has access, and how long it takes to recover the API. The panel does not replace the restoration drill.
ArduMaker allows you to deploy services from Git and work with databases and processes within the VPS. The panel does not replace your application's logic: transactions, permissions, and validations must be correctly designed in the code and the database.
Three checks before publishing
Integrity: if a request is repeated or fails halfway through, do the data end up in a valid state?
Security: can an authenticated user access only their information, and does the API use a base identity with minimal permissions?
Recovery: do you know how to restore the data and revert the compatible code version if an update goes wrong?
If you can answer those three questions with tests, your backend is much better prepared than when you only checked if the endpoint returned 200.
Resources to learn more
To wrap up: choose the simplest solution that allows you to maintain correct, secure, and recoverable data. Performance comes after measuring.