PostgreSQL技术内幕19:逻辑备份工具pg_dump、pg_dumpall

0.简介

上一篇文章介绍了逻辑备份和物理备份概念，主要介绍了PG的物理备份工具，本文主要介绍PG逻辑备份工具g_dump和pg_dumpall，包括其使用方法和对应的流程以及代码分析。

1. 使用方法

pg_dump可以备份单个数据库。它只会备份指定数据库的数据和结构，不包括全局对象、角色和用户定义的函数；而pg_dumpall是备份整个PostgreSQL集群，并且想要包含全局对象和角色等。其中pg_dumall就是调用的pg_dump，从下面参数看pg_dump支持更多格式的导出，而pg_dumpall只能默认导出成sql文件。

1.1 pg_dump

直接使用--help查看

pg_dump --help
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 or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -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         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)
  --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
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --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
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -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, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

1.2 pg_dumpall

也是直接使用--help查看

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
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -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
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --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:
  -d, --dbname=CONNSTR     connect using connection string
  -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.

Report bugs to <pgsql-bugs@postgresql.org>

2.逻辑备份原理概述

概括地说，逻辑导出要干的事情就是连接对应数据库，读出各个数据库对象的定义和数据，此外还包括comment、服务器配置和权限控制等等，这些数据库对象定义的SQL语句会被写入到对应的dump文件中。其中可以设置只导出模式或者只导出数据，默认是导出模式和数据，这样就可以支持分步导出和恢复。而数据表数据可以选择COPY方式或者INSERT语句的方式写入备份文件中。

3.代码分析

3.1 pg_dump

主流程如下：

需要整理待导出对象的原因，一是为了处理依赖顺序，一是先组织数据库对象的基本信息（先占位，避免直接是所有数据导致占用过高）。

下面来看其中导出文件格式的不同处理，其打开输出文件，输出流为g_fout，g_fout是Archive类型，这里比较巧妙的地方就是根据不同的文件格式，会产生不同的g_fout，对应也就使用不同的.c文件独立封装了不同导出的文件格式下的处理函数，这样可以很容易地增加新的导出文件格式，提高了可维护性和扩展性。

可以查看pg_backup_archiver.h文件，其中有大量的函数指针。

typedef void (*ClosePtr) (struct _archiveHandle * AH);
typedef void (*ReopenPtr) (struct _archiveHandle * AH);
typedef void (*ArchiveEntryPtr) (struct _archiveHandle * AH, struct _tocEntry * te);
      这些函数指针，被用到了如下文件中（文件->被调用的函数）：
     pg_backup_custom.c->InitArchiveFmt_Custom(ArchiveHandle *AH)
     pg_backup_null.c->InitArchiveFmt_Null(ArchiveHandle *AH)
     pg_backup_files.c->InitArchiveFmt_Files(ArchiveHandle *AH)
     pg_backup_tar.c->InitArchiveFmt_Tar(ArchiveHandle *AH)
      在数据结构ArchiveHandle中，使用了大量的函数指针，使得在初始化不同导出文件格式的Archive结构时能够为处理函数赋值为各自不同的处理函数。这样在pg_dump.c中，只要根据用户指定的文件格式的参数，就可以调用相应的处理函数，代码如下：
    /* open the output file */
    if (pg_strcasecmp(format, "a") == 0 || pg_strcasecmp(format, "append") == 0)
    {
        /* This is used by pg_dumpall, and is not documented */
        plainText = 1;
        g_fout = CreateArchive(filename, archNull, 0, archModeAppend);
    }
    else if (pg_strcasecmp(format, "c") == 0 || pg_strcasecmp(format, "custom") == 0)
        g_fout = CreateArchive(filename, archCustom, compressLevel, archModeWrite);
    else if (pg_strcasecmp(format, "f") == 0 || pg_strcasecmp(format, "file") == 0)
    {
        /*
         * Dump files into the current directory; for demonstration only, not
         * documented.
         */
        g_fout = CreateArchive(filename, archFiles, compressLevel, archModeWrite);
    }
    else if (pg_strcasecmp(format, "p") == 0 || pg_strcasecmp(format, "plain") == 0)
    {
        plainText = 1;
        g_fout = CreateArchive(filename, archNull, 0, archModeWrite);
    }
    else if (pg_strcasecmp(format, "t") == 0 || pg_strcasecmp(format, "tar") == 0)
        g_fout = CreateArchive(filename, archTar, compressLevel, archModeWrite);
    else
    {
        write_msg(NULL, "invalid output format \"%s\" specified\n", format);
        exit(1);
    }

