openGauss database backup and recovery guide

brief introduction

This instruction is applicable to openGauss database. Through this instruction, you can understand the two backup and recovery types and various backup and recovery schemes supported by openGauss database, and provide data reliability guarantee mechanism in the process of backup and recovery.

Content description

Data backup is one of the important means to protect data security. In order to better protect data security, openGauss database supports two backup and recovery types and a variety of backup and recovery schemes, and provides data reliability guarantee mechanism in the process of backup and recovery.
Backup and recovery types can be divided into logical backup and recovery, physical backup and recovery.

Preconditions

  • Because this experiment is mainly to backup and restore the database on the openEuler operating system, we need to master the basic operation and system commands of Linux system.
  • The experimental premise and database recovery verification need to master the basic operation and SQL syntax of openGauss database. openGauss database supports SQL2003 standard syntax.

Description of experimental environment

  • Networking description
    The experimental environment is openGauss database management system, which is installed on Huawei cloud openEuler elastic server ECS.
  • Equipment introduction
    In order to meet the experimental needs of database principle and practice course, it is recommended that each set of experimental environment adopt the following configuration:
    The correspondence between equipment name, model and version is as follows:
Equipment visibility table
Equipment nameEquipment modelSoftware version
databaseopenGaussopenGauss 1.0.0
operating systemopenEuleropenEuler 20.3LTS

Experimental overview

Database backup and recovery

Experimental introduction

About this experiment

This experiment mainly describes two backup and recovery types and various backup and recovery schemes supported by openGauss database, and can provide data reliability guarantee mechanism in the process of backup and recovery.

Experimental purpose

  • Master the logical backup and recovery methods in openGauss database;
  • Master the methods of physical backup and recovery of openGauss database;
  • It can provide data reliability guarantee mechanism in the process of backup and recovery.

Experimental premise

Before backing up the database, perform the following operations on the database:
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su – omm

Step 2 start the database service

gs_om -t start

Step 3 connect to openGauss database.

gsql -d postgres -p 26000 -r   

Step 4 create customer_t1 table.

postgres=#DROP TABLE IF EXISTS customer_t1;
 
postgres=#CREATE TABLE customer_t1 
( 
    c_customer_sk             integer, 
    c_customer_id             char(5), 
    c_first_name              char(6), 
    c_last_name               char(8) 
);

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE TABLE

Step 5 insert data into the table.

postgres=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES
(3769, 'hello', DEFAULT) ,
(6885, 'maps', 'Joes'), 
(4321, 'tpcds', 'Lily'), 
(9527, 'world', 'James');

The display result is:

INSERT 0 4

Step 6 view the data in the table.

postgres=# select * from customer_t1;

 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(4 rows)

Step 7 create customer_t2 table

postgres=#DROP TABLE IF EXISTS customer_t2;
 
postgres=#CREATE TABLE customer_t2 
( 
    c_customer_sk             integer, 
    c_customer_id             char(5), 
    c_first_name              char(6), 
    c_last_name               char(8) 
);

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE TABLE

Step 8 insert data into the table

postgres=# INSERT INTO customer_t2 (c_customer_sk, c_customer_id, c_first_name) VALUES
(3769, 'hello', DEFAULT) ,
(6885, 'maps', 'Joes'), 
(9527, 'world', 'James');

The display result is:

INSERT 0 3

Step 9 view the data in the table.

select * from customer_t2;

 c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
          3769 | hello         |              |
          6885 | maps          | Joes         |
          9527 | world         | James        |
(3 rows)

Step 10 create user lucy.

postgres=# DROP user IF EXISTS lucy;
postgres=# CREATE USER lucy WITH PASSWORD "Bigdata@123";

Step 11 switch to Lucy user.

postgres=# \c - lucy

Password for user lucy: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "lucy".

Step 12 create the table of lucy shema.

postgres=# DROP TABLE IF EXISTS lucy.mytable;

postgres=# CREATE TABLE mytable (firstcol int);

Step 13 insert data into the table.

postgres=# INSERT INTO mytable values (100);

When the result displays the following information, it indicates that the data insertion is successful.

INSERT 0 1

Step 14 view the data in the table.

postgres=# SELECT * from mytable; 

The display result is:

 firstcol  
---------- 
      100 
(1 row)

Step 15 exit the database:

postgres=#\q

Physical backup and recovery

After openGauss is successfully deployed, various problems and abnormal states will be encountered during the operation of the database. openGauss provides GS_ Base backup tool for basic backup. gs_ The basebackup tool is executed by the operating system user omm.

