Postgresql Logical Backup

                                              Logical Backup in postgresql

1.Pg_dump
2.Pg_dumpall
3.pg_basebackup

=>pg_dump is a utility for backing up a PostgreSQL database,table and role's tablespace etc.
=>It makes consistent backups even if the database is being used concurrently.
=>pg_dump does not block other users accessing the database (readers or writers).

IMPORTANT NOTES:-
=>Except sql dump we can use to restore dump use pg_restore utility.
=>The "directory" format is the only format that supports parallel dumps.
=>SQL Dump is used to restore a data to any platform or higer versions postgresql aslo..

Format of the Pg_dump files:-
p=plain-text SQL script file (the default)
c=custom-format archive suitable for input into pg_restore.
d= Directory-format archive suitable for input into pg_restore.Blob being dumped.
t=A tar-format archive produces a valid directory-format archive.
   when using tar format the relative order of table data items cannot be changed during restore.


Why we choose pg_dump:-

1.Less data to copy - logical backups do not copy all data files,
2.Lower I/O pressure on the system than physical backups
3.It's easy to backup and restore even a single table.
4.Cross-version compatibility in SQL dumps.

Method-1
To dump a database called mydb into a SQL-script file:-(default)
Syntax:-
 pg_dump -d databasename -U username -t tablename -Fc,d,t,p  -f  /backup/dumpfiles/otp_airtel_.sql

Example:1-(sql format)
[root@www.sakthidbtech.com ~]# pg_dump -d otp_airtel -U tel_usr -t chennai_rg  -f  /backup/dumpfiles/otp_airtel_chennai_rg_.sql

Restore a table to another Database:

[root@www.sakthidbtech.com ~]#psql -d otp_range -U tel_usr -t chennai_rg -f   /backup/dumpfiles/otp_airtel_chennai_rg_.sql

Example 2:-
To dump a single table named mytab:

[root@www.sakthidbtech.com ~]pg_dump -t mytab mydb > db.sql

Example 3:-
To dump a database into a custom-format archive file:-
[root@www.sakthidbtech.com ~]#pg_dump -d otp_airtel -U tel_usr -Fc  -f  /backup/dumpfiles/otp_airtel_custom.sql

To reload Dump into a (freshly created) database named newdb:if we take dump in custom format means use pg_restore.

[root@www.sakthidbtech.com ~]#pg_restore  newdb -f /backup/dumpfiles/otp_airtel_custom.sql

Methode 2:-
To dump a database into a Custom-format archive:
[root@www.sakthidbtech.com ~]# pg_dump -Fc mydb > /backup/dumpfiles/dumpfilename.dmp

To dump a database into a directory-format archive:

[root@www.sakthidbtech.com ~]# pg_dump -Fd mydb -f /backup/dumpfiles/dumpfilename2.dmp



NOTE:-
To dump a database into a directory-format archive in parallel with 5 worker jobs:

[root@www.sakthidbtech.com ~]pg_dump -Fd mydb -j 5 -f dumpdir
To reload an archive file into a (freshly created) database named newdb:

[root@www.sakthidbtech.com ~] pg_restore -d newdb db.dump



To dump all tables whose names start with emp in the dinesh schema, except for the table named employee_log:-

[root@www.sakthidbtech.com ~] pg_dump -d postgres -t 'dinesh.emp**' -T dinesh.c3 -U postgres -f  db.sql

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

[root@www.sakthidbtech.com ~]pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

[root@www.sakthidbtech.com ~]pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
To dump all database objects except for tables whose names begin with ts_:

[root@www.sakthidbtech.com ~] pg_dump -T 'ts_*' mydb > db.sql

To specify an upper-case or mixed-case name in -t and related switches,
 you need to double-quote the name; else it will be folded to lower case (see Patterns).
 But double quotes are special to the shell, so in turn they must be quoted.
 Thus, to dump a single table with a mixed-case name, you need something like

[root@www.sakthidbtech.com ~] pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql


II)PG_DUMPALL BACKUP:

To take entire cluster of Data Directory..

[root@www.sakthidbtech.com ~]pg_dumpall >>fulldump.dmp

Restore the dumpall backup

[root@www.sakthidbtech.com ~]Psql -f /backup/dumpfiles/dumpfilename.dmp