Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Steps of PostgreSQL (point in time recovery) PITR

in this post I will explained "how to perform point in time (PITR )" in postgreSQL Server and we can recover our database certain point using old backup and xlog (in PostgreSQL 10 wal log )

1. root- mkdir walbkp basebkp
1.1 chown of both walbkp basebkp to enterprisedb

2. su - enterprisedb- navigate to data, mkdir wals.
2.1 change settings in vi postgresql.conf as below
archive_command='cp %p /opt/PostgresPlus/9.1AS/data/wals/%f'

3. root- restart the cluster using the command 
cd /etc/init.db
./ppas-9.1 restart

4. su - enterprised-- check inside the db where all the settings are been active, using
show archive_mode or show archive_command or show wal_level and make sure everything is active.

5. now create table t using pg_class & pg_description, to generate huge amount of data blocks.

6. while its being created, check whether blocks are moving to wals from pg_xlog.

7. now start a hotbackup, which is optional, but good to have in case primary backup failures.
using the command inside the db, 
edb=# select pg_switch_xlog(); -- to flush the wal buffer and move data to xlog.
edb=# select pg_start_backup('base'); - to start the hotbackup.

8. in root- navigate to basebkp, create a tar file for base cluster i.e data, using command
basebkp# tar -czvf /opt/PostgresPlus/9.1/data

9. su - enterprised-- inside the db, stop the hotbackup process using,
edb=# select pg_stop_backup(); 

10. create tables like t2,t3,t4 etc and also note its time of creation using select current_timestamp.
note: make sure tables are large in size, so use generate series to create huge entries.

11. once all the tables are created now crash the cluster using the command,
kill -9 $(head -1 /opt/PostgresPlus/9.1/data/postmaster.pid)

12. in root- mv data /olddata -- move crashed cluster.

13. now untar the base file in basebkp using
basebkp# tar -xzvf then u ll get a file opt
now navitage to this opt file as , cd opt/PostgresPlus/9.1AS
move this data to home directory location /opt/PostgresPlus/9.1AS

14. su - enterprisedb -- now restart the cluster using command,
./pg_ctl -D /opt/PostgresPlus/9.1AS/data/ restart -m f

15. connect to db and check whether initial table as been recovered.

16. then quit the db and then stop the cluster using command,
./pg_ctl -D /opt/PostgresPlus/9.1AS/data/stop -m f

17. in root- move the wals information from old cluster to walbkp using command,cd /opt/PostgresPlus/9.1AS/olddata/wals
mv 0* /opt/PostgresPlus/9.1AS/walbkp and make sure all enteries are in enterprisedb permission.

18. copy oldcluster pg_xlog to new cluster using the command,
cd /opt/PostgresPlus/9.1AS/olddata/pg_xlog
cp 0* /opt/PostgresPlus/9.1AS/data/pg_xlog ,L
note: we will prompt for overwritten for few enteries..

19. then create a recovery.conf in new cluster data..
cd /opt/PostgresPlus/9.1AS/data/
vi recovery.conf
inside the file, write below as,
restore_command='cp /opt/PostgresPlus/9.1AS/walbkp/%f %p'
recovery_target_time='time of table to b recovered' -- give till seconds i.e dd-mon-yy hh:mm:ss

20. su - enterprisedb, now start the cluster using command,
./pg_ctl -D /opt/PostgresPlus/9.1AS/data/ start

21. now the prob starts here as it cannot login to database when trying with ./psql edb.


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger