Data Management:Archive data to an AnalyticDB for PostgreSQL instance

Usage notes

• We recommend that you archive data to an AnalyticDB for PostgreSQL instance in Serverless mode. This helps reduce storage costs and provides high-performance data analysis. When you create a data archiving ticket, you can select a cost optimization solution. This way, the AnalyticDB for PostgreSQL instance can be automatically started during data archiving and paused after data archiving.

• If you set the Post-behavior parameter to Clean up the archived data of the original table (delete-No Lock) when you configure a data archiving ticket, make sure that the source database has sufficient storage space. This prevents exceptions caused by insufficient storage space during data archiving.

• You can archive data to an AnalyticDB for PostgreSQL instance in elastic storage mode or Serverless mode.

• Data Management (DMS) periodically runs a data archiving task only if both the source and destination databases are managed in Security Collaboration or Stable Change mode. If you need to run a data archiving task only once, the source and destination databases can be managed in any mode.

  Note

  If you want to configure a periodic data archiving task and the source and destination database instances are managed in Stable Change mode, we recommend that you enable security hosting for the database instances or change the control mode of the database instances to Security Collaboration. Otherwise, the data archiving task may fail due to instance logon expiration. For more information, see the Enable security hosting section of the "Security hosting" topic and Change the control mode of an instance.

Procedure

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Solution > Data Archiving.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner and choose All functions > Solution > Data Archiving.

3. In the upper-right corner of the Data ArchivingTickets page, click Data Archiving.

4. On the Ticket Application page, configure parameters to create a data archiving ticket. The following table describes the parameters.

   Parameter	Required	Description
   Task Name	Yes	The name of the data archiving task. We recommend that you specify a descriptive name that makes it easy to identify the task. This helps you reduce unnecessary communication.
   Archiving Destination	Yes	The destination to which you want to archive data. Select AnalyticDB PostgreSQL.
   AnalyticDB Instance	Yes	The destination AnalyticDB for PostgreSQL instance to which you want to archive data. Note If you select a cost optimization solution, and the AnalyticDB for PostgreSQL instance is in Serverless mode and uses the pay-as-you-go billing method, the following benefits are available: When the data archiving task starts, the instance is automatically started. After the data archiving task is complete, the instance is automatically paused. When the data archiving task is in progress and the AnalyticDB for PostgreSQL instance is running, you are charged computing and storage fees. For more information, see the Billing rules section of the "Manually pause and resume an instance" topic. After the data archiving task is complete and the instance is paused, you are charged only storage fees.
   Source Database	Yes	The source database from which you want to archive data.
   Archiving Configuration	Yes	Specify one or more tables whose data you want to archive. Optional. Specify one or more filter conditions for querying data to be archived in the specified tables. Note If you want to use time variables in scenarios such as archiving data generated six months ago, you can set the variables in the Variable Configuration section before you configure the parameters in the Archive configuration section.
   Archive Table Mapping	No	The table settings to be used in the destination database. You can click Edit in the Actions column of a source table and specify the name, columns, database shard keys, and partition keys of the corresponding archive table in the destination database.
   Variable Configuration	No	The time variables that you want to use for data archiving. For more information, see the Configure time variables section of the "Variables" topic.
   Post-behavior	No	Specify whether to delete the archived data from the source tables. If you select Clean up the archived data of the original table (delete-No Lock), the archived data is automatically deleted from the source tables. You can execute the DELETE statement to delete temporary backup tables. The temporary backup tables are generated in the source database to store the archived data when the source tables are deleted. Make sure that the source database has sufficient storage space to prevent instance unavailability caused by insufficient storage space. After the data is archived and you verify that the archived data is correct, you can create a regular data change ticket to clear the temporary backup tables from the source database. If you do not select Clean up the archived data of the original table (delete-No Lock), the archived data is not deleted from the source tables. In this case, you need to manually delete data from the source tables and optimize the storage usage. To delete the archived data from the source tables, create a regular data change ticket. For more information, see Perform regular data change. To optimize the storage usage of the source tables, create a lock-free change ticket. For more information, see Perform lock-free DDL operations.
   Operation Mode	Yes	The method to be used for running the data archiving task. Valid values: Single execution: After the data archiving ticket is approved, DMS runs the data archiving task only once. Cyclic scheduling: After the data archiving ticket is approved, DMS runs the data archiving task based on the schedule that you specify. For more information, see the Periodic scheduling section of the "Archive data to a Lindorm instance" topic.

5. Click Submit.

6. After the data archiving ticket is approved, DMS automatically runs the data archiving task.

   If the data archiving task fails to be executed, you can click Details in the Execute step to view the logs of the data archiving task and identify the cause of the task failure. If an issue occurs due to a network or database connection failure, you can click Retry Breakpoint to restart the task.

7. Wait until the data archiving task is complete.

Query the archived data

Method 1: Use DMS to query the archived data

1. After the data archiving task is complete, perform the following steps to view the archived data: In the Basic Information section of the Ticket Details page, click View next to Target Database to go to the SQL Console tab.

2. On the left-side Table tab of the SQL Console tab, find the table that you want to manage, double-click the table name, and then click Execute to view the archived data.

   Note

   DMS automatically creates a database and tables in the destination instance based on the names of the source database and tables. Therefore, the name of the destination database is the same as that of the source database.

   The following four columns of data are added to the archived table. This does not affect the use of the original archived data in the table.

   • Data archiving information, including the ticket number and time when data is archived

   • Database name

   • Table name

   • Instance ID, which is the ID that is specified when you register the instance with DMS and corresponds to the real ID of the instance

Method 2: Use AnalyticDB for PostgreSQL to query the archived data

For more information, see Query data.