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. For the complete source database configuration steps, see Configure source database.

For source instances running PostgreSQL versions earlier than 9.6, the replication delay metric isn't available by default. 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. Follow these steps:

  1. Define the following function using a superuser account: 
    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. Grant execute permission to the migration user: 
    REVOKE EXECUTE ON FUNCTION pg_stat_replication_user() FROM public;
GRANT EXECUTE ON FUNCTION pg_stat_replication_user() TO <USER>;

For self-managed instances: use SUPERUSER privileges

For self-managed instances, you can grant the SUPERUSER privilege directly to the dedicated migration USER.

Monitor replication delay manually

You can also monitor replication delay manually 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
WHERE application_name LIKE 'cloudsql%';

What's next