towards pg_staging 1.0
If you don’t remember about what pg_staging is all about, it’s a central console from where to control all your PostgreSQL databases. Typically you use it to manage your development and pre-production setup, where developers ask you pretty often to install them some newer dump from the production, and you want that operation streamlined and easy.
The typical session would be something like this:
pg_staging> databases foodb.dev foodb foodb_20100824 :5432 foodb_20100209 foodb_20100209 :5432 foodb_20100824 foodb_20100824 :5432 pgbouncer pgbouncer :6432 postgres postgres :5432 pg_staging> dbsizes foodb.dev foodb.dev foodb_20100209: -1 foodb_20100824: 104 GB Total = 104 GB pg_staging> restore foodb.dev ... pg_staging> switch foodb.dev today
The list of supported commands is quite long now, and documented too (it
comes with two man pages). The
restore one is the most important and will
create the database, add it to the
pgbouncer setup, fetch the backup named
.dump, prepare a filtered object list (more on that), load
SQL scripts, launch
VACUUM ANALYZE the database when
configured to do so, load the
SQL scripts then optionaly
pgbouncer setup to default to this new database.
The newer option is called
tablename_nodata_regexp, and here’s its documentation in full:
List of table names regexp (comma separated) to restore without content. The
This comes to supplement the
schemas_nodata options, that allows
to only restore objects from a given set of
schemas (filtering out triggers
that will calls function that are in the excluded schemas, like
Londiste ones) or to restore only the
TABLE definitions while skipping
TABLE DATA entries.
To setup your environment for pg_staging, you need to take some steps. It’s not complex but it’s fairly involved. The benefit is this amazingly useful central unique console to control as many databases as you need.
You need a
pg_staging.ini file where to describe your environment. I
typically name the sessions in there by the name of the database to restore
followed by a
You need to have all your backups available through
HTTP, and as of now,
served by the famous
mod_dir directory listing. It’s easy to add
support to other methods, but is has not been done yet. You also need to
have a cluster wide
--globals-only backup available somewhere so that you
can easily create the users etc you need from
You also need to run a
pgbouncer daemon on each database server, allowing
you to bypass editing connection strings when you
switch a new database
You also need to install the
client script, have a local
user and allow it to run the client script as root, so that it’s able to
control some services and edit
pgbouncer.ini for you.
I’m still using it a lot (several times a week) to manage a whole development and pre-production environment set, so the very low code activity of the project is telling that it’s pretty stable (last series of commits are all bug fixes and round corners).
Given that, I’m thinking in terms of
pg_staging 1.0 soon! Now is a pretty
good time to try it and see how it can help you.