Skip to content

Instantly share code, notes, and snippets.

@1v

1v/postgresql-backup-and-restore.md

Forked from mcandre/postgresql-backup-and-restore.md
Created October 6, 2022 22:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save 1v/a09ea21b812f762432ed848972dba994 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save 1v/a09ea21b812f762432ed848972dba994 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Andrew revised this gist Apr 9, 2020. No changes.
  2. Andrew revised this gist Apr 9, 2020. No changes.
  3. Andrew revised this gist Apr 9, 2020. 1 changed file with 2 additions and 7 deletions.
    9 changes: 2 additions & 7 deletions postgresql-backup-and-restore.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,18 +1,13 @@
    # Backup
    # POSTGRESQL BACKUP AND RESTORE

    ```console
    $ pg_dumpall --clean -U <user> -f backup.sql
    ```

    # Restore

    ```console
    $ psql -U <user> -f backup.sql
    ```

    # Warnings

    * A PostgreSQL user with administrative privileges is required.
    * Using `pg_dump` instead of `pg_dumpall` results in role privileges being reset to nothing.
    * If you forget the `--clean` / `-c` flag to `ph_dumpall`, then the backup SQL script will not be idempotent, and will collide and error with any existing databases.
    * If you forget the `--clean` / `-c` flag to `pg_dumpall`, then the backup SQL script will not be idempotent, and will collide and error with any existing databases.
    * Using `pg_restore` instead of `psql` will reject the plain SQL backup format.
  4. Andrew created this gist Apr 9, 2020.
    18 changes: 18 additions & 0 deletions postgresql-backup-and-restore.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    # Backup

    ```console
    $ pg_dumpall --clean -U <user> -f backup.sql
    ```

    # Restore

    ```console
    $ psql -U <user> -f backup.sql
    ```

    # Warnings

    * A PostgreSQL user with administrative privileges is required.
    * Using `pg_dump` instead of `pg_dumpall` results in role privileges being reset to nothing.
    * If you forget the `--clean` / `-c` flag to `ph_dumpall`, then the backup SQL script will not be idempotent, and will collide and error with any existing databases.
    * Using `pg_restore` instead of `psql` will reject the plain SQL backup format.