Profile cover photo
Profile photo
Quinn Weaver
8 followers -
PostgreSQL, Python, Perl, and Unix tidbits
PostgreSQL, Python, Perl, and Unix tidbits

8 followers
About
Posts

Post has attachment
A word of praise for TextBelt
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 c...
Add a comment...

Post has attachment
The PATH of cron
Short version Postgres Plus keeps psql out /usr/bin, so you need to set PATH in your cron jobs (including for WAL-E). Longer version Like all good people, I set up a cron jobs to run nightly WAL-E base backups. With one client, this failed the first time: w...
Add a comment...

Post has attachment
Change autovacuum_freeze_max_age without a restart (sort of…)
Short version:

I came up with a useful/terrible hack the other day: setting autovacuum_freeze_max_age as a storage parameter. I definitely don't recommend doing this routinely, but it unblocked us during a critical maintenance window.

ALTER TABLE my_table...
Add a comment...

Post has attachment
Language thoughts
All the languages I've used heavily have one primitive data structure you fall into using as a golden hammer: C: arrays Lisp: lists Perl: hashes JS: "objects" Et cetera. Python is borderline; it's tempting to abuse dicts, but there's a bit less friction to ...
Add a comment...

Post has attachment
Blocked by rdsadmin
One of our clients on RDS had a VACUUM FREEZE that was hanging for a long time. "I bet it's waiting on a lock," I thought. Yup, but the curious part is what it was waiting on: a mysterious process run by the rdsadmin role (see RECORD 2, below): SELECT pg_st...
Add a comment...

Post has attachment
Reflections on (Humans) Trusting (Humans') Trust
One thing I've learned from consulting is that you should trust a competent person's feels about a technology, even if they can't immediately come up with arguments to support them. Seemingly vague statements like "Oh, I hate the Foo webserver, it's so flak...
Add a comment...

Post has attachment
Baidu Bot Blues
A client complained of mysterious connection spikes. "We think we're getting hit by bots, but we're not sure," they said; "Can you look into it?" So I edited postgresql.conf, turned on log_connections and log_disconnections, did a 'pg_ctl -D MY_DATA_DIR rel...
Add a comment...

Post has attachment
syslog Considered Dangerous
A client of ours was suffering mysterious, intermittent lock pileups on a large table. To diagnose the problem, I ran our lock-logging scripts . Querying the resulting log_transaction_locks table, I saw lots of extend locks and RowExclusiveLocks piling up b...
Add a comment...

Post has attachment
RDS for Postgres: List of Supported Extensions
Today I learned that Amazon doesn't keep any list of extensions supported in PostgreSQL. Instead, their documentation tells you to start a psql session and run 'SHOW rds.extensions'. But that creates a chicken-and-egg situation if you have an app that needs...
Add a comment...

Post has attachment
Putting stats_temp_directory on a ramdisk
This hack is an old chestnut among PostgreSQL performance tuners, but it doesn't seem to be widely known elsewhere. That's a shame, because it's pure win, and it's ridiculously easy to set up. You don't even need to restart PostgreSQL. Here's the situation:...
Add a comment...
Wait while more posts are being loaded