3.2 pg_dumpall

pg_dumpall本质上是不断调用pg_dump。

if ((ret = find_other_exec(argv[0], "pg_dump", PGDUMP_VERSIONSTR,
                 pg_dump_bin)) < 0)
  {
    char    full_path[MAXPGPATH];

    if (find_my_exec(argv[0], full_path) < 0)
      strlcpy(full_path, progname, sizeof(full_path));

    if (ret == -1)
      fprintf(stderr,
          _("The program \"pg_dump\" is needed by %s "
            "but was not found in the\n"
            "same directory as \"%s\".\n"
            "Check your installation.\n"),
          progname, full_path);
    else
      fprintf(stderr,
          _("The program \"pg_dump\" was found by \"%s\"\n"
            "but was not the same version as %s.\n"
            "Check your installation.\n"),
          full_path, progname);
    exit_nicely(1);
  }

  pgdumpopts = createPQExpBuffer();

  while ((c = getopt_long(argc, argv, "acd:E:f:gh:l:oOp:rsS:tU:vwWx", long_options, &optindex)) != -1)
  {
    switch (c)
    {
      case 'a':
        data_only = true;
        appendPQExpBufferStr(pgdumpopts, " -a");
        break;

      case 'c':
        output_clean = true;
        break;

      case 'd':
        connstr = pg_strdup(optarg);
        break;

      case 'E':
        dumpencoding = pg_strdup(optarg);
        appendPQExpBufferStr(pgdumpopts, " -E ");
        appendShellString(pgdumpopts, optarg);
        break;

      case 'f':
        filename = pg_strdup(optarg);
        appendPQExpBufferStr(pgdumpopts, " -f ");
        appendShellString(pgdumpopts, filename);
        break;

      case 'g':
        globals_only = true;
        break;

      case 'h':
        pghost = pg_strdup(optarg);
        break;

      case 'l':
        pgdb = pg_strdup(optarg);
        break;

      case 'o':
        appendPQExpBufferStr(pgdumpopts, " -o");
        break;

      case 'O':
        appendPQExpBufferStr(pgdumpopts, " -O");
        break;

      case 'p':
        pgport = pg_strdup(optarg);
        break;

      case 'r':
        roles_only = true;
        break;

      case 's':
        appendPQExpBufferStr(pgdumpopts, " -s");
        break;

      case 'S':
        appendPQExpBufferStr(pgdumpopts, " -S ");
        appendShellString(pgdumpopts, optarg);
        break;

      case 't':
        tablespaces_only = true;
        break;

      case 'U':
        pguser = pg_strdup(optarg);
        break;

      case 'v':
        verbose = true;
        appendPQExpBufferStr(pgdumpopts, " -v");
        break;

      case 'w':
        prompt_password = TRI_NO;
        appendPQExpBufferStr(pgdumpopts, " -w");
        break;

      case 'W':
        prompt_password = TRI_YES;
        appendPQExpBufferStr(pgdumpopts, " -W");
        break;

      case 'x':
        skip_acls = true;
        appendPQExpBufferStr(pgdumpopts, " -x");
        break;

      case 0:
        break;

      case 2:
        appendPQExpBufferStr(pgdumpopts, " --lock-wait-timeout ");
        appendShellString(pgdumpopts, optarg);
        break;

      case 3:
        use_role = pg_strdup(optarg);
        appendPQExpBufferStr(pgdumpopts, " --role ");
        appendShellString(pgdumpopts, use_role);
        break;

      case 4:
        dosync = false;
        appendPQExpBufferStr(pgdumpopts, " --no-sync");
        break;

      default:
        fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
        exit_nicely(1);
    }
  }