Useful Postgres Queries

00_slowest_queries_full.sql

-- In pg_stat_statements, there is a problem: sometimes (quite often), it registers the same query twice (or even more).
-- It's easy to check in your DB: 
--
--   with heh as (
--     select userid, dbid, query, count(*), array_agg(queryid) queryids
--     from pg_stat_statements group by 1, 2, 3 having count(*) > 1
--  ) select left(query, 85) || '...', userid, dbid, count, queryids from heh;
--
-- This query gives you "full picture", aggregating stats for each query-database-username ternary

-- Works with Postgres 9.6

select
  userid, dbid, query,
  sum(calls) as calls,
  sum(total_time) as total_time,
  min(min_time) as min_time,
  max(max_time) as max_time,
  sum(mean_time * calls) / sum(calls) as mean_time,
  -- stddev_time, -- https://stats.stackexchange.com/questions/55999/is-it-possible-to-find-the-combined-standard-deviation
  sum(rows) as rows,
  sum(shared_blks_hit) as shared_blks_hit,
  sum(shared_blks_read) as shared_blks_read,
  sum(shared_blks_dirtied) as shared_blks_dirtied,
  sum(shared_blks_written) as shared_blks_written,
  sum(local_blks_hit) as local_blks_hit,
  sum(local_blks_read) as local_blks_read,
  sum(local_blks_dirtied) as local_blks_dirtied,
  sum(local_blks_written) as local_blks_written,
  sum(temp_blks_read) as temp_blks_read,
  sum(temp_blks_written) as temp_blks_written,
  sum(blk_read_time) as blk_read_time,
  sum(blk_write_time) as blk_write_time,
  array_agg(queryid) as queryids -- 9.4+
from pg_stat_statements
group by 1, 2, 3
order by sum(total_time) desc
limit 50;

00_slowest_queries_pg_stat_statements.sql

-- pspg is highly recommended https://github.com/okbob/pspg

select
  row_number() over (order by total_time desc) num,
  round(total_time), calls, rows, mean_time, min_time, max_time, left(query, 500)
from pg_stat_statements
order by total_time desc
limit 50;

-- pre 9.5/9.6

select
  row_number() over (order by total_time desc) num,
  calls, total_time, total_time::numeric/calls avg_time, query
from pg_stat_statements
order by total_time desc
limit 50;

01a_cache_simple.sql

select 
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as percenage,
    sum(heap_blks_hit) as quantity_hit,
    sum(heap_blks_read) as quantity_read
from pg_statio_user_tables;

01b_cache_extended.sql

--https://github.com/dataegret/pg-utils/tree/master/sql
-- Show shared_buffers and os pagecache stat for current database
-- Require pg_buffercache and pgfincore
WITH qq AS (SELECT
    c.oid,
    count(b.bufferid) * 8192 AS size,
    (select sum(pages_mem) * 4096 from pgfincore(c.oid::regclass)) as size_in_pagecache
FROM pg_buffercache b
INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY 1)
SELECT
    pg_size_pretty(sum(qq.size)) AS shared_buffers_size,
    pg_size_pretty(sum(qq.size_in_pagecache)) AS size_in_pagecache,
    pg_size_pretty(pg_database_size(current_database())) as database_size
FROM qq;

02a_btree_bloat_simple.sql

-- enhanced version of https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql

-- WARNING: executed with a non-superuser role, the query inspect only index on tables you are granted to read.
-- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported).
-- This query is compatible with PostgreSQL 8.2 and after

 with data as (
  SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size,
    bs*(relpages-est_pages)::bigint AS extra_size,
    100 * (relpages-est_pages)::float / relpages AS extra_ratio,
    fillfactor, bs*(relpages-est_pages_ff) AS bloat_size,
    100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio,
    is_na
    -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
  FROM (
    SELECT coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
      ) AS est_pages,
      coalesce(1 +
         ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0
      ) AS est_pages_ff,
      bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na
      -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO)
    FROM (
      SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor,
        ( index_tuple_hdr_bm +
            maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
              WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
              ELSE index_tuple_hdr_bm%maxalign
            END
          + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
              WHEN nulldatawidth = 0 THEN 0
              WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
              ELSE nulldatawidth::integer%maxalign
            END
        )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na
        -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO)
      FROM (
        SELECT
          i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid,
          current_setting('block_size')::numeric AS bs, fillfactor,
          CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
            WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
            ELSE 4
          END AS maxalign,
          /* per page header, fixed size: 20 for 7.X, 24 for others */
          24 AS pagehdr,
          /* per page btree opaque data */
          16 AS pageopqdata,
          /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
          CASE WHEN max(coalesce(s.null_frac,0)) = 0
            THEN 2 -- IndexTupleData size
            ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
          END AS index_tuple_hdr_bm,
          /* data len: we remove null values save space using it fractionnal part from stats */
          sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,
          max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
        FROM pg_attribute AS a
          JOIN (
            SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam,
              indrelid, indexrelid, indkey::smallint[] AS attnum,
              coalesce(substring(
                array_to_string(idx.reloptions, ' ')
                 from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor
            FROM pg_index
              JOIN pg_class idx ON idx.oid=pg_index.indexrelid
              JOIN pg_class tbl ON tbl.oid=pg_index.indrelid
              JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
            WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0
          ) AS i ON a.attrelid = i.indexrelid
          JOIN pg_stats AS s ON s.schemaname = i.nspname
            AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl
            OR   (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols
          JOIN pg_type AS t ON a.atttypid = t.oid
        WHERE a.attnum > 0
        GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
      ) AS s1
    ) AS s2
      JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
  ) AS sub
  -- WHERE NOT is_na
)
select
  current_database, schemaname, tblname, idxname,
  real_size, pg_size_pretty(real_size::numeric) as real_size_pretty,
  extra_size, pg_size_pretty(extra_size::numeric) as extra_size_pretty,
  extra_ratio as "extra_ratio, %",
  bloat_size, pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
  bloat_ratio as "bloat_ratio, %",
  fillfactor,
  is_na,
  real_size - bloat_size as live_data_size
