# PostgreSQL Backup and Restore: pg_dump, pg_restore and pgAdmin (With Examples)

> PostgreSQL backup and restore with pg_dump, pg_restore, pg_dumpall and pgAdmin: formats, cron schedules, off-server copies and how to test a restore.

**PostgreSQL backup** comes down to one habit: run **`pg_dump`** on a schedule, keep the file somewhere other than the database server, and restore it now and then to prove it works. This guide shows the exact `pg_dump`, `pg_restore`, `pg_dumpall` and pgAdmin steps, a cron schedule, and how to test a restore before you need it.

Most PostgreSQL backup guides stop at the dump command. The part that bites people is later: the file that only lived on the lost server, the roles that were never saved, or the dump that refuses to load into an older version. If you are still choosing a database, our **[PostgreSQL vs MySQL guide](/postgresql-vs-mysql/)** covers that decision first.

Below you get the commands, the format to pick, and the honest limits, including what xCloud backs up for you today and what it does not.

![PostgreSQL backup methods compared: pg_dump formats, pg_dumpall and pg_basebackup](/_landing/blog/postgresql-backup-and-restore-image-1.png)

## TL;DR

Short on time? Here is the whole guide in a few lines:

- **Daily default:** `pg_dump -Fc` writes one compressed file per database that `pg_restore` can read.
- **Save roles too:** `pg_dump` skips users and roles. Run `pg_dumpall --globals-only` as well.
- **Large databases:** use the directory format (`-Fd`) with `-j` to dump and restore in parallel.
- **Point-in-time recovery** needs a physical backup (`pg_basebackup`) plus WAL archiving, not `pg_dump`.
- **Copy every dump off the server.** A backup that lives on the same disk dies with it.
- **Test a restore** into a scratch database on a schedule, then run `ANALYZE`.
- **On xCloud**, managed PostgreSQL is not in site backups yet, so this routine is how you protect it.

## The Backup Gap Most Guides Skip

**The dump is rarely what fails. The restore is.** Three patterns break PostgreSQL restores again and again:

