Mark Clowes (38M 🇬🇧)

Index - Postgres DDL logging

I cobbled this together recently from a couple of other ideas I found online. I needed a simple way to track changes made in a Postgres database on a test server so they could be written out to a sql file to be replayed on a live server.

It is a simple event trigger that records the current query. When using pgadmin4 for reasons not looked into, two DDL events in one "current query" are logged twice. We add a unique constraint to our logging table to avoid the duplicates.

CREATE SCHEMA ddl_log;
GRANT ALL ON SCHEMA ddl_log TO PUBLIC;

CREATE TABLE ddl_log.ddl_log (
    ts_utc timestamp,
    username CHARACTER VARYING,
    sql_statement CHARACTER VARYING,
    CONSTRAINT no_duplicates UNIQUE(ts_utc, sql_statement)
);

CREATE OR REPLACE FUNCTION ddl_log.on_ddl_event()
    RETURNS event_trigger
    LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO ddl_log.ddl_log
    SELECT now() AT TIME ZONE 'utc', current_user, current_query()
    ON CONFLICT DO NOTHING;
END
$function$;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ddl_log TO hubris_php;

CREATE EVENT TRIGGER on_ddl_event
ON ddl_command_end 
EXECUTE PROCEDURE ddl_log.on_ddl_event();

CREATE TABLE ddl_log.foo ();

SELECT * FROM ddl_log.ddl_log;

We can turn it on and off:

ALTER EVENT TRIGGER on_ddl_event DISABLE;
ALTER EVENT TRIGGER on_ddl_event ENABLE;