8 Articles tagged “pg_staging”

I’ve been asked about my opinion on backup strategy and best practices, and it so happens that I have some kind of an opinion on the matter. I tend to think best practice here begins with defining properly the backup plan you want to implement. It’s quite a complex matter, so be sure to ask yourself about your needs: what do you want to be protected from? The two main things to want to protect from are hardware loss (crash disaster, plane in the data center, fire, water flood, etc) and human error ( UPDATE without a where clause).

On the PostgreSQL Hackers mailing lists, Andrew Dunstan just proposed some new options for pg_dump and pg_restore to ease our lives. One of the answers was talking about some scripts available to exploit the pg_restore listing that you play with using options -l and -L, or the long name versions --list and --use-list. The pg_staging tool allows you to easily exploit those lists too. The pg_restore list is just a listing of one object per line of all objects contained into a custom dump, that is one made with pg_dump -Fc.

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.

One of the most important feedback I got about the presentation of pgstaging were the lack of pictures, something like a bird-view of how you operate it. Well, thanks to ditaa and Emacs picture-mode here it is: Hope you enjoy, it should not be necessary to comment much if I got to the point! Of course I commited the text source file to the Git repository. The only problem I ran into is that ditaa defaults to ouputing a quite big right margin containing only white pixels, and that didn’t fit well, visually, in this blog.

At pgday there was this form you could fill to give speakers some feedback about their talks. And that’s a really nice way as a speaker to know what to improve. And as Magnus was searching a nice looking chart facility in python and I spoke about matplotlib, it felt like having to publish something. Here is my try at some nice graphics. Well I’ll let you decide how nice the result is: