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 [2021/09/22 15:11] – [PGBOUNCER] 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>
 psql -p 6432 -U pgbouncer pgbouncer psql -p 6432 -U pgbouncer pgbouncer
  
 SHOW STATS; SHOW STATS;
 +SHOW STATS_TOTALS;
 +SHOW STATS_AVERAGES;
 SHOW CONFIG; SHOW CONFIG;
 SHOW DATABASES; SHOW DATABASES;
 </code> </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]] [[https://www.postgresql.org/docs/current/pgstatstatements.html]]
  
Line 56: Line 71:
 </code> </code>
  
-==== POSTGIS ====+==== postgis ====
 [[https://postgis.net/]] [[https://postgis.net/]]
 <code> <code>
 sudo apt install --no-install-recommends postgresql-13-postgis-3{,-scripts} sudo apt install --no-install-recommends postgresql-13-postgis-3{,-scripts}
 +</code>
 +<code>
 +SELECT postgis_full_version();
 </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 ====
 +[[https://www.postgresql.org/docs/current/pgbuffercache.html]]
 +<code>
 +\c <database>;
 +CREATE EXTENSION PG_BUFFERCACHE;
 +</code>
 +<code SQL>
 +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;
 +</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>
postgresqlextensions.1632323484.txt.gz · Last modified: 2025/03/08 22:23 (external edit)