PostgreSQL Database Backup Tools – Pg_dump

• As everything that contains valuable data, PostgreSQL DBs should be backed up regularly.
• DB backups allow DBs to be restored if a disk drive fails, a table is accidentally dropped, or a DB file is accidentally deleted.
• The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the DB in the same state as it was at the time of the dump.

Dump Individual DBs with pg_dump 
PostgreSQL provides the utility program pg_dump for dumping individual DBs:

pg_dump dbname > outfile

• pg_dump writes its results to the standard output.
• pg_dump is a regular PostgreSQL client application.
• This means that you can do this backup procedure from any remote host that has access to the DB.
• pg_dump does not operate with special permissions. You must have read access to all tables that you want to back up.
• Large objects (blobs) are not dumped by default,

Restoring the dump 
The text files created by pg_dump are intended to be read in by the psql program:

psql dbname < infile

•  The DB dbname will not be created by this command, you must create it yourself before.
• psql and pg_dump support options for controlling the DB server location and the user names.

PRACTICAL 1:
Dump the workshop DB in the file “workshop.dump”:

$> pg_dump workshop >workshop.dump

–Look at the file “workshop.dump”:

$> more workshop.dump

–Create a new DB for restoring:

$> createdb dump_test

–Restore the workshop DB into the dump_test DB:

$> psql dump_test < workshop.dump

–Connect to the dump_test DB:

$> psql dump_test

–The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a DB directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname

PRACTICAL 2:
Tricks for large DBs–>Compressed dumps:

pg_dump dbname | gzip > filename.gz

Reload with

PRACTICAL 3:
split allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

pg_dump dbname | split -b 1m - filename

Reload with

2.Dumping a Complete Cluster with pg_dumpall 
pg_dumpall is a utility for dumping all PostgreSQL DBs of a cluster into one file. It also dumps global objects that are common to all DBs:

pg_dumpall > outfile

The resulting dumps can be restored with psql.
Example “dump and reload all DBs”:

$ pg_dumpall >cluster.dump

Reload the DBs (into an empty cluster space as the server will not accept duplicate DB name) with:

$ psql -f cluster.dump template1

File system level backup :
An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database cluster with whatever method you prefer for doing file system backups, for example:

tar -cf backup.tar /home/fred/databases/postgresql/data

