--- title: "PostgreSQL backup with pg_dump and cron: script, rotation and restore | StreetHosting" description: "Back up PostgreSQL daily with pg_dump in custom format, schedule it with cron, delete old copies, send them off the VPS and test the restore with pg_restore." url: "https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron" type: "page" language: "en-US" --- VPS · 9 min · Intermediate Published on Nov 13, 2026 · Updated on Nov 13, 2026 # PostgreSQL backup with pg\_dump and cron, and how to really restore it A backup is only worth something if you can restore it. See how to create PostgreSQL dumps with pg\_dump, keep roles and globals with pg\_dumpall, automate it with cron, rotate old copies, move them off the VPS and test the restore. By [Equipe StreetHosting](https://streethosting.com.br/en/autores#equipe-streethosting) · StreetHosting infrastructure and support team [Backup and recovery](https://streethosting.com.br/en/guides/topics/backup) [Security and hardening](https://streethosting.com.br/en/guides/topics/security) [Linux administration](https://streethosting.com.br/en/guides/topics/linux) [Containers and Docker](https://streethosting.com.br/en/guides/topics/docker) Summarize with: [](https://chat.openai.com/?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "ChatGPT") [](https://claude.ai/new?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Claude") [](https://www.google.com/search?udm=50&aep=11&q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Google AI Mode") [](https://x.com/i/grok?text=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Grok") [](https://www.perplexity.ai/search/new?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Perplexity") Share: [](https://x.com/intent/tweet?text=PostgreSQL%20backup%20with%20pg%5Fdump%20and%20cron%3A%20script%2C%20rotation%20and%20restore&url=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron "Share on X") [](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron "Share on Facebook") [](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron "Share on LinkedIn") [](https://wa.me/?text=PostgreSQL%20backup%20with%20pg%5Fdump%20and%20cron%3A%20script%2C%20rotation%20and%20restore%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Fpostgresql-backup-pg-dump-cron "Share on WhatsApp") For agents: Copy as Markdown [.md](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron.md) In this guide 9 sections * [pg\_dump, pg\_dumpall and the formats](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#formatos) * [The first backup and the first restore](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#manual) * [A script with the date in the name](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#script) * [Run without a password: postgres user or .pgpass](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#senha) * [Daily cron and rotation](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#cron) * [A database inside Docker](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#docker) * [Move it off the VPS](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#fora) * [Test the restore for real](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#testar) * [Where to run this at StreetHosting](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#onde-rodar) Quick answer Create the backup with `pg_dump -Fc -f backup.dump db_name`, keep roles and globals with `pg_dumpall --globals-only` and restore with `pg_restore --clean --if-exists -d db_name backup.dump`. Automate it with a script that puts the date in the name, run it from cron as the postgres user (or with a .pgpass file set to mode 600), delete whatever is older than N days and copy it off the VPS. The step almost nobody does and the most important one: really restore into a test database. ## pg\_dump, pg\_dumpall and the formats[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#formatos) PostgreSQL ships two logical backup tools. `pg_dump` exports **one database**: tables, data, indexes, functions. `pg_dumpall` exports the **whole server**, and the part that matters there is the roles (users and groups) and global objects, which pg\_dump leaves out. Without the roles, a restore on a new server throws errors when it tries to give ownership of tables to a user that does not exist. pg\_dump picks the format with the `-F` option: * **custom (-Fc):** a compressed, binary file. It restores with pg\_restore, lets you choose what to bring back (a table, a schema) and supports parallel restore. This is what we recommend. * **text (default):** a plain SQL file. You can read and edit it, but it is larger and restores whole with psql. * **directory (-Fd):** a folder with one file per table, useful for large databases because it allows a parallel dump. These commands make a logical backup: they export the content as statements. They do not replace copying the disk files in one go or continuous WAL archiving, which lets you go back to an exact minute. For most sites and small applications, a daily dump does the job. If you are installing the database now, the [guide to PostgreSQL on Ubuntu](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps) shows the start. ## The first backup and the first restore[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#manual) Before automating, run it by hand to see it work. As the postgres user, which has local access without a password by default on Ubuntu: `sudo -u postgres pg_dump -Fc -f /tmp/mydb.dump mydb sudo -u postgres pg_dumpall --globals-only -f /tmp/globals.sql ls -lh /tmp/mydb.dump /tmp/globals.sql` To restore into a new database, create the empty database and point pg\_restore at it: `# 1) roles first, only if the server is new sudo -u postgres psql -f /tmp/globals.sql # 2) empty database and restore sudo -u postgres createdb mydb_test sudo -u postgres pg_restore -d mydb_test /tmp/mydb.dump` To overwrite a database that already exists with the dump's content, use `--clean`, which drops each object before recreating it, together with `--if-exists`, which avoids an error when the object does not exist yet: `sudo -u postgres pg_restore --clean --if-exists -d mydb /tmp/mydb.dump` `--clean` destroys the current data in the database you point it at. Run it on a test database first and only aim it at production when you are sure the dump is the right one and the application is stopped. ## A script with the date in the name[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#script) The script below does it all at once: database dump, globals dump, file validation and cleanup of old ones. It writes to a temporary name first and only renames at the end, so an incomplete backup never looks like a good one. Save it as `/usr/local/bin/backup-postgres.sh`: `#!/usr/bin/env bash set -euo pipefail BACKUP_DIR="/var/backups/postgres" DB_NAME="mydb" DAYS=14 STAMP="$(date +%F_%H%M)" mkdir -p "$BACKUP_DIR" # database dump in custom format, under a temporary name pg_dump -Fc -f "$BACKUP_DIR/${DB_NAME}_${STAMP}.dump.tmp" "$DB_NAME" # pg_restore must be able to read the file pg_restore --list "$BACKUP_DIR/${DB_NAME}_${STAMP}.dump.tmp" > /dev/null mv "$BACKUP_DIR/${DB_NAME}_${STAMP}.dump.tmp" "$BACKUP_DIR/${DB_NAME}_${STAMP}.dump" # roles and global objects pg_dumpall --globals-only -f "$BACKUP_DIR/globals_${STAMP}.sql" # rotation: delete anything older than N days find "$BACKUP_DIR" -type f \( -name "*.dump" -o -name "globals_*.sql" \) -mtime +"$DAYS" -delete echo "backup ok: ${DB_NAME}_${STAMP}.dump"` Prepare the directory and give it execute permission. Since the script will run as postgres, the directory has to belong to that user: `sudo mkdir -p /var/backups/postgres sudo chown postgres:postgres /var/backups/postgres sudo chmod 700 /var/backups/postgres sudo chmod +x /usr/local/bin/backup-postgres.sh sudo -u postgres /usr/local/bin/backup-postgres.sh` `set -euo pipefail` makes the script stop at the first error. If pg\_dump fails, nothing is renamed and nothing old is deleted, which protects you from a rotation that wipes every good backup after a week of silent failures. ## Run without a password: postgres user or .pgpass[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#senha) A scheduled script cannot stop to ask for a password. There are two ways. The simplest is to run as the `postgres` system user, which connects through the local socket with no password. That is what the example above does. The second is to run as another user, or to connect to a remote server, with a **.pgpass** file in the home folder. Each line follows the format `hostname:port:database:username:password`, and PostgreSQL ignores the file if its permission is more open than 600: `echo "127.0.0.1:5432:mydb:backup:YOUR_PASSWORD" >> ~/.pgpass chmod 600 ~/.pgpass # pg_dump must give the host and user so they match the line pg_dump -h 127.0.0.1 -U backup -Fc -f /tmp/teste.dump mydb` Create a role just for backups, with read permission, instead of using the superuser. Avoid putting the password on the command line, because it shows up in the process list and in the shell history. The PostgreSQL documentation also advises against the PGPASSWORD variable, since some systems let other users read a process's environment. ## Daily cron and rotation[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#cron) Edit the postgres user's cron and schedule the script for a quiet hour. The cron format and the environment pitfalls are in the [guide to cron on a VPS](https://streethosting.com.br/en/guides/vps/schedule-tasks-vps-cron). `sudo -u postgres crontab -e # every day at 3:15 am, logging to a file 15 3 * * * /usr/local/bin/backup-postgres.sh >> /var/backups/postgres/backup.log 2>&1` The rotation is already in the script, on the `find ... -mtime +14 -delete` line. Choose the number of days by the space you have and how far back you need to go: 14 days is a reasonable start. Work out the space: the size of one dump times the number of days. The [disk space guide](https://streethosting.com.br/en/guides/vps/find-what-uses-disk-space-on-vps) helps you keep track. Be careful with the rotation command. Test it first by swapping `-delete` for `-print` to see which files would be deleted, and confirm the path points only at the backup folder. ## A database inside Docker[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#docker) If PostgreSQL runs in a container, the container's own pg\_dump does the work and you redirect the output to a file on the host. One detail that ruins backups: **do not use the -t option** of `docker exec`. It creates a terminal that converts line breaks and corrupts the binary file of the custom format. `# backup (no -t) docker exec postgres pg_dump -U myuser -Fc mydb > /var/backups/postgres/mydb_$(date +%F).dump # roles and globals docker exec postgres pg_dumpall -U myuser --globals-only > /var/backups/postgres/globals_$(date +%F).sql # restore: use -i to send the file through standard input docker exec -i postgres pg_restore -U myuser -d mydb --clean --if-exists < /var/backups/postgres/mydb_2026-11-13.dump` Replace `postgres`with your container's name (see it in `docker ps`) and `myuser`with the database user. In cron, use the full path to docker and run as a user that can reach it. Remember that the container's data volume is not a backup: if the volume goes, the database goes with it. The Compose structure is in the [Docker Compose guide](https://streethosting.com.br/en/guides/vps/docker-compose-vps-guide). ## Move it off the VPS[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#fora) A backup kept only on the same disk as the database protects against a command mistake, but not against a failure of the disk, the server or the account. The rule is to have a copy somewhere else. The full strategy, with the number of copies and media types, is in the guide to the [3-2-1 backup strategy](https://streethosting.com.br/en/guides/infrastructure/3-2-1-backup-strategy). In practice, add to the script (or to cron) a transfer to another server or to object storage. With rsync over SSH it looks like this: `rsync -a --delete-after /var/backups/postgres/ backup@OTHER_SERVER:/backups/vps1/postgres/` If you prefer a tool that encrypts, deduplicates and keeps history, the [guide to backups with restic and cron](https://streethosting.com.br/en/guides/vps/vps-backup-restic-cron) shows how to include the dumps folder in it. Dumps hold all of the database's data, sensitive data included, so encrypt before sending to a place you do not control. ## Test the restore for real[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#testar) A backup file that nobody has ever restored is a hope, not a backup. Set aside time, at the start and then every few months, for this routine, on a test database and never on production: 1. Take the latest dump, preferably the copy that is off the VPS, so you also test the way back. 2. Create an empty database with `createdb mydb_test`. 3. Restore with `pg_restore -d mydb_test backup.dump` and read the errors, if any. 4. Compare with production: number of tables and row counts of the main tables, with `SELECT count(*)`. 5. If you can, point a copy of the application at the test database and check that it starts and reads the data. 6. Write down how long it took. That is your real recovery time on a bad day. 7. Delete the test database with `dropdb mydb_test`. Add an alert for when the backup fails. The cron log file helps, but nobody reads logs until they need to. A simple alert when the script exits with an error saves you from finding out at the wrong moment that the backup stopped a month ago. ## Where to run this at StreetHosting[](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#onde-rodar) On StreetHosting VPS plans you get root access, KVM virtualization and NVMe disk, in São Paulo. That gives you the freedom to install PostgreSQL, the backup clients and cron the way this guide describes, and it keeps the dump and the restore fast. Keeping the last few days of dumps on the same server is only one of the copies: the one that matters on disaster day is the one somewhere else. Activation is automatic in about 60 seconds after payment by Pix or card. In this guide * [pg\_dump, pg\_dumpall and the formats](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#formatos) * [The first backup and the first restore](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#manual) * [A script with the date in the name](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#script) * [Run without a password: postgres user or .pgpass](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#senha) * [Daily cron and rotation](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#cron) * [A database inside Docker](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#docker) * [Move it off the VPS](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#fora) * [Test the restore for real](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#testar) * [Where to run this at StreetHosting](https://streethosting.com.br/en/guides/vps/postgresql-backup-pg-dump-cron#onde-rodar) ## Frequently asked questions Which pg\_dump format should I use? For production backups, the custom format (the -Fc option). It is compressed, lets you restore just one table or schema with pg\_restore and supports parallel restore with the -j option. The text format (plain SQL) is readable, but it is larger and the restore is more limited. Does pg\_dump lock the database while it runs? No. pg\_dump takes a consistent snapshot of the database using a transaction and does not block reads or writes from other connections. Only schema changes, such as ALTER TABLE, wait for it to finish. It uses CPU and disk while it works, so schedule it for a quiet hour if the database is large. Does pg\_dump save users and passwords? No. pg\_dump saves one database at a time and does not carry the roles (users) or the server's global objects. For that use pg\_dumpall with the --globals-only option and keep the file next to the database dumps. How often should I run the backup? It depends on how much data you accept losing. A daily dump means that, in the worst case, you lose up to 24 hours. If that is too much, run it every hour or look into continuous WAL archiving, which is a separate subject. In any case, keep a copy off the VPS. How do I know the backup is good? Only by restoring it. The pg\_restore --list command shows whether the file is readable, but the real test is to create an empty database, restore the dump into it and check that the tables and row counts match production. Do this regularly. Next step See VPS plans Root VPS in Brazil with NVMe and Anti-DDoS. [See VPS plans](https://streethosting.com.br/en/vps) ## Related guides [VPS Intermediate How to install PostgreSQL on an Ubuntu VPS securely PostgreSQL is the most complete open source relational database and the default for many modern applications. Learn how to install it on an Ubuntu VPS, create a user and database for your application, understand authentication, allow remote access without exposing the port, schedule backups and tune memory. 10 min Read guide](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps) [VPS Advanced How to automate VPS backups with restic and cron A backup that depends on you remembering does not work. With restic and cron you create encrypted snapshots, send them off the VPS, control retention and test restores without daily effort. 9 min Read guide](https://streethosting.com.br/en/guides/vps/vps-backup-restic-cron) [Infrastructure Beginner The 3-2-1 backup strategy for servers The 3-2-1 rule is one of the most reliable ways to reduce data loss after a technical failure or a human mistake. 2 min Read guide](https://streethosting.com.br/en/guides/infrastructure/3-2-1-backup-strategy) [← Back to the Guide Center](https://streethosting.com.br/en/guides)