After you change the schema of a database in Data Management (DMS), DMS saves the latest schema to a version list. You can use the list to download, compare, and restore historical schema versions.
Background
Schema versioning operates at the database level, and each version captures the schema of all tables in the database. A new version is automatically created whenever the schema of any table is modified. The following operations in DMS create a new schema version:
-
Executing schema-altering SQL statements in SQLConsole.
-
Executing schema-altering SQL statements in tickets for normal data modify, lock-free data change, schema design, or schema synchronization.
-
Executing schema-altering SQL statements in an SQL task initiated by an administrator.
-
If a schema change occurs outside of DMS, you can synchronize the metadata in DMS to capture and save the latest schema. For more information, see synchronize a data dictionary.
-
For instances in the Security Collaboration control mode, you can also synchronize metadata by clicking the
icon in SQLConsole.
Prerequisites
-
You have query permission for the target table or database.
-
Your database is one of the following types:
-
MySQL-compatible: ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and OceanBase databases in MySQL mode.
-
PostgreSQL-compatible: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, and AnalyticDB for PostgreSQL.
-
PolarDB for PostgreSQL (Compatible with Oracle).
-
SQL Server.
-
MariaDB.
-
Oracle.
-
Limitations
-
The number of schema versions that can be retained varies by control mode:
-
Flexible Management: 3
-
Stable Change: 20
-
Security Collaboration: Unlimited
-
-
Schema versioning is not supported for the following types of databases:
-
Databases with more than 1,024 tables.
-
System databases, such as the
information_schemaorsysdatabase in MySQL.
-
Manage schema versions
Log in to DMS 5.0.
-
Go to the Version Management page.
Method 1: From Database Instances
In the Database Instances section, find your target database, right-click the database name, and select Version Management.
Method 2: From SQLConsole
Go to SQLConsole and click the Version Management
icon in the upper-right corner.Method 3: From the Instances page
This method is available only to administrators and DBAs.
Move the pointer over the
icon in the upper-left corner of the DMS console and choose . NoteIf you use the DMS console in normal mode, choose in the top navigation bar.
-
On the Database List tab, find your target database.
-
In the Actions column, choose .
-
On the Database version list page, find the target version and perform one of the following operations:
-
Click Change Details to view the SQL script for the schema change.
-
Click Rollback to revert the schema to the previous version.
-
Select two versions and click Version Comparison to compare their schemas.
-