本文共 18836 字，大约阅读时间需要 62 分钟。
ExecuteSqlStatement(fout, "BEGIN"); if (fout->remoteVersion >= 90100) { if (serializable_deferrable) ExecuteSqlStatement(fout, "SET TRANSACTION ISOLATION LEVEL " "SERIALIZABLE, READ ONLY, DEFERRABLE"); else ExecuteSqlStatement(fout, "SET TRANSACTION ISOLATION LEVEL " "REPEATABLE READ"); } else ExecuteSqlStatement(fout, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); 2. pg_dump在备份数据开始前, 需要对进行备份的对象加ACCESS SHARE锁, 代码如下 : if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION) { resetPQExpBuffer(query); appendPQExpBuffer(query, "LOCK TABLE %s IN ACCESS SHARE MODE", fmtQualifiedId(fout, tblinfo[i].dobj.namespace->dobj.name, tblinfo[i].dobj.name)); ExecuteSqlStatement(fout, query->data); } if (lockWaitTimeout && fout->remoteVersion >= 70300) { /* * Arrange to fail instead of waiting forever for a table lock. * * NB: this coding assumes that the only queries issued within the * following loop are LOCK TABLEs; else the timeout may be undesirably * applied to other things too. */ resetPQExpBuffer(query); appendPQExpBuffer(query, "SET statement_timeout = "); appendStringLiteralConn(query, lockWaitTimeout, GetConnection(fout)); ExecuteSqlStatement(fout, query->data); } pg93@db-172-16-3-33-> psqlpsql (9.3devel)Type "help" for help.digoal=# begin;BEGINdigoal=# truncate table test;TRUNCATE TABLE-- 不结束事务.SESSION B :
pg93@db-172-16-3-33-> pg_dump -f ./test.dmp
digoal=# select query,waiting from pg_stat_activity; query | waiting ---------------------------------------------+--------- LOCK TABLE public.test IN ACCESS SHARE MODE | t -- 这条就是pg_dump发起的. truncate table test; | f select query,waiting from pg_stat_activity; | f(3 rows)如果不像让pg_dump一直等待下去, 那么可以使用--lock-wait-timeout参数. 例如以下命令, 等待5秒未成功获得锁则退出pg_dump.
pg93@db-172-16-3-33-> pg_dump -f ./test.dmp --lock-wait-timeout=5spg_dump: [archiver (db)] query failed: ERROR: canceling statement due to statement timeoutpg_dump: [archiver (db)] query was: LOCK TABLE public.test IN ACCESS SHARE MODE
pg93@db-172-16-3-33-> psqlpsql (9.3devel)Type "help" for help.digoal=# \dt List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | pwd_dictionary | table | postgres public | tbl_user | table | postgres public | test | table | postgres(3 rows)digoal=# select sum(hashtext(t.*::text)) from pwd_dictionary t; sum ------------ -719496483(1 row)digoal=# select sum(hashtext(t.*::text)) from tbl_user t; sum --------------- -131178135551(1 row)digoal=# select sum(hashtext(t.*::text)) from test t; sum ----- (1 row)
pg93@db-172-16-3-33-> pg_dump -f ./digoal.dmp -F p -C -E UTF8 --no-tablespaces -h 127.0.0.1 -p 1999 -U postgres digoal删除digoal库.
digoal=# \c postgresYou are now connected to database "postgres" as user "postgres".postgres=# drop database digoal;DROP DATABASE还原, 直接执行备份SQL即可 :
pg93@db-172-16-3-33-> psql postgres postgres -f ./digoal.dmp SETSETSETSETSETSETCREATE DATABASEALTER DATABASEYou are now connected to database "digoal" as user "postgres".SETSETSETSETSETSETCREATE SCHEMAALTER SCHEMACREATE EXTENSIONCOMMENTSETCREATE FUNCTIONALTER FUNCTIONCREATE FUNCTIONALTER FUNCTIONSETCREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLESETCREATE TABLEALTER TABLESETALTER TABLE setval -------- 1(1 row)SETSETALTER TABLEALTER TABLEREVOKEREVOKEGRANTGRANTREVOKEREVOKEGRANTGRANT检查还原后的hash值, 与备份前一致.
pg93@db-172-16-3-33-> psqlpsql (9.3devel)Type "help" for help.digoal=# select sum(hashtext(t.*::text)) from pwd_dictionary t; sum ------------ -719496483(1 row)digoal=# select sum(hashtext(t.*::text)) from tbl_user t; sum --------------- -131178135551(1 row)digoal=# select sum(hashtext(t.*::text)) from test t; sum ----- (1 row)
#!/bin/bash# 环境变量PATH=$PATH:$HOME/binexport PATHexport LANG=en_US.utf8export PGHOME=/opt/pgsqlexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.# 程序变量TODAY=`date +%Y%m%d`EMAIL="digoal@126.com"BAKBASEDIR="/database/pgbak"RESERVE_DAY=4HOST="10.10.10.10"PORT="1921"ROLE="postgres"# 不一致备份, 按单表进行.for DB in `psql -A -q -t -h $HOST -p $PORT -U $ROLE postgres -c "select datname from pg_database where datname not in ('postgres','template0','template1')"`doecho -e "------`date +%F\ %T`----Start Backup----IP:$HOST PORT:$PORT DBNAME:$DB TYPE:$BAKTYPE TO:$BAKBASEDIR------"for TABLE in `psql -A -q -t -h $HOST -p $PORT -U $ROLE $DB -c "select schemaname||'.'||tablename from pg_tables where schemaname !~ '^pg_' and schemaname <>'information_schema'"`dopg_dump -f ${BAKBASEDIR}/${DB}-${TABLE}-${TODAY}.dmp.ing -F c -t $TABLE --lock-wait-timeout=6000 -E UTF8 -h ${HOST} -p ${PORT} -U ${ROLE} -w ${DB}if [ $? -ne 0 ]; thenecho -e "backup $HOST $PORT $DB $BAKBASEDIR error \n `date +%F%T` \n"|mutt -s "ERROR : PostgreSQL_backup " ${EMAIL}echo -e "------`date +%F\ %T`----Error Backup----IP:$HOST PORT:$PORT DBNAME:$DB TABLE:$TABLE TO:$BAKBASEDIR------"rm -f ${BAKBASEDIR}/${DB}-${TABLE}-${TODAY}.dmp.ingbreakfimv ${BAKBASEDIR}/${DB}-${TABLE}-${TODAY}.dmp.ing ${BAKBASEDIR}/${DB}-${TABLE}-${TODAY}.dmpecho -e "------`date +%F\ %T`----Success Backup----IP:$HOST PORT:$PORT DBNAME:$DB TABLE:$TABLE TO:$BAKBASEDIR------"donedoneecho -e "find ${BAKBASEDIR}/${DB}_${TABLE}_${TODAY}.dmp* -daystart -mtime +${RESERVE_DAY} -delete"find ${BAKBASEDIR}/${DB}_${TABLE}_${TODAY}.dmp* -daystart -mtime +${RESERVE_DAY} -delete digoal=# do language plpgsql $$digoal$# declaredigoal$# v_sql text;digoal$# begindigoal$# for i in 1..1000 loopdigoal$# v_sql := 'create table test_'||i||'(id int, info text)';digoal$# execute v_sql;digoal$# v_sql := 'insert into test_'||i||'(id,info) select generate_series(1,1000),''test''';digoal$# execute v_sql;digoal$# end loop;digoal$# end;digoal$# $$;备份, 并行度为10, 备份到./paralleldmp目录, 这个目录会自动创建 :
pg93@db-172-16-3-33-> pg_dump -f ./paralleldmp -F d -C -E UTF8 --no-tablespaces -j 10 -h 127.0.0.1 -p 1999 -U postgres digoal
pg93@db-172-16-3-33-> psqlpsql (9.3devel)Type "help" for help.digoal=# \c postgres postgresYou are now connected to database "postgres" as user "postgres".postgres=# drop database digoal;DROP DATABASE还原 :
pg93@db-172-16-3-33-> pg_restore -C -h 127.0.0.1 -p 1999 -U postgres -d postgres -j 10 ~/paralleldmp检查是否还原 :
pg93@db-172-16-3-33-> psqlpsql (9.3devel)Type "help" for help.digoal=# \dt List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | pwd_dictionary | table | postgres public | tbl_user | table | postgres public | test | table | postgres public | test_1 | table | postgres public | test_10 | table | postgres public | test_100 | table | postgres public | test_1000 | table | postgres public | test_101 | table | postgres public | test_102 | table | postgres public | test_103 | table | postgres public | test_104 | table | postgres public | test_105 | table | postgres public | test_106 | table | postgres public | test_107 | table | postgres public | test_108 | table | postgres public | test_109 | table | postgres public | test_11 | table | postgres public | test_110 | table | postgres public | test_111 | table | postgres public | test_112 | table | postgres public | test_113 | table | postgres public | test_114 | table | postgres public | test_115 | table | postgres public | test_116 | table | postgres public | test_117 | table | postgres public | test_118 | table | postgres public | test_119 | table | postgres public | test_12 | table | postgres public | test_120 | table | postgres public | test_121 | table | postgres public | test_122 | table | postgres public | test_123 | table | postgres public | test_124 | table | postgres public | test_125 | table | postgres... 略
pg93@db-172-16-3-33-> pg_restore ~/paralleldmp -l >./toc.list
pg93@db-172-16-3-33-> less toc.list ;; Archive created at Mon May 27 08:58:40 2013; dbname: digoal; TOC Entries: 2026; Compression: -1; Dump Version: 1.12-0; Format: UNKNOWN; Integer: 4 bytes; Offset: 8 bytes; Dumped from database version: 9.3devel; Dumped by pg_dump version: 9.3devel;;; Selected TOC Entries:;8744; 1262 26431 DATABASE - digoal postgres6; 2615 2200 SCHEMA - public postgres8745; 0 0 COMMENT - SCHEMA public postgres8746; 0 0 ACL - public postgres7; 2615 26432 SCHEMA - test postgres8747; 0 0 ACL - test postgres1176; 3079 12536 EXTENSION - plpgsql 8748; 0 0 COMMENT - EXTENSION plpgsql 1189; 1255 26433 FUNCTION public alter_role_pwd(name, text) postgres1190; 1255 26434 FUNCTION public create_role(name, text) postgres171; 1259 26435 TABLE public pwd_dictionary postgres172; 1259 26441 TABLE public tbl_user postgres173; 1259 26444 SEQUENCE public tbl_user_id_seq postgres8749; 0 0 SEQUENCE OWNED BY public tbl_user_id_seq postgres174; 1259 26446 TABLE public test postgres176; 1259 26457 TABLE public test_1 postgres185; 1259 26511 TABLE public test_10 postgres275; 1259 27051 TABLE public test_100 postgres1175; 1259 32451 TABLE public test_1000 postgres276; 1259 27057 TABLE public test_101 postgres277; 1259 27063 TABLE public test_102 postgres278; 1259 27069 TABLE public test_103 postgres截取一行解释一下 :
8744; 1262 26431 DATABASE - digoal postgres8744 对应 dumpId1262 对应 catalogId.tableoid26431 对应 catalogId.oidDATABASE 对应 desc- 对应 te->namespace ? te->namespace : "-"digoal 对应 tagpostgres 对应 owner
voidPrintTOCSummary(Archive *AHX, RestoreOptions *ropt){ ArchiveHandle *AH = (ArchiveHandle *) AHX; TocEntry *te; OutputContext sav; char *fmtName; sav = SaveOutput(AH); if (ropt->filename) SetOutput(AH, ropt->filename, 0 /* no compression */ ); ahprintf(AH, ";\n; Archive created at %s", ctime(&AH->createDate)); ahprintf(AH, "; dbname: %s\n; TOC Entries: %d\n; Compression: %d\n", AH->archdbname, AH->tocCount, AH->compression); switch (AH->format) { case archFiles: fmtName = "FILES"; break; case archCustom: fmtName = "CUSTOM"; break; case archTar: fmtName = "TAR"; break; default: fmtName = "UNKNOWN"; } ahprintf(AH, "; Dump Version: %d.%d-%d\n", AH->vmaj, AH->vmin, AH->vrev); ahprintf(AH, "; Format: %s\n", fmtName); ahprintf(AH, "; Integer: %d bytes\n", (int) AH->intSize); ahprintf(AH, "; Offset: %d bytes\n", (int) AH->offSize); if (AH->archiveRemoteVersion) ahprintf(AH, "; Dumped from database version: %s\n", AH->archiveRemoteVersion); if (AH->archiveDumpVersion) ahprintf(AH, "; Dumped by pg_dump version: %s\n", AH->archiveDumpVersion); ahprintf(AH, ";\n;\n; Selected TOC Entries:\n;\n"); /* We should print DATABASE entries whether or not -C was specified */ ropt->createDB = 1; for (te = AH->toc->next; te != AH->toc; te = te->next) { if (ropt->verbose || _tocEntryRequired(te, ropt, true) != 0) ahprintf(AH, "%d; %u %u %s %s %s %s\n", te->dumpId, te->catalogId.tableoid, te->catalogId.oid, te->desc, te->namespace ? te->namespace : "-", te->tag, te->owner); if (ropt->verbose && te->nDeps > 0) { int i; ahprintf(AH, ";\tdepends on:"); for (i = 0; i < te->nDeps; i++) ahprintf(AH, " %d", te->dependencies[i]); ahprintf(AH, "\n"); } } if (ropt->filename) RestoreOutput(AH, sav);} ;176; 1259 26457 TABLE public test_1 postgres;7740; 0 26457 TABLE DATA public test_1 postgres然后调整顺序
275; 1259 27051 TABLE public test_100 postgres1175; 1259 32451 TABLE public test_1000 postgres调整为
1175; 1259 32451 TABLE public test_1000 postgres275; 1259 27051 TABLE public test_100 postgres
digoal=# \c postgresYou are now connected to database "postgres" as user "postgres".postgres=# drop database digoal;DROP DATABASE还原 :
pg93@db-172-16-3-33-> pg_restore -h 127.0.0.1 -p 1999 -U postgres -C -d postgres -j 10 -L ./toc.list -v ~/paralleldmp >./restore.log 2>&1查看restore.log日志, 注意到顺序调整生效 :
pg_restore: processing item 1175 TABLE test_1000pg_restore: creating TABLE test_1000pg_restore: processing item 275 TABLE test_100pg_restore: creating TABLE test_100同时进入数据库查看test_1表没有被还原.
digoal=# \d test_1Did not find any relation named "test_1".
pg93@db-172-16-3-33-> pg_dumpall -g -h 127.0.0.1 -p 1999 -U postgres -f ./global.dmp内容如下 :
pg93@db-172-16-3-33-> cat global.dmp ---- PostgreSQL database cluster dump--SET client_encoding = 'UTF8';SET standard_conforming_strings = on;---- Roles--CREATE ROLE client1;ALTER ROLE client1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md596bdd340a56d9ab240581edede7a13c6';CREATE ROLE digoal;ALTER ROLE digoal WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5c08bdd942d14da5ede9d9cef2b17ef9c';CREATE ROLE gp1;ALTER ROLE gp1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;CREATE ROLE new;ALTER ROLE new WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md54a5ca2a5e9aaed4c781e7d72d7fe945f';CREATE ROLE postgres;ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md53175bce1d3201d16594cebf9d7eb3f9d';CREATE ROLE sslcertgroup;ALTER ROLE sslcertgroup WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;CREATE ROLE u4;ALTER ROLE u4 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5bbc9d1a9f9e201c9c9d3c153f85771cc';---- Role memberships--GRANT gp1 TO client1 GRANTED BY postgres;GRANT sslcertgroup TO client1 GRANTED BY postgres;GRANT sslcertgroup TO digoal GRANTED BY postgres;GRANT sslcertgroup TO postgres GRANTED BY postgres;---- Tablespaces--CREATE TABLESPACE tbs_digoal OWNER postgres LOCATION '/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal';---- Per-Database Role Settings --ALTER ROLE postgres IN DATABASE postgres SET work_mem TO '10240MB';---- PostgreSQL database cluster dump complete--
digoal=# copy test_10 to '/home/pg93/test_10.dmp' with csv header;COPY 1000digoal=# select sum(hashtext(t.*::text)) from test_10 t; sum ------------ -432745392(1 row)digoal=# truncate test_10;TRUNCATE TABLE导入 :
digoal=# copy test_10 from '/home/pg93/test_10.dmp' with csv header;COPY 1000digoal=# select sum(hashtext(t.*::text)) from test_10 t; sum ------------ -432745392(1 row)
转载地址：http://dtenx.baihongyu.com/