Point in time recovery PostgresSQL 9.4.1 (without using pg_basebackup)
9:35 AM1. Backup Process
a) Edit configuration file: vi /var/lib/pgsql/9.4/data/postgresql.conf
wal_level = hot_standby (uncomment wal_level and set it as hot_standby) archive_mode = on (uncomment archive_mode and set it as on) archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f' (uncomment archive_command and set the command as mentioned here)
b) Create archive directory, change ownership and permission
shell> mkdir /var/lib/pgsql/pg_log_archive shell> chown postgres.postgres /var/lib/pgsql/pg_log_archive && chmod 700 /var/lib/pgsql/pg_log_archive
c) Restart database instance
shell> /etc/init.d/postgresql-9.4 restart
d) Backup your database with super user
postgres-#SELECT pg_start_backup('varlibpgsql', true);
e) Perform file system back of postgresql data directory
shell> tar cfP /var/lib/pgsql/db_file_backup.tar /var/lib/pgsql/9.4/data
f) Stop backup process
postgres-#SELECT pg_stop_backup();
2. Restore process
a) Stop Database instance
shell> /etc/init.d/postgresql-9.4 stop
b) Create a tmp directory and move all the data from datadir to tmp directory
shell> mkdir /tmp/data.old shell> mv /var/lib/pgsql/9.4/data/* /tmp/data.old/
c) Now copy backup file to datadir and extract the files
shell>cp /var/lib/pgsql/db_file_backup.tar /var/lib/pgsql/9.4/data/ shell>cd /var/lib/pgsql/9.4/data/ shell>tar -xvfP db_file_backup.tar shell> rm -f db_file_backup.tar
d) Remove any files present in pg_xlog
shell>rm -rf /var/lib/pgsql/9.4/data/pg_xlog/*.*
e) Compare and copy unarchived files from /tmp/data.old/pg_xlog to /var/lib/pgsql/9.4/data/pg_xlog
shell>ls -ltrh /tmp/data.old/pg_xlog total 65M -rw------- 1 postgres postgres 16M May 14 02:37 000000010000000000000005 -rw------- 1 postgres postgres 16M May 14 02:37 000000010000000000000002 -rw------- 1 postgres postgres 302 May 14 02:37 000000010000000000000002.00000028.backup drwx------ 2 postgres postgres 4.0K May 14 02:39 archive_status -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000003 -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000004 -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000006 -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000007 -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000008 shell>ls -ltrh /var/lib/pgsql/pg_log_archive/ total 97M -rw------- 1 postgres postgres 16M May 14 02:37 000000010000000000000001 -rw------- 1 postgres postgres 16M May 14 02:37 000000010000000000000002 -rw------- 1 postgres postgres 302 May 14 02:37 000000010000000000000002.00000028.backup -rw------- 1 postgres postgres 16M May 14 02:39 000000010000000000000003 -rw------- 1 postgres postgres 41 May 14 02:54 00000002.history -rw------- 1 postgres postgres 16M May 14 02:59 000000020000000000000003 -rw------- 1 postgres postgres 16M May 14 03:25 000000020000000000000004 -rw------- 1 postgres postgres 16M May 14 03:31 000000020000000000000005 -rw------- 1 postgres postgres 295 May 14 03:31 000000020000000000000005.00000028.backup shell> cp /tmp/data.old/pg_xlog/000000010000000000000006 /var/lib/pgsql/9.4/data/pg_xlog/ shell> cp /tmp/data.old/pg_xlog/000000010000000000000007 /var/lib/pgsql/9.4/data/pg_xlog/ shell> cp /tmp/data.old/pg_xlog/000000010000000000000008 /var/lib/pgsql/9.4/data/pg_xlog/
f) Create a recovery file and place it in datadir and also temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful.
shell>cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf shell>chown postgres.postgres /var/lib/pgsql/9.4/data/recovery.conf && chmod 600 /var/lib/pgsql/9.4/data/recovery.conf
g) Edit recovery file, uncomment and update restore _command: vi /var/lib/pgsql/9.4/data/recovery.conf
restore_command = 'cp /var/lib/pgsql/pg_log_archive/%f %p'
h) Start Database instance
shell> service postgresql-9.4 start Starting postgresql-9.4 service: [ OK ]
i) Check for file under datadir
-rw------- 1 postgres postgres 5.5K May 14 02:53 recovery.done
0 comments