{"id":381,"date":"2024-09-09T10:07:13","date_gmt":"2024-09-09T10:07:13","guid":{"rendered":"https:\/\/linux.atheesh.com\/?p=381"},"modified":"2024-09-09T10:12:04","modified_gmt":"2024-09-09T10:12:04","slug":"automating-postgresql-backups-with-logging-a-complete-script-for-cron-job-integration","status":"publish","type":"post","link":"https:\/\/linux.atheesh.com\/index.php\/2024\/09\/09\/automating-postgresql-backups-with-logging-a-complete-script-for-cron-job-integration\/","title":{"rendered":"Automating PostgreSQL Backups with Logging: A Complete Script for Cron Job Integration"},"content":{"rendered":"\n

make the script compatible with cron jobs and have it log each backup event, including any errors, you can modify the script to write to a log file. This will ensure that every time the backup script runs, it logs the event (success or failure) to a log file.<\/p>\n\n\n

PostgreSQL Backup Script with Logging<\/h2>\n\n

<\/h3>\n\n
\n
\"\"
Backup<\/figcaption><\/figure><\/div>\n\n\n
<\/pre>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
#!\/bin\/bash<\/span><\/span>\n<\/span>\n# <\/span>Database<\/span> <\/span>credentials<\/span><\/span>\nDATABASE<\/span>=<\/span>"<\/span>DB NAME<\/span>"<\/span><\/span>\nUSERNAME<\/span>=<\/span>"<\/span>DB USERNAME<\/span>"<\/span><\/span>\nPASSWORD<\/span>=<\/span>"<\/span>DB PASSWORD<\/span>"<\/span><\/span>\nHOST<\/span>=<\/span>"<\/span>localhost<\/span>"<\/span><\/span>\nPORT<\/span>=<\/span>"<\/span>5432<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Directory<\/span> <\/span>to<\/span> <\/span>back<\/span> <\/span>up<\/span><\/span>\nSOURCE_DIR<\/span>=\/var<\/span>\/<\/span>www<\/span>\/html\/pwdwebsite\/public_html\/<\/span><\/span>\n<\/span>\n# <\/span>Target<\/span> <\/span>directory<\/span><\/span>\nTARGET_DIR<\/span>=\/<\/span>home<\/span>\/<\/span>websiteuser<\/span>\/<\/span>backup<\/span>\/<\/span>bak<\/span>\/<\/span><\/span>\n<\/span>\n# <\/span>Log<\/span> <\/span>file<\/span> <\/span>path<\/span><\/span>\nLOG_FILE<\/span>=\/<\/span>home<\/span>\/<\/span>websiteuser<\/span>\/<\/span>backup<\/span>\/<\/span>backup<\/span>.<\/span>log<\/span><\/span>\n<\/span>\n# <\/span>Output<\/span> <\/span>files<\/span><\/span>\nNOW<\/span>=<\/span>$<\/span>(<\/span>date<\/span> <\/span>+<\/span>"<\/span>%Y_%m_%d_%H_%M_%S<\/span>"<\/span>)<\/span><\/span>\nDB_OUTPUT<\/span>=<\/span>$TARGET_DIR<\/span>\/<\/span>db<\/span>.<\/span>$NOW<\/span>.<\/span>sql<\/span>.<\/span>gz<\/span><\/span>\nFILES_OUTPUT<\/span>=<\/span>$TARGET_DIR<\/span>\/<\/span>files<\/span>.<\/span>$NOW<\/span>.<\/span>zip<\/span><\/span>\n<\/span>\n# <\/span>Export<\/span> <\/span>PostgreSQL<\/span> <\/span>password<\/span> <\/span>so<\/span> <\/span>it<\/span> <\/span>is<\/span> <\/span>not<\/span> <\/span>prompted<\/span><\/span>\nexport<\/span> <\/span>PGPASSWORD<\/span>=<\/span>$PASSWORD<\/span><\/span>\n<\/span>\n# <\/span>Function<\/span> <\/span>to<\/span> <\/span>log<\/span> <\/span>messages<\/span><\/span>\nlog_message<\/span>() <\/span>{<\/span><\/span>\n    <\/span>echo<\/span> <\/span>"<\/span>$(date +<\/span>"<\/span>%<\/span>Y<\/span>-%<\/span>m<\/span>-%<\/span>d<\/span> <\/span>%<\/span>H<\/span>:<\/span>%<\/span>M<\/span>:<\/span>%<\/span>S<\/span>"<\/span>) : $1<\/span>"<\/span> <\/span>>><\/span> <\/span>$LOG_FILE<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n# <\/span>Start<\/span> <\/span>backup<\/span> <\/span>log<\/span><\/span>\nlog_message<\/span> <\/span>"<\/span>Backup started.<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Back<\/span> <\/span>up<\/span> <\/span>files<\/span><\/span>\nif<\/span> <\/span>zip<\/span> <\/span>-<\/span>r<\/span> <\/span>$FILES_OUTPUT<\/span> <\/span>$SOURCE_DIR<\/span> <\/span>>\/<\/span>dev<\/span>\/null<\/span> <\/span>2<\/span>>&<\/span>1<\/span>;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Files backup successful: $FILES_OUTPUT<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Files backup failed.<\/span>"<\/span><\/span>\nfi<\/span><\/span>\n<\/span>\n# <\/span>Back<\/span> <\/span>up<\/span> <\/span>PostgreSQL<\/span> <\/span>database<\/span><\/span>\nif<\/span> <\/span>pg_dump<\/span> <\/span>-<\/span>h<\/span> <\/span>$HOST<\/span> <\/span>-<\/span>p<\/span> <\/span>$PORT<\/span> <\/span>-<\/span>U<\/span> <\/span>$USERNAME<\/span> <\/span>-<\/span>d<\/span> <\/span>$DATABASE<\/span> <\/span>|<\/span> <\/span>gzip<\/span> <\/span>><\/span> <\/span>$DB_OUTPUT<\/span> <\/span>2<\/span>>\/<\/span>dev<\/span>\/null;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Database backup successful: $DB_OUTPUT<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Database backup failed.<\/span>"<\/span><\/span>\nfi<\/span><\/span>\n<\/span>\n# <\/span>Unset<\/span> <\/span>the<\/span> <\/span>password<\/span> <\/span>variable<\/span> <\/span>for<\/span> <\/span>security<\/span><\/span>\nunset<\/span> <\/span>PGPASSWORD<\/span><\/span>\n<\/span>\n# <\/span>Remove<\/span> <\/span>files<\/span> <\/span>older<\/span> <\/span>than<\/span> <\/span>5<\/span> <\/span>days<\/span><\/span>\nif<\/span> <\/span>find<\/span> <\/span>$TARGET_DIR<\/span> <\/span>-type<\/span> f -mtime +5 | xargs rm -f 2>\/dev\/null<\/span>;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Old backups removed.<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Failed to remove old backups.<\/span>"<\/span><\/span>\nfi<\/span><\/span>\n<\/span>\n# <\/span>End<\/span> <\/span>backup<\/span> <\/span>log<\/span><\/span>\nlog_message<\/span> <\/span>"<\/span>Backup completed.<\/span>"<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n
Explanation :<\/h3>\n\n\n
    \n
  1. Log File Path<\/strong>:\n
      \n
    • A log file (LOG_FILE=\/home\/websiteuser\/backup\/backup.log<\/strong><\/code>) is created. This file will store messages about each backup operation.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • Logging Function<\/strong>:<\/li>\n<\/ol>\n\n\n\n
      <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
      log_message<\/span>() <\/span>{<\/span><\/span>\n    <\/span>echo<\/span> <\/span>"<\/span>$(date +<\/span>"<\/span>%<\/span>Y<\/span>-%<\/span>m<\/span>-%<\/span>d<\/span> <\/span>%<\/span>H<\/span>:<\/span>%<\/span>M<\/span>:<\/span>%<\/span>S<\/span>"<\/span>) : $1<\/span>"<\/span> <\/span>>><\/span> <\/span>$LOG_FILE<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
      A function called log_message<\/code> <\/strong>is created to handle the logging. It records the date and time along with the message passed to it and appends it to the log file.<\/p>\n\n\n
      Backing up Files:<\/h3>\n\n\n
      <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
      if<\/span> <\/span>zip<\/span> <\/span>-<\/span>r<\/span> <\/span>$FILES_OUTPUT<\/span> <\/span>$SOURCE_DIR<\/span> <\/span>>\/<\/span>dev<\/span>\/null<\/span> <\/span>2<\/span>>&<\/span>1<\/span>;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Files backup successful: $FILES_OUTPUT<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Files backup failed.<\/span>"<\/span><\/span>\nfi<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
        \n
      • The zip command logs whether the file backup succeeded or failed.<\/li>\n<\/ul>\n\n\n
        Backing up the PostgreSQL Database:<\/h3>\n\n\n
        <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
        if<\/span> <\/span>pg_dump<\/span> <\/span>-<\/span>h<\/span> <\/span>$HOST<\/span> <\/span>-<\/span>p<\/span> <\/span>$PORT<\/span> <\/span>-<\/span>U<\/span> <\/span>$USERNAME<\/span> <\/span>-<\/span>d<\/span> <\/span>$DATABASE<\/span> <\/span>|<\/span> <\/span>gzip<\/span> <\/span>><\/span> <\/span>$DB_OUTPUT<\/span> <\/span>2<\/span>>\/<\/span>dev<\/span>\/null;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Database backup successful: $DB_OUTPUT<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Database backup failed.<\/span>"<\/span><\/span>\nfi<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
          \n
        • Logs success or failure for the database backup.<\/li>\n<\/ul>\n\n\n
          Removing Old Files:<\/h3>\n\n\n
          <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
          if<\/span> <\/span>find<\/span> <\/span>$TARGET_DIR<\/span> <\/span>-type<\/span> f -mtime +5 | xargs rm -f 2>\/dev\/null<\/span>;<\/span> <\/span>then<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Old backups removed.<\/span>"<\/span><\/span>\nelse<\/span><\/span>\n    <\/span>log_message<\/span> <\/span>"<\/span>Failed to remove old backups.<\/span>"<\/span><\/span>\nfi<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
            \n
          • Logs success or failure when removing backups older than 5 days.<\/li>\n<\/ul>\n\n\n
            Ending the Backup:<\/h3>\n\n\n
            <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
            log_message<\/span> <\/span>"<\/span>Backup completed.<\/span>"<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
              \n
            • Logs the completion of the entire backup process.<\/li>\n<\/ul>\n\n\n
              Cron Job Compatibility:<\/h2>\n\n\n
                \n
              • In a cron job, output isn’t visible directly, so logging to a file ensures you can check later if the job ran correctly.<\/li>\n\n\n\n
              • Redirecting command output (>\/dev\/null 2>&1) ensures that only logs are written to the log file without showing any command output on the terminal, which is important when running via cron.<\/li>\n<\/ul>\n\n\n
                Cron Job Setup<\/h2>\n\n\n
                You can schedule this script to run regularly using a cron job. For example, to run the script every day at 2 AM:<\/p>\n\n\n\n
                  \n
                1. Open your crontab:<\/li>\n<\/ol>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  crontab<\/span> <\/span>-<\/span>e<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  2. Add the following line to schedule the script to run at 2 AM every day:<\/p>\n\n\n\n
                  <\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
                  0<\/span> <\/span>2<\/span> <\/span>*<\/span> <\/span>*<\/span> <\/span>*<\/span> <\/span>\/<\/span>path<\/span>\/<\/span>to<\/span>\/<\/span>your<\/span>\/<\/span>backup_script<\/span>.<\/span>sh<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
                  Make sure to replace \/path\/to\/your\/backup_script.sh with the actual path to the script.<\/p>\n\n\n\n
                  This will run the script daily, log the results in the specified log file, and ensure the backups are handled correctly.<\/p>\n","protected":false},"excerpt":{"rendered":"
                  make the script compatible with cron jobs and have it log each backup event, including any errors, you can modify the script to write to a log file. This will ensure that every time the backup script runs, it logs the event (success or failure) to a log file. PostgreSQL Backup Script with Logging Explanation […]<\/p>\n","protected":false},"author":1,"featured_media":386,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/381"}],"collection":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/comments?post=381"}],"version-history":[{"count":4,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/381\/revisions"}],"predecessor-version":[{"id":388,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/381\/revisions\/388"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media\/386"}],"wp:attachment":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media?parent=381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/categories?post=381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/tags?post=381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}