Database consulting and application development...

...because your data is your business

 
Sept. 26, 2018, 11:06 a.m.

Find the value of all database sequences

Upon occasion, you want to get the current value of all the sequences in the database. For example, you may have fields that are integer rather than bigint, and you’re concerned how close you are to overflowing one of them (since sequences are bigint and will happily crash through the size of a 32-bit integer). […]


Posted by Christophe Pettus
Sept. 12, 2018, 8:22 p.m.

“Securing PostgreSQL” at PDXPUG PostgreSQL Day 2018

The slides from my presentation, Securing PostgreSQL at PDXPUG PostgreSQL Day 2018 are now available.


Posted by Christophe Pettus
Sept. 4, 2018, noon

CHAR: What is it good for?

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 […]


Posted by Christophe Pettus
Aug. 28, 2018, 5 p.m.

Don’t LOCK tables. Just don’t.

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. […]


Posted by Christophe Pettus
Aug. 9, 2018, 10:06 a.m.

Three Steps to pg_rewind Happiness

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 […]


Posted by Christophe Pettus
Aug. 7, 2018, 5:58 p.m.

Does anyone really know what time it is?

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 statement […]


Posted by Christophe Pettus
July 23, 2018, 9 a.m.

One of those things: pgbouncer and self-signed certificates

(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 […]


Posted by Christophe Pettus
July 18, 2018, 9 a.m.

pg_rewind and checkpoints: caution!

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, […]


Posted by Christophe Pettus
July 17, 2018, 10:38 a.m.

That Google Checksum Tool

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 […]


Posted by Christophe Pettus
July 16, 2018, 8:33 p.m.

One of those things: /tmp/pgpool_status

(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 to […]


Posted by Christophe Pettus
May 10, 2018, noon

PostgreSQL Replication at PerconaLive 2018

The slides from my talk on PostgreSQL Replication at PerconaLive 2018 are available.


Posted by Christophe Pettus
April 10, 2018, 6:36 p.m.

Change these: wal_level and wal_hint_bits

(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 is […]


Posted by Christophe Pettus
April 6, 2018, 8 a.m.

Change these: Logging

(An intermittent series on PostgreSQL parameters whose default settings you should change.) Here’s my preferred logging configuration: log_destination = 'csvlog' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y%m%d-%H%M%S.log' log_rotation_size = 1GB log_rotation_age = 1d log_min_duration_statement = 250ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_statement = 'ddl' log_temp_files = […]


Posted by Christophe Pettus
April 3, 2018, 1 p.m.

Change these: archive_mode and archive_command

(An intermittent series on PostgreSQL parameters whose default settings you should change.) By default archive_mode is set to off, and thus archive_command is ignored. Even if you are not going to be using WAL archiving, you should change these. You can use settings such as: archive_mode = on archive_command = '/bin/true' It requires a server […]


Posted by Christophe Pettus
March 30, 2018, 9:28 p.m.

Change This: wal_compression

(An intermittent series on PostgreSQL parameters whose default settings you should change.) Introduced in PostgreSQL 9.5, wal_compression is off by default, but you should probably turn it on. First, what does it do? The documentation helpfully explains: When this parameter is on, the PostgreSQL server compresses a full page image written to WAL when full_page_writes […]


Posted by Christophe Pettus
March 27, 2018, 6:12 p.m.

Mountpoints and the Single PostgreSQL Server

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 […]


Posted by Christophe Pettus
March 20, 2018, 6:28 a.m.

Why PostgreSQL is Terrible? at Nordic PGDay

The slides from my talk at Nordic PGDay, Why PostgreSQL is Terrible, are now available.


Posted by Christophe Pettus
Jan. 2, 2018, 10 p.m.

A Replication Cheat-Sheet

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


Posted by Christophe Pettus
Oct. 30, 2017, 2 p.m.

Commitment Issues

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… BEGIN; UPDATE table SET money=money+100000; COMMIT; -- And you get an error that the server has disconnected Did that transaction commit, or didn’t it? You don’t really know, because you […]


Posted by Christophe Pettus
Oct. 27, 2017, 1:29 a.m.

Streaming replication stopped? One more thing to check.

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 […]


Posted by Christophe Pettus
Oct. 27, 2017, 1:06 a.m.

“Humans do not have a primary key” at PGConf.EU

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


Posted by Christophe Pettus
Sept. 7, 2017, 10:19 a.m.

“Humans do not have a primary key” at PGOpen SV

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


Posted by Christophe Pettus
Sept. 5, 2017, 8:51 a.m.

A brief reminder about casting TIMESTAMPTZ

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 […]


Posted by Christophe Pettus
Sept. 4, 2017, 1:18 a.m.

Blog Moved.

This is probably no big deal to anyone, but the blog has moved to a new server. Whee!


Posted by Christophe Pettus
June 9, 2017, 11:27 a.m.

shared_buffers is not a sensitive setting

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 […]


Posted by Christophe Pettus