Experimental premise

Create a folder to store backup files before backing up the database.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm    

Step 2 create a folder to store backup files.

mkdir -p /home/omm/physical/backup

Physical backup

Step 1 if the database service is not started, start the database service (be sure to start the database service with the operating system user omm. If not, switch the user).

gs_om -t start 

The results are displayed as:

Starting cluster.
=========================================
=========================================
Successfully started.

Step 2 make a physical backup of the database.

gs_basebackup -D /home/omm/physical/backup -p 26000

Parameter - D directory indicates the directory of backup file output. Required.
The results are displayed as:

INFO:  The starting position of the xlog copy of the full build is: 1/D5000028. The slot minimum LSN is: 0/0.
begin build tablespace list
finish build tablespace list
begin get xlog by xlogstream
 check identify system success
 send START_REPLICATION 1/D5000000 success
 keepalive message is received
 keepalive message is received
 keepalive message is received
 keepalive message is received
 keepalive message is received
 keepalive message is received
 keepalive message is received
 keepalive message is received

Step 3 switch to the storage backup folder to view the backup files.

cd /home/omm/physical/backup
ls

The display is as follows:

backup_label        mot.conf     pg_errorinfo      pg_llog       pg_snapshots  pg_xlog               server.key
base                pg_clog      pg_hba.conf       pg_multixact  pg_stat_tmp   postgresql.conf       server.key.cipher
cacert.pem          pg_copydir   pg_hba.conf.bak   pg_notify     pg_tblspc     postgresql.conf.bak   server.key.rand
global              pg_csnlog    pg_hba.conf.lock  pg_replslot   pg_twophase   postgresql.conf.lock
gswlm_userinfo.cfg  pg_ctl.lock  pg_ident.conf     pg_serial     PG_VERSION    server.crt

Physical backup and recovery

When the database fails, it needs to be restored from the backup file. Because gs_basebackup is to back up the database in binary, so you can directly copy and replace the original files during recovery, or directly start the database on the backed up library.
explain:

  • If the current database instance is running, there may be port conflicts when starting the database directly from the backup file. In this case, you need to modify the port parameter of the configuration file or specify a port when starting the database.
  • If the current backup file is the primary and standby database, you may need to modify the replication connection between the primary and standby databases. Postgres. In the configuration file replconninfo1 and replconninfo2 in conf.