from data
order by bloat_size desc
;

02a_table_bloat_simple.sql

-- enhanced version of https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql

/* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read.
* This query is compatible with PostgreSQL 9.0 and more
*/

with data as (
  SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size, 
    (tblpages-est_tblpages)*bs AS extra_size,
    CASE WHEN tblpages - est_tblpages > 0
      THEN 100 * (tblpages - est_tblpages)/tblpages::float
      ELSE 0
    END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size,
    CASE WHEN tblpages - est_tblpages_ff > 0
      THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
      ELSE 0
    END AS bloat_ratio, is_na
    -- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag
  FROM (
    SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
      ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
      tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
      -- , stattuple.pgstattuple(tblid) AS pst
    FROM (
    SELECT
        ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
            - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
          - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
        ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
        toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
      FROM (
        SELECT
          tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
          tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
          coalesce(toast.reltuples, 0) AS toasttuples,
          coalesce(substring(
            array_to_string(tbl.reloptions, ' ')
            FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS fillfactor,
          current_setting('block_size')::numeric AS bs,
          CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
          24 AS page_hdr,
          23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
             + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
         sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size,
          bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
        FROM pg_attribute AS att
          JOIN pg_class AS tbl ON att.attrelid = tbl.oid
          JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
          JOIN pg_stats AS s ON s.schemaname=ns.nspname
            AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
          LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
        WHERE att.attnum > 0 AND NOT att.attisdropped
          AND tbl.relkind = 'r'
        GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
        ORDER BY 2,3
      ) AS s
    ) AS s2
  ) AS s3
  -- WHERE NOT is_na
  --   AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
)
select current_database, schemaname, tblname,
   real_size, pg_size_pretty(real_size::numeric) as real_size_pretty,
   extra_size, pg_size_pretty(extra_size::numeric) as extra_size_pretty,
   extra_ratio as "extra_ratio, %",
   bloat_size, pg_size_pretty(bloat_size::numeric) as bloat_size_pretty,
   bloat_ratio as "bloat_ratio, %",
   fillfactor,
   is_na,
   real_size - bloat_size as live_data_size
from data
order by bloat_size desc
;

02b_bloat_tables_extended.sql

--https://github.com/dataegret/pg-utils/tree/master/sql
--pgstattuple extension required
--WARNING: without table name/mask query will read all available tables which could cause I/O spikes
select nspname,
relname,
pg_size_pretty(relation_size + toast_relation_size) as total_size,
pg_size_pretty(toast_relation_size) as toast_size,
round(((relation_size - (relation_size - free_space)*100/fillfactor)*100/greatest(relation_size, 1))::numeric, 1) table_waste_percent,
pg_size_pretty((relation_size - (relation_size - free_space)*100/fillfactor)::bigint) table_waste,
round(((toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor)*100/greatest(relation_size + toast_relation_size, 1))::numeric, 1) total_waste_percent,
pg_size_pretty((toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor)::bigint) total_waste
from (
    select nspname, relname,
    (select free_space from pgstattuple(c.oid)) as free_space,
    pg_relation_size(c.oid) as relation_size,
    (case when reltoastrelid = 0 then 0 else (select free_space from pgstattuple(c.reltoastrelid)) end) as toast_free_space,
    coalesce(pg_relation_size(c.reltoastrelid), 0) as toast_relation_size,
    coalesce((SELECT (regexp_matches(reloptions::text, E'.*fillfactor=(\\d+).*'))[1]),'100')::real AS fillfactor
    from pg_class c
    left join pg_namespace n on (n.oid = c.relnamespace)
    where nspname not in ('pg_catalog', 'information_schema')
    and nspname !~ '^pg_toast' and relkind = 'r'
    --put your table name/mask here
    and relname ~ ''
) t
order by (toast_free_space + relation_size - (relation_size - free_space)*100/fillfactor) desc
limit 20;

02c_bloat_indices_extended.sql

--https://github.com/dataegret/pg-utils/tree/master/sql
--pgstattuple extension required
--WARNING: without index name/mask query will read all available indexes which could cause I/O spikes
with indexes as (
    select * from pg_stat_user_indexes
)
select schemaname,
table_name,
pg_size_pretty(table_size) as table_size,
index_name,
pg_size_pretty(index_size) as index_size,
idx_scan as index_scans,
round((free_space*100/index_size)::numeric, 1) as waste_percent,
pg_size_pretty(free_space) as waste
from (
    select schemaname, p.relname as table_name, indexrelname as index_name,
    (select (case when avg_leaf_density = 'NaN' then 0 
        else greatest(ceil(index_size * (1 - avg_leaf_density / (coalesce((SELECT (regexp_matches(reloptions::text, E'.*fillfactor=(\\d+).*'))[1]),'90')::real)))::bigint, 0) end)
        from pgstatindex(schemaname || '.' || p.indexrelid::regclass::text)
    ) as free_space,
    pg_relation_size(p.indexrelid) as index_size,
    pg_relation_size(p.relid) as table_size,
    idx_scan
    from indexes p
    join pg_class c on p.indexrelid = c.oid
    where pg_get_indexdef(p.indexrelid) like '%USING btree%' and
    --put your index name/mask here
    indexrelname ~ ''
) t
order by free_space desc;

03_rarely_used_indices.sql

--https://github.com/dataegret/pg-utils/tree/master/sql

SELECT
        pg_stat_user_indexes.schemaname||'.'||pg_stat_user_indexes.relname as tablename,
        indexrelname,
        pg_stat_user_indexes.idx_scan,
        (coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)) as write_activity,
        pg_stat_user_tables.seq_scan,
        pg_stat_user_tables.n_live_tup,
	pg_size_pretty(pg_relation_size(pg_index.indexrelid::regclass)) as size
from pg_stat_user_indexes
join pg_stat_user_tables
        on pg_stat_user_indexes.relid=pg_stat_user_tables.relid
join pg_index
        ON pg_index.indexrelid=pg_stat_user_indexes.indexrelid
where
        pg_index.indisunique is false
        and pg_stat_user_indexes.idx_scan::float/(coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)+1)::float<0.01
        and (coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0))>10000
