Validate database objects post-migration from Microsoft SQL Server to Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL

Database object validation plays a key role in the database migration process. It’s the process of determining whether all the source database objects have been successfully migrated to the target database by comparing their types and counts. If you miss the validation phase, you may encounter run time errors due to missing database objects which can be a blocker for unit testing and code deployments.

For database migration, you can use the AWS Schema Conversion Tool (AWS SCT) to convert your existing database schema from one database engine to another. AWS Database Migration Service (AWS DMS) then migrates the data from source to target after converting the schema. These migrated database objects should be validated to ensure a successful migration.

AWS SCT generates assessment reports that show the complexity of the code and the effort required to migrate to the target database. This report identifies the percentage of code that can be converted automatically and the code that needs manual effort. Because schema migration with AWS SCT isn’t a fully automated process, validation plays a crucial role in delivering a successful migration.

Although AWS DMS has integrated data validation, best practice dictates that we should validate the schema objects converted by AWS SCT prior to migrating any data. In this post, we explain how to validate the database schema objects migrated from Microsoft SQL Server to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition.

Database object validation

The following list contains the various types of database objects that we validate using their counts and detail-level information, which helps us identify missing or partially migrated database objects. We ignore the system schemas in the source and target databases.

• Schemas
• Tables
• Views
• Stored Procedures
• Functions
• Indexes
• Triggers
• Constraints
• Sequences

Let’s dive deep into each object type and its validation process. We can use SQL Server Management Studio to connect Microsoft SQL Server database and pgAdmin to connect Amazon RDS for PostgreSQL or Aurora PostgreSQL database and run the following queries to validate each object.

Schemas

Schemas are used to represent a collection of database objects that serve a related functionality in an application or a micro service. You can validate the schemas at the source and target databases using SQL queries.

For SQL Server, use the following query:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, you have two query options. The following code is the first option:

SELECT nspname
FROM   pg_catalog.pg_namespace
WHERE  ( nspname NOT LIKE 'pg_toast%'
         AND nspname NOT LIKE 'pg_temp%'
         AND nspname NOT LIKE '%pg_toast_temp%'
         AND nspname NOT IN 
( 'pg_catalog', 'public', 'information_schema',
'aws_sqlserver_ext','aws_sqlserver_ext_data' ))
 ORDER  BY 1; 

The following screenshot shows our output:

The following code is the second option:

SELECT schema_name
FROM   information_schema.schemata
WHERE  ( schema_name NOT LIKE 'pg_toast%'
         AND schema_name NOT LIKE 'pg_temp%'
         AND schema_name NOT LIKE '%pg_toast_temp%'
 AND schema_name NOT IN ( 'pg_catalog', 'public', 'information_schema','aws_sqlserver_ext',
'aws_sqlserver_ext_data' ) )
ORDER  BY schema_name;

The following screenshot shows our output:

When you convert your database or data warehouse schema, AWS SCT adds an additional schema to your target database. This schema implements SQL system functions of the source database that are required when writing your converted schema to your target database. This additional schema is called the extension pack schema. You can directly deploy these schemas to the target database after verifying the native equivalent options in both the source and target databases.

When migrating database from Microsoft SQL Server to Amazon RDS for PostgreSQL or Aurora PostgreSQL, AWS SCT creates these extension packs – aws_sqlServer_ext and aws_sqlServer_ext_data.

Tables

AWS SCT converts source SQL Server tables to the equivalent target (PostgreSQL) tables with appropriate data types and relative table definitions using the default or custom mapping rules applied. The following scripts return the counts and detail-level information for all the tables, assuming the source database doesn’t have any partitioned tables.

For SQL Server, use the following code:

SELECT table_schema      AS Schema_name,
       Count(table_name) AS Tables_Count
FROM   information_schema.tables
WHERE  table_catalog = 'Your Database'
       AND table_type = 'BASE TABLE'
GROUP  BY table_schema
ORDER  BY table_schema; 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following query for counts:

SELECT   table_schema     AS schema_name,
Count(table_name)AS table_count
FROM     information_schema.tables
WHERE    table_type ='BASE TABLE'
AND      table_catalog = ‘your database’
AND      table_schema NOT IN ('information_schema', 'pg_catalog', 'public','aws_sqlserver_ext', 'aws_sqlserver_ext_data') GROUP BY table_schema
ORDER BY table_schema ;