When the database has problems and needs to be restored from backup, the steps are as follows:
Step 1 stop opengauss ((be sure to stop the database service with the operating system user omm. If not, please switch users).

gs_om -t stop

The results displayed are:

Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.

Step 2 clean up all or part of the files in the original library.

cd /gaussdb/data/
ls

View the folder name of the database node (the folder name is defined during database installation, and different data may be different).

db1

The list of files to view is as follows:

cd db1
ls

base                mot.conf     pg_errorinfo      pg_llog       pg_serial     PG_VERSION            postmaster.opts
cacert.pem          pg_clog      pg_hba.conf       pg_location   pg_snapshots  pg_xlog               server.crt
gaussdb.state       pg_copydir   pg_hba.conf.bak   pg_multixact  pg_stat_tmp   postgresql.conf       server.key
global              pg_csnlog    pg_hba.conf.lock  pg_notify     pg_tblspc     postgresql.conf.bak   server.key.cipher
gswlm_userinfo.cfg  pg_ctl.lock  pg_ident.conf     pg_replslot   pg_twophase   postgresql.conf.lock  server.key.rand

Delete files and destroy database files.

rm -rf *
ls

After deleting the file, the list is as follows:

base    pg_clog     pg_csnlog     pg_llog      pg_multixact  pg_replslot  pg_snapshots  pg_tblspc    pg_xlog
global  pg_copydir  pg_errorinfo  pg_location  pg_notify     pg_serial    pg_stat_tmp   pg_twophase

Or empty:

[omm@ecs-ecs-c9bf db1]$

Step 3 use the database system user permissions to restore the required database files from the backup. Db1 in "/ gaussdb/data/db1" is the name of the database node folder. Different databases may be different. Please check and confirm.

cp -r /home/omm/physical/backup/.  /gaussdb/data/db1

The backup takes about a few minutes. The list of files after recovery is as follows:

cd /gaussdb/data/db1
ls

backup_label.old    mot.conf      pg_hba.conf       pg_multixact  pg_tblspc            postgresql.conf.lock  server.key.rand
base                pg_clog       pg_hba.conf.bak   pg_notify     pg_twophase          postmaster.opts
cacert.pem          pg_copydir    pg_hba.conf.lock  pg_replslot   PG_VERSION           postmaster.pid
gaussdb.state       pg_csnlog     pg_ident.conf     pg_serial     pg_xlog              server.crt
global              pg_ctl.lock   pg_llog           pg_snapshots  postgresql.conf      server.key
gswlm_userinfo.cfg  pg_errorinfo  pg_location       pg_stat_tmp   postgresql.conf.bak  server.key.cipher

If there is a linked file in the database, you need to modify it to link to the correct file.
Step 4 restart the database server and check the contents of the database to ensure that the database has been restored to the required state.

gs_om -t start

Starting cluster.
=========================================
=========================================
Successfully started.

Logical backup and recovery

The data is backed up through logical export. Logical backup can only dump the data based on the backup time, so it can only recover the data saved during backup. For the data between the failure point and the backup point, logical backup is powerless. Logical backup is suitable for backing up the data with little change. When these data are damaged due to misoperation, it can be quickly recovered through logical backup. If the whole database is restored through logical backup, it usually needs to rebuild the database and import backup data. For databases with high availability requirements, this recovery time is too long and is usually not adopted. Because logical backup is platform independent, it is more common that logical backup is used as a main means of data migration and movement.

Experimental premise

Create a folder to store backup files before backing up the database.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm              

Step 2 create a folder to store backup files.

mkdir -p /home/omm/logical/backup

gs_dump

matters needing attention

  • If openGauss has any local data to add to the template1 database, set GS_ The output of dump is restored to a real empty database, otherwise an error may occur because the definition of the added object is copied. To create an empty database without local addition, you need to copy from template0 instead of template1, for example:
CREATE DATABASE foo WITH TEMPLATE template0;
  • The file size of tar archive format shall not exceed 8GB (the inherent limitation of tar file format). There is no limit to the overall size of the tar document and any other output format, which may be required by the operating system.
  • By GS_ The dump file generated by dump does not contain the statistics used by the optimizer to make execution plan decisions. Therefore, it is best to run ANALYZE after recovering from a dump file to ensure the best effect. The dump file does not contain any ALTER DATABASE... SET commands, which are SET by gs_dumpall dump, as well as database users and other installation settings.
  • Use gs_dump dumps the database to SQL text files or other formats: in the example“ Bigdata@123 ”Indicates the database user password. "/ home/omm/logical/backup/MPPDB_backup.sql" indicates the exported file; "26000" indicates the database server port; "postgres" indicates the name of the database to be accessed.

Note: when exporting, please ensure that the directory exists and that the current operating system user has read and write permissions.

gs_dump backup example 1

Execute gs_dump, export the full information of postgres database, and export the mppdb_ backup. The SQL file format is plain text.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 execute gs_dump, exported mppdb_ backup. The SQL file format is plain text.

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/backup/MPPDB_backup.sql -p 26000 postgres -F p

After execution, it is displayed as:

gs_dump[port='26000'][postgres][2020-07-29 14:00:49]: The total objects number is 348.
gs_dump[port='26000'][postgres][2020-07-29 14:00:49]: [100.00%] 348 objects have been dumped.
gs_dump[port='26000'][postgres][2020-07-29 14:00:49]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-07-29 14:00:49]: total time: 176  ms

Step 3 switch to the backup folder and view the MPPDB_backup.sql file.

ll /home/omm/logical/backup/
total 4.0K
-rw------- 1 omm dbgrp 2.7K Jul 29 14:00 MPPDB_backup.sql

cat /home/omm/logical/backup/MPPDB_backup.sql
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: omm
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: lucy; Type: SCHEMA; Schema: -; Owner: lucy
--
CREATE SCHEMA lucy;
ALTER SCHEMA lucy OWNER TO lucy;
SET search_path = lucy;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: mytable; Type: TABLE; Schema: lucy; Owner: lucy; Tablespace: 
--
CREATE TABLE mytable (
    firstcol integer
)
WITH (orientation=row, compression=no);
ALTER TABLE lucy.mytable OWNER TO lucy;
SET search_path = public;
--
-- Name: customer_t1; Type: TABLE; Schema: public; Owner: omm; Tablespace: 
--
CREATE TABLE customer_t1 (
    a integer,
    b integer,
    c integer,
    d integer
)
WITH (orientation=row, compression=no);
ALTER TABLE public.customer_t1 OWNER TO omm;
......
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT ALL ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--

gs_dump backup example 2

