Physical Backup and Restoration

gs_basebackup

Background

After MogDB is deployed, problems and exceptions may occur during database running. gs_basebackup, provided by MogDB, is used to perform basic physical backup. gs_basebackup copies the binary files of the database on the server using a replication protocol. To remotely execute gs_basebackup, you need to use the system administrator account. gs_basebackup supports hot backup and compressed backup.

NOTE:

• gs_basebackup supports only full backup.
• gs_basebackup supports hot backup and compressed backup.
• gs_basebackup cannot back up tablespaces containing absolute paths on the same server. This is because the absolute path is unique on the same machine, and brings about conflicts. However, it can back up tablespaces containing absolute paths on different machines.
• If the functions of incremental checkpoint and dual-write are enabled, gs_basebackup also backs up dual-write files.
• If the pg_xlog directory is a soft link, no soft link is created during backup. Data is directly backed up to the pg_xlog directory in the destination path.
• If the backup permission is revoked during the backup, the backup may fail or the backup data may be unavailable.
• MogDB does not support version upgrade.

Prerequisites

• The MogDB database can be connected.
• User permissions are not revoked during the backup.
• In the pg_hba.conf file, the replication connection is allowed and the connection is established by a system administrator.
• If the Xlog transmission mode is stream, the number of max_wal_senders must be configured to at least one.
• If the Xlog transmission mode is fetch, the wal_keep_segments parameter must be set to a large value so that logs are not removed before the backup ends.
• During the restoration, backup files exist in the backup directory on all the nodes. If backup files are lost on any node, them to it from another node.

Syntax

• Display help information.

  gs_basebackup -? | --help

• Display version information.

  gs_basebackup -V | --version

Parameter Description

The gs_basebackup tool can use the following types of parameters:

• -D directory

Directory for storing backup files. This parameter is mandatory.

• Common parameters

  • -c, -checkpoint=fast|spread

    Sets the checkpoint mode to fast or spread (default).

  • -l, -label=LABEL

    Adds tags for the backup.

  • -P, -progress

    Enables the progress report.

  • -v, -verbose

    Enables the verbose mode.

  • -V, -version

    Prints the version and exits.

  • -?, -help

    Displays gs_basebackup command parameters.

  • -T, -tablespace-mapping=olddir=newdir

    During the backup, the tablespace in the olddir directory is relocated to the newdir directory. For this to take effect, olddir must exactly match the path where the tablespace is located (but it is not an error if the backup does not contain the tablespaces in olddir). olddir and newdir must be absolute paths. If a path happens to contain an equal sign (=), you can escape it with a backslash (). This option can be used multiple times for multiple tablespaces.

  • -F, -format=plain|tar

    Sets the output format to plain (default) or tar. If this parameter is not set, the default value -format=plain is used. The plain format writes the output as a flat file, using the same layout as the current data directory and tablespace. When the cluster has no extra tablespace, the entire database is placed in the target directory. If the cluster contains additional tablespaces, the primary data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path on the server. The tar mode writes the output as a tar file in the target directory. The primary data directory is written to a file named base.tar, and other tablespaces are named after their OIDs. The generated .tar package must be decompressed using the gs_tar command.

  • -X, -xlog-method=fetch|stream

    Sets the Xlog transmission mode. If this parameter is not set, the default value -xlog-method=stream is used. The required write-ahead log files (WALs) are included in the backup. This includes all WALs generated during the backup. In fetch mode, WAL files are collected at the end of the backup. Therefore, the wal_keep_segments parameter must be set to a large value so that logs are not removed before the backup ends. If it has been rotated when the log is to be transmitted, the backup fails and is unavailable. In stream mode, WALs are streamed when a backup is created. This will open a second connection to the server and start streaming WALs while the backup is running. Therefore, it will use up to two connections configured by the max_wal_senders parameter. As long as the client can receive WALs, no additional WALs need to be stored on the host.

  • -x, -xlog

    Equivalent to using -X with the fetch method.

  • -Z -compress=level

    Enables gzip compression for the output of the tar file and sets the compression level (0 to 9, where 0 indicates no compression and 9 indicates the best compression). The compression is available only when the tar format is used. The suffix .gz is automatically added to the end of all .tar file names.

  • -z

    Enables gzip compression for tar file output and uses the default compression level. The compression is available only when the tar format is used. The suffix .gz is automatically added to the end of all .tar file names.

• Connection parameters

  • -h, -host=HOSTNAME

    Specifies the host name of the machine on which the server is running or the directory for the Unix-domain socket.

  • -p, -port=PORT

    Specifies the port number of the database server.

    You can modify the default port number using this parameter.

  • -U, -username=USERNAME

    Specifies the user that connects to the database.

  • -s, -status-interval=INTERVAL

    Specifies the time for sending status packets to the server, in seconds.

  • -w,-no-password

    Never issues a password prompt.

  • -W, -password

    Issues a password prompt when the -U parameter is used to connect to a local or remote database.

