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 STATS_TOTALS;
SHOW STATS_AVERAGES;
SHOW CONFIG;
SHOW DATABASES;

Any changes to the pgbouncer.ini file can be updated via …

RELOAD;

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}

SELECT postgis_full_version();

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

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

\c <database>;
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;

https://pgbadger.darold.net/

--exclude-line       Ignore these log entries          'decrypt_iv'
--include-query      Only include these SQL queries    'country'
--top (t)            Number of top SQL queries         50
--sample-number (s)  Number of example queries         5
--watch-mode (w)     Only report errors              
--begin (b)          From this date                    '2023-09-01 00:00:00'
--end (e)            To this date                      '2023-09-01 23:59:59'
--nocomment (C)      Remove comments from queries
--dbclient (c)       Report on this client only        '192.168.0.1'
--dbname (d)         Report on this database only      'aqmetrics'
--format (f)         Log format                        rds
--jobs (j)           Number of jobs to run             4

# Display only country queries
pgbadger -f rds --include-query "country" -t 200 -o Backfill.html \
         /home/ubuntu/logs/postgresql_2023-09-12.log
 
# Report only between certain times
pgbadger -b '2023-09-01 12:00:00' -e '2023-09-01 15:00:00' -f rds \
         --exclude-line 'decrypt_iv' -t 50 -o Report.html /home/ubuntu/logs

PGBadger Reports