Posted
11 days
ago
So there it is, this newer contribution of mine that I presented at PGDay is
now in 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
... [More]
lot of commands to either dump or restore a database. It
comes with documentation covering about it all, except for the londiste
support part, which will be there in time for 1.0.0 release. The Todo list
is getting smaller and smaller, the version you'll soon find in debian sid
is already called 0.9.
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
The pg_staging setup consists of one pg_staging.ini file wherein you
describe your different target databases (those dev and 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
use either 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 pgbouncer:
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
non-interactive SSH connection from the controler to the hosts where the
databases will get restored. Then you have to do a minimal setup pgbouncer
on the hosts with a trust connection. It'll get used from pg_staging for
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 londiste services.
See man 5 pg_staging for available options, including schemas to filter out
either completely or just skipping data restoring in those.
pg_staging usage
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 triggers
that are using a function which is defined in a filtered out schema, for
example a 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!
Roadmap to 1.0.0
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... [Less]
Posted
11 days
ago
by
pr...@datanamic.com
Datanamic is pleased to announce the release of DeZign for Databases V6.0, its database design and modeling tool.
Posted
12 days
ago
by
nos...@example.com (David Fetter)
Thanks to the Brisbane PHP/MySQL User Group for a lovely evening.
First, we...
Continue reading "Pre-OSDC"
Posted
12 days
ago
by
nos...@example.com (David Fetter)
Finally, the people doing all kinds of clustering in PostgreSQL have met and sketched out the issues.
Expect great things to come from this...
And that was before the official conference even got started.
Thanks to all the
... [More]
people who made the JPUG 10th Anniversary great!
Continue reading "PostgreSQL Conference Japan...and onward" [Less]
Posted
14 days
ago
The intro question of the night was "what is your favorite cupcake flavor?" The majority of answers were chocolate with blue frosting and sprinkles, which Dan Colish brought that night. Dan tried to distract us by talking about materialized views
... [More]
while people were munching on cupcakes. He successfully gathered people's attention by talking about materialized view's benefits for decision support, OLAP, replication, load balancing, and controlling data access.
Reminder: There will be no meeting in Decembers, but everyone is invited to the Winter Coders' Social Tuesday, December 8, 2009 from 6–11pm at NedSpace Old Town. Details for signing up are here: http://calagator.org/events/1250457765 [Less]
Posted
14 days
ago
A report from the Brazilian PostgreSQL conference in Portuguese is at:
http://pgcon.postgresql.org.br/2009/asl/pgcconbr-2009_prest_contas.pdf
Posted
15 days
ago
The PostgreSQL community and the database industry have a bewildering profusion of clustering product and projects, utilizing a plethora of technologies and optimized for a diversity of different requirements. Clusters are single-master
... [More]
, multi-master, middleware, database engine, application-based, statement-based, group communication, distributed table, partitioned database, federated, shared disk, shared memory, shared nothing, highly available, load balanced, and/or eventually consistent. [Less]
Posted
16 days
ago
UPDATE: See bottom of post.
We held a PostgreSQL cluster developer meeting on Thursday, November 19, 2009 in Tokyo. About 25 people were in attendance, and seven projects presented status updates. Projects represented included
... [More]
pgCluster, PostgresForest, Postgres-R, Streaming Replication (slated to be included in core for version 8.5), Postgres-2 (not quite available), GridSQL, the Skype Skytools team (Londiste), Bucardo and Slony. Details of our discussions are being documented on the PostgreSQL wiki, and we’ve started a new mailing list.
The group of developers came up with a list of features that they would appreciate being implemented in Postgres soon, and we will be filling out the details in the coming weeks.
Our first milestone as a group is to create a detailed matrix of features to help users more easily navigate between the different solutions. I’m also going to arrange for a documentation sprint, dedicated to creating introductory documentation for new database administrators interested in clustering technology for Postgres. I’ll report out in December about how this work is going!
Josh has also posted a summary of the cluster meeting, and our next actions. [Less]
Posted
16 days
ago
For the last several years, PostgreSQL has suffered from having numerous, incomplete, unpolished, and poorly-publicized clustering and replication tools. It's time to fix that.
Posted
17 days
ago
by
nos...@example.com (Andrew Dunstan)
H/T Crad for this one
