When data is lost or corrupted, you can use the console's instance cloning feature to restore data to a specific time point or from a backup set. The restorable time is determined by the log retention period and the full-backup time. You can also directly download the backup and restore it to a self-built database. A detailed description is provided below.
Note:
If transparent data encryption (TDE) is enabled for the TencentDB for PostgreSQL instance, recovery to a self-built database is not supported.
Downloading a Backup in the Console for Restoration
1.Prerequisite
The PostgreSQL database with the same version as the backup data must be installed in the Cloud Virtual Machine (CVM) where data is to be restored. For detailed installation instructions, refer to the official documentation for PostgreSQL. For Linux CVM configuration, see Quickly Configuring Linux CVM.
2.Creating a Recovery Directory with the Postgres User
To recover data, you first need to create a recovery directory on the CVM and set permissions.
mkdir -p /var/lib/pgsql/16/recovery
chown postgres /var/lib/pgsql/16/recovery
Here, 16 is an example of a major database version number, and recovery is an example directory. You can customize the recovery directory name based on the actual situation. Subsequent examples will not distinguish between directory names of different versions. The actual directory name should prevail. For example, it is /var/lib/pgsql/12 for PostgreSQL 12.x.
3.Downloading Full Backup Files
3.1. Log in to the PostgreSQL console. In the instance list, click Manage in the Operation column to go to the management page.
3.2. Select the Backup and Restoration page. In the Data Backup List, choose the backup set to be restored and click Download in the Operation column.
3.3. Download the backup file from the provided VPC address or public network address.
If downloading the backup using the VPC network address, the cloud database must be in the same VPC as the CVM. Execute the following command to download the backup to the pre-created recovery directory on the CVM. For example, use the /var/lib/pgsql/16/recovery directory. Among them, file_name is a custom file name provided by the user, and download_address is the download address provided by the system.
If you choose to download the backup file locally, after downloading, you need to upload the backup file to the pre-created recovery directory on the CVM. For example, use the /var/lib/pgsql/16/recovery directory. For specific operation methods, see How to Copy Local Files to CVMs.
After the backup is downloaded or uploaded, query whether the backup file is successfully placed in the specified directory through the following command:
[root@VM-10-5-tencentos postgresql-16.8]# ls -lh /var/lib/pgsql/16/recovery
total 3952-rw-r--r-- 1 root root 4045802 May 7 20:42 manual-20250507204222.tar.zst
4.Decompressing Full Backup Files
Decompressing backup files requires the zstd decompression tool. You can query whether this tool exists in the current CVM through the following command.
*** zstd command line interface 64-bits v1.4.4, by Yann Collet ***
If the version number information is displayed, it means the zstd decompression tool has been installed. If other information is prompted, the zstd decompression tool is not installed in the current CVM. Execute the following command to install.
Execute the following command, and use the vi editor to modify the configuration file postgresql.conf.
[root@VM-10-5-tencentos recovery]# vi postgresql.conf
After command execution, enter the content page of the file, and press the i key to proceed with editing.
6.1. Find the following content in the file, add # at the beginning of each row to make it a comment. If any appears repeatedly, comment out all of them.
pg_stat_statements.track
synchronous_standby_names
extension_blacklist
archive_mode
basebackup_exclude_paths
tencentdb_syscache_max_num
shared_preload_libraries
tencentdb_relcache_max_num
archive_command
disable_dblink_connect_to_other
tencentdb_az_five
tencentdb_relcache_evict_num
pg_stat_statements.max
soft_limit_connections
tencentdb_syscache_evict_num
tencentdb_enable_trusted_extension
synchronous_commit
tencentdb_enable_superuser_unsafe_behaviour
tencentdb_enable_copy_to
local_preload_libraries
Note:
Note that you must comment out the line include = 'standby.conf'.
6.2. Change log_destination = 'csvlog,auditlog' to log_destination = 'csvlog'.
log_destination = ‘csvlog’
6.3. Find port in the file and change its value to 5433 to avoid conflict with the primary instance port.
port = '5433' ## Change the value of the port parameter to 5433
6.4. Add the following content at the end of the file, indicating that the strong sync mode is no longer in use.
synchronous_commit = local
synchronous_standby_names = ''
After modification, press the esc key to exit edit mode, then enter :wq to save changes and exit the file.
7.Using the Root User to Change Folder Permissions
After modification, you can enter the following commands to view the changes. Among them, the third column returned is the file permission owner, which should display as postgres after changing.
[root@VM-10-5-tencentos recovery]# ls -al
total 4064
drwx------ 19 postgres postgres 4096 May 8 09:43 .
drwxr-xr-x 3 root root 4096 May 7 20:44 ..
-rw------- 1 postgres postgres 225 May 7 20:42 backup_label
-rw------- 1 postgres postgres 225 May 7 20:31 backup_label.old
drwx------ 6 postgres postgres 4096 May 7 21:13 base
-rw------- 1 postgres postgres 56 May 7 20:31 current_audit_logfiles
-rw------- 1 postgres postgres 35 May 7 20:31 current_logfiles
drwx------ 2 postgres postgres 4096 May 7 21:13 global
-rw-r--r-- 1 postgres postgres 4045802 May 7 20:42 manual-20250507204222.tar.zst
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_commit_ts
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_dynshmem
-rw------- 1 postgres postgres 308 May 7 20:31 pg_hba.conf
-rw------- 1 postgres postgres 2640 May 7 20:31 pg_ident.conf
drwx------ 4 postgres postgres 4096 May 7 20:31 pg_logical
drwx------ 4 postgres postgres 4096 May 7 20:31 pg_multixact
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_notify
drwx------ 2 postgres postgres 4096 May 7 20:42 pg_replslot
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_serial
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_snapshots
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_stat
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_stat_tmp
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_subtrans
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_tblspc
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_twophase
-rw------- 1 postgres postgres 3 May 7 20:31 PG_VERSION
drwx------ 3 postgres postgres 4096 May 7 21:13 pg_wal
drwx------ 2 postgres postgres 4096 May 7 20:31 pg_xact
-rw------- 1 postgres postgres 3276 May 7 21:35 postgresql.conf
-rw------- 1 postgres postgres 0 May 7 20:31 standby.signal
-rw------- 1 postgres postgres 0 May 7 20:42 tablespace_map
-rw------- 1 postgres postgres 2 May 7 20:31 TENCENTDB_RELEASE
8.Use the incremental backup file (optional).
After the previous steps, the content of the restored database is the content of the full backup database. The backup methods of TencentDB for PostgreSQL include full backup and incremental backup. For details, see Backup Principles and Solutions.
The format of full backups differs from that of incremental backup files, and their recovery methods also vary. If you wish to restore data from incremental backup files to a CVM, you need to complete this step.
If you skip this step, the content of the restored database will be the content of the database when the full backup started.
For example, if a full backup was performed at 12:00 and the database is restored using this full backup file, and then all xlog files from 12:00 to 13:00 are placed in the pg_wal folder, the database will be restored to the data content at 13:00.
Note:
When the PostgreSQL version is 9.x, the folder path for placing xlog files is /var/lib/pgsql/9.x/recovery/pg_xlog.
8.1. Select the Backup and Restoration page. In the Log Backup List, select the log backup set to be restored and click Download in the Operation column.
Similar to a full backup file, you can directly download the file to the pg_wal directory on the CVM or download it to local and then upload it to the pg_wal directory under the recovery directory on the CVM.
If you download the backup via the VPC network address, the cloud database (CDB) must be in the same VPC as the CVM. Run the following command to download the backup to the pg_wal directory under the recovery directory.
After downloading, execute the following commands to confirm whether the file is successfully placed in the specified location.
[root@VM-10-5-tencentos recovery]# cd /var/lib/pgsql/16/recovery/pg_wal
[root@VM-10-5-tencentos pg_wal]# ls -lh
total 33M
-rw------- 1 postgres postgres 16M May 7 20:42 000000010000000000000003
-rw------- 1 postgres postgres 16M May 7 20:42 000000010000000000000004
-rw-r--r-- 1 root root 1.5K May 8 10:31 20250508103101_20250508103101-20250508101527-000000010000000000000005_000000010000000000000005.tar.zst
drwx------ 2 postgres postgres 4.0K May 7 21:13 archive_status
If you choose to download the file locally, after downloading, you need to upload the backup file to the pg_wal directory under the recovery directory. For specific operation methods, see How to Copy Local Files to a CVM.
8.2. Decompress the incremental backup files into the pg_wal folder to get xlog files.
[root@VM-10-5-tencentos pg_wal]# tar -I zstd -xvf file_name.tar.zst
000000010000000000000005
Execute the following command to view the decompression result.
[root@VM-10-5-tencentos pg_wal]# ls -lh
total 49M
-rw------- 1 postgres postgres 16M May 7 20:42 000000010000000000000003
-rw------- 1 postgres postgres 16M May 7 20:42 000000010000000000000004
-rw------- 1 1003 users 16M May 8 10:31 000000010000000000000005
-rw-r--r-- 1 root root 1.5K May 8 10:31 20250508103101_20250508103101-20250508101527-000000010000000000000005_000000010000000000000005.tar.zst
drwx------ 2 postgres postgres 4.0K May 7 21:13 archive_status
You can also manually export backup data and then restore it on the CVM. This scheme is applicable to both Windows and Linux regardless of the file system where physical files reside.
Note:
It is recommended not to use the postgres database as the target database.
Note: It is recommended to use a newly created database on the target side to import data.
If you use a database with existing data on the target side to import, it may fail due to object conflict.
1. Dump data from the CVM
The command format is: pg_dump -h <host> -p <port> -U <username> -Fc <dbname> <dumpdir>. For more usage instructions, refer to the official documentation for pg_dump.
Parameter
Description
host
The connection address of the TencentDB for PostgreSQL instance.
port
The port of the TencentDB for PostgreSQL instance.
username
The account name of the TencentDB for PostgreSQL instance.
-Fc
Output format, which is suitable for pg_restore for restoration.
When the command prompts Password:, enter the access account password, and the file will be successfully exported. You can run ll testdb_bkp.dump to ensure the file is generated.
2. Create the corresponding user in the self-built database
Connect to the self-built database on the target side, create the corresponding database account, and ensure the target account exists. An example is as follows:
The command format is: pg_restore -h <host> -p <port> -U <username> -d <dbname> <dumpdir> -c. For more usage guidelines, refer to the official documentation for pg_restore.
Parameter
Description
host
The connection address of the self-built PostgreSQL instance.
port
The port of the self-built PostgreSQL instance.
username
The account name of the self-built PostgreSQL instance.
dbname
The name of the database to be imported.
dumpdir
The path and name of the backup file to be imported.
-c
-c is an optional parameter, indicating that the relevant data in the target database will be cleared before data restoration.
