DB Wiki

User Tools

Site Tools

Trace:
postgresqlextensions

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
postgresqlextensions [2024/03/01 15:18] – z0hpvkpostgresqlextensions [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 1: Line 1:
 ===== PostgreSQL Extensions ===== ===== PostgreSQL Extensions =====
  
-==== PG_CRON ====+==== pg_cron ====
 [[https://github.com/citusdata/pg_cron/]]\\ [[https://github.com/citusdata/pg_cron/]]\\
  
Line 22: Line 22:
 <code SQL> <code SQL>
 SELECT cron.schedule('0 3 * * *', $$DELETE FROM events WHERE event_time < now()$$); 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 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> </code>
  
-==== PGLOGICAL ====+==== pglogical ====
 [[https://www.2ndquadrant.com/en/resources/pglogical/]] [[https://www.2ndquadrant.com/en/resources/pglogical/]]
  
Line 35: Line 44:
 </code> </code>
  
-==== PGBOUNCER ====+==== pgbouncer ====
 [[https://www.pgbouncer.org/config.html]]\\ [[https://www.pgbouncer.org/config.html]]\\
 <code> <code>
Line 50: Line 59:
 RELOAD; RELOAD;
 </code> </code>
-==== PG_STAT_STATEMENTS ====+==== pg_stat_statements ====
 [[https://www.postgresql.org/docs/current/pgstatstatements.html]] [[https://www.postgresql.org/docs/current/pgstatstatements.html]]
  
Line 62: Line 71:
 </code> </code>
  
-==== POSTGIS ====+==== postgis ====
 [[https://postgis.net/]] [[https://postgis.net/]]
 <code> <code>
Line 71: Line 80:
 </code> </code>
  
-==== POSTGRES_FDW ====+==== postgres_fdw ====
 [[https://www.postgresql.org/docs/current/postgres-fdw.html]] [[https://www.postgresql.org/docs/current/postgres-fdw.html]]
  
-==== PG_BUFFERCACHE ====+==== pg_buffercache ====
 [[https://www.postgresql.org/docs/current/pgbuffercache.html]] [[https://www.postgresql.org/docs/current/pgbuffercache.html]]
 <code> <code>
Line 97: Line 106:
 </code> </code>
  
-==== PGBADGER ====+==== pgbadger ====
 [[https://pgbadger.darold.net/]]\\ [[https://pgbadger.darold.net/]]\\
  
Line 103: Line 112:
 <code text> <code text>
 --exclude-line       Ignore these log entries          'decrypt_iv' --exclude-line       Ignore these log entries          'decrypt_iv'
---include-query      Only include these SQL queries    'backfill_mifidii'+--include-query      Only include these SQL queries    'country'
 --top (t)            Number of top SQL queries         50 --top (t)            Number of top SQL queries         50
 --sample-number (s)  Number of example queries         5 --sample-number (s)  Number of example queries         5
Line 127: Line 136:
 </code> </code>
 [[PGBadgerReports| PGBadger Reports]]\\ [[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>
postgresqlextensions.1709306297.txt.gz · Last modified: 2025/03/08 22:23 (external edit)