DB Wiki

https://github.com/citusdata/pg_cron/

Can be installed using the apt.postgresql.org repository

sudo apt install postgresql-13-cron

Add the following entries in the postgresql.conf file …

shared_preload_libraries = 'pg_cron'
cron.database = '<database_name>'

\c postgres;
CREATE EXTENSION pg_cron;

SELECT cron.schedule('0 3 * * *', $$DELETE FROM events WHERE event_time < now()$$);
SELECT cron.unschedule(<job_id>);
SELECT * FROM cron.job;
SELECT * FROM cron.job_run_details;

https://www.2ndquadrant.com/en/resources/pglogical/

SELECT * FROM pglogical.local_sync_status;
SELECT * FROM pglogical.subscription;

https://www.pgbouncer.org/config.html

psql -p 6432 -U pgbouncer pgbouncer

SHOW STATS;
SHOW CONFIG;
SHOW DATABASES;

https://www.postgresql.org/docs/current/pgstatstatements.html

SELECT pg_stat_statements_reset();
 
SELECT query, calls, total_time, ROWS, 
       100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
  FROM pg_stat_statements 
ORDER BY total_time DESC LIMIT 5;

https://postgis.net/

sudo apt install --no-install-recommends postgresql-13-postgis-3{,-scripts}

https://www.postgresql.org/docs/current/postgres-fdw.html

https://www.postgresql.org/docs/current/pgbuffercache.html

CREATE EXTENSION PG_BUFFERCACHE;

SELECT n.nspname, c.relname, 
       CASE c.relkind WHEN 'r' THEN 'Table'
                      WHEN 'i' THEN 'Index'
                      WHEN 't' THEN 'Toast' 
       END AS "Type",
       COUNT(*) AS buffers, 
       COUNT(*)*8/1024 AS size_in_mb
  FROM pg_buffercache b 
  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()))
  JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname, c.relkind
ORDER BY 4 DESC LIMIT 10;