疑惑
经常执行pg_dump这个命令,来备份数据库，为什么偶尔会影响业务呢，这中间出现了什么了呢，我们想过吗？为什么执行命令期间DDL会卡住呢，因为它在执行期间会执行REPEATABLE READ级别的事务快照并根据快照号进行的备份，所有能达到一致性事务级别
可是为什么会卡住呢
执行命令输出的到stdout
postgres@BJ-015908:/home/daihu/postgresql-14.7/src/bin/pg_controldata$ /usr/local/pgsqldebug/bin/pg_dump
--
-- PostgreSQL database dump
--
-- Dumped from database version 14.7
-- Dumped by pg_dump version 14.7
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: foobar; Type: TABLE; Schema: public; Owner: postgres
--
CREATE UNLOGGED TABLE public.foobar (
id integer
);
--
-- PostgreSQL database dump complete
--
执行命令输出的到file
postgres@BJ-015908:~$ usr/local/pgsqldebug/bin/pg_dump --format p -f dd.sql
postgres@BJ-015908:~$ vim dd.sql
postgres@BJ-015908:~$
源码位置
src/bin/pg_dump/pg_dump.c
pg_dump开始执行的位置
if (argc > 1)
{
if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
{
help(progname);
exit_nicely(0);
}
if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
{
puts("pg_dump (PostgreSQL) " PG_VERSION);
exit_nicely(0);
}
}
InitDumpOptions(&dopt);
while ((c = getopt_long(argc, argv, "abBcCd:e:E:f:F:h:j:n:N:Op:RsS:t:T:U:vwWxZ:",
long_options, &optindex)) != -1)
{
switch (c)
{
case 'a': /* Dump data only */
dopt.dataOnly = true;
break;
case 'b': /* Dump blobs */
dopt.outputBlobs = true;
break;
case 'B': /* Don't dump blobs */
dopt.dontOutputBlobs = true;
break;
case 'c': /* clean (i.e., drop) schema prior to create */
dopt.outputClean = 1;
break;
case 'C': /* Create DB */
dopt.outputCreateDB = 1;
break;
输出到文件
/* Open the output file */
fout = CreateArchive(filename, archiveFormat, compressLevel, dosync,
archiveMode, setupDumpWorker);
干活的地方 dumpDumpableObject
/*
* First the special entries for ENCODING, STDSTRINGS, and SEARCHPATH.
*/
dumpEncoding(fout);
dumpStdStrings(fout);
dumpSearchPath(fout);
/* The database items are always next, unless we don't want them at all */
if (dopt.outputCreateDB)
dumpDatabase(fout);
/* Now the rearrangeable objects. */
for (i = 0; i < numObjs; i++)
dumpDumpableObject(fout, dobjs[i]);
/*
* Set up options info to ensure we dump what we want.
*/
ropt = NewRestoreOptions();
ropt->filename = filename;
gdb调试信息
postgres@BJ-015908:~$ gdb --args usr/local/pgsqldebug/bin/pg_dump --format p -f dd.sql
GNU gdb (Ubuntu 12.1-0ubuntu1~22.04.2) 12.1
Copyright (C) 2022 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Type "show copying" and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<https://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
<http://www.gnu.org/software/gdb/documentation/>.
For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from usr/local/pgsqldebug/bin/pg_dump...
(gdb)
(gdb) l
295 const char *objname,
296 const char *objnamespace);
297 static const char *getAttrName(int attrnum, const TableInfo *tblInfo);
298 static const char *fmtCopyColumnList(const TableInfo *ti, PQExpBuffer buffer);
299 static bool nonemptyReloptions(const char *reloptions);
300 static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
301 const char *prefix, Archive *fout);
302 static char *get_synchronized_snapshot(Archive *fout);
303 static void setupDumpWorker(Archive *AHX);
304 static TableInfo *getRootTableInfo(const TableInfo *tbinfo);
(gdb) list
305
306
307 int
308 main(int argc, char **argv)
309 {
310 int c;
311 const char *filename = NULL;
312 const char *format = "p";
313 TableInfo *tblinfo;
314 int numTables;
(gdb) b 312
Breakpoint 1 at 0x6520: file pg_dump.c, line 312.
(gdb) n
The program is not being run.
(gdb) run
pg_dump调试
pg_dump执行事务SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
打印连接串信息
postgres@BJ-015908:~$ gdb --args /usr/local/pgsqldebug/bin/pg_dump --format p -f dd.sql
(gdb) list
295 const char *objname,
296 const char *objnamespace);
297 static const char *getAttrName(int attrnum, const TableInfo *tblInfo);
298 static const char *fmtCopyColumnList(const TableInfo *ti, PQExpBuffer buffer);
299 static bool nonemptyReloptions(const char *reloptions);
300 static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions,
301 const char *prefix, Archive *fout);
302 static char *get_synchronized_snapshot(Archive *fout);
303 static void setupDumpWorker(Archive *AHX);
304 static TableInfo *getRootTableInfo(const TableInfo *tbinfo);
(gdb) b 1215
Breakpoint 1 at 0x8e57: file pg_dump.c, line 1215.
(gdb) run
Starting program: /usr/local/pgsqldebug/bin/pg_dump --format p -f dd.sql
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Breakpoint 1, setup_connection (AH=AH@entry=0x5555555c5250, dumpencoding=dumpencoding@entry=0x0, dumpsnapshot=dumpsnapshot@entry=0x0, use_role=<optimized out>, use_role@entry=0x0) at pg_dump.c:1215
1215 ExecuteSqlStatement(AH, "BEGIN");
(gdb) s
ExecuteSqlStatement (AHX=AHX@entry=0x5555555c5250, query=query@entry=0x555555598127 "BEGIN") at pg_backup_db.c:283
283 {
(gdb) s
287 res = PQexec(AH->connection, query);
(gdb) s
PQexec (conn=0x5555555c9610, query=query@entry=0x555555598127 "BEGIN") at fe-exec.c:2242
2242 {
(gdb) s
2243 if (!PQexecStart(conn))
(gdb) info args
conn = 0x5555555c9610
query = 0x555555598127 "BEGIN"
(gdb) print conn
$1 = (PGconn *) 0x5555555c9610
(gdb) print *conn
$2 = {pghost = 0x0, pghostaddr = 0x0, pgport = 0x5555555d2970 "5432", connect_timeout = 0x0, pgtcp_user_timeout = 0x0, client_encoding_initial = 0x0,
pgoptions = 0x5555555d2990 "", appname = 0x0, fbappname = 0x5555555d29b0 "pg_dump", dbName = 0x5555555d2650 "postgres", replication = 0x0,
pguser = 0x5555555d2930 "postgres", pgpass = 0x0, pgpassfile = 0x5555555d1e60 "/home/postgres/.pgpass", channel_binding = 0x5555555d2950 "disable",
keepalives = 0x0, keepalives_idle = 0x0, keepalives_interval = 0x0, keepalives_count = 0x0, sslmode = 0x5555555d29d0 "disable",
sslcompression = 0x5555555d29f0 "0", sslkey = 0x0, sslcert = 0x0, sslpassword = 0x0, sslrootcert = 0x0, sslcrl = 0x0, sslcrldir = 0x0,
sslsni = 0x5555555d2a10 "1", requirepeer = 0x0, gssencmode = 0x5555555d2a50 "disable", krbsrvname = 0x5555555d2a70 "postgres", gsslib = 0x0,
ssl_min_protocol_version = 0x5555555d2a30 "TLSv1.2", ssl_max_protocol_version = 0x0, target_session_attrs = 0x5555555d2a90 "any", Pfdebug = 0x0,
traceFlags = 0, noticeHooks = {noticeRec = 0x7ffff7f78460 <defaultNoticeReceiver>, noticeRecArg = 0x0, noticeProc = 0x55555558aff0 <notice_processor>,
noticeProcArg = 0x0}, events = 0x0, nEvents = 0, eventArraySize = 0, status = CONNECTION_OK, asyncStatus = PGASYNC_IDLE, xactStatus = PQTRANS_IDLE,
last_sqlstate = "\000\000\000\000\000", options_valid = true, nonblocking = false, pipelineStatus = PQ_PIPELINE_OFF, singleRowMode = false,
copy_is_binary = 0 '\000', copy_already_done = 0, notifyHead = 0x0, notifyTail = 0x0, nconnhost = 1, whichhost = 0, connhost = 0x5555555d1e30,
connip = 0x0, cmd_queue_head = 0x0, cmd_queue_tail = 0x0, cmd_queue_recycle = 0x5555555d2830, sock = 3, laddr = {addr = {ss_family = 1,
__ss_padding = '\000' <repeats 117 times>, __ss_align = 0}, salen = 2}, raddr = {addr = {ss_family = 1,
__ss_padding = "/tmp/.s.PGSQL.5432", '\000' <repeats 99 times>, __ss_align = 0}, salen = 110}, pversion = 196608, sversion = 140007,
auth_req_received = true, password_needed = false, sigpipe_so = false, sigpipe_flag = true, write_failed = false, write_err_msg = 0x0,
target_server_type = SERVER_TYPE_ANY, try_next_addr = false, try_next_host = false, addrlist = 0x0, addr_cur = 0x0, addrlist_family = 1,
send_appname = true, be_pid = 2689916, be_key = -1602598770, pstatus = 0x5555555d2b30, client_encoding = 6, std_strings = true,
default_transaction_read_only = PG_BOOL_NO, in_hot_standby = PG_BOOL_NO, verbosity = PQERRORS_DEFAULT, show_context = PQSHOW_CONTEXT_ERRORS,
lobjfuncs = 0x0, inBuffer = 0x5555555c99e0 "C", inBufSize = 16384, inStart = 15, inCursor = 15, inEnd = 15, outBuffer = 0x5555555cd9f0 "Q",
outBufSize = 16384, outCount = 0, outMsgStart = 1, outMsgEnd = 28, rowBuf = 0x5555555d1a00, rowBufLen = 32, result = 0x0, next_result = 0x0,
sasl_state = 0x0, ssl_in_use = false, errorMessage = {data = 0x5555555d1c10 "", len = 0, maxlen = 256}, workBuffer = {data = 0x5555555d1d20 "SET", len = 3,
maxlen = 256}}
(gdb) print
LOCK TABLE IN ACCESS SHARE MODE
这个位置就是为什么会卡住DDL的地方，DDL是八级锁，AS是普通1级锁，他们互斥
TableInfo *
getTables(Archive *fout, int *numTables)
if (tblinfo[i].dobj.dump &&
(tblinfo[i].relkind == RELKIND_RELATION ||
tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"LOCK TABLE %s IN ACCESS SHARE MODE",
fmtQualifiedDumpable(&tblinfo[i]));
ExecuteSqlStatement(fout, query->data);
}
/* Emit notice if join for owner failed */
if (strlen(tblinfo[i].rolname) == 0)
pg_log_warning("owner of table \"%s\" appears to be invalid",
tblinfo[i].dobj.name);
}
文章转载自SmallDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
