========================================================
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.
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;
[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.
[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.
