Re: New 8.4 hot standby feature

On Tuesday 27 January 2009 21:47:36 you wrote:
> Hi,
>
> On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien <gabi(dot)julien(at)broadsign(dot)com>
wrote:
> > Yes, the logs are shipped every minute but the recevory is 3 or 4 times
> > longer.
>
> Are you disabling full_page_writes? It may slow down recovery several
> times.

It looks like you found my problem. Everything I needed to know is described
here:

http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html

Setting checkpoint_timeout to 55 seconds speeds up the recovery to the level I
want. Ironically, it makes the pg_last_recovered_xact_timestamp() function
more reliable too on how up to date the replica is. I am not sure that I can
take this for granted however.

I will disable full_page_writes to make sure this agressive checkpoint_timeout
setting won't slow down my master database too much. Can I be reassured on
the fact that, if the master database crashes and some data is lost, at least
the replica would keep its integrity (even though it is not in sync)?

My settings:
full_page_writes = off
checkpoint_timeout = 55s
checkpoint_completion_target = 0.7
archive_mode = on
archive_command = './archive_command.sh "%p" "%f"'
archive_timeout = 60

Also, would it be possible to recompile postgresql by using a different size
(smaller) then 16M for WAL logs and would that be a smart thing to try?

Thanks a lot to all of you.

>
> > Thanks I will take a look at it. Also, I came across the record log
> > shipping feature too in my research:
> >
> > http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STAN
> >DBY-RECORD
> >
> > Could this help? If the logs are smaller then I could potentially afford
> > shipping then at a higher frequency.
>
> No. Even if the logs are shipped frequently, they cannot be applied until
> the log file fills.
>
> Regards,