Example

gs_basebackup -D /home/test/trunk/install/data/backup -h 127.0.0.1 -p 21233
INFO:  The starting position of the xlog of the full build is: 0/1B800000. The slot minimum LSN is: 0/1B800000.

Restoring Data from Backup Files

If a database is faulty, restore it from backup files. gs_basebackup backs up the database in binary mode. Therefore, you can directly and replace the original files or start the database on the backup database.

NOTE:

• If the current database instance is running, a port conflict may occur when you start the database from the backup file. In this case, you need to modify the port parameter in the configuration file or specify a port when starting the database.
• If the current backup file is a primary/standby database, you may need to modify the replication connections between the master and slave databases. That is, replconninfo1 and replconninfo2 in the postgresql.conf file.

To restore the original database, perform the following steps:

1. Stop the database server. For details, see Administrator Guide.
2. the original database and all tablespaces to another location for future use.
3. Delete all or part of the files from the original database.
4. Use the database system user rights to restore the required database files from the backup.
5. If a link file exists in the database, modify the link file so that it can be linked to the correct file.
6. Restart the database server and check the database content to ensure that the database is restored to the required status.

NOTE:

• Incremental restoration from backup files is not supported.
• After the restoration, check that the link file in the database is linked to the correct file.

PITR Recovery

Background

When a database breaks down or needs to be rolled back to a previous state, the point-in-time recovery (PITR) function of MogDB can be used to restore the database to any point in time after the backup and archive data is generated.

NOTE:

• PITR can only be restored to a point in time after the physical backup data is generated.
• Only the primary node can be restored using PITR. The standby node needs to be fully built to synchronize data with the primary node.

Prerequisites

• Full data files have been physically backed up.
• WAL log files have been archived.

PITR Recovery Process

1. Replace the target database directory with the physical backup files.
2. Delete all files in the database directory pg_xlog/.
3. the archived WAL log file to the pg_xlog file. (Or you can configure restore_command in the recovery.conf file to skip this step.)
4. Create the recovery command file recovery.conf in the database directory and specify the database recovery degree.
5. Start the database.
6. Connect to the database and check whether the database is recovered to the expected status.
7. If the expected status is reached, run the pg_xlog_replay_resume() command so that the primary node can provide services externally.

Configuring the recovery.conf File

Archive Recovery Configuration

• restore_command = string

The shell command is used to obtain the archived WAL files among the WAL file series. Any %f in the string is replaced by the name of the file to retrieve from the archive, and any %p is replaced by the path name to it to on the server. Any %r is replaced by the name of the file containing the last valid restart point.

For example:

restore_command = 'cp /mnt/server/archivedir/%f %p'

• archive_cleanup_command = string

This option parameter declares a shell command that is executed each time the system is restarted. archive_cleanup_command provides a mechanism for deleting unnecessary archived WAL files from the standby database. Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow recovery to be restartable, so all files older than %r can be safely removed.

For example:

archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r'

If multiple standby servers need to be recovered from the same archive path, ensure that WAL files are not deleted from any standby server before the recovery.

• recovery_end_command = string

This parameter is optional and is used to declare a shell command that is executed only when the recovery is complete. recovery_end_command provides a cleanup mechanism for future replication and recovery.

Recovery Object Configuration

• recovery_target_name = string

This parameter declares that the name is recovered to a recovery point created using pg_create_restore_point().

For example:

recovery_target_name = 'restore_point_1'

• recovery_target_time = timestamp

This parameter declares that the name is recovered to a specified timestamp.

For example:

recovery_target_time = '2020-01-01 12:00:00'

• recovery_target_xid = string

This parameter declares that the name is recovered to a transaction ID.

For example:

recovery_target_xid = '3000'

• recovery_target_lsn = string

This parameter declares that the name is recovered to the LSN specified by log.

For example:

recovery_target_lsn = '0/0FFFFFF'

• recovery_target_inclusive = boolean

This parameter declares whether to stop the recovery after the recovery target is specified (true) or before the recovery target is specified (false). This declaration supports only the recovery targets recovery_target_time, recovery_target_xid, and recovery_target_lsn.

For example:

recovery_target_inclusive = true

NOTE:

• Only one of the four configuration items recovery_target_name, recovery_target_time, recovery_target_xid, and recovery_target_lsn can be used at a time.
• If no recovery targets are configured or the configured target does not exist, data is recovered to the latest WAL log point by default.