The following screenshot shows our output:

Views

You can validate the views count converted by AWS SCT by using the following queries on the source and target databases.

For SQL Server, use the following code:

SELECT table_schema      AS Schema_name,
       Count(table_name) AS Views_Count
FROM   information_schema.TABLES
WHERE  table_catalog = 'Your Database'
       AND table_type = 'VIEW'
GROUP  BY table_schema
ORDER  BY table_schema; 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, you have two options. The following code is the first option:

SELECT   table_schema     AS schema_name,
         Count(table_name)AS view_count
FROM     information_schema.TABLES
WHERE    table_type ='VIEW'
AND      table_catalog = ‘your database’
AND      table_schema NOT IN ('information_schema',
'pg_catalog',  'public','aws_sqlserver_ext',
                           'aws_sqlserver_ext_data')
GROUP BY table_schema
ORDER BY table_schema ;

The following screenshot shows our output:

The following code is the second option:

Stored procedures

In SQL Server, we use stored procedures to perform operations like Data Definition Language (DDL) such as CREATE, ALTER, and DROP, and Data Manipulation Language (DML) such as INSERT, UPDATE, DELETE, and the functions to retrieve the data.

While in PostgreSQL, we use functions to perform DDL and, DML operations, and to retrieve data.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS ProcedureCount
FROM   sys.objects
WHERE  TYPE = 'P'
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id) AS SchemaName,
       name                   AS ProcedureName
FROM   sys.objects WHERE  TYPE = 'P'
ORDER  BY Schema_name(schema_id), name; 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname        AS SchemaName,
       Count(p.proname) AS ProcedureCount
FROM   pg_proc p
       join pg_namespace n
         ON p.pronamespace = n.oid
WHERE  n.nspname NOT IN ( 'pg_catalog', 'information_schema','aws_sqlserver_ext', 'aws_sqlserver_ext_data' )  AND p.prokind = 'p'
GROUP  BY n.nspname
ORDER  BY n.nspname;

The following screenshot shows our output:

Use the following query for detail-level information:

SELECT n.nspname AS SchemaName,
       p.proname AS ProcedureName
FROM   pg_proc p
       join pg_namespace n ON p.pronamespace = n.oid
WHERE  n.nspname NOT IN ( 'pg_catalog','information_schema',
'aws_sqlserver_ext','aws_sqlserver_ext_data' ) AND p.prokind = 'p';

The following screenshot shows our output:

The preceding screenshot shows, there are no procedures created at the target database. They are converted as functions in PostgreSQL.

AWS SCT provides an option to convert the source stored procedures to functions or stored procedures in the PostgreSQL database. You should convert stored procedures to functions on PostgreSQL because PostgreSQL stored procedures have limitations.

Functions

Given the limitations of stored procedures and functions in PostgreSQL, we convert some of the stored procedures to functions. This count gets added to the already existing functions count in the source database. In both the source and target databases, the following queries provide counts and detail-level information about the functions.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id),
       Count(name) AS FunctionCount
FROM   sys.objects
WHERE  TYPE in  ('FN', 'TF')
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id) AS SchemaName,
       name                   AS FunctionName
FROM   sys.objects
WHERE  TYPE in  ('FN', 'TF')
ORDER  BY Schema_name(schema_id), name; 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname        AS SchemaName,
       Count(p.proname) AS FunctionCount
FROM   pg_proc p
       join pg_namespace n
         ON p.pronamespace = n.oid
WHERE  n.nspname NOT IN ( 'pg_catalog', 'information_schema','aws_sqlserver_ext','aws_sqlserver_ext_data' ) 
AND p.prokind = 'f'
GROUP  BY n.nspname
ORDER  BY n.nspname; 

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT n.nspname AS SchemaName,
       p.proname AS FunctionName
FROM   pg_proc p
       join pg_namespace n
         ON p.pronamespace = n.oid
WHERE  n.nspname NOT IN ( 'pg_catalog',
'information_schema','aws_sqlserver_ext',
'aws_sqlserver_ext_data' )AND p.prokind = 'f'; 