1. **The only copy was on the server.** When the server is gone, so is the dump.
2. **The roles were never saved.** `pg_dump` "only dumps a single database", per the [PostgreSQL pg_dump reference](https://www.postgresql.org/docs/17/app-pgdump.html). Owners and grants then point at roles that do not exist.
3. **The version went backwards.** A dump made on a newer PostgreSQL, such as 17, can fail to import into an older one, such as 15, because the dump can carry a setting the older server does not recognize.

Each one is cheap to prevent and painful to discover during an outage. The rest of this guide is built around avoiding all three.

## Logical vs Physical Backups

The PostgreSQL manual lists **three fundamentally different approaches** to backup: SQL dump, file system level backup, and continuous archiving ([PostgreSQL 17, Backup and Restore](https://www.postgresql.org/docs/17/backup.html)). In practice you choose between two families.

| | **Logical (pg_dump, pg_dumpall)** | **Physical (pg_basebackup + WAL)** |
|---|---|---|
| **What it copies** | SQL or an archive of one database's objects and data | The whole cluster's data files |
| **Restore scope** | One database, one schema, even one table | The whole cluster only |
| **Point-in-time recovery** | No | Yes, with archived WAL |
| **Across major versions** | Yes, into the same or newer version | No, same major version only |
| **Setup effort** | One command | Config changes and an archive location |
| **Best for** | Most apps, moving data between servers, daily backups | Large, busy databases where minutes of data loss matter |

If you only remember one line: **logical backups move data, physical backups rewind time.** Most Laravel and Node.js apps need the first one, done well.

`pg_basebackup` cannot back up a single database. Its reference page says backups "are always taken of the entire database cluster" (pg_basebackup reference). Recovery to a point in time also needs "a continuous sequence of archived WAL files" from the start of the base backup, so it is a project, not a one-liner.

## pg_dump and pg_restore Examples

Every command below uses placeholders. Replace `appdb` with your database, `appuser` with its role, and add `-h` and `-p` if your server is not local.

### Pick a format

`pg_dump` writes four formats, chosen with `-F`:

| Format | Flag | Output | Restore with | Parallel |
|---|---|---|---|---|
| **Plain** | `-Fp` (default) | One readable `.sql` file | `psql` | No |
| **Custom** | `-Fc` | One compressed file | `pg_restore` | Restore only |
| **Directory** | `-Fd` | A folder, one file per table | `pg_restore` | Dump and restore |
| **Tar** | `-Ft` | One `.tar` file | `pg_restore` | No |

Custom is the safe default. It is compressed, it restores selectively, and `pg_restore` can run it with several jobs.

### Back up one database

```bash
# Custom format: the everyday backup
pg_dump -U appuser -Fc -f appdb.dump appdb

# Plain SQL you can read and diff
pg_dump -U appuser -f appdb.sql appdb

# Directory format with 4 parallel jobs, for large databases
pg_dump -U appuser -Fd -j 4 -f appdb_dir appdb
```

`pg_dump` makes a consistent backup "even if the database is being used concurrently" and "does not block other users", so you can run it while the app is live.

To back up part of a database, add `-n public` for one schema or `-t orders` for one table.

### Back up roles and the whole server

```bash
# Roles and tablespaces only (run this next to every pg_dump routine)
pg_dumpall -U postgres --globals-only > globals.sql

# Every database plus roles, as one SQL script
pg_dumpall -U postgres > cluster.sql
```

`pg_dumpall` writes plain SQL only, which you restore with `psql`. One catch from the manual: the snapshots of different databases in one `pg_dumpall` run are not synchronized.

### Restore a dump

Create an empty database first, then restore into it:

```bash
# Roles first, if this is a new server
psql -U postgres -f globals.sql

# Custom or directory format
createdb -U postgres -T template0 -O appuser appdb
pg_restore -U postgres -d appdb -j 4 appdb.dump

# Plain SQL: stop at the first error instead of carrying on
psql -U postgres -X --set ON_ERROR_STOP=on -d appdb -f appdb.sql
```

To overwrite a database that already has tables, add **`--clean --if-exists`** to `pg_restore`. That drops each object in the dump before it creates it again, so **the data in those tables is replaced**. Before you restore with `--clean`, run `pg_dump` once more so you can undo it.

Other `pg_restore` options worth knowing:

- **`-l` (`--list`) and `-L` (`--use-list`):** list what is inside a dump, edit the list, and restore only those items.
- **`--no-owner`:** let the connecting user own everything, useful when the original roles do not exist.
- **`--single-transaction`:** all or nothing. It cannot be combined with `-j`.

All options are in the pg_restore reference in the PostgreSQL 17 manual.

## Back Up and Restore With pgAdmin

pgAdmin runs the same tools underneath. Its Backup dialog "uses the pg_dump utility" ([pgAdmin backup dialog](https://www.pgadmin.org/docs/pgadmin4/latest/backup_dialog.html)), and its Restore dialog "invokes options of the pg_restore client utility" ([pgAdmin restore dialog](https://www.pgadmin.org/docs/pgadmin4/latest/restore_dialog.html)).

1. In **Object Explorer**, right-click the database and choose **Backup...**.
2. Enter a **Filename**, keep **Format** on **Custom**, and click **Backup**.
3. Download the file from **Tools → Storage Manager** if pgAdmin runs on a server.

To restore, right-click the target database, choose **Restore...**, keep **Format** on **Custom or tar**, and pick the file. On the **Query Options** tab, **Clean before restore** drops each object before it restores it.

pgAdmin is the friendly option for one-off exports. For a schedule, use cron.

## Schedule It (Cron) and Copy It Off the Server

A backup you have to remember is a backup you will skip. This script dumps one database, keeps seven days of files, and saves the roles.

Run it as root, from root's crontab. On a default Debian or Ubuntu install, PostgreSQL lets the `postgres` system user log in without a password, so the script calls each tool through `sudo -u postgres`. Root creates the backup folder and hands it to `postgres`:

```bash
#!/bin/bash
# /usr/local/bin/pg-backup.sh (runs as root)
set -euo pipefail
umask 077
DIR=/var/backups/postgresql
STAMP=$(date +%F)
mkdir -p "$DIR"
chown postgres:postgres "$DIR"
chmod 700 "$DIR"

sudo -u postgres pg_dump -Fc -f "$DIR/appdb-$STAMP.dump" appdb
sudo -u postgres pg_dumpall --globals-only -f "$DIR/globals-$STAMP.sql"

# Check the dump file opens (a quick check, not a test restore)
sudo -u postgres pg_restore --list "$DIR/appdb-$STAMP.dump" > /dev/null
# Keep the last 7 days of files, delete older ones
find "$DIR" -type f -mtime +6 -delete
```

`umask 077` keeps the backups private to the admin user, so other users on the server cannot read your data or the role password hashes in `globals-*.sql`.

`pg_restore --list` only reads the dump's table of contents, not the data. It catches a file that is empty or not a valid dump, but only a real restore proves the backup works: see [Test Your Restore](#test-your-restore).

On xCloud, PostgreSQL runs inside a Docker container (see [below](#protect-postgresql-on-xcloud-today)), so use this version of the script instead. It finds the container first, runs each command inside it with `docker exec`, and writes the files on the server, not inside the container. Add it as a server cron job that runs as the root user, so it needs no `sudo` inside the script. `umask 077` does the same job here: it keeps the backups private to the admin user.

Writing to `/usr/local/bin` needs `sudo`, so on xCloud create the file over SSH as your server's **[sudo user](/docs/usudo-users-and-site-users-in-xcloud/)**, for example with `sudo nano /usr/local/bin/pg-backup.sh`, and paste the script:

```bash
#!/bin/bash
# /usr/local/bin/pg-backup.sh (xCloud version, runs as root)
set -euo pipefail
umask 077
DIR=/var/backups/postgresql
STAMP=$(date +%F)
PG_CONTAINER=$(docker ps --filter name=postgresql- --format '{{.Names}}')
mkdir -p "$DIR"
chmod 700 "$DIR"

docker exec "$PG_CONTAINER" pg_dump -U postgres -Fc appdb > "$DIR/appdb-$STAMP.dump"
docker exec "$PG_CONTAINER" pg_dumpall -U postgres --globals-only > "$DIR/globals-$STAMP.sql"

# Check the dump file opens (a quick check, not a test restore)
docker exec -i "$PG_CONTAINER" pg_restore --list < "$DIR/appdb-$STAMP.dump" > /dev/null
# Keep the last 7 days of files, delete older ones
find "$DIR" -type f -mtime +6 -delete
```

Whichever version you use, make the script runnable, or cron fails with "Permission denied":

```bash
sudo chmod 700 /usr/local/bin/pg-backup.sh
```

Then run it nightly at 02:30 with a line in root's crontab (`sudo crontab -e`):

```bash
30 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1
```

New to cron syntax? Our guide to **[scheduling tasks with cron jobs](/schedule-tasks-with-cron-jobs/)** explains each field.

Then **copy the folder off the server**. Any tool you already trust works: `scp` to another machine, or `rclone copy /var/backups/postgresql remote:bucket/postgresql` to S3, R2 or another bucket you configured with `rclone config`. Keep at least one copy in a different provider or region. Our post on **[website backup mistakes](/website-backup-mistakes/)** explains why same-server backups do not count.

![A PostgreSQL backup routine: dump, check, copy off the server, test restore](/_landing/blog/postgresql-backup-and-restore-image-2.png)

## Test Your Restore

A dump you have never restored is a hope, not a backup. Once a month, restore the latest file into a scratch database:

```bash
createdb -U postgres -T template0 appdb_restore_test
pg_restore -U postgres -d appdb_restore_test --no-owner appdb-2026-11-08.dump
psql -U postgres -d appdb_restore_test -c "ANALYZE;"
psql -U postgres -d appdb_restore_test -c "SELECT count(*) FROM orders;"
dropdb -U postgres appdb_restore_test
```

On xCloud, the PostgreSQL tools live inside the container, so run the same steps through `docker exec` (add `-i` and `<` for the restore):

```bash
PG_CONTAINER=$(sudo docker ps --filter name=postgresql- --format '{{.Names}}')
sudo docker exec "$PG_CONTAINER" createdb -U postgres -T template0 appdb_restore_test
sudo docker exec -i "$PG_CONTAINER" pg_restore -U postgres -d appdb_restore_test --no-owner < appdb-2026-11-08.dump
sudo docker exec "$PG_CONTAINER" psql -U postgres -d appdb_restore_test -c "ANALYZE;"
sudo docker exec "$PG_CONTAINER" psql -U postgres -d appdb_restore_test -c "SELECT count(*) FROM orders;"
sudo docker exec "$PG_CONTAINER" dropdb -U postgres appdb_restore_test
```

Compare a few row counts with production, and time the restore. That number is how long your app stays down on a bad day.

The manual recommends running **`ANALYZE`** after any restore "so the query optimizer has useful statistics" (Backup and Restore: SQL Dump). Skip it and the restored app can feel slow for no clear reason.

Also check the version: **restore into the same major version or a newer one.** A dump from a newer PostgreSQL is not guaranteed to load into an older server.

## Troubleshooting Common Restore Errors

| Error or symptom | Likely cause | Fix |
|---|---|---|
| `role "appuser" does not exist` | Roles were not restored | Restore `globals.sql` first, or use `--no-owner` |
| `relation already exists` | Restoring over existing tables | Restore into an empty database, or add `--clean --if-exists` |
| `unrecognized configuration parameter` | Dump from a newer major version | Restore into the same or a newer PostgreSQL |
| `input file appears to be a text format dump` | Plain `.sql` given to `pg_restore` | Restore it with `psql` instead |
| Restore "works" but data is missing | `psql` continued past an error | Rerun with `--set ON_ERROR_STOP=on` |

## Protect PostgreSQL on xCloud Today

On xCloud, **[managed PostgreSQL](/managed-postgresql-and-pgadmin-on-xcloud/)** runs as one PostgreSQL 17 service per Nginx server, inside Docker. It is reachable only from the server itself, not from the internet. You install it from **Management → Settings → Install a Service**, and **PHP App**, **Laravel** and **Node.js** sites can choose PostgreSQL when you create them.

Here is the honest part: **PostgreSQL databases are not included in xCloud site backups yet.** Site backups still cover your files and MySQL or MariaDB databases, as before. For PostgreSQL, you run the routine in this guide.

On xCloud the commands run inside the PostgreSQL container, so you prefix them with `docker exec`:

```bash
PG_CONTAINER=$(sudo docker ps --filter name=postgresql- --format '{{.Names}}')
sudo docker exec "$PG_CONTAINER" pg_dump -U postgres -Fc appdb > appdb.dump
```

👉 Follow **[How to Back Up and Restore a PostgreSQL Database in xCloud](/docs/back-up-and-restore-postgresql-in-xcloud/)** for the full pgAdmin and SSH steps, including restore.

A few xCloud pieces help with the rest of the routine:

- **Schedule it:** a server cron job can run as the root user, which `docker exec` needs. See **[how to add a custom cron job in xCloud](/docs/add-a-custom-cron-job-in-xcloud/)**.
- **Inspect it from your desktop:** **[connect to PostgreSQL with an SSH tunnel](/docs/connect-to-postgresql-with-an-ssh-tunnel-in-xcloud/)** to check a restored database in DBeaver or TablePlus.
- **Need PostgreSQL as its own app?** The one-click **[PostgreSQL hosting](/postgresql-hosting/)** page covers that option.

Run PostgreSQL that another server can reach? **[PgBackWeb hosting](/pgbackweb-hosting/)** schedules backups of it to local disk or S3. It cannot reach managed PostgreSQL, which only accepts connections from its own server.

### What xCloud does not do for PostgreSQL yet

- No PostgreSQL in site backups, and no built-in scheduled PostgreSQL backup.
- PostgreSQL sites cannot be cloned, migrated, staged or snapshotted yet, so a dump is how you move the data.
- Managed PostgreSQL runs on Nginx servers only, not OpenLiteSpeed.
- Deleting a site keeps its database and role. Take a dump before you remove them.

## Make Your PostgreSQL Backup Restorable This Week

A good PostgreSQL backup is not one command. It is four steps: dump with `pg_dump -Fc`, save roles with `pg_dumpall --globals-only`, copy everything off the server, and restore it on a schedule.

This week, set up the cron job, send the files to a second location, and do one test restore into a scratch database. Time it. If the restore works and you know how long it takes, you can restore with confidence. If you run PostgreSQL on xCloud, start with the **[step-by-step xCloud doc](/docs/back-up-and-restore-postgresql-in-xcloud/)**.

If you have found this blog helpful, feel free to [**subscribe to our blogs**](https://xcloud.host/blog/) for valuable tutorials, guides, knowledge, and tips on web hosting and server management. You can also join our [**Facebook community**](https://www.facebook.com/groups/xcloud.community) to share insights and engage in discussions.

## Frequently Asked Questions

### What is the best way to back up a PostgreSQL database?

For most apps, a nightly pg_dump in custom format (-Fc), copied off the server and test-restored once a month. Add pg_dumpall -g (globals only) so you also keep the roles. Move to pg_basebackup with WAL archiving only when you need to restore to a point in time.

### Does pg_dump lock the database or stop my app?

No for normal reads and writes. pg_dump does not block other users who read or write the database. It does make changes to your table structure wait until the dump finishes, so do not deploy database changes during the nightly backup.

### Does pg_dump back up users and roles?

No. pg_dump saves one database, not roles or tablespaces. Run pg_dumpall -g (globals only) to save those, and restore that file with psql before you restore the database dumps.

### Can I restore a PostgreSQL dump into an older PostgreSQL version?

It is not guaranteed. A dump from a newer server can contain settings an older server does not know, and the restore then fails. Restore into the same major version or a newer one.

### Are PostgreSQL databases included in xCloud backups?

Not yet. xCloud site backups cover your files and MySQL or MariaDB databases, not managed PostgreSQL. Export PostgreSQL with pgAdmin or pg_dump, as this guide shows, and keep the file off the server.