Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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 -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();


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL