Differences

This shows you the differences between two versions of the page.

--- postgresqlextensions [2021/09/26 16:00] – z0hpvk
+++ postgresqlextensions [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ===== PostgreSQL Extensions =====
-==== PG_CRON ====
+==== pg_cron ====
 [[https://github.com/citusdata/pg_cron/]]\\
@@ Line 22: / Line 22: @@
 <code SQL>
 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 * FROM cron.job;
-SELECT * FROM cron.job_run_details;
+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;
 </code>
-==== PGLOGICAL ====
+==== pglogical ====
 [[https://www.2ndquadrant.com/en/resources/pglogical/]]
@@ Line 35: / Line 44: @@
 </code>
-==== PGBOUNCER ====
+==== pgbouncer ====
-[[https://www.pgbouncer.org/config.html]]
+[[https://www.pgbouncer.org/config.html]]\\
 <code>
 psql -p 6432 -U pgbouncer pgbouncer
 SHOW STATS;
+SHOW STATS_TOTALS;
+SHOW STATS_AVERAGES;
 SHOW CONFIG;
 SHOW DATABASES;
 </code>
-==== PG_STAT_STATEMENTS ====
+Any changes to the pgbouncer.ini file can be updated via ...
+<code>
+RELOAD;
+</code>
+==== pg_stat_statements ====
 [[https://www.postgresql.org/docs/current/pgstatstatements.html]]
@@ Line 56: / Line 71: @@
 </code>
-==== POSTGIS ====
+==== postgis ====
 [[https://postgis.net/]]
 <code>
 sudo apt install --no-install-recommends postgresql-13-postgis-3{,-scripts}
+</code>
+<code>
+SELECT postgis_full_version();
 </code>
-==== POSTGRES_FDW ====
+==== postgres_fdw ====
 [[https://www.postgresql.org/docs/current/postgres-fdw.html]]
-==== PG_BUFFERCACHE ====
+==== pg_buffercache ====
 [[https://www.postgresql.org/docs/current/pgbuffercache.html]]
+<code>
+\c <database>;
+CREATE EXTENSION PG_BUFFERCACHE;
+</code>
 <code SQL>
 SELECT n.nspname, c.relname,
@@ Line 82: / Line 104: @@
 GROUP BY n.nspname, c.relname, c.relkind
 ORDER BY 4 desc limit 10;
+</code>
+==== pgbadger ====
+[[https://pgbadger.darold.net/]]\\
+=== Parameters ===
+<code text>
+--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
+</code>
+=== Examples ===
+<code bash>
+# 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
+</code>
+[[PGBadgerReports| PGBadger Reports]]\\
+==== pg_repack ====
+=== AWS Instructions ===
+https://aws.amazon.com/blogs/database/remove-bloat-from-amazon-aurora-and-rds-for-postgresql-with-pg_repack/
+=== Download ===
+For PostgreSQL 14 on AWS, use version 1.4.7. \\
+https://pgxn.org/dist/pg_repack/1.4.7/
+=== Installation ===
+https://reorg.github.io/pg_repack/#installation
+<code bash>
+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
+</code>
+<code postgresql>
+CREATE EXTENSION pg_repack;
+</code>
+=== Usage ===
+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. \\
+<code bash>
+cd pg_repack-1.4.5/bin
+./pg_repack -k -h localhost -U postgres -D -t football.teams doob
 </code>