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_mode=on
archive_command='cp %p /opt/PostgresPlus/9.1AS/data/wals/%f'
wal_level=archive


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.

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger