Re: 8.2 "real-time" recovery

On Wed, Aug 25, 2010 at 6:11 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> [Rearranged somewhat.  Please don't top-post, but put responses at
> the appropriate point in-line.  I've had to guess a bit at what was
> responding to what; apologies for any misinterpretation.]

Sorry about that:)

>
> Dragos Valentin Moinescu <dragos(dot)moinescu(at)gmail(dot)com> wrote:
>> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
>> I used rsync to create a base backup. Though I have 10 huge tables
>> (min 1GB each) that are modified several times a second, thus
>> creating a base backup hourly means I have to sync arround 10G
>> each time (which is pretty time consuming).
>
> Are you sure you used a rsync daemon, rather than having rsync on
> one end look directly at the files on the other end?  That can make
> a big difference in the performance for a situation like this.

I did not use rsyncd. I used "rsync -avz user(at)host:/pgsqldata
/localpgdata". Will read about rsyncd

>
>>> I am inferring that you want to bring your standby up to run
>>> read-only queries once per hour, and then resume replication?
>
>> I do not want to use the standby server. This is why I am pretty
>> happy with stop - start + recover.
>
> I'm not sure I understand you -- you don't need to run any read-only
> queries on the standby server?  Why are you stopping it once per
> hour?  Perhaps you just haven't properly implemented warm standby?

I do not need a read-only server. I just want a server that is almost
a mirror to the other server.

> (A warm standby accepts new WAL files as they arrive, to stay
> relatively up-to-date -- they never reach a "recovery completed"
> state unless told to do so, because the recovery script waits for
> the next file instead of failing.)
>

What I did in terms of warm standby:
1. base backup using rsync
2. rsync all wal files
3. edited recoverfy.conf with a recovery_command="cp /wal-archive/%f %p"

So I need to write a new recovery_command that actually process any
files from there, but waits for other files to arrive.
"recovery-command.sh %f %p"

Thank you

>> I cannot base backup anymore :(
>
> What do you mean by that?
>
> -Kevin
>

--
Best regards,
Dragos Moinescu