Execute gs_dump, export the full information of postgres database, and export the MPPDB_backup.tar file format is tar.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 execute gs_dump, exported mppdb_ backup. The tar file format is tar format.

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/backup/MPPDB_backup.tar -p 26000 postgres -F t	

The results after operation are:

gs_dump[port='26000'][postgres][2020-07-28 16:19:14]: The total objects number is 341.
gs_dump[port='26000'][postgres][2020-07-28 16:19:14]: [100.00%] 341 objects have been dumped.
gs_dump[port='26000'][postgres][2020-07-28 16:19:14]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-07-28 16:19:14]: total time: 169  ms

Step 3: view the generated file information.

ll /home/omm/logical/backup/

total 12K
-rw------- 1 omm dbgrp 1.5K Jul 28 13:47 MPPDB_backup.sql
-rw------- 1 omm dbgrp 8.0K Jul 28 19:31 MPPDB_backup.tar

gs_dump backup example 3

Execute gs_dump, export the full information of postgres database, and export the mppdb_ backup. The DMP file format is a custom archive format.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 execute gs_dump, exported mppdb_ backup. The DMP file format is a custom archive format.

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/backup/MPPDB_backup.dmp -p 26000 postgres -F c

The results after operation are:

gs_dump[port='26000'][postgres][2020-07-28 19:32:20]: The total objects number is 341.
gs_dump[port='26000'][postgres][2020-07-28 19:32:20]: [100.00%] 341 objects have been dumped.
gs_dump[port='26000'][postgres][2020-07-28 19:32:20]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-07-28 19:32:20]: total time: 167  ms

Step 3: view the generated file information.

ll /home/omm/logical/backup/

total 16K
-rw------- 1 omm dbgrp 2.5K Jul 28 19:32 MPPDB_backup.dmp
-rw------- 1 omm dbgrp 1.5K Jul 28 13:47 MPPDB_backup.sql
-rw------- 1 omm dbgrp 8.0K Jul 28 19:31 MPPDB_backup.tar

gs_dump backup example 4

Execute gs_dump, export the full information of postgres database, and export the mppdb_ The backup file format is directory format.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 execute gs_dump, exported mppdb_ The backup file format is directory format.

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/backup/MPPDB_backup -p 26000  postgres -F d

The results after operation are:

gs_dump[port='26000'][postgres][2020-07-28 19:35:59]: The total objects number is 341.
gs_dump[port='26000'][postgres][2020-07-28 19:35:59]: [100.00%] 341 objects have been dumped.
gs_dump[port='26000'][postgres][2020-07-28 19:35:59]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-07-28 19:35:59]: total time: 173  ms

Step 3: view the generated file information.

ll /home/omm/logical/backup/

total 20K
drwx------ 2 omm dbgrp 4.0K Jul 28 19:35 MPPDB_backup
-rw------- 1 omm dbgrp 2.5K Jul 28 19:32 MPPDB_backup.dmp
-rw------- 1 omm dbgrp 1.5K Jul 28 13:47 MPPDB_backup.sql
-rw------- 1 omm dbgrp 8.0K Jul 28 19:31 MPPDB_backup.tar

gs_dump backup example 5

Execute gs_dump, export the table (or view, or sequence, or appearance) objects of the postgres database, such as table customer_t1.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 execute gs_dump, exported table customer_t1.

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/backup/bkp_shl2.sql -t public.customer_t1 -p 26000 postgres

The results after operation are:

gs_dump[port='26000'][postgres][2020-08-15 19:54:12]: The total objects number is 336.
gs_dump[port='26000'][postgres][2020-08-15 19:54:12]: [100.00%] 336 objects have been dumped.
gs_dump[port='26000'][postgres][2020-08-15 19:54:12]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-08-15 19:54:12]: total time: 157  ms

Step 3: view the generated file information.

ll /home/omm/logical/backup/

total 480K
-rw------- 1 omm dbgrp  939 Aug 15 19:54 bkp_shl2.sql
drwx------ 2 omm dbgrp 4.0K Aug 15 19:39 MPPDB_backup
-rw------- 1 omm dbgrp 112K Aug 15 19:38 MPPDB_backup.dmp
-rw------- 1 omm dbgrp 110K Aug 15 19:33 MPPDB_backup.sql
-rw------- 1 omm dbgrp 248K Aug 15 19:36 MPPDB_backup.tar

Step 4: view the generated sql file.

cat /home/omm/logical/backup/bkp_shl2.sql 

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'SQL_ASCII';
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: customer_t1; Type: TABLE; Schema: public; Owner: omm; Tablespace: 
--

