Обсуждение: Re: PostgreSQL backup idea
Renato Oliveira wrote: > I can't implement PITR right now on our live systems, for > commercial reasons. What do you mean? Most of your email seems to describe techniques very much like PITR; why would that be OK but the existing, tested PITR not be OK? It's hard to know what to suggest without understanding the answers to those questions. -Kevin
On Wed, Jan 20, 2010 at 6:48 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Renato Oliveira wrote:What do you mean? Most of your email seems to describe techniques
> I can't implement PITR right now on our live systems, for
> commercial reasons.
very much like PITR; why would that be OK but the existing, tested
PITR not be OK? It's hard to know what to suggest without
understanding the answers to those questions.
PITR is a one or two line update to the postgresql.conf + a base backup. What you're talking about is trying to build your own version of this (significantly more complex). What are the 'commercial' reasons that you have for not using PITR, ISTM that you'd really be wasting time not using it.
--Scott
I just noticed that the list has not been copied on most of this exchange. Please keep the list copied, as others are likely to contribute useful ideas. Renato Oliveira <renato.oliveira@grant.co.uk> wrote: > Can I ask few questions about it, if possible? Sure. Please keep it "on list", though. > We are running Linux Redhat 4. That should make building from source pretty easy. > [postgres@78674-db1 ~]$ pg_config > VERSION = PostgreSQL 8.2.4 > [postgres@db2 ~]$ pg_config > VERSION = PostgreSQL 8.3.4 Well, there's your problem, right there. Your primary is 8.2.4, but your secondary is 8.3.4. The other information matches nicely and there were no special build options -- it's just that there are two different major versions of PostgreSQL here, and PITR backups definitely won't deal with that. Is there a reason for having your secondary server on a newer major release (like there was some query which wouldn't run correctly or within a reasonable amount of time without it)? Wait -- I just had a thought. Old version copying to new version. Slony in the mix. This sounds like a situation where the old DBA was trying to use Slony to upgrade with minimal down time. Problem was, you haven't been able to keep the newer version up-to-date through Slony, so you couldn't cut over? If that's the situation, it changes the focus. And it also means we need someone who understands Slony in the discussion.... -Kevin
On Wed, 2010-01-20 at 10:04 -0600, Kevin Grittner wrote: > I just noticed that the list has not been copied on most of this > exchange. Please keep the list copied, as others are likely to > contribute useful ideas. > > Renato Oliveira <renato.oliveira@grant.co.uk> wrote: > > > Can I ask few questions about it, if possible? > > Sure. Please keep it "on list", though. > > > We are running Linux Redhat 4. > > That should make building from source pretty easy. > > > [postgres@78674-db1 ~]$ pg_config > > VERSION = PostgreSQL 8.2.4 > > > [postgres@db2 ~]$ pg_config > > VERSION = PostgreSQL 8.3.4 > > Well, there's your problem, right there. Your primary is 8.2.4, but > your secondary is 8.3.4. The other information matches nicely and > there were no special build options -- it's just that there are two > different major versions of PostgreSQL here, and PITR backups > definitely won't deal with that. > > Is there a reason for having your secondary server on a newer major > release (like there was some query which wouldn't run correctly or > within a reasonable amount of time without it)? > > Wait -- I just had a thought. Old version copying to new version. > Slony in the mix. This sounds like a situation where the old DBA > was trying to use Slony to upgrade with minimal down time. Problem > was, you haven't been able to keep the newer version up-to-date > through Slony, so you couldn't cut over? If that's the situation, > it changes the focus. And it also means we need someone who > understands Slony in the discussion.... If the issue here is the OP wants to move from 8.2.4 to 8.3.9 (as opposed to 8.3.4) with Slony, here is what to do. 1: Build 8.3.9 from source. 2: Build the same Slony version of slony that is running against the 8.3.9 tree 3 : Upgrade the 8.3.4 version to 8.3.9 (Slony is irrelevant here - normal minor version PG upgrade) 4: When ready to upgrade, issue the Slony Move Master command to move from the 8.2.4 node to the 8.3.9. 5: If you still need a Slony replica - drop the 8.2.4 node from replication and rebuild it as an 8.3.9 replica. OP if you need any more information about the Slony portions of this - please ask them on the Slony list. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.