Przejdź do głównej treści

PostgreSQL pg_restore

PostgreSQL's `pg_restore` is a utility for restoring a PostgreSQL database from an archive created by `pg_dump`. It is used to reconstruct the database to its original state from a backup file.

Usage

The `pg_restore` tool is employed when you need to restore data from a backup file into a PostgreSQL database. It supports various archive formats (e.g., custom, directory, tar) and options to control what is restored.

bash
pg_restore [options] backup_file

In this syntax, `backup_file` specifies the archive file to restore from, and `[options]` includes various flags to fine-tune the restoration process.

Examples

1. Basic Restore

bash
pg_restore -d mydatabase mybackup.dump

This restores the entire content of `mybackup.dump` into the `mydatabase` database.

2. Restore with Specific Schema

bash
pg_restore -d mydatabase --schema=public mybackup.dump

This command restores only the `public` schema from `mybackup.dump` into `mydatabase`, allowing for selective restoration.

3. Restore with Parallel Jobs

bash
pg_restore -d mydatabase -j 4 mybackup.dump

Here, the `-j 4` option enables parallel processing with four jobs, which speeds up the restoration of large databases.

4. Restore with Specific Table

bash
pg_restore -d mydatabase --table=mytable mybackup.dump

This command restores only the `mytable` table from `mybackup.dump` into `mydatabase`.

5. Data-Only Restore

bash
pg_restore -d mydatabase --data-only mybackup.dump

This option restores only the data, without creating any database schema objects.

6. Schema-Only Restore

bash
pg_restore -d mydatabase --schema-only mybackup.dump

This option restores only the schema objects, excluding the data.

Tips and Best Practices

  • Use the correct format. Ensure that the archive format of the backup file matches the capabilities of `pg_restore`.
  • Test restores regularly. Perform test restores to verify that your backups are valid and can be restored as expected.
  • Leverage parallel jobs. Use the `-j` option to speed up restoration, especially for large databases.
  • Selective restoration. Use options like `--table`, `--schema`, `--data-only`, or `--schema-only` to restore specific parts of the database when full restoration isn't necessary.
  • Check dependencies. Be aware of object dependencies when restoring specific tables or schemas to avoid missing references. Consider using the `--disable-triggers` option to manage dependencies effectively during data-only restores.
  • Error Handling. Common errors during restoration include missing tablespaces or roles. Ensure that the target database environment matches the original setup. Use log files to diagnose and troubleshoot errors.