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
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
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:
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 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
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,
INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
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?
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