Vivek Web Wiki: 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.

Vivek Web Wiki

Vivek

Tuesday 29 October 2013

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

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

1 comment:

Total Pageviews