In addition to the familiar text types VARCHAR and TEXT, PostgreSQL has a type CHAR. It’s little used… and that’s for a reason. It has some very unusual behaviors, which can be quite a surprise if you are not expecting them.

First, CHAR is a fixed-width type. When character data is stored in it, it’s padded out

It’s not uncommon that an application needs to serialize access to one or more resources. The temptation is very high to use the LOCK TABLE SQL statement to do this.

Resist the temptation.

There are many issues with using LOCK:

• It blocks autovacuum, which can cause bloat or even transaction ID wraparound in extreme cases.

pg_rewind is a utility included with PostgreSQL since 9.x. It’s used to “rewind” a server so that it can be attached as a secondary to a primary. The server being rewound could be the former primary of the new primary, or a secondary that was a peer of the new primary.

In pg_rewind terminology, and in this post, the

PostgreSQL has a variety of ways of telling time: now(), statement_timestamp(), and clock_timestamp(). Each has a different sense of when “now” is:

• now() is the time at the start of the transaction; it never changes while the current transaction is open.
• statement_timestamp() is the time that the current statement started running. It changes from

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

Since version 1.7, pgbouncer has had built-in TLS support. This is very handy, since the previous versions required stunnel or something similar to provide TLS.

Frequently, either for test purposes or because you’re in an environment where checking

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