Skip to content

Instantly share code, notes, and snippets.

@akhdaniel

akhdaniel/auto backup + restore PGSQL.md

Created June 21, 2022 03:14
Show Gist options
  • Save akhdaniel/2bc59542c80d821e9f16d5fcf6e0cfbc to your computer and use it in GitHub Desktop.
Save akhdaniel/2bc59542c80d821e9f16d5fcf6e0cfbc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. akhdaniel created this gist Jun 21, 2022.
    43 changes: 43 additions & 0 deletions auto backup + restore PGSQL.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,43 @@
    creat backup script ~/scripts/db_backup.sh
    -----
    ```sh
    #!/bin/bash
    DIR=`date +%d-%m-%y`
    DEST=/db_backups/$DIR
    mkdir $DEST

    PGPASSWORD='postgres_password' pg_dump --inserts --column-inserts --username=postgres_user --host=postgres_host --port=postgres_port postgres_database_name > dbbackup.sql
    ```


    Now chmod the script to allow it to for execution
    ---

    ```sh
    chmod +x ~/scripts/db_backup.sh
    ```


    How to set up Cron (to automate the process)
    ---

    ```sh
    crontab -e
    ```

    Paste the below commands at the bottom to automate the process

    ```sh
    0 0 * * * ~/scripts/db_backup.sh # take a backup every midnight
    ```


    Db Restore script ~/scripts/db_restore.sh
    ----

    ```sh
    pg_restore -d db_name /path/to/your/file/dump_name.sql -c -U db_user
    ```