Yet Another PostgreSQL tool hits debian
So there it is, this newer contribution of mine that I presented at
debian NEW queue.
pg_staging will empower you with respect to what
you do about those nightly backups (
pg_dump -Fc or something).
The tool provides a lot of commands to either
restore a database. It
comes with documentation covering about it all, except for the
support part, which will be there in time for
1.0.0 release. The
is getting smaller and smaller, the version you’ll soon find in
is already called
So, how do you go about using this software, and what service it implements?
it’s all about deriving a staging environment from your backups
To validate backups, you want to restore them and check the database you get from them. And your developers will want to sometime refresh the database they’re working with. And you could have both an integration environment and a pre-live one: On the former, you develop new code atop a stable set of data; while on the latter you test stable enough code (ready to go live) on a set of data as near as live data as possible.
And you want to be flexible about it, so that there’s not a fulltime job to handle retoring databases each and every days, for project A integration or project B pre-live testing, or project C accounting snapshot. Or you name it.
And of course you want to have a single point of control of all your databases. Let’s call it the controler.
setting up pg_staging
pg_staging setup consists of one
pg_staging.ini file wherein you
describe your different target databases (those
prelive ones), and
of course where to get the production backups from. Currently you have to
serve the backups file in a format suitable for
pg_restore (that means you
pg_dump -Ft or
pg_dump -Fc) on an
apache folder. The produced
HTML will get parsed.
So you setup the
DEFAULT section with common settings, then one section per
target: the databases you want to restore. Tell
pg_staging where they are
host), etc, and it’ll be able to drive them.
In order to being able to host more than a single restored dump on a staging
server, for the same database, we use
pg_staging> pgbouncer some_db.dev some_db some_db_20091029 :5432 some_db_20090717 some_db_20090717 :5432 some_db_20091029 some_db_20091029 :5432
So as explained into the
pg_staging(1) man page, you have to open
SSH connection from the
controler to the
hosts where the
databases will get restored. Then you have to do a minimal setup pgbouncer
hosts with a
trust connection. It’ll get used from
adding newly restored database and have them accessible. Then you can also
switch the new database to being the virtual
some_db so that you avoid
editing any connection string on your softwares.
Also, install the
pgstaging-client package on every host you target. The
client is a simple shell script that must run as root (
sudo is used) in
order to replace your
pgbouncer setup or manage your
man 5 pg_staging for available options, including
schemas to filter out
either completely or just skipping data restoring in those.
Now you’re all setup, you can begin to enjoy using
pgstaging. Enter the
console and see what you have in there.
$ pg_staging Welcome to pg_staging 0.9. pg_staging> databases ... pg_staging> restore some_db.dev ... pg_staging> pgbouncer some_db.dev ... pg_staging> dbsizes --all some_db.dev ... pg_staging> psql some_db.dev some_db_20091125=#
And as you can see in
man pg_staging there are a lot of commands
already. You can for example obtain a new
pg_restore catalog from a dump
file, with some
schemas commented out. It will even comment out
that are using a
function which is defined in a filtered out
PGQ trigger. And much much more.
pg_staging will even allow you to
dump your production databases, but
consider installing a separate instance of it on the machine serving the
backups to your local network thanks to an
apache directory listing!
What’s remain to be done is testing and having
PITR based restoring to work,
and adding some documentation (tutorial, which this blog post about is; and
londiste support). At this point, unless some reader here asks for a new
feature (set), I’ll consider
pg_staging ready for
1.0.0. After all, we’re
using it about daily here :)
Consider commenting, you should be able to easily spot my private mail address…