Re: How to upgrade from 9.1 to 9.2 with replication?

On Mon, Oct 29, 2012 at 10:23 AM, Claudio Freire <klaussfreire(at)gmail(dot)com>wrote:

> On Mon, Oct 29, 2012 at 9:09 AM, Matheus de Oliveira
> <matioli(dot)matheus(at)gmail(dot)com> wrote:
> >> > If you have incremental backup, a restore_command on recovery.conf
> seems
> >> > better than running rsync again when the slave get out of sync.
> Doesn't
> >> > it?
> >>
> >> What do you mean?
> >>
> >> Usually, when it falls out of sync like that, it's because the
> >> database is undergoing structural changes, and the link between master
> >> and slave (both streaming and WAL shipping) isn't strong enough to
> >> handle the massive rewrites. A backup is of no use there either. We
> >> could make the rsync part of a recovery command, but we don't want to
> >> be left out of the loop so we prefer to do it manually. As noted, it
> >> always happens when someone's doing structural changes so it's not
> >> entirely unexpected.
> >>
> >> Or am I missing some point?
> >
> >
> > What I meant is that *if* you save you log segments somewhere (with
> > archive_command), you can always use the restore_command on the slave
> side
> > to catch-up with the master, even if streaming replication failed and you
> > got out of sync. Of course if you structural changes is *really big*,
> > perhaps recovering from WAL archives could even be slower than rsync (I
> > really think it's hard to happen though).
>
> I imagine it's automatic.

If you don't set restore_command *and* get more segments than
max_wal_keep_segments, PostgreSQL will not read the archived segments (it
does not even know where it is actually).

> We have WAL shipping in place, but even that
> gets out of sync (more segments generated than our quota on the
> archive allows - we can't really keep more since we lack the space on
> the server we put them).
>

Yeah, in that case there is no way. If you cannot keep *all* segments
during your "structural changes" you will have to go with a rsync (or
something similar).
But that's an option for you to know, *if* you have enough segments, than
it is possible to restore from them. In some customers of mine (with little
disk space) I even don't set max_wal_keep_segments too high, and prefer to
"keep" the segments with archive_command, but that's not the better
scenario.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados PostgreSQL
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres