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.schedule_in_database('Vacuum Analyze', '0 6 * * 0', $$VACUUM ANALYZE$$, 'doob');
SELECT cron.unschedule(<job_id>);
 
SELECT jobid, jobname, schedule, DATABASE, active FROM cron.job;
 
SELECT jobname, 
       STATUS, to_char(start_time,'DD-MM-YYYY HH24:MI') AS start_date, 
       to_char(end_time - start_time,'HH24:MI:SS') AS time_taken,
       return_message 
  FROM cron.job_run_details jrd
  JOIN cron.job j ON j.jobid = jrd.jobid
ORDER BY start_time;

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

https://aws.amazon.com/blogs/database/remove-bloat-from-amazon-aurora-and-rds-for-postgresql-with-pg_repack/

For PostgreSQL 14 on AWS, use version 1.4.7.
https://pgxn.org/dist/pg_repack/1.4.7/

https://reorg.github.io/pg_repack/#installation

sudo apt install postgresql-server-dev-12 gcc zlib1g-dev
 
unzip pg_repack-1.4.5.zip
cd pg_repack-1.4.5
make
sudo make install

CREATE EXTENSION pg_repack;

pg_repack will attempt to get an exclusive lock on the table being re-organised.
If it can’t then, by default, it will attempt to terminate any sessions that are blocking it.

Use option –no-kill-backend (-D) to allow pg_repack to terminate if it can’t get a lock.

cd pg_repack-1.4.5/bin
./pg_repack -k -h localhost -U postgres -D -t football.teams doob