Vivek

Tuesday, 29 October 2013

A guide to Online backup and Point-in-time Recovery (PITR) method. Postgress Incremental Backup.

========================================================
PostgresSQL "Point-in-time Recovery" also called as incremental database backup, online backup or may be archive backup. At all times, PostgresSQL server records all users 'data modification transactions like insert, update or delete' and write them into a file called Write-Ahead-Log(WAL) file which is located in the 'pg_xlog/' sub directory of the cluster's data directory. This log exists primarily for crash-safety purposes. If the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the backup and then replay from the backed-up WAL files to bring the backup up to current time.


Summary of PostgreSQL Backup Steps
=============================================================================
1. Create database cluster with name 'data' & initialize it.
2. Modify postgresql.conf to support archive log.
3. Start database.
4. Create 'testdb' database with user 'postgres'.
5. Create table 'testPITR1' and populate it.
6. Create a full databse backup – base backup
7. Create table 'testPITR2' and populate it.
PostgreSQL Backup Steps
===================================================================

 Step 1: Create database cluster with name 'data' & initialize it.

             1.1 Create 'data' db cluster
        
             [root@localhost 8.3]#pwd
                 /opt/PostgreSQL/8.3/
             [root@localhost 8.3]#mkdir data
             [root@localhost 8.3]#chown postgres:postgres -R data
             [root@localhost 8.3]#chmod 0700 -R data
           
           1.2 Initialize database cluster
            
             [root@localhost 8.3]#su postgres
              bash-3.2$bin/initdb data
            
            1.3 Create 'pg_log' folder if it is not there
         
                [root@localhost 8.3]#mkdir pg_log
                [root@localhost 8.3]#chown postgres:postgres -R pg_log  
  
 Step 2: Modify postgresql.conf to support archive log.
  
            we need to make some changes in postgresql.conf file to tell PostgreSQL
            how to copy or archive WAL files that generated from PostgreSQL server.
  
            2.1 Open postgresql.conf
          
                [root@localhost data]# vi postgresql.conf
              
            2.2 Make following changes in postgresql.conf
     
                fsync = on
                synchronous_commit = on
                wal_sync_method = fsync
                full_page_writes = on
                checkpoint_segments = 4
                archive_mode = on
                archive_command = 'cp %p /opt/pgsqlbackup/wals/%f'
  
            2.3 Create a wals folder
          
                [root@localhost pgsqlbackup]pwd
                   /opt/pgsqlbackup
                [root@localhost pgsqlbackup]#mkdir /opt/pgsqlbackup/wals

Notes:- Relation between 'pg_xlog/' folder and 'wals/' folder.
===================================================================
            The 'pg_xlog/' folder contains WAL segment files.Each segment file size is 16MB.
            The segment files are given numeric names that reflect their position in the abstract
            WAL sequence.At any time,number of WAL segment files in this folder limited by
            this formula (2*checkpoint_segments+1).  Suppose,if any new log file being created
            is exceeds this limit,then oldest WAL segment from this folder copied to            
            '/opt/pgsqlbackup/wals' folder and it recyles the that oldest WAL segment file.
Step 3: Start database
  
            [root@localhost ~] /etc/init.d/postgresql-8.3 start
           
                                         OR

            [root@localhost ~] /opt/PostgreSQL/8.3/bin/pg_ctl start -D /opt/PostgreSQL/8.3/data -l tmp/pg.log
                 
Step 4: Create 'testdb' database  with user 'postgres'
          
            [root@localhost ~]su postgres
            bash-3.2$psql
            postgres=#CREATE DATABASE testdb;
            postgres=#GRANT ALL PRIVILEGES ON DATABASE testdb to test;
    
Step 5: Create table 'testPITR1' and populate this 455,252 records like this 
          
            [root@localhost ~]su postgres
            bash-3.2$ psql -d testdb -U postgres
            testdb=#create table testPITR1 as select * from pg_class, pg_description;
          
 Step 6: Create a full databse backup – base backup
                          
            6.1.Connect to the database as a superuser, and issue the command
          
                [root@localhost ~]# su postgres          
                bash-3.2$ psql -d testdb -U postgres
          
            6.2 Issue the base back up command
          
                testdb=#SELECT pg_start_backup('backup1');
          
                pg_start_backup()
                ——————–
                0/6BA9328
                (1 row)  
              
            6.3 Use a tar command to compress all 'data'(database cluster) to make a
                 database base backup.
              
                [root@localhost pgsqlbackup]pwd
                   /opt/pgsqlbackup
                [root@localhost pgsqlbackup]# tar -cvzf /opt/pgsqlbackup/pgdatabk2012Dec12.tar
                    /opt/PostgreSQL/8.3/data/
              
                Note:-
                      1. This command has be executed in other terminal.
                      2. pgdatabk2012Dec12.tar this is the full database backup (base backup) including
                          Postgresql configuration , system and all others files and folder.
              
            6.4 Again connect to the database as a superuser, and issue the command   
          
                select pg_stop_backup();
                ————————
                0/6BA9384
                (1 row)
              
 Step 7:    Create table 'testPITR2' and populate this records like this
  
            [root@localhost ~]su postgres
            bash-3.2$ psql -d testdb -U postgres
            testdb=#create table testPITR2 as select * from pg_class, pg_description;
  
  
    Note:-We created 2 tables for PITR recovery.First, testPIRT1 table is created
          & populated.After creation of this table, base backup of 'data'
          data cluster has been taken.Next,testPIRT2 is created.Assume that
          system was crashed after creation of testPIRT2 table.Now,the base back up
          contains only the testPITR1 table backup,but not testPITR2 table.
        
        
          Now,the Using PITR method whole data base can be recovered till the last
          checkpoint by using 'full base backup'+ archived WAL segment files
          in '/opt/pgsqlbackup/wals/' + remained Unarchived WAL segment files
          which are in 'pg_xlog/' folder of 'data' datacluster which is crashed.


