• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Blog

      • Home
      • Blog
      • Blog
      • PostgreSQL Backup & Restore

      PostgreSQL Backup & Restore

      • Posted by 2ndnijam
      • Categories Blog
      • Date January 4, 2019
      • Comments 0 comment

      If you are using PostgreSQL in a production environment, it is important to take precautions to ensure that your users’ data is not lost. By frequently backing up your database, and/or automating backups with a cron task, you will be able to quickly restore your system in the event that your database is lost or corrupted. Fortunately, PostgreSQL includes tools to make this task simple and easy to manage.

      Before You Begin 

      You should have a working installation of PostgreSQL on your system before beginning this guide. Go through our How to Install PostgreSQL on Ubuntu guide to install PostgreSQL and create a sample database.

      Note

      The steps in this guide require root privileges. Be sure to run the steps below as root or with the sudo prefix. For more information on privileges, see our Users and Groups guide.

      One-Time SQL Dump 

      Single Database 

      PostgreSQL provides the pg_dump utility to simplify backing up a single database. This command must be run as a user with read permissions to the database you intend to back up.

      1. Log in as the postgres user:
        su - postgres
        
      2. Dump the contents of a database to a file by running the following command. Replace dbnamewith the name of the database to be backed up.
        pg_dump dbname > dbname.bak
        

        The resulting backup file, dbname.bak, can be transferred to another host with scp or stored locally for later use.

      3. To demonstrate restoring lost data, delete your example database and create an empty database in its place:
        dropdb dbname
        createdb dbname
        
      4. Restore the database using psql:
        psql test < dbname.bak
        

        There are several options for the backup format:

        • *.bak: compressed binary format
        • *.sql: plaintext dump
        • *.tar: tarball

        Note

        By default, PostgreSQL will ignore any errors that occur during the backup process. This can result in an incomplete backup. To prevent this, you can run the pg_dump command with the -1 option. This will treat the entire backup procedure as a single transaction, which will prevent partial backups in the event of an error:

        pg_dump dbname > dbname.bak
        

      Remote Database

      Just as psql allows you to connect to a remote host, pg_dump can be run from a client computer to back up data on a remote server. Use the -h flag to specify the IP address of your  2ND and -pto identify the port on which PostgreSQL is listening:

      pg_dump -h 198.51.100.0 -p 5432 dbname > dbname.bak
      

      All Databases

      Because pg_dump only creates a backup of one database at a time, it does not store information about database roles or other cluster-wide configuration. To store this information, and back up all of your databases simultaneously, you can use pg_dumpall.

      1. Create a backup file:
        pg_dumpall > pg_backup.bak
        
      2. Restore all databases from the backup:
        psql -f pg_backup.bak postgres
        

      Automate Backups with a Cron Task

      You may want to set up a cron job so that your database will be backed up automatically at regular intervals. The steps in this section will set up a cron task that will run pg_dump once every week.

      1. Make sure you are logged in as the postgres user:
        su - postgres
        
      2. Create a directory to store the automatic backups:
        mkdir -p ~/postgres/backups
        
      3. Edit the crontab to create the new cron task:
        crontab -e
        
      4. Add the following line to the end of the crontab:
        crontab
        0 * * * 0 pg_dump -U postgres dbname > ~/postgres/backups/dbname.bak
      5. Save and exit from the editor. Your database will be backed up at midnight every Sunday. To change the time or frequency of the updates, see our Schedule Tasks with Cron guide.
      6. Sample crontjob sheduler

      • Share:
      2ndnijam

      Previous post

      Taking PostgreSQL VIEWS backup with Seperate File
      January 4, 2019

      Next post

      Script To Listing Postgresql dead tubles
      January 4, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • Are you going to take your first ste
      • How to start working on an application?
      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      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

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now