PostgreSQL Pg_basebackup Prerequest And Postrequest

• pg_basebackup is used to take base backups of a running PostgreSQL database cluster. These are taken without affecting other clients to the database.
• These can be used For Point-in-time recovery,Streaming replication standby servers
• Backups are always taken of the entire database cluster
• It is not possible to back up individual databases or database objects.
• Individual database backups, a tool such as pg_dump must be used.
• pg_basebackup can make a base backup from not only the master but also the standby. To take a backup from the standby

some limitations in an online backup from the standby:-

• backup history file is not created
• If the standby is promoted to the master during online backup, the backup fails

Some Important Options in Pg_basebackup:-

-D (OR)--pgdata ---> pg_basebackup will create the directory and any parent directories if necessary Or Destinatioon Location
-F (OR)--format --->Selects the format for the output.
               Format can be one of the following:
                            p ---> plain text(default)
                            t ---> tar format

--xlogdir  ---> Specifies the location for the transaction log directory.
-x  (or) --xlog       ---> Using this option is equivalent of using -X with method fetch.

X  (or) --xlog-method --->Includes the required transaction log files (WAL files) in the backup.

  The following methods for collecting the transaction logs are supported:
               f  fetch --->The transaction log files are collected at the end of the backup. Therefore, it is necessary for the wal_keep_segments parameter to be set high enough that the log is not removed before the end of the backup. If the log has been rotated when it's time to transfer it, the backup will fail and be unusable.
                s stream --->Stream the transaction log while the backup is created. This will open a second connection to the server and start streaming the transaction log in parallel while running the backup. Therefore, it will use up two connections configured by the max_wal_senders parameter. As long as the client can keep up with transaction log received, using this mode requires no extra transaction logs to be saved on the master.

-z (or) --gzip ---> Enables gzip compression of tar file output, with the default compression level. Compression is only available when using the tar format.
-Z (or) --compress   ---> Enables gzip compression of tar file output, and specifies the compression level (0 through 9, 0 being no compression and 9 being best compression). Compression is only available when using the tar format.
 -l (or) --label  ---> Sets the label for the backup. If none is specified, a default value of "pg_basebackup base backup" will be used.
-P(caps letter) (or) --progress ---> Estimat target size without WAL
-v (or) --verbose ---> show the exact file name that is currently being processed if progress reporting is also enabled.
-d (or) --dbname  --->Database name
-p (or) --port ---> Database port number
-U (or) --username  ---> User name to connect as.
-h (or) --host    ----> Specifies the host name
-? (or) --help ---> Show help about pg_basebackup command line arguments, and exit.

Step By Step Backup And Restore Postgres Database Using Pg_basebackup Utility Perform Remote And Local System

Prerequest:-
Step 1.Check The Server what Version And what type of Linux We Are Using ,here 'nijam' is the hostname

nijam:$ lsb_release -aNo LSB modules are available.Distributor ID: DebianDescription:Debian GNU/Linux 8.2 (jessie)Release:8.2Codename: jessiepostgres@nijam:$ psql -A -t -c "select version()"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Step 2. Configure database server &Increase upper limit of the number of simultaneously running Write Ahead Log sender processes.
Set wal_level to at least archive degree

nijam:$ sudo sed -i -e "s/^#wal_level = minimal/wal_level = archive/" /etc/postgresql/9.4/main/postgresql.confnijam:$ sudo sed -i -e "s/^#max_wal_senders = 0/max_wal_senders = 4/" /etc/postgresql/9.4/main/postgresql.conf

Step 3.Create database role which role is allowed to carry out replication backup mode.

postgres@nijam:$ psql -c "CREATE ROLE rep REPLICATION LOGIN ENCRYPTED PASSWORD 'password';"

Step 4.Grant the permission to "postgres" user to perform "local" backup.

nijam:$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.conflocal replication postgres  peerEOF

Step 5.Grant the permission to "rep" role to perform remote "database" backup.

nijam:$ cat << EOF | sudo tee -a /etc/postgresql/9.4/main/pg_hba.confhostreplication rep  0.0.0.0/0  md5EOF

