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.
Attention:
If Transparent Data Encryption (TDE) is enabled on a TencentDB for PostgreSQL instance, restoring data to a self-built database is not supported.
Downloading a Backup in the Console for Restoration
1. Prerequisites
The CVM where data is to be restored must have a PostgreSQL database installed that is the same version as the backup data. For detailed installation instructions, see the PostgreSQL official documentation. For CVM configuration on Linux, see Quick Configuration of Linux CVM.
2. Creating a Recovery Catalog with the postgres User
To restore data, first create a recovery catalog for the data in the CVM and set the permissions.
mkdir -p /var/lib/pgsql/16/recovery
chown postgres /var/lib/pgsql/16/recovery
Here, 16 is the example database major version number, and recovery is the example catalog. You can customize the recovery catalog name based on your actual situation. In subsequent examples, directory names for different versions will no longer be distinguished in the description. Please refer to the actual configuration, for example, PostgreSQL 12.x is /var/lib/pgsql/12.
3. Downloading a Full Backup File
3.1. Log in to the TencentDB for 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, select the backup set you want to restore, and click Download in the Operation column.
3.3. Download the backup file from the provided VPC address or public network address.
If you use a VPC address to download the backup, the cloud database must be in the same VPC as the CVM. Run the following command to download the backup to the recovery catalog created in advance on the CVM. The example uses the /var/lib/pgsql/16/recovery catalog. Here, file_name is the user-defined file name, and download_address is the download address provided by the system.
If you choose to download the backup file locally, you need to upload it to the recovery catalog created in advance on the CVM after downloading. The example uses the /var/lib/pgsql/16/recovery catalog. For specific operations, see How to Copy Local Files to a CVM.
After the backup is downloaded or uploaded, you can use the following command to check whether the backup file has been successfully placed in the specified catalog:
[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 720:42 manual-20250507204222.tar.zst
4. Extracting a Full Backup File
To decompress the backup file, you need the zstd decompression tool. You can use the following command to check whether the tool exists on the current CVM.
*** zstd command line interface 64-bits v1.4.4, by Yann Collet ***
If the version number is displayed, the zstd decompression tool is already installed. If other information is prompted, the tool is not installed on the current CVM. Run the following command to install it.
Run the following command to modify the postgresql.conf configuration file using the vi editor.
[root@VM-10-5-tencentos recovery]# vi postgresql.conf
After the command is executed, you will enter the file content page. Press the i key to start editing.
6.1. In the file, locate the following content and add a # at the beginning of each row to comment it out. If duplicates exist, 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_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:
Specifically, you must comment out the row include = 'standby.conf'.
6.2. Change log_destination = 'csvlog,auditlog' to log_destination = 'csvlog'.
log_destination = ‘csvlog’
6.3. In the file, locate port and change its value to 5433 to avoid conflicts with the primary instance port.
port ='5433'## Change the value of the port parameter to 5433.
6.4. Append the following content to the end of the file to indicate that the strong synchronous schema is no longer used.
synchronous_commit =local
synchronous_standby_names =''
After making the modifications, press the esc key to exit edit mode, then input :wq to save the changes and exit the file.
After making the modifications, you can run the following command to view the results. In the output, the third column is displayed as the file permission owner, which should show postgres after the change.
[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 720:44 ..
-rw------- 1 postgres postgres 225 May 720:42 backup_label
-rw------- 1 postgres postgres 225 May 720:31 backup_label.old
drwx------ 6 postgres postgres 4096 May 721:13 base
-rw------- 1 postgres postgres 56 May 720:31 current_audit_logfiles
-rw------- 1 postgres postgres 35 May 720:31 current_logfiles
drwx------ 2 postgres postgres 4096 May 721:13 global
-rw-r--r-- 1 postgres postgres 4045802 May 720:42 manual-20250507204222.tar.zst
drwx------ 2 postgres postgres 4096 May 720:31 pg_commit_ts
drwx------ 2 postgres postgres 4096 May 720:31 pg_dynshmem
-rw------- 1 postgres postgres 308 May 720:31 pg_hba.conf
-rw------- 1 postgres postgres 2640 May 720:31 pg_ident.conf
drwx------ 4 postgres postgres 4096 May 720:31 pg_logical
drwx------ 4 postgres postgres 4096 May 720:31 pg_multixact
drwx------ 2 postgres postgres 4096 May 720:31 pg_notify
drwx------ 2 postgres postgres 4096 May 720:42 pg_replslot
drwx------ 2 postgres postgres 4096 May 720:31 pg_serial
drwx------ 2 postgres postgres 4096 May 720:31 pg_snapshots
drwx------ 2 postgres postgres 4096 May 720:31 pg_stat
drwx------ 2 postgres postgres 4096 May 720:31 pg_stat_tmp
drwx------ 2 postgres postgres 4096 May 720:31 pg_subtrans
drwx------ 2 postgres postgres 4096 May 720:31 pg_tblspc
drwx------ 2 postgres postgres 4096 May 720:31 pg_twophase
-rw------- 1 postgres postgres 3 May 720:31 PG_VERSION
drwx------ 3 postgres postgres 4096 May 721:13 pg_wal
drwx------ 2 postgres postgres 4096 May 720:31 pg_xact
-rw------- 1 postgres postgres 3276 May 721:35 postgresql.conf
-rw------- 1 postgres postgres 0 May 720:31 standby.signal
-rw------- 1 postgres postgres 0 May 720:42 tablespace_map
-rw------- 1 postgres postgres 2 May 720:31 TENCENTDB_RELEASE
8.Use the incremental backup file (optional)
After the preceding steps, the restored database content is the full backup database content. TencentDB for PostgreSQL supports both full and incremental backups. For details, see Backup Principles and Solutions.
Full backups and incremental backups differ in file format and restoration methods. If you also want to restore data from incremental backup files to a CVM, you need to complete this step.
If you skip this step, the restored database content will be the database content at the time the full backup was initiated.
For example, after a full backup taken at 12:00 is restored, if you place all WAL files from 12:00 to 13:00 into the pg_wal folder, the database will be restored to its state at 13:00.
Note:
For PostgreSQL version 9.x, the folder path for storing WAL 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 you want to restore, and click Download in the Operation column.
Similar to full backup files, you can download the files directly to the pg-wal catalog on the CVM, or download them locally and then upload them to the pg_wal catalog under the recovery catalog on the CVM.
If you download the backup using a VPC address, the cloud database must be in the same VPC as the CVM. Run the following command to download the backup to the pg_wal catalog under the recovery catalog.
After downloading, run the following command to confirm whether the file has been 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 720:42 000000010000000000000003
-rw------- 1 postgres postgres 16M May 720:42 000000010000000000000004
-rw-r--r-- 1 root root 1.5K May 810:31 20250508103101_20250508103101-20250508101527-000000010000000000000005_000000010000000000000005.tar.zst
drwx------ 2 postgres postgres 4.0K May 721:13 archive_status
If you choose to download the file locally, you need to upload the backup file to the pg_wal catalog under the recovery catalog after downloading. For specific operations, see How to Copy Local Files to a CVM.
8.2. Extract the incremental backup file to the pg_wal folder to obtain the WAL file.
[root@VM-10-5-tencentos pg_wal]# tar -I zstd -xvf file_name.tar.zst
000000010000000000000005
Run 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 720:42 000000010000000000000003
-rw------- 1 postgres postgres 16M May 720:42 000000010000000000000004
-rw------- 11003users 16M May 810:31 000000010000000000000005
-rw-r--r-- 1 root root 1.5K May 810:31 20250508103101_20250508103101-20250508101527-000000010000000000000005_000000010000000000000005.tar.zst
drwx------ 2 postgres postgres 4.0K May 721: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.
Attention:
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. Dumping Data on the CVM
Log in to the self-built database from the CVM and run the following command. If prompted with "Password:", input the access account password.
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.
If the cloud database and the CVM are in the same VPC, it is recommended to use the private network address for connection.
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.
dbname
The name of the database to be exported.
dumpdir
The path and name of the exported backup file.
Run the following command to check whether the file has been successfully generated.
[root@VM-10-5-tencentos ~]#ll testdb_bkp.dump
2. Creating the Corresponding user on 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.