CREATE TABLE customer_t1 (
    c_customer_sk integer,
    c_customer_id character(5),
    c_first_name character(6),
    c_last_name character(8)
)
WITH (orientation=row, compression=no);


ALTER TABLE public.customer_t1 OWNER TO omm;

--
-- Data for Name: customer_t1; Type: TABLE DATA; Schema: public; Owner: omm
--

COPY customer_t1 (c_customer_sk, c_customer_id, c_first_name, c_last_name) FROM stdin;
3769	hello	\N	\N
6885	maps 	Joes  	\N
4321	tpcds	Lily  	\N
9527	world	James 	\N
\.
;

--
-- PostgreSQL database dump complete
--

gs_dump backup example 6

Execute gs_dump, export the postgres database information, but not / home / mppdb_ temp. Table information specified in SQL. Exported mppdb_ backup. The SQL file format is plain text.
Step 1 log in to the database master node as the operating system user omm.

su - omm

Step 2 create MPPDB_temp.sql, fill in the table name of the table that will not be exported. For example, customer_t1 table.

vi /home/omm/logical/MPPDB_temp.sql

Enter "i", switch to INSERT mode, and enter "public customer_ T1 ", press" ESC ", enter" wq "and exit after saving.

Step 3 execute gs_dump, export postgres database information, but not mppdb_ temp. Table information specified in SQL.

gs_dump -U omm -W Bigdata@123 -p 26000 postgres --exclude-table-file=/home/omm/logical/MPPDB_temp.sql -f /home/omm/logical/backup/MPPDB_backup2.sql

The results after operation are:

gs_dump[port='26000'][postgres][2020-08-15 20:15:24]: The total objects number is 403.
gs_dump[port='26000'][postgres][2020-08-15 20:15:24]: [100.00%] 403 objects have been dumped.
gs_dump[port='26000'][postgres][2020-08-15 20:15:24]: dump database postgres successfully
gs_dump[port='26000'][postgres][2020-08-15 20:15:24]: total time: 238  ms

Step 4: view the generated file information.

ll /home/omm/logical/backup/

total 592K
-rw------- 1 omm dbgrp  939 Aug 15 19:54 bkp_shl2.sql
drwx------ 2 omm dbgrp 4.0K Aug 15 19:39 MPPDB_backup
-rw------- 1 omm dbgrp 110K Aug 15 20:15 MPPDB_backup2.sql
-rw------- 1 omm dbgrp 112K Aug 15 19:38 MPPDB_backup.dmp
-rw------- 1 omm dbgrp 110K Aug 15 19:33 MPPDB_backup.sql
-rw------- 1 omm dbgrp 248K Aug 15 19:36 MPPDB_backup.tar

Step 5 check the generated sql file and confirm that the customer is not backed up_ T1 table.

cat /home/omm/logical/backup/MPPDB_backup2.sql 

--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET xmloption = content;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: omm
--
COMMENT ON DATABASE postgres IS 'default administrative connection database';
--
-- Name: lucy; Type: SCHEMA; Schema: -; Owner: lucy
--
CREATE SCHEMA lucy;
ALTER SCHEMA lucy OWNER TO lucy;
SET search_path = lucy;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: mytable; Type: TABLE; Schema: lucy; Owner: lucy; Tablespace: 
--
CREATE TABLE mytable (
    firstcol integer
)
WITH (orientation=row, compression=no);
ALTER TABLE lucy.mytable OWNER TO lucy;
SET search_path = public;

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM omm;
GRANT ALL ON SCHEMA public TO omm;
GRANT USAGE ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--

gs_dumpall

gs_dumpall is a tool used by openGauss to export all database related information. It can export all data of openGauss database, including data of default database postgres, data of custom database, and global objects common to all openGauss databases.
gs_dumpall exports all openGauss databases in two parts:
gs_dumpall itself exports global objects common to all databases, including information about database users and groups, tablespaces, and attributes (for example, access rights applicable to the whole database).
gs_dumpall calls gs_dump to export the SQL script file of each database in openGauss. The script file contains all the SQL statements required to restore the database to its saved state.
The results of the above two parts are SQL script files in plain text format. Running the script file with gsql can restore the openGauss database.
Parameter Description:

