Point in time recovery using pg_basebackup with PostgresSQL 9.4.1
9:36 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) max_wal_senders =2 (uncomment and set it as 2)
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 shell> chmod 700 /var/lib/pgsql/pg_log_archive
c) Create a user with replication privilege
shell> su - postgres -bash-4.1$ psql postgres=# create role opbackup_user replication login password '$0m3p@ssw0rd';
d) Edit pg_hba.conf file: vi /var/lib/pgsql/9.4/data/pg_hba.conf
host replication opbackup_user 127.0.0.1/32 trust
e) Restart database instance
shell> /etc/init.d/postgresql-9.4 restart
f) Backup your database
shell> /usr/pgsql-9.4/bin/pg_basebackup -h127.0.0.1 -U opbackup_user -D /var/lib/pgsql/backup -Ft -z -P 21081/21081 kB (100%), 1/1 tablespace NOTICE: pg_stop_backup complete, all required WAL segments have been archived
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/backup/base.tar.gz /var/lib/pgsql/9.4/data/ shell>cd /var/lib/pgsql/9.4/data/ shell>tar -zxvf base.tar.gz shell> rm -f base.tar.gz
d) Create a recovery file and place it in datadir
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
e) 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'
f) Start Database instance
shell> service postgresql-9.4 start Starting postgresql-9.4 service: [ OK ]
g) Check for two files under datadir
-rw------- 1 postgres postgres 206 May 14 02:37 backup_label.old -rw------- 1 postgres postgres 5.5K May 14 02:53 recovery.done
Restore complete.
3. Cleanup archive logs time to time
Delete logs before last backup point. Backup point files are denoted with .backup extension under your archive directory.shell>yum install postgresql94-contrib-9.4.1-1PGDG.rhel6.x86_64 shell>/usr/pgsql-9.4/bin/pg_archivecleanup -d /var/lib/pgsql/pg_log_archive/ `cd /var/lib/pgsql/pg_log_archive/ ; ls -rt *.backup | tail -1` pg_archivecleanup: keep WAL file "/usr/local/pgsql/pg_log_archive//000000010000000000000006" and later
0 comments