• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      PostgreSQL Backup script to schedule daily -pg_basebackup

      • Home
      • PostgreSQL Backup script to schedule daily -pg_basebackup

      PostgreSQL Backup script to schedule daily -pg_basebackup

      • Posted by 2ndnijam
      • Date January 24, 2019

      Physical backups are all-or-nothing – you take a full copy of the database cluster and you can restore all of it. The challenge is to make the backup consistent. Of course, the easiest way to do that would be to stop PostgreSQL completely and then make a copy of the data using one of the typical Unix tools like cp, scp or rsync. Unfortunately, it is not often that you have the possibility to shutdown your database. Therefore you need to take some precautions to ensure your backup is consistent. To take manual, consistent, physical backups, following steps are required:

      1. You need to create a checkpoint using pg_start_backup(‘some label’)
      2. Copy contents of the data directory
      3. Stop backup using pg_stop_backup()

      This can be simplified by running pg_basebackup:

      1
      root@2ndQ-ubuntu-trusty-64:~# pg_basebackup -U psql -h 10.0.0.110 -x -D /pgbase/

      If you want to have Point-In-Time-Recovery, you need to configure WAL archiving by enabling it:

      1
      2
      wal_level = archive
      archive_mode = on

      You also want to define archive_command in a way it will copy WAL segments to a separate location.

      Restoring base backup is easy – copy the data into clear PostgreSQL data directory and you are all set – once you start the PostgreSQL process, you will restore your data up to the time of the backup. To recover your PostgreSQL to point-in-time, using archived WAL logs, you still need to recover the base backup but then you need to create a recovery.conf file with, at least, a restore_command defined – you want to tell PostgreSQL how it can copy back archived WAL segments to be replayed at the start of the PostgreSQL process.

      This script will take the backup of postgreSQL cluster  daily basis  and it will  remove the backup files if backup age  reached 7 days. its the 

      #Backup Dir
      #Make sure this is a dedicated mount point to PostgreSQL Backups
      #Don't put traling / in path
      #Backup Details
      backup_dir=/backup
      export PGPASSWORD="Nijam@1234"
      export PGPORT="5432"
      echo -e "\n\nBackup Status: $(date +"%d-%m-%y")" >> $backup_dir/Status.log
      echo -e "-----------------------" >> $backup_dir/Status.log
      echo -e "\nStart Time: $(date)\n" >> $backup_dir/Status.log
      /usr/lib/postgresql/9.6/bin/pg_basebackup -U postgres  -w -D $backup_dir/PostgreSQL_Base_Backup_$(date +"%d-%m-%y") -l "`date`" -P -F tar -z -R &>> $backup_dir/Status.log
      echo -e "\nEnd Time: $(date)" >> $backup_dir/Status.log
      
      #Auto Deletion for Backups
      #Value 7 for retention_duration will keep 8 days backups
      
      #retention_duration=7
      #find $backup_dir/PostgreSQL_Base_Backup* -type d -mtime +$retention_duration -exec rm -rv {} \;

      To Shedule the backup at crontab :

      export VISUAL=vim; crontab -e
      0 2 * * * sh /var/lib/postgresql/fullbck.sh

      Note: Once backup is completed please check it and restore the backup to someone testing server, it is for validating the postgresql backup whether script taking backup correctly or not.

      Contact Us

      Fields with (*) are compulsory.

      • Share:
      2ndnijam

      Previous post

      Writing PostgreSQL Script for Enabling Audit Related Parameter
      January 24, 2019

      Next post

      How to upgrade postgreSQL with zero & without Zero DownTime ?
      January 25, 2019

      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?