-- Usage:
-- psql -h psql -h ContentsDMS PostgreSQL Support Bundle
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho
\qecho Overview
\qecho
\qecho
\t
select 'Current Database Time : ' || date_trunc('second', clock_timestamp()::timestamp) || '';
\qecho
SELECT 'Database Startup Time : ' || date_trunc('second', pg_postmaster_start_time()::timestamp) || '';
\qecho
SELECT 'Total Active Sessions : ' || count(*) || '' from pg_stat_activity where state ='active' ;
\qecho
SELECT 'Total Number of Sessions : ' || count(*) || '' from pg_stat_activity;
\qecho
\qecho Schema name being Migrated/Replicated : :v_owner
\qecho
\qecho PostgreSQL User Name which DMS will use to connect : :v_connector
\qecho
\qecho
\qecho
\qecho
\t
\pset format html
-- New Major heading :
\qecho
\qecho
\qecho Database Configuration
-- New Minor heading and block :
\qecho
\qecho Database Name and Locale:
\qecho
-- START SQL
with db_role as
(
select case pg_is_in_recovery()
when true then 'Slave'
else 'Master' end as database_role
),
tz as
(
SELECT current_setting('TIMEZONE') as timezone
),
enc as
(
SELECT pg_encoding_to_char(encoding) as encoding
FROM pg_database
WHERE datname = current_database()
),
db_details as
(
select datname as name,
age(datfrozenxid) max_frozen_txn,
encoding as character_set,
datctype as locale
from pg_database
where datname = current_database()
)
select name, encoding, locale, timezone, database_role, max_frozen_txn as "Maximum Used Transaction IDs"
from db_role, tz, enc, db_details;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Database Version :
\qecho
\qecho Please check the following documentation for supported database versions.
\qecho PostgreSQL Source
\qecho PostgreSQL Target
\qecho
\qecho Note:
\qecho
\qecho
\qecho
-- START SQL
select substring(version()::text from '(^.*) on') as "PostgreSQL Version";
-- END SQL
\qecho
\qecho
-- Operating System version
\qecho
\qecho Operating System version
\qecho
-- START SQL
select substring(version()::text from '(?<=on ).*') as "Operating System";
-- END SQL
\qecho
\qecho
-- Database Host
\qecho
\qecho Database Host :
\qecho
-- START SQL
select :'HOST' as "Database host";
-- END SQL
\qecho
\qecho
-- RDS / Non RDS : :
\qecho
\qecho RDS / Non RDS :
\qecho
-- START SQL
\if :is_rds
\qecho Database Type: RDS
\else
\qecho Database Type: NON-RDS
\endif
-- END SQL
\qecho
\qecho
-- Database Parameter Prerequisites
\qecho
\qecho Database Parameter Prerequisites :
\qecho
-- START SQL
select name as "Database Parameter",
setting as "Current Value",
CASE name
WHEN 'wal_level' THEN 'logical'
WHEN 'max_wal_senders' THEN '>1'
WHEN 'max_replication_slots' THEN '>1'
WHEN 'idle_in_transaction_session_timeout' THEN '0'
WHEN 'wal_sender_timeout' THEN '0'
END as "Required Value"
from pg_settings
where name in ('wal_level',
'max_replication_slots',
'max_wal_senders',
'wal_sender_timeout',
'idle_in_transaction_session_timeout',
'shared_preload_libraries')
order by 1;
\qecho
\qecho
\if :is_rds
\qecho Note: If the source is RDS or Aurora PostgreSQL, please set rds.logical_replication to 1. As part of applying this parameter, AWS DMS sets parameters wal_level, max_wal_senders, max_replication_slots above.
\qecho Using PostgreSQL Logical Replication with Aurora
\qecho Logical Replication for PostgreSQL on Amazon RDS
\qecho
show rds.logical_replication;
\else
\qecho Note: Please ensure the above parameter prerequisites for using a PostgreSQL database as a source.
\endif
-- END SQL
\qecho
\qecho
-- Replication Slot Details
\qecho
\qecho Replication Slot Details :
\qecho
\qecho Replication Slot (Ignore if no rows returned)
\qecho
\qecho Note:
\qecho
\qecho
-- START SQL
select
regexp_replace(slot_name::text,'(?<=^.{26}).*' ,repeat('X',45)) as slot_name_obfuscated
, plugin
, slot_type
, datoid
, database
\if :is_ge_10
, temporary
\endif
, active
, active_pid
, xmin
, catalog_xmin
, restart_lsn
, confirmed_flush_lsn
from pg_replication_slots;
\qecho
\qecho Replication Slot Size (Ignore if no rows returned)
\qecho
\qecho Note:
\qecho
\qecho
\if :is_eq_9
select regexp_replace(slot_name::text,'(?<=^.{26}).*' ,repeat('X',45)) as slot_name_obfuscated,
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn)) as replicationSlotLag,
active
from pg_replication_slots ;
\elif :is_ge_10
select regexp_replace(slot_name::text,'(?<=^.{26}).*' ,repeat('X',45)) as slot_name_obfuscated,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as replicationSlotLag,
active
from pg_replication_slots;
\endif
-- END SQL
\qecho
\qecho
\qecho
\qecho
heartbeatEnable=Y;heatbeatFrequency=1
heartbeatEnable: DMS task will create a dummy table "awsdms_heartbeat" in public schema to advance restart_lsn to mitigate the storage issue.
heatbeatFrequency: The frequency to advance restart_lsn. Default is 5 minutes.
\qecho
-- Is High Availability set
\qecho
\qecho Is High Availability set :
\qecho
\qecho Note:
\qecho
\qecho
\qecho
-- START SQL
select case when usename = :'v_connector' then usename else 'other user' end as "User name"
, case when usename = :'v_connector' then application_name else 'other application' end as "Application name"
, client_hostname, backend_start, backend_xmin, state -- , sent_lsn, write_lag, flush_lag
from pg_stat_replication;
-- END SQL
\qecho
-- If it is read replica :
\qecho If the current PostgreSQL database is a master or replica:
\qecho
\qecho
\qecho Note: You cannot use Amazon RDS PostgreSQL Read Replicas for CDC ongoing replication. Refer to setting up an Amazon RDS PostgreSQL DB instance as a source.
\qecho
-- START SQL
select case when pg_is_in_recovery='t' then 'Replica' else 'Master' end as "Master/Replica" from pg_is_in_recovery();
-- END SQL
\qecho
\qecho
-- >Extension Check:
\qecho
\qecho Extension Check :
\qecho
-- PostgreSQL extension supported by DMS for CDC
-- START SQL
select * from pg_Available_Extensions where name like 'pglogical';
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Size Details
-- New Minor heading and block :
\qecho
\qecho Database size :
\qecho
-- START SQL
select pg_size_pretty(pg_database_size(current_database())) as "Database Size";
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Schema Sizes :
\qecho
\qecho Note: This section gives approximate total size of tables in the schema being migrated and compares it to rest of the database.
\qecho
\qecho
-- START SQL
SELECT schema_name as "Schema Name",
pg_size_pretty(sum(table_size)::bigint) as "Total table Size",
trunc((sum(table_size) / pg_database_size(current_database()) * 100),2) as "Percentage Size"
FROM (
SELECT case when pg_catalog.pg_namespace.nspname = :'v_owner' then pg_catalog.pg_namespace.nspname else 'other schemas' end as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Total size of migration schema tables :
\qecho
-- START SQL
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
(select inhrelid, inhparent
FROM pg_inherits
UNION
SELECT child.inhrelid, parent.inhparent
FROM pg_inherit child, pg_inherits parent
WHERE child.inhparent = parent.inhrelid),
pg_inherit_short AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit)),
all_tables as (
SELECT pg_class.oid
, reltuples
, relname
, relnamespace
, pg_class.reltoastrelid
, COALESCE(inhparent, pg_class.oid) parent
, CASE WHEN pg_class.oid = (COALESCE(inhparent, pg_class.oid)) THEN 'parent' ELSE 'child' END as "parent_or_child"
FROM pg_class
LEFT JOIN pg_inherit_short ON inhrelid = oid
LEFT JOIN pg_namespace n ON n.oid = relnamespace
WHERE relkind IN ('r', 'p') and n.nspname = :'v_owner'
)
select pg_size_pretty(sum(pg_total_relation_size(oid))) as "Size",
case when parent_or_child = 'parent' then 'non-partitioned tables' else 'partitioned tables' end as "Table Type"
from all_tables
group by parent_or_child;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Top objects by size :
\qecho
-- START SQL
WITH RECURSIVE pg_inherit(inhrelid, inhparent) AS
(select inhrelid, inhparent
FROM pg_inherits
UNION
SELECT child.inhrelid, parent.inhparent
FROM pg_inherit child, pg_inherits parent
WHERE child.inhparent = parent.inhrelid),
pg_partitions AS (SELECT * FROM pg_inherit WHERE inhparent NOT IN (SELECT inhrelid FROM pg_inherit))
SELECT table_schema
, TABLE_NAME
, row_estimate
, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT c.oid
, nspname AS table_schema
, relname AS TABLE_NAME
, SUM(c.reltuples) OVER (partition BY parent) AS row_estimate
, SUM(pg_total_relation_size(c.oid)) OVER (partition BY parent) AS total_bytes
, SUM(pg_indexes_size(c.oid)) OVER (partition BY parent) AS index_bytes
, SUM(pg_total_relation_size(reltoastrelid)) OVER (partition BY parent) AS toast_bytes
, parent
FROM (
SELECT pg_class.oid
, reltuples
, relname
, relnamespace
, pg_class.reltoastrelid
, COALESCE(inhparent, pg_class.oid) parent
FROM pg_class
LEFT JOIN pg_partitions ON inhrelid = oid
WHERE relkind IN ('r', 'p')
) c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
) a
WHERE oid = parent and table_schema = :'v_owner'
) a
ORDER BY total_bytes DESC;
-- END SQL
\qecho
\qecho
-- Database Load
\qecho
\qecho
\qecho Database Load
-- Rate of WAL generation
\qecho
\qecho Rate of WAL generation:
\qecho
\qecho Note: In this section, we find the volume of WAL generated in last 10 minutes. Though this may not paint complete picture considering that
\qecho several factors can impact WAL generation, we are just getting a rough idea. This information may be useful to troubleshoot replication latency issues.
\qecho
\qecho
\qecho
\qecho
-- START SQL
\if :is_eq_9
select 'For PostgreSQL versions 9.x and below, please work with AWS support to find WAL size' as WAL_size;
\endif
\if :is_ge_10
select pg_size_pretty(sum(size)) as "Volume of WAL generated in last 10 minutes"
from pg_ls_waldir() where modification > (now() - interval '10 minutes') ;
\endif
-- END SQL
\qecho
\qecho
\qecho
\qecho CDC specific Load summary table :
\qecho
\qecho Taking a snapshot of database view PG_STAT_DATABASE because of the important database statistics in it.
\qecho
For more information, refer to:
\qecho PG_STAT_DATABASE view
-- START SQL
\qecho
\qecho PG_STAT_DATABASE
select * from pg_stat_database
where datname = current_database();
\qecho
\qecho
\qecho Taking a snapshot of database view PG_STAT_BGWRITER to gather information related to database checkpoints.
\qecho Needed to troubleshoot replication performance issues.
\qecho
For more information, refer to:
\qecho PG_STAT_BGWRITER view
\qecho
\qecho PG_STAT_BGWRITER
select * from pg_stat_bgwriter ;
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Table Load
-- New Minor heading and block :
\qecho
\qecho Top 25 Objects by total DML Changes :
\qecho
\qecho Note: DMS being a logical replication tool, we find volume of DML activity here. We also need to know if vacuuming is done regularly for performance reasons.
\qecho
-- START SQL
select relname as "Table name",
n_tup_ins as "# Inserts",
n_tup_upd as "# Updates",
n_tup_del as "# Deletes",
n_tup_hot_upd as "# Hot Updates",
(n_tup_ins + n_tup_upd + n_tup_del) as "Total changes",
n_live_tup as "# Live tuples = # NUM ROWS",
n_dead_tup as "# Dead tuples",
n_mod_since_analyze as "# Changes since last analyze",
vacuum_count as "# Manual vacuum counts",
autovacuum_count as "# Auto vacuum counts"
FROM pg_stat_all_tables
WHERE schemaname = :'v_owner'
order by (n_tup_ins + n_tup_upd + n_tup_del) desc
limit 25;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Top 25 tables by inserts :
\qecho
-- START SQL
select relname as "Table name",
n_tup_ins as "# Inserts"
FROM pg_stat_all_tables
WHERE schemaname = :'v_owner'
order by n_tup_ins desc
limit 25;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Top 25 tables by updates :
\qecho
-- START SQL
select relname as "Table name",
n_tup_upd as "# Updates"
FROM pg_stat_all_tables
WHERE schemaname = :'v_owner'
order by n_tup_upd desc
limit 25;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Top 25 tables by deletes :
\qecho
-- START SQL
select relname as "Table name",
n_tup_del as "# Deletes"
FROM pg_stat_all_tables
WHERE schemaname = :'v_owner'
order by n_tup_del desc
limit 25;
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Table Details
-- New Minor heading and block :
\qecho
\qecho Existing Materialized Views :
\qecho
-- START SQL
select schemaname as schema_name
,matviewname as view_name
,matviewowner as owner
,ispopulated as is_populated
from pg_matviews
order by schema_name, view_name;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Object Type count :
\qecho
-- START SQL
SELECT
n.nspname as schema_name
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'm' THEN 'materialized view'
WHEN 's' THEN 'special'
END as object_type
,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','m','s')
AND n.nspname = :'v_owner'
GROUP BY n.nspname,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'm' THEN 'materialized view'
WHEN 's' THEN 'special'
END
ORDER BY n.nspname,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'm' THEN 'materialized view'
WHEN 's' THEN 'special'
END;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Data Type count :
\qecho
-- START SQL
select table_schema as Schema ,data_type as "Data type", count(*)
from information_schema.columns where
table_schema = :'v_owner'
group by table_schema,data_type order by 3 desc;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Replica Identity details :
\qecho
\qecho Note: Replica Identity controls the WAL records for a table being modified. Use REPLICATE IDENTITY FULL carefully for each table as FULL generates an extra amount of WAL that may not be necessary.
\qecho
\qecho
\qecho
-- START SQL
SELECT relname AS table_name,
CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END AS replica_identity,
n.nspname AS "Table schema"
FROM pg_class c JOIN PG_NAMESPACE n ON c.relnamespace = n.oid
WHERE n.nspname = :'v_owner'
AND relkind in ('r','p')
AND relreplident <> 'd'
ORDER BY 1;
-- END SQL
\qecho
\qecho
-- DMS Artifacts
\qecho
\qecho DMS Artifacts
\qecho
\qecho Note: DMS creates several objects to capture data definition language (DDL) events, implement heartbeat mechanism for advancing restart_lsn, and provide useful migration statistics for migration status. Refer to DMS artifacts of a PostgreSQL source, DMS control table task settings, and DMS data validation for details.
\qecho
\qecho
\qecho
-- START SQL
SELECT
c.relname
,n.nspname as schema_name
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'm' THEN 'materialized view'
WHEN 's' THEN 'special'
END as object_type
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','m','s')
AND c.relname like 'aws%'
order by 1;
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Archival Information
-- New Minor heading and block :
\qecho
\qecho Archive mode and other details :
\qecho
-- START SQL
show archive_mode;
\qecho
\if :is_non_rds
show archive_command;
\endif
\qecho
show wal_level;
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Permissions
-- New Minor heading and block :
\qecho
\qecho System privileges :
\qecho
\qecho Note:
\qecho
\qecho
\qecho
-- START SQL
\du+ :v_connector
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Table Grants :
\qecho
For more information, refer to:
\qecho GRANT on Database Objects
\qecho
-- START SQL
with obj_permissions as
(
select
c.relname,
coalesce(nullif(s[1], ''), 'public') as grantee,
s[2] as privileges
from
pg_class c
join pg_namespace n on n.oid = relnamespace
join pg_roles r on r.oid = relowner,
unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s
where nspname = :'v_owner'
and relkind in ('r','p')
)
select * from obj_permissions
where grantee = :'v_connector';
-- END SQL
\qecho
\qecho
------------------------------------------------------------------------------
-- New Major heading :
\qecho
\qecho
\qecho Potential Issues
-- New Minor heading and block :
\qecho
\qecho Unsupported data types :
\qecho
\qecho Note: Refer to the default mapping to AWS DMS data types from PostgreSQL source and the target PostgreSQL data types mapped from the AWS DMS data types.
\qecho
-- START SQL
select table_schema as Schema
,data_type as "Data type"
, count(*)
from information_schema.columns where
table_schema = :'v_owner'
and data_type in ('timestamp with time zone')
group by table_schema,data_type order by 3 desc;
-- END SQL
\qecho
\qecho
-- Tables with No PK
\qecho
\qecho Tables with No PK :
\qecho
\qecho Note:
\qecho
\qecho
\qecho
-- START SQL
select tbl.table_schema,
tbl.table_name
from information_schema.tables tbl
where table_type = 'BASE TABLE'
and table_schema not in ('pg_catalog', 'information_schema')
and table_schema = :'v_owner'
and table_name not like 'awsdms%'
and not exists (select 1
from information_schema.key_column_usage kcu
where kcu.table_name = tbl.table_name
and kcu.table_schema = tbl.table_schema);
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho TEXT and JSONB columns with NOT NULL set :
\qecho
\qecho Note: TEXT and JSONB data types are treated as LOB during DMS replication. Full LOB mode comes with penalty of slow performance.
\qecho
\qecho
-- START SQL
-- JSONB columns with NOT NULL set
select table_name as "Table name"
,column_name as "Column name"
,data_type as "Data type"
from information_schema.columns
where is_nullable = 'NO'
and table_schema = :'v_owner'
and data_type in ('jsonb','text')
and table_name not like 'awsdms%';
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Long running sql (more than 10 minutes) :
\qecho
\qecho Note:
\qecho
\qecho
\qecho
-- START SQL
-- Long running sql for more than 10 minutes
SELECT
pid as "process id",
usename as username,
xact_start as "transaction start",
current_timestamp - query_start AS runtime,
wait_event_type,
state,
backend_xid,
backend_xmin,
\if :is_ge_10
backend_type,
\endif
query
FROM pg_stat_activity
WHERE state = 'active'
AND (current_timestamp - query_start) > interval '10 minutes'
AND query not like '%vacuum%'
ORDER BY 1 DESC
LIMIT 10;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Tables with triggers :
\qecho
\qecho Note: Though triggers on source do not have much impact from DMS standpoint, it is important to know if these triggers exist on target or not. For brevity purpose we list only first 10 triggers.
\qecho
\qecho tgenabled: O = trigger fires in "origin" and "local" modes, D = trigger is disabled, R = trigger fires in "replica" mode, A = trigger fires always.
\qecho
\qecho
-- START SQL
SELECT cast(tgrelid::regclass as text) as table_name, tgname, tgenabled
FROM pg_trigger
WHERE tgisinternal is false
AND tgrelid in
(SELECT oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace in
(SELECT oid
FROM pg_namespace
WHERE nspname = :'v_owner'
)
)
ORDER BY cast(tgrelid::regclass as text)
LIMIT 10;
-- END SQL
\qecho
\qecho
-- New Minor heading and block :
\qecho
\qecho Database Views :
\qecho
\qecho Note: Currently AWS DMS supports only the Full Load for views in PostgreSQL source endpoints.
\qecho
\qecho
-- START SQL
SELECT
c.relname as "View"
,n.nspname as "Schema"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v')
and n.nspname = :'v_owner';
-- END SQL
\qecho
\qecho
\qecho End of the report