Latest posts from David Youatt

PostgreSQL has supported streaming replication and hot standbys since version 9.0 (2010), and synchronous replication since version 9.1 (2011). Streaming replication (and in this case we're referring to "binary" streaming replication, not "logical") sends the PostgreSQL WAL

Creation and clean up of WAL files in the primary's pg_wal folder (pg_xlog prior to PG10) is a normal part of PostgreSQL operation. The WAL files on the primary are used to ensure data consistency during crash recovery. Use of write-ahead logs (also called redo logs or transaction logs in other products) is common for data stores that must provide durability and consistency of data when writing to storage. The same technique is used in modern journaling and log-structured filesystems.

As the DB is operating, blocks of data are first written serially and synchronously as WAL files, then some time later, usually a very short time later, written to the DB data files. Once the data contained in these WAL files has been flushed out to their final destination in the data files, they are no longer needed by the primary. At some point, depending on your configuration, the primary will remove or recycle the WAL files whose data has been committed to the DB. This is necessary to keep the primary's disk from filling up. However, these WAL files are also what streaming replicas read when they are replicating data from the primary. If the replica is able to keep up with the primary, using these WAL files generally isn't an issue.

If the replica falls behind or is disconnected from the primary for an extended period of time, the primary may have already removed or recycled the WAL file(s) that a replica needs (but see Streaming Replication Slots

PgBouncer is a commonly deployed and recommended connection pooler for PostgreSQL. It supports a number of authentication methods including TLS/SSL client certificate authentication.

Since PgBouncer is located logically between the client and PostgreSQL you have the option of using TLS and cert authentication from client to PgBouncer and from PgBouncer to PostgreSQL. In this brief blog post, we’ll describe configuring securing the client-to-PgBouncer transport first, then build on that to use client certificate authentication to PgBouncer.

A central part of this is TLS and tools for creating and maintaining keys, certificates, signing requests, signing and more. For this talk we use the widely used open source software OpenSSL, but any utilities that produce valid keys and certificates could be used.

The client certificates will need to be signed by the same CA (certificate authority) that signed the PgBouncer certificate. For testing and for this article we’ll use self-signed certificates but for production you should at least create a local CA, or preferably, use a public CA, though the latter can get expensive if you have many client certificates. Both PgBouncer