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.

So many types of replication, so little time! What kind of PostgreSQL replication should you use?

One of the essentials of any database system is that a transaction is either in progress, committed, or rolled back. But consider what happens if…

1
BEGIN;
2
UPDATE table SET money=money+100000;
3
COMMIT;
4
-- And you get an error that the server has disconnected

Did that transaction commit, or didn’t it? You

We recently were asked by a client to look at a strange replication situation. Replication had simply stopped from a primary to a streaming replication secondary. Everything looked fine:

• The secondary was connected to the primary, as we could see in pg_stat_replication.
• The secondary was not receiving any queries, so delays in applying incoming page changes

The slides for “Humans do not have a primary key” as presented at PGConf EU are available here. Three links from the presentation are:

• Personal names around the world
• Kudzu and the California Marriage Amendment
• General Data Protection Regulations in the EU

I’ll be presenting “Humans do not have a primary key” at PGOpen SV today. Two links from the presentation are:

• Personal names around the world
• Kudzu and the California Marriage Amendment

I spent a few minutes worrying I had lost my mind: Results coming back to my application were different from those obtained by running psql directly on the database server, for identical queries.

As is often the case, the problem was time zones.

In particular, when casting a TIMESTAMPTZ to a DATE, the current client time zone

There’s a lot of mystery-cult information floating around out there about what to set shared_buffers to in PostgreSQL. Most of it is, at best, overthinking a fairly simple setting.

You can get 90% of the way there, on 95%+ of PostgreSQL systems, just by setting it to 25% of total system memory, to a maximum of 32GB. (It used

For some reason, this is something that even experienced PostgreSQL people don’t know about, which is: What do you do if you’ve forgotten the postgres user’s password, and you have no other superuser available?

The answer is: Bring up PostgreSQL in single-user mode, reset the password, bring it back up in standard mode.

Of course, this requires