Table 1-3 gs_dumpall parameter description
parameterParameter description
-f, –filename=FILENSends the output to the specified file. If omitted here, standard output is used.
-a, –data-onlyOnly dump data, not dump mode (data definition).
-c, –cleanExecute SQL statements to clean up (delete) the databases before re creating them. Dump commands for roles and tablespaces have been added.
-g, –globals-onlyDump only global objects (roles and tablespaces), no database.
-s, –schema-onlyDump only object definitions (schemas), not data.
-t, –tablespaces-onlyDump only tablespaces, not databases or roles.
-h, –hostSpecifies the name of the host. This parameter is only for openGauss, and only 127.0.0.1 can be used for the native machine in openGauss.
-l, –databaseSpecify the database name of the connected dump global object and find out which other databases need to be dumped. If not specified, the postgres database will be used. If the postgres database does not exist, template1 will be used.
-p, –portSpecify the TCP port or local Unix domain socket suffix that the server listens on to ensure connectivity. The default value is set to the PGPORT environment variable. When the thread pool is enabled, it is recommended to use pooler port, that is, listening port + 1.
-U, –usernameThe user name of the connection.
-W, –passworSpecifies the password for the user connection. If the authentication policy of the host is trust, the password of the system administrator will not be verified, that is, there is no need to enter the - W option; If there is no - W option and you are not a system administrator, the Dump Restore tool prompts the user for a password

Operation steps:

Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm  

Step 2 use gs_dumpall exports all openGauss databases at once.

gs_dumpall -f  /home/omm/logical/backup/bkp2.sql -p 26000

The results are displayed as:

gs_dump[port='26000'][dbname='postgres'][2020-07-28 20:39:29]: The total objects number is 345.
gs_dump[port='26000'][dbname='postgres'][2020-07-28 20:39:29]: [100.00%] 345 objects have been dumped.
gs_dump[port='26000'][dbname='postgres'][2020-07-28 20:39:29]: dump database dbname='postgres' successfully
gs_dump[port='26000'][dbname='postgres'][2020-07-28 20:39:29]: total time: 172  ms
gs_dumpall[port='26000'][2020-07-28 20:39:29]: dumpall operation successful
gs_dumpall[port='26000'][2020-07-28 20:39:29]: total time: 213  ms

Step 3: view the generated file information.

ll /home/omm/logical/backup/

total 48K
-rw------- 1 omm dbgrp 6.2K Feb  5 15:36 bkp2.sql
-rw------- 1 omm dbgrp  934 Feb  5 15:33 bkp_shl2.sql
drwx------ 2 omm dbgrp 4.0K Feb  5 15:32 MPPDB_backup
-rw------- 1 omm dbgrp 3.0K Feb  5 15:35 MPPDB_backup2.sql
-rw------- 1 omm dbgrp 4.1K Feb  5 15:32 MPPDB_backup.dmp
-rw------- 1 omm dbgrp 3.0K Feb  5 15:30 MPPDB_backup.sql
-rw------- 1 omm dbgrp  14K Feb  5 15:31 MPPDB_backup.tar

gs_restore

gs_restore is provided by openGauss for gs_dump is an import tool for exporting data. With this tool, you can use GS_ Import the export file generated by dump.
gs_ The restore tool is executed by the operating system user omm.
The main functions include:

  • Import to database:
    If a database is specified in the connection parameters, the data will be imported into the specified database. Where, parallel import must specify the password of the connection.
  • Import to script file:
    If the import database is not specified, a script containing the SQL statements necessary to rebuild the database is created and written to a file or standard output. Equivalent to direct use of gs_dump export to plain text format.

Parameter Description:

Table 1-4gs_ Description of restore parameter
parameterParameter description
-d, –dbname=NAMEConnect to the database dbname and import it directly into the database.
-f, –file=FILENAMESpecify the output file of the generated script or the output file of the list when - l is used. The default is standard output.
-F, –format=c/d/tSpecify the archive format. Due to gs_restore automatically determines the format, so there is no need to specify the format. Value range: c/custom: the filing form is GS_ Custom format of dump. d/directory: this archive form is a directory archive form. t/tar: this archive is a tar archive.
-a, -data-onlyImport only data, not schema (data definition). gs_restore is imported by appending.
-c, –cleanClean up (delete) database objects that already exist in the database to be restored before re creating the database objects.
-e, –exit-on-errorIf there is an error when sending SQL statement to the database, please exit. It will continue by default, and a series of error messages will be displayed after import.
-n, –schema=NAMEImport only objects in enumerated schemas. This option can be used with the - t option to import a specified table. Multiple input - n_ schemaname_ Multiple modes can be imported.
-s, –schema-onlyOnly import the mode (data definition), not the data (table content). The current sequence value will not be imported.
-s, –schema-onlyOnly import the mode (data definition), not the data (table content). The current sequence value will not be imported.
-t, –table=NAMEImport only enumerated table definitions, data, or definitions and data. When this option is used together with the - n option, it is used to specify the table object in a certain mode- n when the parameter is not entered, it defaults to PUBLIC mode. Multiple input of - n -t can import multiple tables in the specified mode.
-h, --host=HOSTNAMEThe specified host name. If the value starts with a slash, it will be used as the directory of Unix domain sockets. The default value is taken from PGHOST environment variable; If not set, a Unix domain socket will be started to establish a connection. This parameter is only for openGauss, and only 127.0.0.1 can be used for the native machine in openGauss.
-p, --port=PORTSpecify the TCP port or local Unix domain socket suffix that the server listens on to ensure connectivity. The default value is set to the PGPORT environment variable. When the thread pool is enabled, it is recommended to use pooler port, that is, listening port + 1.
-U, --username=NAMEThe user name of the connection.
-W, --password=PASSWORDSpecifies the password for the user connection. If the authentication policy of the host is trust, the password of the system administrator will not be verified, that is, there is no need to enter the - W parameter; If there is no - W parameter and you are not a system administrator, "gs_restore" will prompt the user for a password.