The following screenshot shows our output:

Indexes

Indexes play a key role in improving query (performance). Because tuning methodologies differ from database to database, the number of indexes and their types vary with SQL Server and PostgreSQL databases based on different use cases, so index counts also may differ.

With the following scripts, you can get the counts of indexes and their types in both SQL Server and PostgreSQL databases.

For SQL Server, use the following code:

SELECT sc.name            AS Schema_Name,
       i.type_desc,
       Count(i.type_desc) AS Index_Count
FROM   sys.INDEXES i
       inner join sys.objects o
               ON i.object_id = o.object_id
       inner join sys.schemas sc
               ON o.schema_id = sc.schema_id
WHERE  i.name IS NOT NULL
       AND o.TYPE = 'U' AND Db_name() = 'Your Database'
GROUP  BY sc.name,
          i.type_desc ORDER  BY sc.name,i.type_desc ;

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT schemaname,Count(indexname) AS IndexesCount
FROM   pg_indexes
WHERE  schemaname NOT IN ( 'pg_catalog','aws_sqlserver_ext',
'aws_sqlserver_ext_data' ) GROUP  BY schemaname
ORDER  BY schemaname; 

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT schemaname,indexname
FROM   pg_indexes
WHERE  schemaname NOT IN ( 'pg_catalog', 'aws_sqlserver_ext',
                           'aws_sqlserver_ext_data' )
ORDER  BY schemaname,indexname ; 

The following screenshot shows our output:

Triggers

Triggers help you audit the DMLs or DDL changes in the database. They can also impact performance based on the usage in appropriate areas. The following queries give you the count and details of triggers, for both the source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)  AS TriggerCount
FROM   sys.objects
WHERE  TYPE = 'TR' GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       name                          AS TriggerName,
       Object_name(parent_object_id) AS TableName
FROM   sys.objects
WHERE  TYPE = 'TR'
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT trigger_schema      AS SchemaName,
       Count(trigger_name) AS TriggerCount
FROM   information_schema.TRIGGERS
WHERE  trigger_schema NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',   'pg_catalog' )
GROUP  BY trigger_schema
ORDER  BY trigger_schema;

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT trigger_schema      AS TriggerSchemaName,
       event_object_schema AS TableSchema,
       event_object_table  AS TableName,
       trigger_name,
       event_manipulation  AS TriggerType
FROM   information_schema.TRIGGERS
WHERE  trigger_schema NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data', 'pg_catalog' )
ORDER  BY trigger_schema; 

The following screenshot shows our output:

These keys should be turned off, before performing AWS DMS migration.

Constraints (primary key, foreign key, unique key, check & default)

Along with database object’s validation, you need to ensure the data is consistent and bound to integrity. Different types of constraints provide you with the flexibility to control and check the data during insertion to avoid run time data integrity issues.

Primary keys

Primary keys allow you to have unique values for columns, which prevents information from being duplicated, following the normalization process.

This key helps improve the search based on the key values, and avoid table scans.

The following queries help you extract the counts and details of primary keys in source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS PK_Count
FROM   sys.objects
WHERE  TYPE = 'PK'
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       Object_name(parent_object_id) AS TableName,
       name                          AS PKName
FROM   sys.objects
WHERE  TYPE = 'PK'
ORDER  BY Schema_name(schema_id) 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname      AS SchemaName,
       Count(conname) AS ConstraintCount
FROM   pg_constraint c
       join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'p' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 'aws_sqlserver_ext_data', 'pg_catalog' )
GROUP  BY n.nspname
ORDER  BY n.nspname;

Use the following code for detail-level information:

SELECT n.nspname                       AS SchemaName,
       conname                         AS ConstraintName,
       conrelid :: regclass :: VARCHAR AS ObjectName
FROM   pg_constraint c
       join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'p' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',  'pg_catalog' )
ORDER  BY n.nspname, 2; 

Foreign keys

Foreign keys help you identify the relations between the tables, which you can use to form database normalized forms that store relevant data in appropriate tables. These keys should be turned off, before performing the AWS DMS migration.

With the following queries, you get the counts and detail-level information about the foreign keys in both the source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS FK_Count
FROM   sys.objects
WHERE  TYPE = 'FK'
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       Object_name(parent_object_id) AS TableName,
       name                          AS FKName
