Back from PgCon2010
This year’s edition has been the best pgcon ever for me. Granted, it’s only my third time, but still :) As Josh said the “Hall Track” in particular was very good, and the Dev Meeting has been very effective!
This time I prepared some slides to present the extension design and I tried hard to make it so that we get to agree on a plan, even recognizing it’s not solving all of our problems from the get go. I had been talking about the concept and design with lots of people already, and continued to do so while in Ottawa on Monday evening and through all Tuesday. So Wednesday, I felt prepared. It proved to be a good thing, as I edited the slides with ideas from several people I had the chance to expose my ideas to! Thanks Greg Stark and Heikki Linnakangas for the part we talked about at the meeting, and a lot more people for the things we’ll have to solve later (Hi Stefan!).
So the current idea for
extensions is for the
backend support to start with a
pg_config --sharedir/extensions/foo/control` containing
metadata. From that we know if we can install an extension
and how. Here’s an example:
name = foo version = 1.0 custom_variable_classes = 'foo' depends = bar (>= 1.1), baz conflicts = bla (< 0.8)
The other files should be
foo.conf. The only
command the user will have to type in order for using the extension in his
database will then be:
INSTALL EXTENSION foo;
For that to work all that needs to happen is for me to write the code. I’ll
keep you informed as soon as I get a change to resume my activities on the
git branch I’m using. You can already find my first attempt at a
Heikki’s talk about
Built-in replication in PostgreSQL 9.0 left me with lots of
thinking. In particular it seems we need two projects out of core to complete
9.0 has to offer, namely something very simple to prepare a base backup
and something more involved to manage a pool of standbys.
The idea I had listening to the talk was that it might be possible to ask the
server, in a single SQL query, for the list of all the files it’s using. After
all, there’s those
pg_read_file() functions, we could put
them to good use. I couldn’t get the idea out of my head, so I had to write
some code and see it running:
pg_basebackup is there at
github, grab a copy!
What it does is very simple, in about 100 lines of self-contained python code
it get all the files from a running server through a normal PostgreSQL
connection. That was my first
recursive query. I had to create a new function
to get the file contents as the existing one returns text, and I want
here, of course.
Note that the code depends on the
bytea representation in use, so it’s only
9.0 as of now. Can be changed easily though, send a patch or just
ask me to do it!
Lastly, note that even if
pg_basebackup will compress each chunk it sends over
libpq connection, it won’t be your fastest option around. Its only
advantage there is its simplicity. Get the code, run it with 2 arguments: a
connection string and a destination directory. There you are.
wal proxy, wal relay
The other thing that we’ll miss in
9.0 is the ability to both manage more than
a couple of
standby servers and to manage failover gracefully. Here the idea
would be to have a proxy server acting as both a
walreceiver and a
walsender. Its role would be to both
archive the WAL and
relay them to the real
Then in case of master’s failure, we could instruct this proxy to be fed from the elected new master (manual procedure), the other standbys not being affected. Well apart than apparently changing the timeline (which will happen as soon as you promote a standby to master) while streaming is not meant to be supported. So the proxy would also disconnect all the slaves and have them reconnect.
If we need such a finesse, we could have the
restore_command on the
prepared so that it’ll connect to the
proxy’s archive. Now on failover, the
standbys are disconnected from the stream, get a
WAL file with a new
archive, replay it, and reconnect.
That means that for a full
HA scenario you could get on with three
servers. You’re back to two servers at failover time and need to rebuild the
crashed master as a standby, running a base backup again.
If you’ve followed the idea, I hope you liked it! I still have to motivate some
volunteers so that some work gets done here, as I’m probably not the one to ask
to as far as coding this is concerned, if you want it out before
9.1 kicks in!
We also had a nice
Hall Tack session with
Marko Kreen and
about how to get a single general (enough) queueing solution for PostgreSQL. It
happens that the Slony queueing ideas made their way into
PGQ and that we’d
want to add some more capabilities to this one.
What we talked about was adding more interfaces (event producers, event format translating at both ends of the pipe) and optimising how many events from the past we keep in the queue for the subscribers, in a cascading environment.
It seems that the basic architecture of the queue is what
PGQ 3 provides
already, so it could even be not that much of a hassle to get something working
out of the ideas exchanged.
Of course, one of those ideas has been discussed at the Dev Meeting, it’s about deriving the transaction commit order from the place which already has the information rather than reconstructing it after the fact. We’ll see how it goes, but it started pretty well with a design mail thread.
I went to some other talks too, of course, unfortunately with an attention span far from constant. Between the social events (you should read that as beer drinking evenings) and the hall tracks, more than once my brain were less present than my body in the talks. I won’t risk into commenting them here, but overall it was very good: in about each talk, new ideas popped into my head. And I love that.
Conclusion: I’m addicted.
The social aspect of the conference has been very good too. Once more, a warm welcome from the people that are central to the project, and who are so easily available for a chat about any aspect of it! Or just for sharing a drink.
Meeting our users is very important too, and
pgcon allows for that also. I’ve
met some people I’m used to talk to via
IRC, and it was good fun sharing a beer
All in all, I’m very happy I made it to Ottawa despite the volcano activity, there’s so much happening over there! Thanks to all the people who made it possible by either organizing the conference or attending to it! See you next year, I’m addicted…