openGauss备份的学习
数据备份是保护数据的重要手段，对于一个DBA来说，备份重于一切，在学习一个新的数据库的时候，在完成了基础环境的安装搭建后，应该从备份入手学习，保护数据。
openGauss数据库支持两种备份类型，可分为物理备份和逻辑备份。
一: 备份类型
1.物理备份
通过物理文件拷贝的方式对数据库进行备份，以磁盘块为基本单位将数据备份。通过备份的数据文件及归档日志等文件，数据库可以进行完全恢复。物理备份速度快，一般被用作对数据进行备份和恢复，用于全量备份的场景。通过合理规划，可以低成本进行备份与恢复。openGauss的物理备份gs_basebackup，是对数据库做一个全库的备份。物理备份的好处是恢复的时候可以直接替换原有的文件。
2.逻辑备份
通过逻辑导出对数据进行备份，逻辑备份只能基于备份时刻进行数据转储，所以恢复时也只能恢复到备份时保存的数据。对于故障点和备份点之间的数据，逻辑备份无能为力，逻辑备份适合备份那些很少变化的数据，当这些数据因误操作被损坏时，可以通过逻辑备份进行快速恢复。如果通过逻辑备份进行全库恢复，通常需要重建数据库，导入备份数据来完成，对于可用性要求很高的数据库，这种恢复时间太长，通常不被采用。由于逻辑备份具有平台无关性，所以更为常见的是，逻辑备份被作为一个数据迁移及移动的主要手段。所以逻辑备份的适用场景，主要是数据量小的情况，可以单独的备份某个表等，业务场景适用于开发或者运维人员要对表的表进行修改之前，单独将该表备份处理。
以上的备份并不难理解，对标oracle的话，物理备份类似于rman，而逻辑备份类似于exp/expdp。
二: gs_basebackup命令详解
root@modb:/usr/local/opengauss# su - omm
omm@modb:~$ gs_basebackup --help
gs_basebackup takes a base backup of a running openGauss server.
Usage:
gs_basebackup [OPTION]...
Options controlling the output:
-D, --pgdata=DIRECTORY receive base backup into directory
-F, --format=p|t output format (plain (default), tar)
-T, --tablespace-mapping=OLDDIR=NEWDIR
relocate tablespace in OLDDIR to NEWDIR
-x, --xlog include required WAL files in backup (fetch mode)
-X, --xlog-method=fetch|stream
include required WAL files with specified method
-z, --gzip compress tar output
-Z, --compress=0-9 compress tar output with given compression level
General options:
-c, --checkpoint=fast|spread
set fast or spread checkpointing
-l, --label=LABEL set backup label
-P, --progress show progress information
-v, --verbose output verbose messages
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
read-write timeout during idle connection.(in seconds)
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
Report bugs to community@opengauss.org> or join opengauss community <https://opengauss.org>.
-s, --status-interval=INTERVAL
time between status packets sent to server (in seconds)
-t, --rw-timeout=RW_TIMEOUT
从以上输出可以看出gs_basebackup这个备份命令，最基本的选项是参数 D，指明了备份到什么目录下：
以下将对备份命令进行测试。
omm@modb:~/backup$ gs_basebackup -D /home/omm/backup/
INFO: The starting position of the xlog copy of the full build is: 0/6000028. The slot minimum LSN is: 0/0.
[2021-09-13 18:31:02]:begin build tablespace list
[2021-09-13 18:31:02]:finish build tablespace list
[2021-09-13 18:31:02]:begin get xlog by xlogstream
[2021-09-13 18:31:02]: check identify system success
[2021-09-13 18:31:02]: send START_REPLICATION 0/6000000 success
[2021-09-13 18:31:02]: keepalive message is received
[2021-09-13 18:31:02]: keepalive message is received
[2021-09-13 18:31:07]:gs_basebackup: base backup successfully
omm@modb:~/backup$
omm@modb:~/backup$ pwd
/home/omm/backup
omm@modb:~/backup$ ls
PG_VERSION base gswlm_userinfo.cfg pg_clog pg_errorinfo pg_llog pg_perf pg_snapshots pg_twophase postgresql.conf.lock
asp_data global mot.conf pg_csnlog pg_hba.conf pg_multixact pg_replslot pg_stat_tmp pg_xlog sql_monitor
backup_label gs_profile pg_audit pg_ctl.lock pg_ident.conf pg_notify pg_serial pg_tblspc postgresql.conf
通过以上的命令可以看出已经进行了备份。
三: gs_dump命令详解
omm@modb:~/backup$ gs_dump --help
gs_dump dumps a database as a text file or to other formats.
Usage:
gs_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))
-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
-?, --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
-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, --sysadmin=NAME system admin 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)
--include-table-file=FileName dump the named table(s) only
--exclude-table-file=FileName do NOT dump the named table(s)
-x, --no-privileges/--no-acl do not dump privileges (grant/revoke)
--column-inserts/--attribute-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
--exclude-table-data=TABLE do NOT dump data for the named table(s)
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-tablespaces do not dump tablespace assignments
--no-unlogged-table-data do not dump unlogged table data
--include-alter-table dump the table delete column
--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
--dont-overwrite-file do not overwrite the existing file in case of plain, tar and custom format
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
--with-encryption=AES128 dump data is encrypted using AES128
--with-key=KEY AES128 encryption key, must be 16 bytes in length
--with-salt=RANDVALUES used by gs_dumpall, pass rand value array
--include-extensions include extensions in dump
--binary-upgrade for use by upgrade utilities only
--binary-upgrade-usermap="USER1=USER2" to be used only by upgrade utility for mapping usernames
--non-lock-table for use by OM tools utilities only
--include-depend-objs dump the object which depends on the input object
--exclude-self do not dump the input object
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, --no-password never prompt for password
-W, --password=PASSWORD the password of specified database user
--role=ROLENAME do SET ROLE before dump
--rolepassword=ROLEPASSWORD the password for role
If no database name is supplied, then the PGDATABASE environment
variable value is used.
在openGauss测试库中，查询要进行操作的数据库和表。
对hr库下的departments表进行备份。
omm@modb:~/backup$ gs_dump -f dmp.sql hr -t departments
gs_dump[port='5432'][hr][2021-09-13 18:47:04]: The total objects number is 382.
gs_dump[port='5432'][hr][2021-09-13 18:47:04]: [100.00%] 382 objects have been dumped.
gs_dump[port='5432'][hr][2021-09-13 18:47:04]: dump database hr successfully
gs_dump[port='5432'][hr][2021-09-13 18:47:04]: total time: 75 ms
对备份出来的dmp.sql文件进行查看，可以看到备份出来的信息是文本格式的sql文件
omm@modb:~/backup$ cat dmp.sql
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: departments; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
CREATE TABLE departments (
department_id numeric(4,0) NOT NULL,
department_name character varying(30) NOT NULL,
manager_id numeric(6,0),
location_id numeric(4,0)
)
WITH (orientation=row, compression=no);
--
-- Name: departments_pkey; Type: CONSTRAINT; Schema: public; Owner: omm; Tablespace:
--
ALTER TABLE departments
ADD CONSTRAINT departments_pkey PRIMARY KEY (department_id);
--
-- Name: dept_loc_fk; Type: FK CONSTRAINT; Schema: public; Owner: omm
--
ALTER TABLE departments
ADD CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations(location_id);
--
-- PostgreSQL database dump complete
--
ALTER TABLE public.departments OWNER TO omm;
--
-- Data for Name: departments; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY departments (department_id, department_name, manager_id, location_id) FROM stdin;
\.
;
omm@modb:~/backup$ cat dmp.sql|more
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public;
SET default_tablespace = '';
SET default_with_oids = false;
--
CREATE TABLE departments (
department_id numeric(4,0) NOT NULL,
-- Name: departments; Type: TABLE; Schema: public; Owner: omm; Tablespace:
--
department_name character varying(30) NOT NULL,
manager_id numeric(6,0),
location_id numeric(4,0)
)
WITH (orientation=row, compression=no);
ALTER TABLE public.departments OWNER TO omm;
--
-- Data for Name: departments; Type: TABLE DATA; Schema: public; Owner: omm
--
COPY departments (department_id, department_name, manager_id, location_id) FROM stdin;
\.
;
--
-- Name: departments_pkey; Type: CONSTRAINT; Schema: public; Owner: omm; Tablespace:
--
ALTER TABLE departments
ADD CONSTRAINT departments_pkey PRIMARY KEY (department_id);
--
-- Name: dept_loc_fk; Type: FK CONSTRAINT; Schema: public; Owner: omm
--
ALTER TABLE departments
ADD CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations(location_id);
--
-- PostgreSQL database dump complete
--
以上，主要对openGauss提供的物理备份命令gs_basebackup和逻辑备份命令gs_dump进行了学习和测试。
最后修改时间：2021-09-13 19:30:14
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
