Hi,
I am designing backup strategy for a PostgreSQL database (v9.3). I have a scenario for recovery of tablespaces:
1. Backup of whole database (including individual tablespaces which are stored on different disks) has been taken at 11AM
2. My disk which stores tablespace- tblspc1 crashed at 2:00PM
3. Can I restore the backup of 11AM (only for one tablespace) and then recover that tablespace to 2:00PM state?
Is this possible? I have attached the steps I tried (I believe logically my steps are wrong, since I am using recovery.conf but I am not replacing data directory).
But is there any way to specify in recovery.conf or otherwise that I would allow me to do recovery of transactions of a particular tablespace? A custom solution which occurs to me is using pg_xlogdump contrib. Has anyone tried something similar?
Best Regards,
Sameer Kumar | Database Consultant
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
M: +65 8110 0350 T: +65 6438 3504 | www.ashnik.com
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).
Step1:
List of Tablespace:
Name | Owner | Location
-------------+----------+----------------------------------
acct_tbsp | postgres | /opt/PostgreSQL/tbspc
pg_default | postgres |
pg_global | postgres |
test_tblspc | postgres | /opt/PostgresPlus/9.2AS/tblspc_1
Step2:
postgres=# select pg_start_backup('online_backup');
pg_start_backup
-----------------
0/15000028
(1 row)
Step3:
Take backup of each tablespace location
cd /opt/PostgresPlus/9.2AS
tar -xzvf tblspc_1.tar.gz tblspc_1
cd /opt/PostgreSQL
tar -xzvf tbspc.tar.gz tbspc
Step4:
Take Backup of data directory.
cd $PGDATA/..
tar -czvf data.tar.gz data
Step5:
postgres=# select pg_stop_backup()
postgres-# ;
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/15015228
(1 row)
Step6: Create test tables on both the tablespaces
postgres=# create table after_online_backup_at_acct_tbsp(col1 int) tablespace acct_tbsp;
CREATE TABLE
postgres=# create table after_online_backup_at_test_tblspc(col1 int) tablespace test_tblspc;
CREATE TABLE
Step7:
Remove the directory for tablespace in another window:
rm -rf /opt/PostgreSQL/tbspc
Step8: Try to access the table which points to removed file
postgres=# select * from after_online_backup_at_tblspc1;
ERROR: relation "after_online_backup_at_tblspc1" does not exist
LINE 1: select * from after_online_backup_at_tblspc1;
Step9: Restart the server- pg_ctl restart -m fast
Check logs: Error noted-
2014-05-20 20:57:24 SGT LOG: database system was shut down at 2014-05-20 20:57:23 SGT
2014-05-20 20:57:24 SGT LOG: could not open tablespace directory "pg_tblspc/41918/PG_9.3_201306121": No such file or directory
2014-05-20 20:57:24 SGT LOG: autovacuum launcher started
2014-05-20 20:57:24 SGT LOG: database system is ready to accept connections
List the tablesapces:
postgres=# \db
List of tablespaces
Name | Owner | Location
-------------+----------+----------------------------------
acct_tbsp | postgres | /opt/PostgreSQL/tbspc
pg_default | postgres |
pg_global | postgres |
test_tblspc | postgres | /opt/PostgresPlus/9.2AS/tblspc_1
Step10: Prepare for a recovery:
Stop the server
pg_ctl stop -m fast
go to data directory
cd $PGDATA
create recovery.conf with below content
restore_command = 'cp /opt/PostgresPlus/arch_dir/%f %p'
restore the tablespace backup:
cd /opt/PostgreSQL
tar -xzvf tbspc.tar.gz
start PostgreSQL
pg_ctl start