gs_restore import example 1

Execute gs_restore, the mppdb to be exported_ backup. Import tar file (tar format) into db_tpcc01 database.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm  

Step 2 connect to openGauss database.

gsql -d postgres -p 26000 -r

Step 3 create the database.

postgres=#  DROP DATABASE IF EXISTS db_tpcc01;
postgres=#  CREATE DATABASE db_tpcc01;

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE DATABASE

Step 4 exit the database.

postgres=# \q

Step 5 perform gs_restore, the mppdb to be exported_ backup. Import tar file (tar format) into db_tpcc01 database.

gs_restore /home/omm/logical/backup/MPPDB_backup.tar -p 26000 -d db_tpcc01

If successful, the following is displayed:

start restore operation ...
table customer_t1 complete data imported !
Finish reading 11 SQL statements!
end restore operation ...
restore operation successful
total time: 19  ms

Step 6 connect db_tpcc01 database.

gsql -d db_tpcc01 -p 26000 -r

Step 7: view the recovered customers in the database_ T1 table.

db_tpcc01=# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(4 rows)

Step 8 exit db_tpcc01 database.

db_tpcc01=# \q

gs_restore import example 2

Execute gs_restore, the mppdb to be exported_ backup. Import DMP file (custom archive format) into db_tpcc02 database.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm  

Step 2 connect to openGauss database.

gsql -d postgres -p 26000 -r

Step 3 create the database.

postgres=#  DROP DATABASE IF EXISTS db_tpcc02;
postgres=#  CREATE DATABASE db_tpcc02;

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE DATABASE

Step 4 exit the database.

postgres=# \q

Step 5 perform gs_restore, the mppdb to be exported_ backup. Import DMP file (custom archive format) into db_tpcc02 database.

gs_restore /home/omm/logical/backup/MPPDB_backup.dmp -p 26000 -d db_tpcc02

If successful, the following is displayed:

start restore operation ...
table customer_t1 complete data imported !
Finish reading 11 SQL statements!
end restore operation ...
restore operation successful
total time: 19  ms

Step 6 connect DB_ Cctp02 database.

gsql -d db_tpcc02 -p 26000 -r

Step 7: view the recovered customers in the database_ T1 table.

db_tpcc02=# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(4 rows)

Step 8 exit db_tpcc02 database.

db_tpcc02=# \q

gs_restore import example 3

Execute gs_restore, the mppdb to be exported_ The backup file (directory format) is imported into the postgres database.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm  

Step 2 connect to openGauss database.

gsql -d postgres -p 26000 -r

Step 3 create the database.

postgres=#  DROP DATABASE IF EXISTS db_tpcc03;
postgres=#  CREATE DATABASE db_tpcc03;

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE DATABASE

Step 4 exit the database.

postgres=# \q

Step 5 perform gs_restore, the mppdb to be exported_ Import backup file (directory format) into db_tpcc03 database.

gs_restore /home/omm/logical/backup/MPPDB_backup -p 26000 -d db_tpcc03

If successful, the following is displayed:

start restore operation ...
table customer_t1 complete data imported !
Finish reading 11 SQL statements!
end restore operation ...
restore operation successful
total time: 19  ms

Step 6 connect db_tpcc03 database.

gsql -d db_tpcc03 -p 26000 -r

Step 7: view the recovered customers in the database_ T1 table.

db_tpcc02=# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(4 rows)

Step 8 exit db_tpcc03 database.

