Aurora PostgreSQL Artifact Extraction Prerequisites

This topic briefs about the prerequisites required for Aurora PostgreSQL artifact extraction.

In This Topic:

Introduction
Artifact Extraction
Getting Help

Introduction

LeapLogic’s Assessment profiles existing inventory, identifies complexity, performs dependency analysis, and provides recommendations for migration to modern data platform.

Artifact Extraction

LeapLogic requires certain artifacts to perform an assessment. As a prerequisite, to fetch the required data you must have admin privileges. You can retrieve these artifacts from your GIT instance or the PostgreSQL repository where all the artifacts such as DDL scripts, stored procedures, functions, query execution logs, DML scripts, and other database objects are stored. LeapLogic needs all these artifacts in the form of .sql files.

DDL Scripts

One of the easiest and most direct ways to export the required DDLs from the database is by using the pg_dump command in PostgreSQL.

Note:

It is recommended to execute the pg_dump command for all databases, particularly the ones you intend to migrate.

Use the following command to export the DDL scripts to the machine where PostgreSQL is installed. Modify the command details highlighted based on your requirements. This command will export all the DDLs of the database objects present in the PostgreSQL database.

pg_dump -h host -U username -d database -p 5432 –schema-only -f /path/pg_dump_database_name.sql

Query Execution Logs

To export the query execution logs, make sure that the error reporting and logging are enabled in PostgreSQL. Follow the below given steps to enable logging.

Config File Location

Run the command below in PostgreSQL to find the location of the configuration file.

postgres=# SHOW config_file;

Data Directory Path

The data directory is the storage location for the query execution logs. Run the command given below in PostgreSQL to find the data directory path.

postgres=# SHOW data_directory;

Logging

Open the Amazon RDS console and choose Databases from the navigation menu.
Choose the DB instance for which you want the logging.
Choose the Configuration view to see the details of the DB instance and to see the parameter group associated with the DB instance.

Note:

When you create a DB instance, it is associated with the default DB parameter group. Because you can’t modify this group, create a custom parameter group to modify it.

Choose Parameter group from the Instance configuration pane and then choose the parameter group that you want to modify.
Click Edit Parameter.
In the Filter parameters field, select the parameters given below to change:

Enter log_statement and change the value to all.
Enter log_destination and change the value to csvlog.
Enter log_filename and change the value to postgresql.log.%Y-%m-%d-%H%M.

Click Save changes.

Restarting Services

When you change the DB instance parameter group (from default to a custom group), you must reboot the DB instance.

Copy the log file from the data directory location and share it with the LeapLogic team.

Download Log Files

Follow the steps below to download the log files from the AWS Management Console.

Open Amazon RDS.
Choose your RDS/Aurora instance.
Choose Logs & events.

In Logs, choose the all-csv log files.
Click Download.

Share all the CSV log files with the LeapLogic team.

Other Database Objects

To enhance the assessment results for your environment and workloads, it is recommended to export additional database objects. Please refer to the below script to export the required database objects as separate delimited files.

– – – 2.3.1 Database Objects

SELECT table_name,

table_catalog AS databasename,

table_schema AS SchemaName,

table_type

FROM information_schema.tables

WHERE table_type = ‘BASE TABLE’

AND table_schema NOT IN

(‘pg_catalog’, ‘information_schema’)

UNION

SELECT table_name,

table_catalog AS databasename,

table_schema AS SchemaName,

‘VIEW’ AS table_type

FROM information_schema.views

WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)

AND table_name !~ ‘^pg_’;

– – – 2.3.2 Database Objects Count.

SELECT databasename,

table_type,

count(table_name) AS table_count

FROM (SELECT table_name,

table_catalog AS databasename,

table_schema AS SchemaName,

table_type

FROM information_schema.tables

WHERE table_type = ‘BASE TABLE’

AND table_schema NOT IN

(‘pg_catalog’, ‘information_schema’)

UNION

SELECT table_name,

table_catalog AS databasename,

table_schema AS SchemaName,

‘VIEW’ AS table_type

FROM information_schema.views

WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)

AND table_name !~ ‘^pg_’) as subquery

GROUP BY 1, 2;

– – – 2.3.3 Database Volume

select t1.datname AS db_name,

pg_size_pretty(pg_database_size(t1.datname)) as db_size

from pg_database t1

order by pg_database_size(t1.datname) desc;

– – – 2.3.4 Table volume

SELECT

table_catalog AS databaseName,

table_schema,

table_name,

pg_size_pretty(pg_total_relation_size(quote_ident(table_schema)||’.’||quote_ident(table_name))) AS total_size

from information_schema.tables

where table_schema = ‘public’

order by 4 desc;

– – – 2.3.5 User List

SELECT usename FROM pg_user;

Other Scripts and Artifacts

Copy any other scripts such as DML scripts etc. from your GIT instance and share them with the LeapLogic team to produce more extensive insights.

Getting Help

Contact LeapLogic technical support at info@leaplogic.io