Skip to content

Instantly share code, notes, and snippets.

@redlotus

redlotus/pgsql_backup.sh

Forked from sirbrillig/pgsql_backup.sh
Created July 28, 2018 13:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save redlotus/d6ff84c0e9c618537027cf88a265fa74 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save redlotus/d6ff84c0e9c618537027cf88a265fa74 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @sirbrillig sirbrillig revised this gist Jan 24, 2013. 1 changed file with 8 additions and 11 deletions.
    19 changes: 8 additions & 11 deletions pgsql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -5,27 +5,24 @@

    BACKUP_DIR=/pg_backup
    DAYS_TO_KEEP=14
    FILE=`date +"%Y%m%d%H%M"`_pg_backup.sql
    FILE_SUFFIX=_pg_backup.sql
    DATABASE=
    USER=postgres

    OUTPUT_FILE=${BACKUP_DIR}/${FILE}
    FILE=`date +"%Y%m%d%H%M"`${FILE_SUFFIX}

    # (2) in case you run this twice in one day, remove the previous version of the file
    unalias rm 2> /dev/null
    rm ${OUTPUT_FILE} 2> /dev/null
    rm ${OUTPUT_FILE}.gz 2> /dev/null
    OUTPUT_FILE=${BACKUP_DIR}/${FILE}

    # (3) do the database backup (dump)
    # do the database backup (dump)
    # use this command for a database server on localhost. add other options if need be.
    pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}

    # (4) gzip the mysql database dump file
    # gzip the mysql database dump file
    gzip $OUTPUT_FILE

    # (5) show the user the result
    # show the user the result
    echo "${OUTPUT_FILE}.gz was created:"
    ls -l ${OUTPUT_FILE}.gz

    # (6) prune old backups
    find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-pg_backup.sql.gz" -exec rm -rf '{}' ';'
    # prune old backups
    find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'
  2. @sirbrillig sirbrillig revised this gist Jan 24, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion pgsql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@

    BACKUP_DIR=/pg_backup
    DAYS_TO_KEEP=14
    FILE=`date +"%Y%m%d"`_pg_backup.sql
    FILE=`date +"%Y%m%d%H%M"`_pg_backup.sql
    DATABASE=
    USER=postgres

  3. @sirbrillig sirbrillig revised this gist Jan 24, 2013. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions pgsql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    #!/bin/bash
    #
    # Backup a Postgresql database into a daily file.
    #
  4. @sirbrillig sirbrillig revised this gist Jan 24, 2013. 1 changed file with 19 additions and 14 deletions.
    33 changes: 19 additions & 14 deletions pgsql_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,30 @@
    FILE=`date +"%Y%m%d"`_backup.sql
    DBSERVER=127.0.0.1
    #
    # Backup a Postgresql database into a daily file.
    #

    BACKUP_DIR=/pg_backup
    DAYS_TO_KEEP=14
    FILE=`date +"%Y%m%d"`_pg_backup.sql
    DATABASE=
    USER=postgres
    PASS=

    OUTPUT_FILE=${BACKUP_DIR}/${FILE}

    # (2) in case you run this twice in one day, remove the previous version of the file
    unalias rm 2> /dev/null
    rm ${FILE} 2> /dev/null
    rm ${FILE}.gz 2> /dev/null

    # (3) do the mysql database backup (dump)

    # use this command for a database server on a separate host:
    #mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}
    rm ${OUTPUT_FILE} 2> /dev/null
    rm ${OUTPUT_FILE}.gz 2> /dev/null

    # (3) do the database backup (dump)
    # use this command for a database server on localhost. add other options if need be.
    pg_dump ${DATABASE} > ${FILE}
    pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}

    # (4) gzip the mysql database dump file
    gzip $FILE
    gzip $OUTPUT_FILE

    # (5) show the user the result
    echo "${FILE}.gz was created:"
    ls -l ${FILE}.gz
    echo "${OUTPUT_FILE}.gz was created:"
    ls -l ${OUTPUT_FILE}.gz

    # (6) prune old backups
    find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-pg_backup.sql.gz" -exec rm -rf '{}' ';'
  5. @dangerousbeans dangerousbeans renamed this gist Sep 30, 2011. 1 changed file with 0 additions and 0 deletions.
    0 pgsql_backup → pgsql_backup.sh
    View file
    File renamed without changes.
  6. @dangerousbeans dangerousbeans created this gist Sep 30, 2011.
    25 changes: 25 additions & 0 deletions pgsql_backup
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    FILE=`date +"%Y%m%d"`_backup.sql
    DBSERVER=127.0.0.1
    DATABASE=
    USER=postgres
    PASS=

    # (2) in case you run this twice in one day, remove the previous version of the file
    unalias rm 2> /dev/null
    rm ${FILE} 2> /dev/null
    rm ${FILE}.gz 2> /dev/null

    # (3) do the mysql database backup (dump)

    # use this command for a database server on a separate host:
    #mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}

    # use this command for a database server on localhost. add other options if need be.
    pg_dump ${DATABASE} > ${FILE}

    # (4) gzip the mysql database dump file
    gzip $FILE

    # (5) show the user the result
    echo "${FILE}.gz was created:"
    ls -l ${FILE}.gz