Friday, February 15

Author: Peter Eisentraut

PostgreSQL with passphrase-protected SSL keys under systemd

Eisentraut's PlanetPostgreSQL
PostgreSQL supports SSL, and SSL private keys can be protected by a passphrase. Many people choose not to use passphrases with their SSL keys, and that's perhaps fine. This blog post is about what happens when you do have a passphrase. If you have SSL enabled and a key with a passphrase and you start the server, the server will stop to ask for the passphrase. This happens automatically from within the OpenSSL library. Stopping to ask for a passphrase obviously prevents automatic starts, restarts, and reboots, but we're assuming here that you have made that tradeoff consciously. When you run PostgreSQL under systemd, which is very common nowadays, there is an additional problem. Under systemd, the server process does not have terminal access, and so it cannot ask for any (more…)

Maintaining feature branches and submitting patches with Git

Eisentraut's PlanetPostgreSQL
I have developed a particular Git workflow for maintaining PostgreSQL feature branches and submitting patches to the pgsql-hackers mailing list and commit fests. Perhaps it's also useful to others. This workflow is useful for features that take a long time to develop, will be submitted for review several times, and will require a significant amount of changes over time. In simpler cases, it's probably too much overhead. You start as usual with a new feature branch off master git checkout -b reindex-concurrently master and code away. Make as many commits as you like for every change you make. Never rebase this branch. Push it somewhere else regularly for backup. When it's time to submit your feature for the first time, first merge in the current master branch, fix any (more…)

Upgrading to PostgreSQL 11 with Logical Replication

2ndQuadrant, Eisentraut's PlanetPostgreSQL, PostgreSQL
It's time. About a year ago, we published PostgreSQL 10 with support for native logical replication. One of the uses of logical replication is to allow low- or no-downtime upgrading between PostgreSQL major versions. Until now, PostgreSQL 10 was the only PostgreSQL release with native logical replication, so there weren't many opportunities for upgrading in this way. (Logical replication can also be used for moving data between instances on different operating systems or CPU architectures or with different low-level configuration settings such as block size or locale -- sidegrading if you will.) Now that PostgreSQL 11 is near, there will be more reasons to make use of this functionality. Let's first compare the three main ways to upgrade a PostgreSQL installation: pg_dump and (more…)

More robust collations with ICU support in PostgreSQL 10

Eisentraut's PlanetPostgreSQL, PostgreSQL
In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year. Sorting Sorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you have sorted (more…)

PostgreSQL 10 identity columns explained

Eisentraut's PlanetPostgreSQL, PostgreSQL
For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns: CREATE TABLE test_old ( id serial PRIMARY KEY, payload text ); INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *; and CREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, payload text ); INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *; do pretty much the same thing, except that the new way is more verbose. ;-) So why bother? Compatibility The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some have lately (more…)