Posts tagged ‘labs’

8 December, 2014

PDXPUG lab report – BDR

by gorthx

For the last PDXPUG lab of the year, we tried out BiDirectional Replication (BDR). Five of us just set up VMs on our laptops and followed the instructions on the wiki.

We only had about 90 minutes time for this lab, so the goal was to get a basic configuration up & running, understand the available configuration parameters, and then (time permitting) break it – because that’s how you learn to put it back together.

Alexander said it worked very well in his tests; Robert set about breaking it and found an interesting edge case involving updates to primary keys. (Advisable or not, we all have a customer who’s going to do it!)

Maher and I were doing pretty well with our setups until we tried configuring BDR between our two machines. After wrestling with VMWare’s network settings and getting absolutely nowhere, I realized this all felt very familiar … Oh right, CentOS’s pre-configured firewall1. Which does not allow Postgres ports, natch. Once we fixed that, our machines could at last communicate correctly with each other, but we ran out of time before we could get BDR working between them. (Which led to some jokes about “NDR”.)

Craig Ringer posted yesterday about the work that’s gone into this project thus far, and some of the side benefits. BDR is a particularly tricky problem to solve; kudos to the team for all the hard work.

The Quick Start guide is very easy to follow. I’m also very happy with the quality of the log messages available from BDR. I encourage you to check it out for yourself!

1 – Took me a bit of poking around to find it; it was moved from “System Administration” to “Sundry” in CentOS 7.

Tags: ,
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: , ,
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: , ,