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.

Usage

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 dbname.date -I.dump, prepare a filtered object list (more on that), load pre SQL scripts, launch pg_restore, VACUUM ANALYZE the database when configured to do so, load the post SQL scripts then optionaly switch the pgbouncer setup to default to this new database.

Filtering

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 and 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 e.g. Londiste ones) or to restore only the TABLE definitions while skipping the TABLE DATA entries.

Setup

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 dev or preprod extension.

You need to have all your backups available through HTTP, and as of now, served by the famous apache 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 pg_staging.

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 version live.

You also need to install the client script, have a local pgstaging system 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.

Status

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.