TutorialDBA - Support | Training | Consultant

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...

PostgreSQL “Point-in-time Recovery” (PITR) also called as incremental database backup , online backup or may be archive backup. The PostgreSQL server records all users’ data modification transaction like insert, update or delete and write it into a file call write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do roll-forward changes made since last database full backup. It is backup the latest archivelog since the last backup instead of full database backup. Advantages Zero down time  – The incremental database backup is important to critical system that can not afford even a minute down time. With Point-in-time Recovery, database backup down time can totally eliminated because this mechanism can make database backup and system access happened at the same time. Save storage size  – with incremental database backup, we backup the latest archive log file since last backup instead of full database backup everyday. Point-in-time Recov...