Flyway and Postgres auto-vacuum

_flyway-pgsql-auto-vaccum.md

Ran into an issue with flyway script executing an alter table statement while Postgres auto-vacuum for the same table was in progress. As tables grow, Postgres runs the auto-vacuum for tables periodically. The flyway script was stuck waiting for the table lock to be released. To avoid this issue, one can disable the auto-vacuum before the flyway migration and re-enable it after migration is finished using beforeMigrate.sql and afterMigrate.sql.

afterMigrate.sql

-- Re-enable autovacuum
DO $$
DECLARE
    tbl RECORD;
BEGIN

    FOR tbl IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'foobar'
        AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I SET (autovacuum_enabled = on);', tbl.table_schema, tbl.table_name);
    END LOOP;

END $$;

beforeMigrate.sql

-- Disable autovacuum for the duration of the migration
DO $$
DECLARE
    tbl RECORD;
BEGIN

    FOR tbl IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'foobar'
        AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I SET (autovacuum_enabled = off);', tbl.table_schema, tbl.table_name);
    END LOOP;

END $$;

Other useful things that can be added to afterMigrate.sql.

Change the owner of all new tables created by the service account and grant read-only access

    FOR tbl IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = 'foobar'
        AND tableowner != 'db_owner_role'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO db_owner_role;', tbl.schemaname, tbl.tablename);
        EXECUTE format('GRANT SELECT ON TABLE %I.%I TO read_only_role;', tbl.schemaname, tbl.tablename);
    END LOOP;