Re: Streaming Replication and archiving

I've been having a look at this, one master + one replica and also one
master + 2 replicas. I gotta say this is a nice piece of functionality
(particularly the multiple replicas).

I've been using the wiki page
(http://wiki.postgresql.org/wiki/Streaming_Replication) as a guide, and
I notice that it recommends the master (and replicas) have a non-trivial
archive_command even after the backup step is completed. ISTM that after
the backup the master's archive_command can be set to '' or '/bin/true'
as the walsender does not make any use of the WAL archive (AFAICS
anyway). Clearly it might be desirable to have the archived segments
around for other reasons - but equally it might be desirable *not* to
have to have to (e.g disk space), or am I overlooking something?

Cheers

Mark

Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
> I've been using the wiki page
> (http://wiki.postgresql.org/wiki/Streaming_Replication) as a guide, and I
> notice that it recommends the master (and replicas) have a non-trivial
> archive_command even after the backup step is completed. ISTM that after the
> backup the master's archive_command can be set to '' or '/bin/true' as the
> walsender does not make any use of the WAL archive (AFAICS anyway). Clearly
> it might be desirable to have the archived segments around for other reasons
> - but equally it might be desirable *not* to have to have to (e.g disk
> space), or am I overlooking something?

I think it's still necessary in the case the connection between a slave
and the master breaks. If the transient error is long enough for the
slave requesting WALs that the master no longer has, restore_command
will get used.

IIUC from the mails here, the restore_command is run directly by the
slave itself, so it needs to have access to master archives embedded in
the restore_command.

Take all this with a huge grain of salt, that's my understanding without
having had the time to read the patch or play with it yet.

Regards,
--
dim

PS: maybe the states and transitions and what data flows from where to
where in each of those would be good at least in the documentation, if
not explicit in the code?

Dimitri Fontaine wrote:
> Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
>
>> I've been using the wiki page
>> (http://wiki.postgresql.org/wiki/Streaming_Replication) as a guide, and I
>> notice that it recommends the master (and replicas) have a non-trivial
>> archive_command even after the backup step is completed. ISTM that after the
>> backup the master's archive_command can be set to '' or '/bin/true' as the
>> walsender does not make any use of the WAL archive (AFAICS anyway). Clearly
>> it might be desirable to have the archived segments around for other reasons
>> - but equally it might be desirable *not* to have to have to (e.g disk
>> space), or am I overlooking something?
>>
>
> I think it's still necessary in the case the connection between a slave
> and the master breaks. If the transient error is long enough for the
> slave requesting WALs that the master no longer has, restore_command
> will get used.
>
> IIUC from the mails here, the restore_command is run directly by the
> slave itself, so it needs to have access to master archives embedded in
> the restore_command.
>
> Take all this with a huge grain of salt, that's my understanding without
> having had the time to read the patch or play with it yet.
>
>
Thanks Dimitri, I'd missed that thread. Ok, slave will need a suitable
restore_comand in addition to primary_conninfo in recovery.conf, and
then extended communication failures (or shutting down the slave for a
while!) will not break the streaming setup (FWIW I tried this just now).

regards

Mark

> Thanks Dimitri, I'd missed that thread. Ok, slave will need a suitable
> restore_comand in addition to primary_conninfo in recovery.conf, and
> then extended communication failures (or shutting down the slave for a
> while!) will not break the streaming setup (FWIW I tried this just now).

Sure, but if the archived WAL segments are NOT needed, how are they
supposed to get deleted? It doesn't take long to run out of disk space
if they're not being rotated.

Masao-san, can you comment on this?

--Josh

Josh Berkus wrote:
>> Thanks Dimitri, I'd missed that thread. Ok, slave will need a suitable
>> restore_comand in addition to primary_conninfo in recovery.conf, and
>> then extended communication failures (or shutting down the slave for a
>> while!) will not break the streaming setup (FWIW I tried this just now).
>>
>
> Sure, but if the archived WAL segments are NOT needed, how are they
> supposed to get deleted? It doesn't take long to run out of disk space
> if they're not being rotated.
>
>

+1

From what I am seeing at the moment (8.5 devel from 2 days ago), the
archived segments are not deleted at all (I have several hundred now
after a number of pgbench runs over the last day or so).

regards

Mark

Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
> Josh Berkus wrote:
>> Sure, but if the archived WAL segments are NOT needed, how are they
>> supposed to get deleted? It doesn't take long to run out of disk space
>> if they're not being rotated.

> From what I am seeing at the moment (8.5 devel from 2 days ago), the
> archived segments are not deleted at all (I have several hundred now
> after a number of pgbench runs over the last day or so).

Huh? *Archived* segments aren't supposed to get deleted, at least not
by any automatic Postgres action. It would be up to the DBA how long
he wants to keep them around.

regards, tom lane

Tom Lane wrote:
> Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> writes:
>
>> Josh Berkus wrote:
>>
>>> Sure, but if the archived WAL segments are NOT needed, how are they
>>> supposed to get deleted? It doesn't take long to run out of disk space
>>> if they're not being rotated.
>>>
>
>
>> From what I am seeing at the moment (8.5 devel from 2 days ago), the
>> archived segments are not deleted at all (I have several hundred now
>> after a number of pgbench runs over the last day or so).
>>
>
> Huh? *Archived* segments aren't supposed to get deleted, at least not
> by any automatic Postgres action. It would be up to the DBA how long
> he wants to keep them around.
>
>
>

Exactly - there was a comment in the 'retry from archive' thread that
suggested otherwise. The likely typical use case for streaming
replication makes a good case and automated safe way of pruning these
guys - I've seen a few cases where overly aggressive cleanup has broken
log shipping setups (usually 8.2, before the restart option was available).

regards

Mark

Mark Kirkwood wrote:
> The likely typical use case for streaming replication makes a good
> case and automated safe way of pruning these guys

Sorry, stupid typo: should read '...makes a good case for an automated
safe way of pruning these....'

> Huh? *Archived* segments aren't supposed to get deleted, at least not
> by any automatic Postgres action. It would be up to the DBA how long
> he wants to keep them around.

OK. The docs indicated that the segments needed to be kept around in
case the slave fell behind. If that's not the case (as it appears not
to be) then they can just be deleted by cron job, or the archive_command
on the master can be changed.

Presumably, however, if the slave falls sufficiently behind and there
are no archive logs, then the slave would not be able to resynch with
the master, no?

--Josh Berkus

On Thu, Jan 21, 2010 at 10:48 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Presumably, however, if the slave falls sufficiently behind and there
> are no archive logs, then the slave would not be able to resynch with
> the master, no?

In that case, we would need to make a fresh backup of the primary,
and start the standby from that data.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Jan 21, 2010 at 1:48 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Huh?  *Archived* segments aren't supposed to get deleted, at least not
>> by any automatic Postgres action.  It would be up to the DBA how long
>> he wants to keep them around.
>
> OK.  The docs indicated that the segments needed to be kept around in
> case the slave fell behind.  If that's not the case (as it appears not
> to be) then they can just be deleted by cron job, or the archive_command
> on the master can be changed.

It's definitely the case.

Generally speaking each base backup image has an oldest archived log
which is needed to make it useful. And each standby database -- which
is just a recovered base backup which has been rolled forward some
distance already -- also has one.

What would be useful is a tool which given a list of standby databases
and list of base backup images can apply a set of policy rules to
determine which base backups and archived logs to delete.

The policy might look something like "keep one base backup per week
going back a month and one per day going back seven days and keep
archived logs going back far enough for any of these base backups or
any of these live replicas."

Bonus points if you can say "also keep one base backup per month going
back three years with just enough archived logs to recover the base
backup to a consistent state".

--
greg

Greg Stark <gsstark(at)mit(dot)edu> wrote:

> What would be useful is a tool which given a list of standby
> databases and list of base backup images can apply a set of policy
> rules to determine which base backups and archived logs to delete.
>
> The policy might look something like "keep one base backup per
> week going back a month and one per day going back seven days and
> keep archived logs going back far enough for any of these base
> backups or any of these live replicas."
>
> Bonus points if you can say "also keep one base backup per month
> going back three years with just enough archived logs to recover
> the base backup to a consistent state".

Hmmm... Our policy is "Keep the most recent base backup and all WAL
files from the point needed to use it to current on the backup
server local to the source database, keep the most recent two weekly
backups and all the WAL files from the point needed to start the
earlier to current on the central backup server (along with a warm
standby instance running for each source to confirm that the backup
and WAL files are usable), and keep the first weekly backup of each
month and just enough WAL files for a consistent start of each on a
mirrored SAN archive for one year."

Just in case you're looking for real-life policies currently in use.

By the way, the .backup files and the information from
pg_controldata and pg_ctl status currently provide just enough
information for this all to be run from bash scripts without much
human attention, but the effort required to get there is far from
trivial. I'm sure that something which made such a policy easy to
implement would be useful to some shops. Hopefully that can be done
without breaking current scripts.

-Kevin