FROM   sys.objects
WHERE  TYPE = 'FK'
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname      AS SchemaName,
       Count(conname) AS ConstraintCount
FROM   pg_constraint c
       join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'f' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',  'pg_catalog' )
GROUP  BY n.nspname
ORDER  BY n.nspname;

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT n.nspname  AS SchemaName, conname   AS ConstraintName,
       conrelid :: regclass :: VARCHAR AS ObjectName
FROM   pg_constraint c   join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'f' )AND conrelid :: regclass :: VARCHAR <> '-' AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',  'pg_catalog' )ORDER  BY n.nspname, 2; 

Unique keys

Using unique keys restricts the uniqueness of data in the column and prevents duplicate values. You can use this key can be used to avoid data redundancy, which indirectly helps with appropriate data storage and retrieval. With the following queries, you get the counts and detail level information about the unique keys in both the source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS UK_Count
FROM   sys.objects WHERE  TYPE = 'UQ' GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       Object_name(parent_object_id) AS TableName,
       name                          AS UK_Name
FROM   sys.objects
WHERE  TYPE = 'UQ'
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname      AS SchemaName,
       Count(conname) AS ConstraintCount
FROM   pg_constraint c
       join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'u' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data', 'pg_catalog' )
GROUP  BY n.nspname
ORDER  BY n.nspname; 

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT n.nspname                       AS SchemaName,
       conname                         AS ConstraintName,
       conrelid :: regclass :: VARCHAR AS ObjectName
FROM   pg_constraint c
       join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'u' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data', 'pg_catalog' )
ORDER  BY n.nspname, 2 ;

Check constraints

The check constraint assists you in validating the inserted or updated data in accordance with the business rules specified in the column. You can add these to a new or existing table with the appropriate data.

The following queries return counts and detail-level information about check constraints in source and target databases.

For SQL Server, you have options for both queries. Firstly, you can use the following code for counts:

SELECT Schema_name(t.schema_id) AS SchemaName,
       Count(cns.name)          AS ConstraintCount
FROM   sys.check_constraints cns
       left outer join sys.objects t
                    ON cns.parent_object_id = t.object_id
       left outer join sys.all_columns col
                    ON cns.parent_column_id = col.column_id
                      AND cns.parent_object_id = col.object_id
GROUP  BY Schema_name(t.schema_id)
ORDER  BY Schema_name(t.schema_id)

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(t.schema_id) AS SchemaName,
        cns.name   AS Constraintname
FROM   sys.check_constraints cns
       left outer join sys.objects t
                    ON cns.parent_object_id = t.object_id
       left outer join sys.all_columns col
                    ON cns.parent_column_id = col.column_id
                      AND cns.parent_object_id = col.object_id 
ORDER  BY Schema_name(t.schema_id) 

The following screenshot shows our output:

Alternatively, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS CCCount
FROM   sys.objects
WHERE  TYPE = 'C'
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

You can also use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       Object_name(parent_object_id) AS TableName,
       name                          AS CCName
FROM   sys.objects
WHERE  TYPE = 'C'
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT n.nspname      AS SchemaName,
       Count(conname) AS ConstraintCount
FROM   pg_constraint c inner join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'c' )AND conrelid :: regclass :: VARCHAR <> '-'  AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',   'pg_catalog' )
GROUP  BY n.nspname
ORDER  BY n.nspname;

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT n.nspname                       AS SchemaName,
       conname                         AS ConstraintName,
       conrelid :: regclass :: VARCHAR AS ObjectName
FROM   pg_constraint c join pg_namespace n
         ON n.oid = c.connamespace
WHERE  contype IN ( 'c' )
       AND conrelid :: regclass :: VARCHAR <> '-'
       AND n.nspname NOT IN ( 'aws_sqlserver_ext', 
'aws_sqlserver_ext_data',   'pg_catalog' )
ORDER  BY n.nspname, 2; 

Default constraints

Default constraints help you insert or update default values into the relative columns, based on the business requirements. The following queries help find the counts and detail-level information of default constraints in the source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(name)            AS DK_Count
FROM   sys.objects
WHERE  TYPE = 'D' GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id)        AS SchemaName,
       Object_name(parent_object_id) AS TableName,
       name                          AS DCName
