Greg Clough Blog

DATABASE ARCHITECTURE & SYSTEMS
RELATIONAL_DB
GREG CLOUGH TECH LOG
2019-10-23 Greg Clough

postgresql.log file grows infinitely, and never rotates… did your disk fill up previously?

An interesting (and currently undocumented) behaviour of PostgreSQL is that when there is an error writing to the postgresql.log configured in log_filename, that all future log rotations are disabled by this piece of code in syslogger.c: if (errno != ENFILE && errno != EMFILE) { ereport(LOG, (errmsg(“disabling automatic rotation (use SIGHUP to re-enable)”))); rotation_disabled = […]

Read Analysis → 0 Comments
RELATIONAL_DB
GREG CLOUGH TECH LOG
2018-03-28 Greg Clough

Don’t use “kill” on PostgreSQL… or you’re gonna have a bad time!

Have you ever been tempted to “kill -9” a non-behaving PostgreSQL client session that won’t die via “pg_cancel_backend()” or “pg_terminate_backend()“? Let’s just say that at first you may believe (incorrectly) that it will affect only the single session you’ve killed, but in reality it’s an incredibly bad idea… as the result will be ALL of […]

Read Analysis → 0 Comments
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