Step 6.Restart PostgreSQL service to apply changes.

postgres@nijam:$ pg_ctlcluster 9.4 main restart

Postgres pgbasebackup:-
Step 1.Create ready to restore local backup as "postgres" user and store it inside "pgbackup" directory.

postgres@nijam:$ pg_basebackup -x -D pgbackup

Step 2.List the Backup Summary of "pgbackup" directory

postgres@nijam:$ ls pgbackup/$ ls -l pgbackup/total 76-rw------- 1 postgres postgres  208 Jan 22 03:02 backup_labeldrwx------ 5 postgres postgres 4096 Jan 22 03:02 basedrwx------ 2 postgres postgres 4096 Jan 22 03:02 globaldrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_clogdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_dynshmemdrwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_logicaldrwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_multixactdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_notifydrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_replslotdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_serialdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_snapshotsdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_statdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat_tmpdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_subtransdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_tblspcdrwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_twophase-rwx------ 1 postgres postgres4 Jan 22 03:02 PG_VERSIONdrwx------ 3 postgres postgres 4096 Jan 22 03:02 pg_xlog-rwx------ 1 postgres postgres 88 Jan 22 03:02 postgresql.auto.conf

Step 3.Just check the information of backup_label in above backup directory

postgres@nijam:$ cat pgbackup/pgbackup/backup_labelSTART WAL LOCATION: 0/2D000028 (file 00000001000000000000002D)CHECKPOINT LOCATION: 0/2D000028BACKUP METHOD: streamedBACKUP FROM: masterSTART TIME: 2016-01-22 03:02:47 CETLABEL: pg_basebackup base backup

Step 3.Create ready to restore local backup as postgres user, store it inside "pglabelbackup" directory and define custom label.

postgres@nijam:$ pg_basebackup -l "Backup created at $(hostname) on $(date)" -x -D pglabelbackuppostgres@nijam:$ cat pglabelbackup/base_backupSTART WAL LOCATION: 0/2E000028 (file 00000001000000000000002E)CHECKPOINT LOCATION: 0/2E000028BACKUP METHOD: streamedBACKUP FROM: masterSTART TIME: 2016-01-22 03:04:14 CETLABEL: Backup created at nijam on Fri Jan 22 03:04:14 CET 2016

Step 4.Create ready to restore backup from remote server using rep role with defined replication attribute, store it inside "pgremotebackup" directory.

postgres@nijam:$ pg_basebackup -x -h 100.0.2.150 -U rep -D pgremotebackup

Step 5.Create ready to restore backup using two connections in parallel (to stream the transaction log while the backup is created) from remote server using rep role with defined replication attribute, store it inside pgremsbackup directory.

postgres@nijam:$ pg_basebackup -X stream -h 100.0.2.150 -U rep -D pgremsbackup

Step 6.Create ready to restore gzipped tar backup from remote server using rep role with defined replication attribute, store it inside "pgremotetarbackup" directory.

postgres@nijam:$ pg_basebackup -x -Ft -z -h 100.0.2.150 -U rep -D pgremotetarbackup

Step 6.List the backup summary of "pgremotetarbackup" directory

postgres@nijam:$ ls -l pgremotetarbackup/total 2396-rw-r--r-- 1 postgres postgres 2453147 Jan 22 03:11 base.tar.gz

Postgres pgbasebackup Recovery :-
Step 1.Stop database server.

postgres@nijam:$ pg_ctlcluster 9.4 main stop

Step 2.Backup current database files.

postgres@nijam:$ mv main Lastmain.backup

Step 3.Copy archived database files to local location.

postgres@nijam:$ cp -r /pgbackup main

Step 4.Start the database server to perform recovery.

postgres@nijam:$ pg_ctlcluster 9.4 main start

Postrequest:-
Step 1.Monitor recovery process using log file.

postgres@nijam:$ tail -f /var/log/postgresql/postgresql-9.4-main.log

Step 2.Check our postgres database whether recovery mode or not

select * from pg_is_in_recovery();