Azure PostgreSQL built-in Health Recovery

During my testing of CLUSTER and PG_REPACK on Azure PostgreSQL, I found that Azure PostgreSQL has an automatic health recovery mechanism. This feature drops running processes on the Postgres database when it detects an unhealthy server condition, such as full storage.

Enable Audit

SHOW shared_preload_libraries;

-- Azure Portal -> Server parameters -> shared_preload_libraries -> Add PGAUDIT

SELECT name

FROM pg_available_extensions

WHERE name = 'pgaudit';

SHOW azure.extensions;

-- Azure Portal -> Server parameters -> azure.extensions -> Add PGAUDIT

CREATE EXTENSION IF NOT EXISTS pgaudit;

SHOW pgaudit.log;

-- Azure Portal -> Server parameters -> pgaudit.log -> ALL

SHOW log_line_prefix;

-- Azure Portal -> Server parameters -> log_line_prefix -> %t-%c-user=%u,db=%d,app=%a,client=%h

CLUSTER

-- This command is metadata only; it set the default index for future CLUSTER operations. It does not actually re-cluster the table.

ALTER TABLE curves.xc_curve

CLUSTER ON xc_curve_pk;

-- verify the clustered index

SELECT

    c.relname AS table_name,

    i.relname AS index_name

FROM pg_class c

JOIN pg_index x

    ON c.oid = x.indrelid

JOIN pg_class i

    ON i.oid = x.indexrelid

WHERE x.indisclustered

  AND c.oid = 'curves.xc_curve'::regclass;

/*

 * This command is Session wide only (for the duration of that specific connection).

 * It will not apply to the whole server or all users.

 */

SET maintenance_work_mem = '8GB';

SHOW maintenance_work_mem;

\timing on

/*

 * For a 1.8 TB table, I would typically want at least 2–2.5 TB of free storage beyond the current database size before attempting a CLUSTER, otherwise there is a real risk of running out of space and having the operation fail.

 */

CLUSTER VERBOSE curves.xc_curve USING xc_curve_pk;

-- Check CLUSER running

SELECT

    pid,

    usename,

    application_name,

    state,

    wait_event_type,

    wait_event,

    now() - query_start AS runtime,

    query

FROM pg_stat_activity

WHERE query ILIKE 'CLUSTER%';

PG_REPACK

CREATE EXTENSION pg_repack;

 -- PGSQL pg_repack

pg_repack -t curves.xc_curve -d axioma -h us5-dp-d-pg1.postgres.database.azure.com -U breakglass -k

-- Check PG_REPACK running:
SELECT pid,

       usename,

       application_name,

       state,

       wait_event_type,

       wait_event,

       query

FROM pg_stat_activity

WHERE application_name LIKE '%repack%';

Health recovery

An internal Azure process executed the pg_terminate_backend command to kill the CLUSTER connection.

/*
 * SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' AND usename<> 'azuresu' AND usename<> 'replication' AND pg_catalog.pg_is_in_recovery() = false and (SELECT setting = 'on' FROM pg_settings WHERE name = 'default_transaction_read_only')

 */