Enable replication delay monitoring for PostgreSQL versions earlier than 9.6

This page describes how to enable replication delay monitoring when you migrate from a source instance running PostgreSQL versions earlier than 9.6 to AlloyDB for PostgreSQL. For complete source database configuration instructions, see Configure source database.

For source instances running PostgreSQL versions earlier than 9.6, the replication delay metric isn't available by default because non-superusers can't access replication statistics in the pg_stat_replication view. You can use the following approaches if you want to monitor replication delay for such sources.

Create a security definer function

The recommended option to track replication delay for sources running PostgreSQL versions earlier than 9.6 is to create a security definer function allowing Database Migration Service to query replication delay statistics. Follow these steps:

  1. Connect to your source database using a superuser account and define the pg_stat_replication_user() function: 
    CREATE OR REPLACE FUNCTION pg_stat_replication_user()
RETURNS TABLE (
  pid               integer,
  usesysid          oid,
  username          name,
  application_name  text,
  client_addr       inet,
  client_hostname   text,
  client_port       integer,
  backend_start     timestamp with time zone,
  backend_xmin      xid,
  state             text,
  sent_location     pg_lsn,
  write_location    pg_lsn,
  flush_location    pg_lsn,
  replay_location   pg_lsn,
  sync_priority     integer,
  sync_state        text
)
LANGUAGE SQL
SECURITY DEFINER
AS $$
  SELECT * FROM pg_catalog.pg_stat_replication;
$$;
  2. Revoke public execution rights from the function and grant execute permissions exclusively to your dedicated migration user account: 
    REVOKE EXECUTE ON FUNCTION pg_stat_replication_user() FROM public;
GRANT EXECUTE ON FUNCTION pg_stat_replication_user() TO "MIGRATION_USER_ACCOUNT_NAME";

For self-hosted instances: use SUPERUSER privileges

For self-hosted PostgreSQL instances, you can grant the SUPERUSER privilege directly to your dedicated migration user account:

ALTER USER MIGRATION_USER_ACCOUNT_NAME WITH SUPERUSER;

Monitor replication delay manually

You can also monitor replication delay manually on the source instance using the following query:

SELECT current_timestamp, application_name,
  pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.sent_location) AS sent_location_lag,
  pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.write_location) AS write_location_lag,
  pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.flush_location) AS flush_location_lag,
  pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.replay_location) AS replay_location_lag
FROM pg_stat_replication;

What's next