Skip to content

PostgreSQL: restore backup

By default we take nightly backups of the PostgreSQL instance. Backup defaults:

  • Location: /backups/postgresql
  • Format: Custom

Contact us at support@sre.combell.com if you want changes for these backups.

Depending on the configured backup type, you will need to restore with different commands.

1. Custom restore with pg_restore

pg_restore -d <DATABASE_NAME_RESTORE_INTO> -U <POSTGRESQL_RW_USER> <PATH_TO_BACKUP>

Options

More Info: https://www.postgresql.org/docs/current/app-pgrestore.html

Common options: 

-t table
--table=table
Restore definition and/or data of only the named table. Make sure to read the notes in the official documentation.

-c
--clean
Before restoring database objects, issue commands to DROP all the objects that will be restored. This option is useful for overwriting an existing database. If any of the objects do not exist in the destination database, ignorable error messages will be reported, unless --if-exists is also specified.

-h host
--host=host
Specifies the host name of the machine on which the server is running. Often used when you want to use password authentication.

-x
--no-privileges
--no-acl
Prevent restoration of access privileges (grant/revoke commands).

example: 

pg_restore -d restore_db -U my_rw_user /backups/postgresql/2026-08-04-daily/my_database.custom -h localhost -c -x

2. Plain restore with psql

psql <DATABASE_NAME_RESTORE_INTO> -U <POSTGRESQL_RW_USER> < <(zcat <PATH_TO_BACKUP>)

Options

More Info: https://www.postgresql.org/docs/current/app-psql.html

Common options: 

-h host
--host=host
Specifies the host name of the machine on which the server is running. Often used when you want to use password authentication.


 psql restore_db -U my_rw_user -h localhost < <(zcat /backups/postgresql/latest/my_database.sql.gz)