PostgreSQL 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.
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,
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.
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 dbnamePRACTICAL 2:
Tricks for large DBs-->Compressed dumps:
pg_dump dbname | gzip > filename.gz
Reload withPRACTICAL 3:./createdb dbnamegunzip -c filename.gz | psql dbname
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 - filenameReload with
2.Dumping a Complete Cluster with pg_dumpallcreatedb dbnamecat filename* | psql dbname
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.dumpReload the DBs (into an empty cluster space as the server will not accept duplicate DB name) with:
$ psql -f cluster.dump template1File 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 --
sssdb=# create database sssnew;CREATE DATABASE
-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 GRANTcheck 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$
Comments
Post a Comment