PostgreSQL Point-in-time Recovery Steps
==============================================================================

Stop the database server assuming that it has been crashed.
   
Step 8. Rename 'data' db cluster to 'data.bad.data'
   
            [root@localhost 8.3]#pwd
               /opt/PostgreSQL/8.3/
            [root@localhost 8.3]#mv data data.bad.data
       
Step 9.  Create database cluster with name 'data' & don't initialize
       
            [root@localhost 8.3]#pwd
               /opt/PostgreSQL/8.3/
       
            [root@localhost 8.3]#mkdir data
            [root@localhost 8.3]#chown postgres:postgres -R data
            [root@localhost 8.3]#chmod 0700 -R data
            
Step 10. Extract files from base backup   
          
           10.1 Extract files from tar file
          
                  [root@localhost pgsqlbackup]pwd
                     /opt/pgsqlbackup
                  [root@localhost pgsqlbackup]tar -xvzf pgdatabk2012Dec12.tar
             
             10.2  After extract,move to data folder   
            
                  [root@localhost pgsqlbackup]cd opt/PostgreSQL/8.3/data/
                  [root@localhost data]# pwd
                      /opt/pgsqlbackup/opt/PostgreSQL/8.3/data
             
             10.3 Move all files from 'data' folder to 'data' folder created in
                 
                  Step 9    (**user 'mv' command only)    
                          
                  [root@localhost data] mv * /opt/PostgreSQL/8.3/data
                 
Step 11. Copy files from 'pg_xlog' folder of crashed 'data' folder renamed as
              'data.bad.data' to 'data' cluster files copied in step 10.3
             
              11.1.Before copying,issue commands to clear content of
                     'pg_xlog' of 'data' cluster files copied in step 10.3.
                    
                   [root@localhost 8.3 data]#pwd
                        /opt/PostgreSQL/8.3/data
                   [root@localhost 8.3 data]#cd pg_xlog
                   [root@localhost 8.3 pg_xlog]#pwd
                      /opt/PostgreSQL/8.3/data/pg_xlog
                   [root@localhost 8.3 pg_xlog]#rm -f 0* (remove all wal files)
                   [root@localhost 8.3 pg_xlog]#cd  archive_status
                   [root@localhost archive_status]#pwd
                      /opt/PostgreSQL/8.3/data/pg_xlog/archive_status
                   [root@localhost archive_status]rm -f 0* (remove all wal files)
                  
                  
                   ** Now, no contents in pg_xlog folder except 'archive_status' folder.
                  
             
              11.1 Go to pg_xlog folder of 'data.bad.data'
             
                    [root@localhost 8.3 data.bad.data]#pwd
                       /opt/PostgreSQL/8.3/data.bad.data
                    [root@localhost 8.3 data.bad.data]cd pg_xlog
                    [root@localhost 8.3 pg_xlog]pwd
                      /opt/PostgreSQL/8.3/data.bad.data/pg_xlog
                 
              11.2 Move unarchived files from 'pg_xlog' folder of 'data.bad.data'
                   to 'pg_xlog' folder of 'data' cluster copied in step 10.3.
                  
                   a) To find which are unarchived files, goto
                     'cd /opt/pgsqlbackup/wals/' archived folder of WAL files.
                   b) Find out which file is the last file in above folder.
                      Assume that  '000000010000000000000011' is the last file in
                      the 'wals/' folder.From  that we can understood that
                      from file number  '000000010000000000000012' to last file
                      in 'pg_xlog' folder of crashed 'data.bad.data' cluster has
                      to be copied to 'pg_xlog' folder of 'data' cluster copied
                      in step 10.3
                     
                      ** only user copy command for copying fiels,
                      so that data won't be corrupted
                     
12.Create recovery.conf file
   
           [root@localhost 8.3 data]#pwd
           /opt/PostgreSQL/8.3/data
           [root@localhost 8.3 data]#vi recovery.conf
                #add this content to above file
                restore_command = 'cp /opt/pgsqlbackup/wals/%f %p'

13.Start Recover
   
        [root@localhost ~] /etc/init.d/postgresql-8.3 start
        or
        [root@localhost ~] /opt/PostgreSQL/8.3/bin/pg_ctl  start -D /opt/PostgreSQL/8.3/data -l /tmp/pg.log
       
       a)if recovery is successful,then
            - 'recovery.conf' file name is changed to 'recovery.done'
            - history file is created.
            - WAL segment file number sequence is changed(e.g from x to x+1)
       b)check the log whether data base is recovered or not.
       c)connect to database & check db restoration is done properly or not.

*** Before proceeding recovery,maintain another copy of 'wals' folder contents & 'pg_xlog' folder contents of crashed 'data.bad.data' cluster.

No comments:

Post a Comment