Archive for February, 2014

28 February, 2014

SCALE12x Report

by gorthx

Last week I headed to sunny LA for the SoCal Linux Expo, aka SCALE. This was my first time at SCALE & I’ll definitely be going back (it reminded me of my first OSCON several years ago).

I’ve wanted to go to this conference for a few years now & finally had a reason to go: I gave a talk at LAPgDay (aka the Postgres track) on Friday. The Pg talks were mostly SRO throughout the day! That’s pretty exciting. I had a lot of fun giving my talk about Pg’s vacuum & autovacuum processes & got some good feedback about it. Which reminds me: if you haven’t already, please fill out the speaker survey; there’s a link on the back of your badge.

On Saturday, I spent most of my time in the Postgres booth (of course) with friends old and new. I’m just about finished following up with folks who had questions for me, so if you haven’t heard from me yet, hang tight. :) I managed to take one quick spin through the Expo Hall, and have to say I am very intrigued with the DIYBio movement, and hope the PDX group gets something going soon. I also picked up yet another copy of The Manga Guide to Databases at the NoStarch booth, and gave it away the day I got home. I can’t seem to keep a copy of that book for myself!

Two Pg tips I picked up this weekend (thanks Steven & Joe):
1. “TABLE my_table;” is a handy alias for “SELECT * FROM my_table;”

2. The ‘AS’ keyword is not required to specify a field name for an alias. Apparently it’s been this way for a while, and I just managed to avoid being bitten by it until last weekend when I misplaced a comma.

21 February, 2014

Ideas for future PDXPUG workshops

by gorthx

The recent Streaming Rep Lab at PDXPUG was such an excellent learning experience. I really want to continue having these sessions.

Our definition of a “workshop” is pretty loose. There’s a basic list of topics we want to cover, but no real agenda or leader; it is truly a group effort.

Here are some ideas I’m toying with for future workshops.

– Choosing a High Availability plan
– Different ways to take backups
– Troubleshooting slow queries
– Monitoring (this could easily be a series on its own)
– Disaster Recovery
– Oh no, somebody deleted pg_xlog
– Transaction wraparound
– Postgres on zfs
– Postgres packet captures
– Tour of contrib modules
– Foreign Data Wrappers
– Benchmarking changes to GUCs, e.g. maintenance_work_mem.

Update: I started a wiki page, so other group members can add their ideas.

Tags: , ,
14 February, 2014

I’m speaking at SCALE next week

by gorthx

I’m giving my Autovacuum talk at SCALE next week. There’s a whole track of Postgres talks on Friday.

I’ll also be hanging out at the Pg booth on Saturday – come by & say hello!

7 February, 2014

PDXPUG labs – Streaming Rep Saturday

by gorthx

Several years back, a new fellow on a ride with my bike club had a rather serious crash. I’ll spare you the gory details here1, other than to say that he was very lucky we had two Wilderness First Responders, a nurse, and an Army medic with us. The experience made quite an impression on me, and I got a Wilderness First Aid certification a few months after that.

The way the NOLS courses work is you have lectures about eg “how to splint a broken arm with found materials”, then you divvy up into pairs or small groups for practice. One person is the “victim” and the other has to fix them. The first time I took the class, I “killed” most of my patients. I did much better the next few times, and have had several occasions to be grateful for these skills.

Over the past year, I’ve been thinking a lot about how training like this applies to our everday work as IT specialists. A lot of us inherit systems that may not be documented, or maybe the backup’s not actually running, etc. And then disaster strikes, and we think, “well, I know *in theory* what I’m supposed to do”. Good luck!

My point is, we need to practice this stuff. Preferably in a low-pressure environment where it’s ok to make mistakes. This is right up there with the concept that “you don’t have a backup unless you’ve successfully restored it recently”. Do many places encourage (or even allow) you to practice restores, though? I know of a few that do, but for the most part people are pretty silent about this and I suspect it’s because nobody’s doing “what’s right”.

This was my reasoning behind starting a lab series with PDXPUG. Streaming Rep seemed like a good candidate for the first lab; several PDXPUG members expressed interest, and I personally was feeling a bit rusty because I haven’t had to actually configure it since 9.1.

My goals were:
– create a checklist of steps to follow to set it up. This is one of those things you probably won’t have to do very often, but why not make it as streamlined as possible?
– figure out if it’s actually working correctly
– practice a fail over, practice restoring a standby, etc

The group recap is here.

The checklist is up on github; the group found a few mistakes in my original.

Additional cool tidbits I learned:
wal_level has to be hot_standby on the master, or the standby won’t start up. (Postgres is pretty good about letting you know exactly what the problem is, though.) I was unable to get around this without completely reimaging the master; I would like to dig into that further and see if there’s a shortcut.

SELECT * FROM pg_stat_replication; shows you the current rep status. Combine this with \watch for extra entertainment. Available on the master, and on standbys if you’re cascading. (Hmm, setting up cascading rep might be another fun lab…)

pg_xlogfile_name translates ID -> WAL name, also only available on the master.

\df *log* …somehow, all these years I have missed that you can pass globs to the \ commands.

You can actually ship and stream WALs, but be prepared for 2X the network load.

CREATE USER applies login privs; CREATE ROLE does not.

Items that we’ll be looking at during Streaming Rep part 22:
– what is the “right” # for max_wal_senders – is it just the number of standbys? Any reason you’d want to have more?
– what causes that “FATAL: database is starting up” problem, and how do you fix it?
– how the heck do we monitor this thing, and what is reason for alarm
– the “human-readable” WAL transform

Yes, there will be more labs. Watch the PDXPUG blog for announcements. If you’re in the Portland area, please come join us.



1 – I will tell them over beer, though.
2 – Thursday Feb 27, sign up here

Tags: , ,