order by 4 desc,1,2;

04_current_sequence_values.sql

-- Surrogate PKs check.
-- Use it to determine when you reach INT2/INT4/INT8 limits
do $$
  declare r record;
  begin
    create temp table seqs(name text, val int8);
    for r
      in select * from pg_class where relname ilike '%seq'
    loop
      execute 'do $b$ declare z int8; begin insert into seqs(name, val) select '''
        || r.relname::text || ''', last_value from '
        || r.relname::text || '; end; $b$ language plpgsql';
    end loop;
    for r in
      select * from seqs order by val asc
    loop
      raise info '%: %', r.name, r.val;
    end loop;
    drop table seqs;
  end;
$$ language plpgsql;

05_vacuum_process.sql

-- Use this to monitor your "VACUUM VERBOSE ANALYZE;" or smth
-- TIP: Once executed, you can use "\watch 1" to run it every second
select
  pid, datname, relid,
  (select relname from pg_class where oid  = v.relid) as tablename,
  phase, index_vacuum_count, max_dead_tuples, num_dead_tuples,
  heap_blks_total, heap_blks_scanned,
  round(100 * heap_blks_scanned / heap_blks_total) as "heap scan progress, %"
from pg_stat_progress_vacuum v;

A1_unused_and_redundant_indexes_generate_migrations.sql

-- Use it to generate a database migration (e.g. RoR's db:migrate or Sqitch)
-- to drop unused and redundant indexes.

-- This query generates a set of `DROP INDEX` statements, that
-- can be used in your migration script. Also, it generates
-- `CREATE INDEX`, put them to revert/rollback migration script.

-- It is also a good idea to manually double check all indexes being dropped.
-- WARNING here: when you are dropping an index B which is redundant to some index A, 
-- check that you don't drop the A itself at the same time (it can be in "unused").
-- So if B is "redundant" to A and A is "unused", the script will suggest
-- dropping both. If so, it is probably better to drop B and leave A.
-- -- in this case there is a chance that A will be used. If it will still be unused, 
-- you will drop it during the next cleanup routine procedure.

-- This query doesn't need any additional extensions to be installed
-- (except plpgsql), and doesn't create anything (like views or smth)
-- -- so feel free to use it in your clouds (Heroku, AWS RDS, etc)

-- It also does't do anything except reading system catalogs and
-- printing NOTICEs, so you can easily run it on your
--  production *master* database.
-- (Keep in mind, that on replicas, the whole picture of index usage 
-- is usually very different from master).

-- TODO: take into account type of index and opclass
-- TODO: schemas

with unused as (
  select
      format('unused (idx_scan: %s)', pg_stat_user_indexes.idx_scan)::text as reason,
      pg_stat_user_indexes.relname as tablename,
      pg_stat_user_indexes.schemaname || '.' || indexrelname::text as indexname,
      pg_stat_user_indexes.idx_scan,
      (coalesce(n_tup_ins, 0) + coalesce(n_tup_upd, 0) - coalesce(n_tup_hot_upd, 0) + coalesce(n_tup_del, 0)) as write_activity,
      pg_stat_user_tables.seq_scan,
      pg_stat_user_tables.n_live_tup,
      pg_get_indexdef(pg_index.indexrelid) as indexdef,
      pg_size_pretty(pg_relation_size(pg_index.indexrelid::regclass)) as size,
      pg_index.indexrelid
  from pg_stat_user_indexes
  join pg_stat_user_tables
      on pg_stat_user_indexes.relid = pg_stat_user_tables.relid
  join pg_index
      ON pg_index.indexrelid = pg_stat_user_indexes.indexrelid
  where
      pg_stat_user_indexes.idx_scan = 0 /* < 10 or smth */
      and pg_index.indisunique is false
      and pg_stat_user_indexes.idx_scan::float/(coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)+1)::float<0.01
      and (coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0))>10000
), index_data as (
  select *, string_to_array(indkey::text,' ') as key_array,array_length(string_to_array(indkey::text,' '),1) as nkeys
  from pg_index
), redundant as (
  select
    format('redundant to index: %I', i1.indexrelid::regclass)::text as reason,
    i2.indrelid::regclass::text as tablename,
    i2.indexrelid::regclass::text as indexname,
    pg_get_indexdef(i1.indexrelid) main_indexdef,
    pg_get_indexdef(i2.indexrelid) indexdef,
    pg_size_pretty(pg_relation_size(i2.indexrelid)) size,
    i2.indexrelid
  from
    index_data as i1
    join index_data as i2 on i1.indrelid = i2.indrelid and i1.indexrelid <> i2.indexrelid
  where
    (regexp_replace(i1.indpred, 'location \d+', 'location', 'g') IS NOT DISTINCT FROM regexp_replace(i2.indpred, 'location \d+', 'location', 'g'))
    and (regexp_replace(i1.indexprs, 'location \d+', 'location', 'g') IS NOT DISTINCT FROM regexp_replace(i2.indexprs, 'location \d+', 'location', 'g')) 
    and ((i1.nkeys > i2.nkeys and not i2.indisunique) OR (i1.nkeys=i2.nkeys and ((i1.indisunique and i2.indisunique and (i1.indexrelid>i2.indexrelid)) or (not i1.indisunique and not i2.indisunique and (i1.indexrelid>i2.indexrelid)) or (i1.indisunique and not i2.indisunique))))
    and i1.key_array[1:i2.nkeys]=i2.key_array 
), together as (
  select reason, tablename, indexname, size, indexdef, null as main_indexdef, indexrelid
  from unused
  union all
  select reason, tablename, indexname, size, indexdef, main_indexdef, indexrelid
  from redundant
  order by tablename asc, indexname
), droplines as (
  select format('DROP INDEX %s; -- %s, %s, table %s', max(indexname), max(size), string_agg(reason, ', '), tablename) as line
  from together t1
  group by tablename, indexrelid
  order by tablename, indexrelid
), createlines as (
 select format('%s; -- table %s', max(indexdef), tablename) as line
  from together t2
  group by tablename, indexrelid
  order by tablename, indexrelid
)
select '-- Do migration: --' as out
union all
select * from droplines
union all
select ''
union all
select '-- Revert migration: --'
union all
select * from createlines;

A2_master_or_replica_with_lag.sql

select case when pg_is_in_recovery() then 'Replica' || ' (delay: '
    || ((((case
        when pg_last_xlog_receive_location() = pg_last_xlog_replay_location() then 0
        else extract (epoch from now() - pg_last_xact_replay_timestamp())
      end)::int)::text || ' second')::interval)::text
    || '; paused: ' || pg_is_xlog_replay_paused()::text || ')'
  else 'Master'
end;

Nice stuffs. Thank you

Nice stuffs. Thank you

@talley thanks! this is quite old stuff though, last edited in 2017

so if you liked it, check out this as well: https://github.com/NikolayS/postgres_dba, and this: https://gitlab.com/postgres-ai/postgres-checkup (both require some ❤️ to be up to date too, but at least not so outdated)