--- title: "Restore Postgres" description: "From artifact to running database." url: "https://saved.sh/docs/recover/restore-postgres" --- A Postgres artifact is a `pg_dump` file. Restoring it is `pg_restore`, and everything before that is unwrapping. ## The short path [#the-short-path] ```bash sctl restore postgres --host localhost --database scratch --user postgres ``` The target is described with **flags, not a connection URI**, using the same field names the `postgres` source type uses. `--database` is the only required one; the rest default the way `psql` would. The CLI downloads, decrypts with your key, decompresses, detects whether the dump is custom-format or plain SQL, and runs `pg_restore` or `psql` accordingly. All of it on your machine. ## The long path, without our CLI [#the-long-path-without-our-cli] Four commands, none of them ours. ```bash # 1. Get the bytes sctl artifact download --output ./artifact # or, if you deliver to your own bucket: # aws s3 cp s3://my-bucket////artifact ./artifact # 2. Decrypt, if it is encrypted gpg --decrypt ./artifact > ./app.dump.gz # 3. Decompress, if it is compressed. Check before you assume either. file ./app.dump.gz # "gzip compressed data" means this step is needed gunzip ./app.dump.gz # 4. Restore createdb scratch pg_restore --dbname scratch ./app.dump ``` **Decrypting is not decompressing.** Compression is applied before encryption, so a backup with both leaves you holding a gzip file after `gpg`. `pg_restore` given that file reports a corrupt or unrecognised archive, which reads like a damaged backup and is not one. Run `file` between the two steps. ## Custom format or plain SQL [#custom-format-or-plain-sql] Our `postgres` source always produces **custom format** (`pg_dump -Fc`). A `script` source running `pg_dump` might produce either, so check rather than assume. ```bash head -c 5 ./app.dump # "PGDMP" means custom format ``` | Format | Restore with | Notes | | ---------------- | ------------ | ------------------------------------------ | | Custom (`PGDMP`) | `pg_restore` | Selective restore, parallelism, reordering | | Plain SQL | `psql -f` | Executed top to bottom, no selection | ## Restore into a scratch database first [#restore-into-a-scratch-database-first] Always. Not as a best practice, as the default. ```bash createdb scratch pg_restore --dbname scratch --no-owner --no-privileges ./app.dump psql -d scratch -c '\dt' psql -d scratch -c 'SELECT count(*) FROM users;' ``` A restore into the live database is destructive and irreversible, and it is the wrong first move even during an incident. Restore to scratch, confirm the data is what you expect, then decide how to promote it. `pg_restore --clean` drops objects before recreating them. Run it against the wrong `--dbname` and you have destroyed a working database with a backup command. Type the target twice before you press enter. ## Useful flags [#useful-flags] | Flag | Use | | ------------------------------- | --------------------------------------------------------------------- | | `--no-owner` | Do not restore ownership. Needed when the original roles do not exist | | `--no-privileges` | Skip `GRANT`/`REVOKE` | | `--jobs=4` | Restore in parallel. Custom format only, and much faster on big dumps | | `--single-transaction` | All or nothing. The database is untouched if anything fails | | `--schema-only` / `--data-only` | Restore one half | | `--table=users` | One table | | `--list` | Print the table of contents without restoring anything | `--list` is the one to reach for first when you are not sure what is in the file: ```bash pg_restore --list ./app.dump | head -40 ``` ### Restoring one table [#restoring-one-table] ```bash pg_restore --dbname scratch --table=users --data-only ./app.dump ``` This is the everyday case: somebody deleted rows, and you need yesterday's copy of one table rather than yesterday's copy of the database. Restore it into scratch and copy the rows across with SQL, rather than restoring over the live table. ## Roles are not in the dump [#roles-are-not-in-the-dump] Roles and users are cluster-wide, so `pg_dump` of one database does not include them. A restore into a fresh cluster fails with `role "app" does not exist` unless you either create the roles first or restore with `--no-owner --no-privileges`. If you need roles backed up too, that is a [`script` source](/docs/backups/sources/script) running `pg_dumpall --roles-only`, as a second backup: ```bash pg_dumpall --roles-only > "$SAVED_OUTPUT" ``` ## Version skew [#version-skew] | Direction | Works | | ---------------------------------------- | -------------------------------------------- | | Dump from an older server → newer server | Yes. This is the supported upgrade path | | Dump from a newer server → older server | **No.** Expect syntax errors partway through | Use a `pg_restore` at least as new as the server that produced the dump. If the restore fails with unexpected syntax errors partway through, version skew is the first thing to check, not corruption. ```bash pg_restore --version psql -d postgres -c 'SHOW server_version;' ``` ## The schema-drift trap [#the-schema-drift-trap] This is the failure that rehearsal exists to catch, and the one that bites hardest in an incident. A dump restores the schema **as it was**. Your application expects the schema **as it is**. Restore a backup from before three migrations and the database is internally consistent and still wrong for the code running against it. Two situations, two different answers: | Situation | What to do | | --------------------------------------------- | ----------------------------------------------------------- | | Restoring the whole database after a disaster | Restore, then run migrations forward to current | | Recovering lost rows from an old backup | Restore to scratch, extract just the rows, insert into live | The second is much more common, and restoring the whole database over a live one to recover one table is how a small incident becomes a large one. Keep your migrations in version control and runnable from any historical schema state. A backup you can restore but not migrate forward is only half a recovery plan, and the missing half is not ours to supply. ## Verifying the restore [#verifying-the-restore] Restoring without errors is not the same as restoring correctly. ```bash # Row counts against what you expect psql -d scratch -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;" # The most recent row, against when the backup ran psql -d scratch -c "SELECT max(created_at) FROM orders;" ``` That second query is the important one. It tells you the **actual** recovery point, which is when the dump started rather than when the run finished, and it is the number you report to everyone else during an incident. ## Next [#next]