Обсуждение: [GENERAL] Backup strategy using 'wal_keep_segments'

On Mon, Oct 23, 2017 at 5:57 AM, Rhhh Lin <ruanlinehan@hotmail.com> wrote:
> Is this approach feasible? Assuming obviously, we have sufficient disk space
> to facilitate 1000 WAL files etc.

You expose yourself to race conditions with such methods if a
checkpoint has the bad idea to recycle past segments that your logic
is copying. So I would advise to not do that. Instead of using the
archive command, you should also consider using pg_receivexlog
combined with a replication slot. This brings way more control with
the error handling.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Greetings,

* Rhhh Lin (ruanlinehan@hotmail.com) wrote:
> A colleague recently suggested that instead of implementing an 'archive_command' to push archivable WALs to a
secondarylocation (for further backup to tape for example), we could instead persist the WAL files in their current
locationby setting the "wal_keep_segments" parameter to an extreme value e.g. 1000 and have the 'archive_command' do
nothing.

Michael's points are good and I wouldn't recommend using this archive
command either, but what isn't clear to me is what you're actaully
trying to solve by using such a method..?  You haven't said anywhere
what's wrong with archive_command (I know that there certainly are some
things wrong with it, of course, but there are solutions to a number of
those issues that isn't a hack like this ...).

Thanks!

Stephen

On Tue, Oct 31, 2017 at 9:53 AM, Rhhh Lin <ruanlinehan@hotmail.com> wrote:
> I would actually be an advocate for using a proper archive_command in order
> to facilitate a proper (Per the documentation) PITR and backup strategy.

You should avoid using your own fancy archive command. There are
things that WAL-E for this purpose, and even if you do make sure that
the segment data is flushed to disk before sending back 0 as exit code
to the client.

> However, a colleague had suggested such a creative approach (Possibly, less
> administrative overhead, ease of maintenance? I'm not sure) and I struggled
> to find any evidence online in blogs/white-papers/documentation that this
> was a feasible approach. That said, I couldn't find any info rejecting it as
> a method either, which led me to post here. Essentially, this was a
> difference of opinion on approach, and I was looking to gather information
> in order to make an informed opposing argument.

Backup methods should not be fancy, just reliable.

> My only thought now would be how could I decipher, within the sequence chain
> of WAL files, up to which file has the "archival" progressed to. i.e. which
> files are not susceptible to being called upon again for
> restartpoints/checkpoints. That is, where is my absolute point (or file) of
> archival using something along the lines of 'pg_current_xlog_location'.

There is the system view called pg_stat_archiver for that. If you are
using something like pg_receivexlog, pg_stat_replication is the way to
go to monitor the archiving progress.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Greetings,

* Rhhh Lin (ruanlinehan@hotmail.com) wrote:
> I would actually be an advocate for using a proper archive_command in order to facilitate a proper (Per the
documentation)PITR and backup strategy.
 

Glad to hear it.

> However, a colleague had suggested such a creative approach (Possibly, less administrative overhead, ease of
maintenance?I'm not sure) and I struggled to find any evidence online in blogs/white-papers/documentation that this was
afeasible approach. That said, I couldn't find any info rejecting it as a method either, which led me to post here.
Essentially,this was a difference of opinion on approach, and I was looking to gather information in order to make an
informedopposing argument.
 

I'd strongly suggest considering tried-and-true approachs instead of
coming up with something novel, particularly when it comes to backups
and recovery.

Thanks!

Stephen