• The database server must be shut down before.
• It will not work to restore only certain individual tables or databases from their respective files or directories, because the information contained in these files must be combined with the commit log files pg_clog/*, which contain the commit status of all transactions.
• The file system backup will likely be larger than an SQL dump, because a pg_dump does not need to dump the contents of indexes for example, just the commands to recreate them.

pg_dump -t MyTable mydb > db.sql

-bash-3.2$ ./pg_dump -U sss1 -c -f /u02/spl_bkp/sample_bakcup sssdb
Password:

[root@asmhost u02]# cd spl_bkp/
[root@asmhost spl_bkp]# ls
sample_bakcup
[root@asmhost spl_bkp]# cat sample_bakcup 
--
-- EnterpriseDB database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

DROP EXTENSION edb_dblink_oci;
DROP EXTENSION edb_dblink_libpq;
DROP EXTENSION plpgsql;
DROP SCHEMA public;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: enterprisedb
--
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO enterprisedb;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: enterprisedb
--

COMMENT ON SCHEMA public IS 'Standard public schema';
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--
-- Name: edb_dblink_libpq; Type: EXTENSION; Schema: -; Owner: 
--
CREATE EXTENSION IF NOT EXISTS edb_dblink_libpq WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION edb_dblink_libpq; Type: COMMENT; Schema: -; Owner: 
--
COMMENT ON EXTENSION edb_dblink_libpq IS 'EnterpriseDB Foreign Data Wrapper for PostgreSQL';
--
-- Name: edb_dblink_oci; Type: EXTENSION; Schema: -; Owner: 
--
CREATE EXTENSION IF NOT EXISTS edb_dblink_oci WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION edb_dblink_oci; Type: COMMENT; Schema: -; Owner: 
--
COMMENT ON EXTENSION edb_dblink_oci IS 'EnterpriseDB Foreign Data Wrapper for Oracle';
--
-- Name: public; Type: ACL; Schema: -; Owner: enterprisedb
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM enterprisedb;
GRANT ALL ON SCHEMA public TO enterprisedb;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- EnterpriseDB database dump complete
--

-bash-3.2$ ./psql -d sssnew -U sss1 -f /u02/spl_bkp/sample_bakcup 
Password for user sss1: 
SET
SET
SET
SET
SET
DROP EXTENSION
DROP EXTENSION
psql:/u02/spl_bkp/sample_bakcup:13: ERROR:  cannot drop extension plpgsql because other objects depend on it
DETAIL:  function edbreport(bigint,bigint) depends on language plpgsql
function statio_tables_rpt(integer,integer,integer,text) depends on language plpgsql
function statio_indexes_rpt(integer,integer,integer,text) depends on language plpgsql
function stat_tables_rpt(integer,integer,integer,text) depends on language plpgsql
function stat_indexes_rpt(integer,integer,integer,text) depends on language plpgsql
function stat_db_rpt(integer,integer) depends on language plpgsql
function sesshist_rpt(integer,integer) depends on language plpgsql
function sessid_rpt(integer,integer,integer) depends on language plpgsql
function sess_rpt(integer,integer,integer) depends on language plpgsql
function sys_rpt(integer,integer,integer) depends on language plpgsql
function truncsnap() depends on language plpgsql
function purgesnap(integer,integer) depends on language plpgsql
function get_snaps() depends on language plpgsql
function edbsnap() depends on language plpgsql
function edb_get_sys_info() depends on language plpgsql
function connectby_cyclecheck(anyarray,anyelement) depends on language plpgsql
function convertargdir("char"[],smallint) depends on language plpgsql
view pg_function depends on function convertargdir("char"[],smallint)
function new_time(timestamp without time zone,character varying,character varying) depends on language plpgsql
function showseq(oid) depends on language plpgsql
view user_sequences depends on function showseq(oid)
view all_sequences depends on function showseq(oid)
view dba_sequences depends on function showseq(oid)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT

check the database

sssdb=# \l
                                        List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges       
-----------+--------------+----------+-------------+-------------+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sssdb     | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sssnew    | sss1         | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

-bash-3.2$ ./pg_dump -U sss1 -Fc -f /u02/spl_bkp/level2_bkp sssdb
Password: 
-bash-3.2$
-bash-3.2$ ./pg_restore -l /u02/spl_bkp/level2_bkp 
;
; Archive created at Mon Nov  5 14:32:53 2012
;     dbname: sssdb
;     TOC Entries: 12
;     Compression: -1
;     Dump Version: 1.12-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.1.2.2
;     Dumped by pg_dump version: 9.1.2.2
;
;
; Selected TOC Entries:
;
3476; 1262 40964 DATABASE - sssdb enterprisedb
5; 2615 2200 SCHEMA - public enterprisedb
3477; 0 0 COMMENT - SCHEMA public enterprisedb
3478; 0 0 ACL - public enterprisedb
303; 3079 12456 EXTENSION - plpgsql 
3479; 0 0 COMMENT - EXTENSION plpgsql 
302; 3079 13457 EXTENSION - edb_dblink_libpq 
3480; 0 0 COMMENT - EXTENSION edb_dblink_libpq 
301; 3079 13460 EXTENSION - edb_dblink_oci 
3481; 0 0 COMMENT - EXTENSION edb_dblink_oci 
-bash-3.2$

-bash-3.2$ ./pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME         output file name
  --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
  --help                      show this help, then exit
  --version                   output version information, then exit

Options controlling the output content:
  -a, --data-only             dump only the data, not the schema
  -c, --clean                 clean (drop) databases before recreating
  -g, --globals-only          dump only global objects, no databases
  -o, --oids                  include OIDs in dump
  -O, --no-owner              skip restoration of object ownership
  -r, --roles-only            dump only roles, no databases or tablespaces
  -s, --schema-only           dump only the schema, no data
  -S, --superuser=NAME        superuser user name to use in the dump
  -t, --tablespaces-only      dump only tablespaces, no databases or roles
  -x, --no-privileges         do not dump privileges (grant/revoke)
  --binary-upgrade            for use by upgrade utilities only
  --column-inserts            dump data as INSERT commands with column names
  --disable-dollar-quoting    disable dollar quoting, use SQL standard quoting
  --disable-triggers          disable triggers during data-only restore
  --inserts                   dump data as INSERT commands, rather than COPY
  --no-security-labels        do not dump security label assignments
  --no-tablespaces            do not dump tablespace assignments
  --no-unlogged-table-data    do not dump unlogged table data
  --quote-all-identifiers     quote all identifiers, even if not key words
  --use-set-session-authorization
                              use SET SESSION AUTHORIZATION commands instead of
                              ALTER OWNER commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

bash-3.1$ ./pg_dump -p 5544 -f '/u01/postgre/t1.sql' -t t1 gopaldb
-bash-3.1$ vi /u01/postgre/t1.sql 
-bash-3.1$