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

embedded/2024/11/26 23:31:08/

文章目录

    • 0.简介
    • 1. 使用方法
      • 1.1 pg_dump
      • 1.2 pg_dumpall
    • 2.逻辑备份原理概述
    • 3.代码分析
      • 3.1 pg_dump
      • 3.2 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 exitOptions 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 inplain-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 hasaccess 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 tomatch at least one entity each--use-set-session-authorizationuse SET SESSION AUTHORIZATION commands instead ofALTER OWNER commands to set ownershipConnection 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 dumpIf 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 exitOptions 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-authorizationuse SET SESSION AUTHORIZATION commands instead ofALTER OWNER commands to set ownershipConnection 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 dumpIf -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文件独立封装了不同导出的文件格式下的处理函数,这样可以很容易地增加新的导出文件格式,提高了可维护性和扩展性。

类型源文件说明
custompg_backup_custom.c导出对象存储到二进制格式的文件中
filepg_backup_files.c导出对象存储到指定的文件中
plainpg_backup_null.c导出文件到标准输出
tarpg_backup_tar.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);elsefprintf(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);}}

http://www.ppmy.cn/embedded/140758.html

相关文章

2024年11月21日Github流行趋势

项目名称&#xff1a;twenty 项目维护者&#xff1a;charlesBochet, lucasbordeau, Weiko, FelixMalfait, bosiraphael项目介绍&#xff1a;正在构建一个由社区支持的现代化Salesforce替代品。项目star数&#xff1a;21,798项目fork数&#xff1a;2,347 项目名称&#xff1a;p…

aws建立多区域只读库

文章目录 一、Aurora数据库创建多区域注意项&#xff1a;二、aws-rds多区域只读库建立三、cli 创建实例: 一、Aurora数据库创建多区域注意项&#xff1a; aurora数据库 开启跨区域必须是主库不低于db.r5.large规格, 目标区域規格使用db.r5.large&#xff0c;使用低于此规格的将…

MongoDB相关问题

视频教程 【GeekHour】20分钟掌握MongoDB Complete MongoDB Tutorial by Net Ninja MongoDB开机后调用缓慢的原因及解决方法 问题分析&#xff1a; MongoDB开机后调用缓慢&#xff0c;通常是由于以下原因导致&#xff1a; 索引重建&#xff1a; MongoDB在启动时会重建索引…

利用 GitHub 和 Hexo 搭建个人博客【保姆教程】

利用 GitHub 和 Hexo 搭建个人博客 利用 GitHub 和 Hexo 搭建个人博客一、前言二、准备工作&#xff08;一&#xff09;安装 Node.js 和 Git&#xff08;二&#xff09;注册 GitHub 账号 三、安装 Hexo&#xff08;一&#xff09;创建博客目录&#xff08;二&#xff09;安装 H…

Leetcode146. LRU 缓存(HOT100)

链接 代码&#xff1a; class LRUCache { private:struct Node{int key,val;Node* left,*right;Node(int _key,int _val):key(_key),val(_val){}}*L,*R;unordered_map<int,Node*> hash;int n;public:void remove(struct Node* p){p->left->right p->right;p-…

Docker部署Canal实现将Mysql数据同步至ES

目录 Canal 是什么?一、安装docker1.安装2.启动二、安装docker-compose1.卸载旧版本2.下载最新版3.授权4.检查版本三、配置MySQL1.开启 Binlog 写入,配置 binlog-format 为 ROW 模式2.授权 canal 有 slave 的权限四、创建docker网络五、部署canal-admin1.在数据库中创建canal…

C++ —— 以真我之名 如飞花般绚丽 - 智能指针

目录 1. RAII和智能指针的设计思路 2. C标准库智能指针的使用 2.1 auto_ptr 2.2 unique_ptr 2.3 简单模拟实现auto_ptr和unique_ptr的核心功能 2.4 shared_ptr 2.4.1 make_shared 2.5 weak_ptr 2.6 shared_ptr的缺陷&#xff1a;循环引用问题 3. shared_ptr 和 unique_…

Linux的开发工具(二)

1.vim的基本操作 正常模式到插入模式 输入a 输入i 输入o 示例 输入iao下面的就会变成INSERT模式 插入模式到正常模式 按Esc键 正常模式到低行模式 shift&#xff1b; &#xff1a;w保存当前文件 &#xff1a;wq保存并退出 &#xff1a;q&#xff01;强制退出 2.vi…