db_tpcc03=# \q

gs_restore import example 4

Execute gs_restore, mppdb using custom archive format_ backup. DMP file to perform the following import operations. During import, clean up (delete) the database objects that already exist in the database to be restored before re creating the database objects.
Step 1 since all objects have been restored to dB in backup example 1_ Tpcc01 is in the database, so if you do not delete the existing objects before importing again, an error message will appear.

gs_restore -W Bigdata@123 /home/omm/logical/backup/MPPDB_backup.dmp -p 26000 -d db_tpcc01

The display is as follows:

start restore operation ...
......
Error from TOC entry 601; 1259 41883 TABLE customer_t1 omm
could not execute query: ERROR:  relation "customer_t1" already exists
    Command was: CREATE TABLE customer_t1 (
    c_customer_sk integer,
    c_customer_id character(5),
    c_first_name character(6),
    c_l...
table customer_t1 complete data imported !
Finish reading 11 SQL statements!
end restore operation ...
WARNING: errors ignored on restore: 3
restore operation successful
total time: 19  ms

Step 2 connect db_tpcc01 database

gsql -d db_tpcc01 -p 26000 -r

Step 3: view the recovered customers in the database_ T1 table.

db_tpcc01=# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 

(8 rows)

The original data table has not been deleted. The imported data table imports data in the way of data addition.
Step 4 exit the database

db_tpcc01=# \q

Step 5 enter the following command, manually delete the dependency using the - c parameter, and then re create it after the import is completed.

gs_restore -W Bigdata@123 /home/omm/logical/backup/MPPDB_backup.dmp -p 26000 -d db_tpcc01 -e -c

The display is as follows:

start restore operation ...
Finish reading 11 SQL statements!
end restore operation ...
restore operation successful
total time: 14  ms

Note: if the original object has cross pattern dependencies, manual forced intervention is required.
Step 6 connect db_tpcc01 database

gsql -d db_tpcc01 -p 26000 -r

Step 7: view the recovered customers in the database_ T1 table.

db_tpcc01=# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(8 rows)

Step 8 exit the database

db_tpcc01=# \q

The original data table has been cleared and the imported data table is restored.

gs_restore import example 5

Execute gs_restore, mppdb using custom archive format_ backup. DMP file to perform the following import operations. Import only the table customer in PUBLIC mode_ Definition of T1.
Step 1 switch to the omm user and log in to the database master node with the operating system user omm.

su - omm 

Step 2 connect to openGauss database.

gsql -d postgres -p 26000 -r

Step 3 create the database.

postgres=#  DROP DATABASE IF EXISTS db_tpcc04;
postgres=#  CREATE DATABASE db_tpcc04;

When the result is displayed as the following information, it indicates that the creation is successful.

CREATE DATABASE

Step 4 exit the database.

postgres=# \q

Step 5 perform gs_restore: only import the table customer in PUBLIC mode_ Definition of T1.

gs_restore  /home/omm/logical/backup/MPPDB_backup.dmp -p 26000 -d db_tpcc04 -n public -t customer_t1

If successful, the following is displayed:

start restore operation ...
table customer_t1 complete data imported !
Finish reading 17 SQL statements!
end restore operation ...
restore operation successful
total time: 18  ms

Step 6 connect db_tpcc04 database.

gsql -d db_tpcc04 -p 26000 -r

Step 7: view the recovered customers in the database_ T1 table.

db_tpcc04# select * from customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | maps          | Joes         | 
          4321 | tpcds         | Lily         | 
          9527 | world         | James        | 
          3769 | hello         |              | 
(4 rows)

Step 8: view the table in LUCY mode in the postgres database.

db_tpcc04=# select * from lucy.mytable;

The display results are as follows:

ERROR:  schema "lucy" does not exist
LINE 1: select * from lucy.mytable;

Step 9: check the customer in PUBLIC mode in the postgres database_ T2 table.

db_tpcc04=# select * from customer_t2;

The display results are as follows:

ERROR:  schema "lucy" does not exist
LINE 1: select * from lucy.mytable;

Step 9: check the customer in PUBLIC mode in the postgres database_ T2 table.

db_tpcc04=# select * from customer_t2;

The display results are as follows:

ERROR:  relation "customer_t2" does not exist on dn_6001
LINE 1: select * from customer_t2;

Description: only customers in PUBLIC shema mode are restored_ T1 table.
Step 10 exit the database.

postgres=# \q

The experiment is over.

Keywords: Database opengauss

Added by mband2000 on Mon, 14 Feb 2022 11:16:09 +0200