DB Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== PostgreSQL Extensions =====

==== pg_cron ====
[[https://github.com/citusdata/pg_cron/]]\\

Can be installed using the apt.postgresql.org repository
<code>
sudo apt install postgresql-13-cron
</code>

Add the following entries in the postgresql.conf file ...
<code>
shared_preload_libraries = 'pg_cron'
cron.database = '<database_name>'
</code>

<code>
\c postgres;
CREATE EXTENSION pg_cron;
</code>

<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 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 ====
[[https://www.2ndquadrant.com/en/resources/pglogical/]]

<code SQL>
SELECT * from pglogical.local_sync_status;
SELECT * from pglogical.subscription;
</code>

==== pgbouncer ====
[[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>
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]]

<code SQL>
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;
</code>

==== 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 ====
[[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>