postgresql 备份恢复(一)

fenghzy

　　备份恢复对于每个数据来说都是非常重要的。一般的数据库都支持冷备份的方式，冷备份可以保证数据库在此刻的完整性。但是其缺点也非常的明显，为保持数据一致性。冷备份期间数据库中相关数据是不能够使用的，就大大影响的系统的可用性。不管怎样冷备份在很多的情况下还是很有用的。
　　数据库的冷备份一般支持两种方式：
　　1,操作系统级别的命令备份(cp,copy)
　　2,数据库工具备份(pg_dump)
　　针对postgresql数据库的pg_dump工具进行了一下测试(还碰到一个小问题)。
　　pg_dump工具命令与参数的参考：


代码



pg_dump dumps a database as a text file or to other formats.
Usage:
  pg_dump [OPTION]... [DBNAME]
General options:
  -f, --file=FILENAME      output file name
  -F, --format=c|t|p       output file format (custom, tar, plain text)
  -i, --ignore-version     proceed even when server version mismatches
                           pg_dump version
  -v, --verbose            verbose mode
  -Z, --compress=0-9       compression level for compressed formats
  --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
  -b, --blobs                 include large objects in dump
  -c, --clean                 clean (drop) schema prior to create
  -C, --create                include commands to create database in dump
  -d, --inserts               dump data as INSERT commands, rather than COPY
  -D, --column-inserts        dump data as INSERT commands with column names
  -E, --encoding=ENCODING     dump the data in encoding ENCODING
  -n, --schema=SCHEMA         dump the named schema(s) only
  -N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
  -o, --oids                  include OIDs in dump
  -O, --no-owner              skip restoration of object ownership
                              in plain text format
  -s, --schema-only           dump only the schema, no data
  -S, --superuser=NAME        specify the superuser user name to use in
                              plain text format
  -t, --table=TABLE           dump the named table(s) only
  -T, --exclude-table=TABLE   do NOT dump the named table(s)
  -x, --no-privileges         do not dump privileges (grant/revoke)
  --disable-dollar-quoting    disable dollar quoting, use SQL standard quoting
  --disable-triggers          disable triggers during data-only restore
  --use-set-session-authorization
                              use SESSION AUTHORIZATION commands instead of
                              ALTER OWNER commands to set ownership
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -W, --password           force password prompt (should happen automatically)
If no database name is supplied, then the PGDATABASE environment
variable value is used.
Report bugs to <pgsql-bugs@postgresql.org>.
　　
通过pg_dump工具分别进行数据库，表，schema方式的备份。
　　1，数据库备份


代码



[postgre@daduxiong bin]$ dropdb wyz
[postgre@daduxiong bin]$ createdb wyz
[postgre@daduxiong bin]$ psql wyz
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
wyz=# create table abc(id integer);
CREATE TABLE
wyz=# insert into abc values(1);
INSERT 0 1
wyz=# \q
[postgre@daduxiong bin]$ pg_dump wyz >/usr/local/pgsql/data/wyz.dmp
[postgre@daduxiong bin]$ dropdb wyz
[postgre@daduxiong bin]$ createdb wyz
[postgre@daduxiong bin]$ psql wyz </usr/local/pgsql/data/wyz.dmp
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
[postgre@daduxiong bin]$ psql wyz
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
wyz=# select * from abc;
id
----
  1
(1 row)
wyz=#
对于空间不足或者文件较大时可以采用压缩的方式。
[postgre@daduxiong bin]$ pg_dump wyz |gzip >/usr/local/pgsql/data/wyz.gz
　　
在恢复数据库时，一定要确认数据库已经创建。
　　使用pg_dump工具对数据库进行备份只是备份了数据库中的数据，对于配置文件等非数据文件没有一起备份。显然不如使用tar与gzip命令更方便。
　　2，表备份


代码



[postgre@daduxiong ~]$ psql wyz
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
wyz=# select * from abc;
id
----
  1
(1 row)
wyz=# insert into abc values(2);
INSERT 0 1
wyz=# \q
[postgre@daduxiong ~]$ pg_dump wyz --table abc >/usr/local/pgsql/data/abc.sql
[postgre@daduxiong ~]$ psql wyz
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
wyz=# drop table abc;
DROP TABLE
wyz=# \q
[postgre@daduxiong ~]$ more /usr/local/pgsql/data/abc.sql
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: abc; Type: TABLE; Schema: public; Owner: postgre; Tablespace:
--
CREATE TABLE abc (
    id integer
);

ALTER TABLE public.abc OWNER TO postgre;
--
-- Data for Name: abc; Type: TABLE DATA; Schema: public; Owner: postgre
--
COPY abc (id) FROM stdin;
1
2
\.

--
-- PostgreSQL database dump complete
--
[postgre@daduxiong ~]$ psql wyz </usr/local/pgsql/data/abc.sql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
[postgre@daduxiong ~]$ psql wyz
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
wyz=# select count(*) from abc;
count
-------
     2
(1 row)
wyz=#
　　
表备份时还可以根据参数的选用将表的数据dump成insert语句。
　　3，SCHEMA备份


代码



[postgre@daduxiong ~]$ rm -rf /usr/local/pgsql/data/123.sql
[postgre@daduxiong ~]$ pg_dump wyz -N postgre -h daduxiong -f /usr/local/pgsql/data/123.sql
[postgre@daduxiong ~]$ pg_dump wyz -N wyz  -h daduxiong -f /usr/local/pgsql/data/234.sql
[postgre@daduxiong ~]$ more /usr/local/pgsql/data/123.sql
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: abc; Type: TABLE; Schema: public; Owner: postgre; Tablespace:
--
CREATE TABLE abc (
    id integer
);

ALTER TABLE public.abc OWNER TO postgre;
--
-- Data for Name: abc; Type: TABLE DATA; Schema: public; Owner: postgre
--
COPY abc (id) FROM stdin;
1
2
\.

--
-- Name: public; Type: ACL; Schema: -; Owner: postgre
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgre;
GRANT ALL ON SCHEMA public TO postgre;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--
[postgre@daduxiong ~]$ more /usr/local/pgsql/data/234.sql
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: abc; Type: TABLE; Schema: public; Owner: postgre; Tablespace:
--
CREATE TABLE abc (
    id integer
);

ALTER TABLE public.abc OWNER TO postgre;
--
-- Data for Name: abc; Type: TABLE DATA; Schema: public; Owner: postgre
--
COPY abc (id) FROM stdin;
1
2
\.

--
-- Name: public; Type: ACL; Schema: -; Owner: postgre
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgre;
GRANT ALL ON SCHEMA public TO postgre;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

　　在我的wyz数据库中有两个用户(postgre,wyz),可备份测试的时候都是备份的postgre用户的数据。
在采用参数-n的时候，提示命令错误。




[postgre@daxuxiong ~]$ pg_dump wyz -n wyz -f /usr/local/pgsql/data/456.sql
pg_dump: No matching schemas were found.
　　怀疑是bug，有不同结果的朋友请指正。
postgresql当前版本：8.3.10