FROM   sys.objects
WHERE  TYPE = 'D'
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT col.table_schema,
       Count(column_default) AS DK_count
FROM   information_schema.COLUMNS col
WHERE  col.column_default IS NOT NULL
       AND col.table_schema NOT IN (
           'aws_sqlserver_ext', 'aws_sqlserver_ext_data',
           'pg_catalog' )
GROUP  BY col.table_schema
ORDER  BY col.table_schema;

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT col.table_schema,
       col.table_name,
       col.column_name,
       col.column_default
FROM   information_schema.COLUMNS col
WHERE  col.column_default IS NOT NULL
       AND col.table_schema NOT IN (
           'aws_sqlserver_ext', 'aws_sqlserver_ext_data',
           'pg_catalog' )
ORDER  BY col.table_schema,
          col.table_name; 

Sequences

Sequences help you create and increment integer values for columns based on given ranges and order. Unlike identity columns, sequences aren’t associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

The following queries help you get the counts and detail-level information of sequences available in the source and target databases.

For SQL Server, use the following code for counts:

SELECT Schema_name(schema_id) AS SchemaName,
       Count(NAME)            AS SequencesCount
FROM   sys.sequences
GROUP  BY Schema_name(schema_id)
ORDER  BY Schema_name(schema_id);

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT Schema_name(schema_id) AS SchemaName,
       NAME                   AS SequenceName
FROM   sys.sequences
ORDER  BY Schema_name(schema_id); 

The following screenshot shows our output:

For Amazon RDS for PostgreSQL or Aurora PostgreSQL, use the following code for counts:

SELECT sequence_schema,
       Count(1) AS sequences_count
FROM   information_schema.sequences
GROUP  BY sequence_schema;

The following screenshot shows our output:

Use the following code for detail-level information:

SELECT sequence_schema,
       sequence_name
FROM   information_schema.sequences
ORDER  BY sequence_schema,
          sequence_name; 

Useful PostgreSQL catalog tables

The following table summarizes a few of the SQL Server and PostgreSQL system or catalog tables and views that are useful for database object validation. These tables and views hold the metadata about various database objects and their details, which you can use for the validation.

Identify and fix the missing objects

The queries in this post can help you identify missing database objects during migration from the source to the target database. You can use the query results to perform a comparison to identify the gaps in database objects. This narrows down the focus on the missing objects of the migration. You can use the queries iteratively after you fix the missing objects until you achieve a desired state.

Conclusion

Validating database objects is essential to providing database migration accuracy and confirming that all objects have been migrated correctly. Validating all the database objects helps you ensure target database integrity, thereby allowing your application to function as seamlessly as it did on the source database.

In this post, we discussed post-migration validation of database objects. Following the database migration from SQL Server to Amazon RDS for PostgreSQL or Aurora PostgreSQL, we clarified the significance of the validation process and types of database objects validated, boosting the confidence level of the migrated databases. In case of errors, this solution also helps you identify, it also assists you in identifying the missing or unmatched objects post-migration.

If you have questions or suggestions, leave a comment.

About the Authors

Sai Krishna Namburu is a Database Consultant with AWS Professional Services based out of Hyderabad, India. With good knowledge on relational databases adding hands-on in homogenous and heterogenous database migrations, he helps the customers in migrating to AWS cloud and their optimizations. Enthusiastic about new technologies around the databases and process automation areas. Interested in open source technologies, data analytics and ML which enhances the customer outcome.

Shyam Sunder Rakhecha is a Database Consultant with the Professional Services team at AWS based out of Hyderabad, India and specializes in database migrations. He is helping customers in migration and optimization in AWS cloud. He is curious to explore emerging technology in terms of Databases. He is fascinated with RDBMS and Big Data. He also love to organize team building events and regalement in team.

Tirumala Rama Chandra Murty Dasari is a Database Consultant with AWS Professional Services (Hyderabad, India). With extensive knowledge on relational and non-relational databases, helps the customer migrations to AWS cloud. Curious to learn and implement new technologies which reflects the customer success. Automates the manual process and enhances the existing using relevant technologies.