Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Backup & Recovery

In this tutorial i explained about how to take PostgreSQL Backup,how to restore it and how to recover it and  i explained also what are the method in postgresql  to taking backup or restore.
  • In PostgreSQL Backup & Recovery are very user friendly comparing with other database. 
  • My pictorial presentation on PostgreSQL Backup and Recovery gives a complete conceptial idea. Looking into Diagram you can make out which backups can be used to restore or recover.

PostgreSQL Physical Backup:
1.Hot backup
2.Cold backup

1.Hot backup:

  • cluster will be up and running and the Database should be in Archive Log Mode which is mandatory for Online Backups. 
  • Two system functions will notify the instance about starting and stopping the Hot Backup process(pg_start_backup(),pg_stop_backup()). 
  • this method is not a fully accurate consistency backup but before issue cp command  you specified "select pg_start_backup('label_name') " so your backup get much better consistency .
  • when online backup is in process, couple of the files created and deleted.
  • pg_start_backup('label') and pg_stop_backup are the two system functions to perform the Online Backup. 
  • pg_start_backup('label') a file backup_label is created under $PGDATA directory and with pg_stop_backup() a file 'wal-segement-number.backup' file created under $PGDATA/pg_xlog.
  •  Backup_label will give the start time and Checkpoint location of WAL Segment, it will also notify the PostgreSQL instance that Cluster is in BACKUP-MODE. 
  • 'wal-segment-number.backup' file under $PGDATA/pg_xlog directory describes the start and stop time, Checkpoint location with WAL segement number.  
  • After pg_stop_backup(), backup_label file is deleted by the PostgreSQL instance.

    PostgreSQL Hot backup steps:-
    Step 1.Database should be online no need to stop the server.
    Step 2.PostgreSQL server should be archivelog mode.
    Step 3.Issue the "select pg_start_backup('lable_name');" function in psql terminal before  issuing backup copy command 
    Step 4.Set the PGDATA Environment variable  becouse The PGDATA variable should point to the data directory of PostgreSQL,We’ll backup PGDATA directory, so it WIll contain all databases in the instance (remember that PostgreSQL is a multitenant database)
    Step 5.Issuse the Linux backup copy command following any one 
    cp -r $PGDATA  /your_backup_location
    tar czf backup.tar.gz $PGDATA
    rsync -a $PGDATA /wherever/data 
    Step 6.Issue the "select pg_start_backup('lable_name');" function  in psql terminal after backup completed.

    2.cold backup:
    • its a simple file system backup of /data directory when Postgres Instance is down .
    • the database server should be shut down before copying. 
    • PostgreSQL gives flexibility to keep pg_xlog and pg_tblspce in different mount points via softlink. 
    • While copying the /data directory including the soft link's data, use the below command.
    • If you used this method you will get fully consistency backup than hot backup.
    PostgreSQL cold backup steps:-
    Step 1.Stop the server using "pg_ctl stop -D /your_database_data_directory_path
    Step 2.Set the PGDATA Environment variable.
    Step 3.Issuse the Linux backup copy command following any one
    cp -r $PGDATA  /your_backup_location
    tar czf backup.tar.gz $PGDATA
    rsync -a $PGDATA /wherever/data 
    Step 4.After backup completed start the server using "pg_ctl start -D /your_database_data_directory_path"

    PostgreSQL Logical Backup:
    • pg_dump,pg_dumpall,pg_basebackup and pg_restore utilities are  used for logical backups. 
    • pg_dump will help in taking backups for Database level, Schema level and Table Level. 
    • Pg_dumpall used for cluster level dump.
    Three formats are supported to taking PostgreSQL Backup they are:
    1. Plain SQL format(readable,large and is also Default format )
    2. Custom Format (readable ,small and  is  also called Binary format)
    3. Tar Format(ideal for restore)
    You  can restore the dump two method 
    1. psql 
    2. pg_restore
    • If your dumps(backup) are Custom or Tar Format  you need to use only pg_restore utility 
    • If your dumps(backup) are Plain SQL format you need to use  psql utility 
    Note: I hope you haven’t surprised by $PGDATA. The PGDATA variable should point to the data directory of PostgreSQL. In my previous post,I recommend to declare these variables in .bash_profile script.

    PostgreSQL pg_dump Backup:
    --PostgreSQL plain Format Dump
    $ pg_dump -U username -Fp dbname  >  filename.txt
    $ pg_dump -U username dbname -f  filename.txt
    $ pg_dump -Fp -U username dbname -f  filename.txt 
    --PostgreSQL custom Format Dump 
    $ pg_dump -Fc dbname -f filename.bak
    --PostgreSQL tar Format Dump 
    $ pg_dump -Ft dbname -f filename.tar

    PostgreSQL pg_dump  Restore:
    If your database already exists you only need to run the following restore method:
    --For restoring plain text format
    $ psql -U username -f filename db_name 
    --For restoring custom format
    $ pg_restore -Fc -U username -d dbname filename.bak 
    --For restoring tar format
    $ pg_restore -U username -d dbname filename.tar 
    However, if you're creating your database new from the restore you'll want to run a command similar to the following:
    --For restoring plain text format
    $ psql -C -U username -f filename db_name 
    --For restoring custom format
     $ pg_restore -Fc -C -U username -d dbname filename.bak
    --For restoring tar format
    $ pg_restore -C -U username -d dbname filename.tar 
    Note: Schema Level and Tables Level dumps can be performed in the same way by adding related options. 

    Cluster Level Dump:
    $ pg_dumpall -p portnumber > filename.txt 
    For restoring use psql command
    $ psql -f filename.txt 


    Popular posts from this blog

    How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

    7 Steps to configure BDR replication in postgresql

    How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

    vacuumlo - removing large objects orphans from a database PostgreSQL

    ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory