Thursday, December 6, 2018
Multi-master is one of those features where when you need it, you really, really need it, and if you're in that category, this talk is for you. It's also of interest to anyone trying to figure out the best solution for scaling and redundancy beyond one machine and one data center.
To attend, you must RSVP at Meetup with your full name (for building security's guest list).
Tuesday, October 30, 2018
In fact, sometimes it works a little too silently.
At PostgreSQL Experts we've run into the problem where a client's history files disappear because they are stored in S3, and there's a lifecycle configuration in place that says to move everything over a certain age to Glacier. That's a good policy for WAL files!
Unfortunately, it's not a good policy for history files: without the latest history file you can't restore the latest backup, and without past history files, you are unable to do PITR to certain points in time.
The solution we used was to move the whole WAL archive to S3 Standard-Infrequent Access storage, dissolving the problem with lifecycle configurations while controlling costs. But you could also fix this by editing the lifecycle configuration.
The important thing is this: hold on to all history files. They're tiny text files, and when you need them, you really need them. This is also a good reason to test restores, not just of the latest backup, but of database states at arbitrary points in time.
Addendum: another very common problem we see is WAL archives that become corrupted because a client accidentally pointed a two primaries at the same WAL archive (for instance, they might have copied a postgresql.conf file by hand, or via a DevOps tool like Puppet). In this case, the whole archive is corrupted, and you're best off starting with a fresh S3 bucket or an empty directory and doing a new base backup immediately.
One of the many nice features of pgBackRest is that it will notice this and prevent you from doing it. Fewer footguns → better backups.
Monday, October 29, 2018
In particular, there are a bunch of ways to do case-insensitive searches for text:
- There's standard SQL ILIKE… but than can be expensive — especially if you put %'s at both start and end — and it's overkill if you want an exact string match.
- The same goes for case-insensitive regexp matching: overkill for simple case-insensitive matches. It does work with indexes, though!
- Then there's the citext extension, which is pretty much the perfect answer. It lets you use indexes and still get case-insensitive matching, which is really cool. It Just Works.
Without an index, a case-insensitive match like this…
sandbox# select addy from email_addresses where lower(addy) = 'email@example.com';
… is forced to use a sequential scan, lowercasing the addy column of each row before comparing it to the desired address:
Seq Scan on email_addresses (cost=0.00..1.04 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=1)
Filter: (lower(addy) = 'firstname.lastname@example.org'::text)
Rows Removed by Filter: 3
Planning time: 0.087 ms
Execution time: 0.051 ms
For my toy example, a four-row table, that's not too expensive, but for a real table with thousands or millions of rows, it can become quite a pain point. And a regular index on email_addresses(addy) won't help; the lower() operation forces a sequential scan, regardless of whether an index is present.
sandbox# create index email_addresses__lower__addy on email_addresses (lower(addy));
sandbox# explain analyze select addy from email_addresses where lower(addy) = 'email@example.com';
Index Scan using email_addresses__lower__addy on email_addresses (cost=0.13..8.15 rows=1 width=32) (actual time=0.093..0.095 rows=1 loops=1)
Index Cond: (lower(addy) = 'firstname.lastname@example.org'::text)
Planning time: 0.105 ms
Execution time: 0.115 ms
In short, expression indexes are one of those neat tricks that can save you a lot of pain.
Tuesday, October 23, 2018
Below I give the complete steps, for two different cases:
- The case where you migrate every user.
- The case where you migrate only a subset of users. Step 1 explains why you might need to do this. In this case, you can save time and complexity by creating a containing role, adding all the users you want to migrate to that role, and then configuring that role to work with scram-sha-256. That way you don't have to do it once for each user.
- First, decide which users to migrate to scram-sha-256. The ideal is to migrate everyone, all at once. In practice you may be unable to do that, for a few reasons:
- Older client tools don't support scram-sha-256. You need at least psql 10, or pgAdmin 4.
- Your app's client libraries may not support scram-sha-256. At a minimum, you will probably have to upgrade your client libraries2.
- And it's not enough just to upgrade your libraries; you also have to change the password for each user who's migrating, because PostgreSQL needs to hash this password in scram-sha-256 format. There's no automatic rehash (there can't be, since PostgreSQL doesn't store the cleartext of the old password).
- Finally, besides your own code, you may have third-party apps connecting to your database, whose library versioning policy and password reset policy you don't control.
… where alice, bob, and eve are the users you're moving to scram-sha-256. We'll set their passwords in a minute.
$ sudo su - postgres
postgres=# create role scram_sha_256_users nologin ROLE alice, bob, eve;
If, instead, you're migrating a subset of users, the pg_hba.conf edits depend on which users you're adding. As appropriate for your setup, alter or remove any lines that tell PostgreSQL to use md5 authentication for your affected users; then add equivalent lines for scram-sha-256 authentication.
password_encryption = 'scram-sha-256'
For instance, if those users were formerly allowed to log in to all DBs with an md5 password, either locally or from any IPv4 host, then you would do the following. Note that this is only an example, and the particulars depend on your existing setup!
# Local (Unix domain socket) connections:
local all +scram_sha_256_users scram-sha-256
# IPv4 connections, from any IP address:
host all +scram_sha_256_users 0.0.0.0/0 scram-sha-256
\set HISTFILE /dev/null
psql -U postgres, and set these settings (just for this one session):
set log_min_duration_statement = -1;
set log_statement = 'none';
set password_encryption = 'scram-sha-256';
alter role scram_sha_256_users set password_encryption = 'scram-sha-256';
alter role bob with password 'bobsecret';
alter role alice with password 'alicesecret';
With all that done, you are now on much more secure footing. Enjoy!
\set HISTFILE /dev/null
Support varies among libraries, though most wrap libpq, and hence should Just Work if compiled with libpq 10 (JDBC is a notable exception; it doesn't use libpq, so you need to get the right JDBC version).
Thursday, October 11, 2018
At this month's San Francisco PostgreSQL Users' Group, Sai Srirampur will explain HLL's workings, the postgresql-hll extension, and its applications in distributed PostgreSQL à la Citus.
Check it out!
Wednesday, September 5, 2018
Come join me Friday for a gentle introduction to locks in PostgreSQL. My example-driven talk covers basic lock theory, tools for lock debugging, and common pitfalls and solutions. I hope to see you there!
Time and place info is on the PostgresOpen SV website.
Sunday, July 9, 2017
Quite often I need to kick off a long-running process for a client, then resume work immediately once it's done. pg_restore, pg_basebackup, pg_upgrade, and vacuumdb --analyze-only all come to mind as examples. The ideal thing is to get a text message upon completion. When I'm working on my own boxes, I can just mail(1) my mobile carrier's text gateway, but I can't count on clients' servers having sendmail or the like set up (they usually don't).
Enter TextBelt. This service is a dead-simple HTTP-to-SMS gateway. Adapted from their docs:
curl -X POST https://textbelt.com/text \ --data-urlencode phone="$MY_MOBILE_NUMBER" \ --data-urlencode message='The process completed.' \ -d key="$MY_TEXTBELT_API_KEY"
Cleartext HTTP is also supported, in case the client box has broken SSL libraries. My texts are always nondescript, so I don't mind sending them in the clear.
The whole thing is open-source, so you can set up your own TextBelt server. Or you can be lazy and throw a few dollars their way for a certain number of texts. I rather like this business model, actually, as a way to support open-source work.