Re: Replication and coding good practices

On Mon, Jun 29, 2009 at 07:11:43PM +0800, Craig Ringer wrote:
> On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote:
> > > Are there any rules of thumb to consider for making an application
> > > easier to work with a "general" replication solution?
> > >
> > > The applications I mostly deal with are e-commerce sites.
> >
> > It really depends on what replication solution you choose, along with
> > the environment you're deploying into.
>
> ... and why you need replication. Reliability/Availability? Data storage
> redundancy? Performance? And if performance, read-mostly performance or
> write-heavy performance?

It's this kind of discussion that you might want to hire experts to
help with :) Commandprompt, Endpoint, OmniTI and the outfit I work
for, PostgreSQL Experts <http://www.pgexperts.com> would be examples.

> > That said, I've noticed that the things that are generally good
> > practice help you even more when you're doing replication.
> >
> > Practices I've seen help directly:
> >
> > * Separate read users and code from write users and code.
> >
> > * Separate DDL from both of the above.
> >
> > * Make DDL changes part of your deployment process and only allow them
> > in files which track in your SCM system.
>
> Version your schema, storing the schema version in a 1-row table or
> even as a stable function. This makes it much easier for deployment
> tools or staff to easily see what needs to be done to get the schema
> and app to the latest version - there's no "what the hell is the
> current state of this thing, anyway?" to worry about.

When versioning schemas, make sure your deployment tools are always
atomic and that the schema version can't be modified by anything but
those tools.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate