PostgreSQL

pg_rewind, introduced in PostgreSQL 9.5, is a powerful utility solving a particular problem: If you have a promoted a streaming replication secondary into being a primary, how can you make sure that the former primary, and any other secondaries that used to be connected to it, are able to connect to the new primary? Previously, there was no entirely

Google recently released a tool to check on-disk checksums in PostgreSQL. PostgreSQL being hot, and Google being Google, this generated a lot of press, much of it containing an element of FUD about the integrity of PostgreSQL backups (note that Google’s own announcements were quite straight-forward about it).

First, some background: Since PostgreSQL 9.3, it has had

(This is another intermittent series of small things that are easy to forget, and cause irritation in the PostgreSQL world…)

When setting up pgpool2, it’s common to tweak the configuration file repeatedly… and often get a hostname wrong. One common occurrence is then:

• You fix the hostnames.
• You restart pgpool2.
• It refuses to connect

The slides from my talk on PostgreSQL Replication at PerconaLive 2018 are available.

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

wal_level = logical

wal_level controls how much information is written into the write-ahead log. Higher settings write more information, enabling more features, at the expense of (somewhat) larger write-ahead log volume.

Just set it to logical, the highest setting. The incremental volume

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

Here’s my preferred logging configuration:

1
log_destination = 'csvlog'
2
logging_collector = on
3
log_directory = '/var/log/postgresql'
4
log_filename = 'postgresql-%Y%m%d-%H%M%S.log'
5
log_rotation_size = 1GB
6
log_rotation_age = 1d
7
log_min_duration_statement = 250ms
8
log_checkpoints = on
 

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

By default archive_mode is set to off, and thus archive_command is ignored. Even if you are not going to be using WAL archiving, you should change these. You can use settings such as:

archive_mode = on
archive_command = '/bin/true'

It requires

(An intermittent series on PostgreSQL parameters whose default settings you should change.)

Introduced in PostgreSQL 9.5, wal_compression is off by default, but you should probably turn it on.

First, what does it do? The documentation helpfully explains:

When this parameter is on, the PostgreSQL server compresses a full page image written to WAL when full_page_writes is

Ultimately, a PostgreSQL database is just files, and those files have to be stored somewhere. Absent tablespaces, they are all stored in a single directory structure, traditionally called PGDATA. While smaller instllations can just use the defaults associated with the packaging, larger databases are often installed on their own volume with its own mountpoint.

Two rules to keep in

The slides from my talk at Nordic PGDay, Why PostgreSQL is Terrible, are now available.