PostgreSQL Database Backup Tools – Pg_basebackup

• 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

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 -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 8.2 (jessie)
Release:        8.2
Codename:       jessie

postgres@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.conf
nijam:~$ 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.conf
local   replication     postgres                                peer
EOF

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.conf
host    replication     rep              0.0.0.0/0              md5
EOF

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_label
drwx------ 5 postgres postgres 4096 Jan 22 03:02 base
drwx------ 2 postgres postgres 4096 Jan 22 03:02 global
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_clog
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_dynshmem
drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_logical
drwx------ 4 postgres postgres 4096 Jan 22 03:02 pg_multixact
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_notify
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_replslot
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_serial
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_snapshots
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_subtrans
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_tblspc
drwx------ 2 postgres postgres 4096 Jan 22 03:02 pg_twophase
-rwx------ 1 postgres postgres    4 Jan 22 03:02 PG_VERSION
drwx------ 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_label
START WAL LOCATION: 0/2D000028 (file 00000001000000000000002D)
CHECKPOINT LOCATION: 0/2D000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-01-22 03:02:47 CET
LABEL: 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 pglabelbackup

postgres@nijam:~$ cat pglabelbackup/base_backup
START WAL LOCATION: 0/2E000028 (file 00000001000000000000002E)
CHECKPOINT LOCATION: 0/2E000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2016-01-22 03:04:14 CET
LABEL: 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();