When data is lost or corrupted, you can use the instance clone feature to restore data to a specific time point within the log retention period.
Downloading Backup in Console for Restoration
1. Install PostgreSQL
In the CVM instance where data is to be restored to, install PostgreSQL on the same version as that of the backup data. If PostgreSQL has already been installed, skip this step.
Note:
This document shows you how to install PostgreSQL 10 and restore data in a CentOS 7 CVM instance.
2. Install PostgreSQL. The yum repository method is used in this document. You can click here to find the needed yum repository.
Note:
To restore backup data of PostgreSQL v11.8 or 12.4, you need to modify the version number in the installation package name to install PostgreSQL on the same version as that of the backup data. For example, replace postgresql10-server with postgresql11-server or postgresql12-server.
Run the following command to install PostgreSQL 10:
3. Run the following command to check the installation result:
rpm -aq|grep postgres
A message similar to the following will be returned:
[root@i-87-575-VM vmuser]# rpm -aq| grep postgres
postgresql10-libs-10.11-2PGDG.rhel7.x86_64
postgresql10-server-10.11-2PGDG.rhel7.x86_64
postgresql10-contrib-10.11-2PGDG.rhel7.x86_64
postgresql10-10.11-2PGDG.rhel7.x86_64
2. Create a restoration directory as the postgres user
Switch to the postgres user and create a restoration directory in the CVM instance.
mkdir /var/lib/pgsql/10/recovery
recovery is a sample directory name, which can be modified as needed. In the following examples, the directory names will be the same for one major version. For example, the directory will be /var/lib/pgsql/10 for PostgreSQL 10.x and /var/lib/pgsql/9.5 for PostgreSQL 9.5.x.
Note:
The command for PostgreSQL 9.5 is as follows:
mkdir /var/lib/pgsql/9.5/recovery
3. Download the full backup file
1. Log in to the TencentDB for PostgreSQL console. In the instance list, click Manage in the Operation column to enter the management page.
2. On the Backup Management tab, locate the backup to be restored based on backup time in the backup list and click Download in the Operation column.
3. Download the backup file from the provided VPC address or public network address.
Note:
If a VPC address is to be used, the TencentDB instance and CVM instance should be in the same VPC, and the backup needs to be downloaded to the /var/lib/pgsql/10/recovery directory.
If a public network address is to be used, the downloaded backup file needs to be uploaded to the /var/lib/pgsql/10/recovery directory in the CVM instance. For more information, see Copying Local Files to CVMs.
After upload, the following information will be displayed:
4. Decompress the full backup file
Run the following command to decompress the full backup file:
cd /var/lib/pgsql/10/recovery
tar -xf 20191221010146.tar.gz
After decompression, the following information will be displayed:
5. Remove unnecessary temporary files
Run the following command to remove unnecessary temporary files:
rm -rf backup_label
6. Modify the configuration file
1. Use # at the beginning of a line to comment out the following options in the postgresql.conf configuration file.
Comment all out if there is more than one such option.
shared_preload_libraries
local_preload_libraries
pg_stat_statements.max
pg_stat_statements.track
archive_mode
archive_command
synchronous_commit
synchronous_standby_names
Note:
To restore backup data of PostgreSQL v12.4, include = 'standby.conf' also needs to be commented out.
2. Modify the postgresql.conf configuration file.
port ='5432'## Change the value of the `port` parameter to 5432
unix_socket_directories ='/var/run/postgresql/'## Change the value of `unix_socket_directories` to `/var/run/postgresql/`. This step can be skipped if the value is not set
2. Append configurations to the postgresql.conf configuration file, indicating that the strong sync mode will no longer be used.
After modification, the following information will be displayed:
8. Use the incremental backup file (optional)
If this step is skipped, the content of the database will be that when the full backup was started.
Put the xlog files in the /var/lib/pgsql/10/recovery/pg_wal folder. If the downloaded backup does not contain the pg_wal directory, modify pg_xlog to pg_wal, and PostgreSQL will automatically replay the xlog files.
For example, if a full backup is started at 12:00 and all xlog files between 12:00 and 13:00 are put in the pg_wal folder, then data can be restored to 13:00.
Note:
For PostgreSQL 9.x, the folder is /var/lib/pgsql/9.x/recovery/pg_xlog.
1. On the Backup Management page in the console, get the xlog download address and download the incremental backup file (xlog).
After download, the following information will be displayed:
/usr/pgsql-10/bin/pg_ctl status -D /var/lib/pgsql/10/recovery
If the prompt is "server is running", the database is running.
Manually Exporting Data for Restoration
You can also manually export backup data and then restore it in the CVM instance. This scheme is applicable to both Windows and Linux regardless of the file system where physical files reside.
1. Dump the data from the CVM instance as shown below:
Command format: pg_dump -h <access IP> -U <accessing user> -f <full path of the backup file> -c -C <name of the exported database>
Yes
No
Was this page helpful?