Greg Clough Blog

DATABASE ARCHITECTURE & SYSTEMS
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-02-01 Greg Clough

Monitoring bgwriter efficiency during checkpoints with pg_buffercache

Have you ever wanted to know what’s happening inside your PostgreSQL buffer cache? Is your bgwriter keeping up, or are you suffering with a lot of dirty buffers during a checkpoint? Uncloak some of the mystery by using the pg_buffercache extension: CREATE EXTENSION pg_buffercache; You can then use the following query: SELECT buffer_status, sum(count) AS […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-01-10 Greg Clough

Faster shutdowns with checkpoint

Have you ever restarted your PostgreSQL cluster and wondered why it’s taking so long? The cause of this is generally one of these: You didn’t include the “-m fast” flag (v9.4 and earlier), and sessions are still connected A large update query is rolling back There are a large number of dirty blocks, which are […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-01-10 Greg Clough

Bug in pg_rewind overwrites pg_control

For those of you that don’t monitor the pgsql-bugs mailing list, this interesting bug was posted recently: >> I have encountered a bug in PostgreSQL 10.1: when the target  >> directory for pg_rewind contains a read-only file (e.g. server.key),  >> pg_rewind exits with “could not open target file” (legitimate) and  >> corrupts the control file […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-01-10 Greg Clough

Autovacuum broken by Incorrect localhost entry

If you’re seeing an error like this in your postgresql.log: LOG: could not connect socket for statistics collector: Invalid argument LOG: disabling statistics collector for lack of working socket WARNING: autovacuum not started because of misconfiguration HINT: Enable the “track_counts” option. There’s a good chance that your localhost entry in /etc/hosts is broken. In my […]

Read Analysis → 0 Comments