Re: BUG #14963: Number of wal files are keep on increasing

Michael,

* Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
> On Thu, Dec 14, 2017 at 10:35 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Alvaro Herrera (alvherre(at)alvh(dot)no-ip(dot)org) wrote:
> >> Raghavendra Rao J S V wrote:
> >> > We are taking the backup of database everyday using pg_basebackup utility. We
> >> > have implemented a pg_basebackup utility through shell script.This will be
> >> > invoked everyday at 2AM and takes the backup of database.
> >>
> >> This is a bad idea. If you don't have a very firm grasp of how to write
> >> a backup system, it's better not to write it yourself, because there are
> >> many possible errors you can make that can lead you to end up with
> >> backups that look ok but are actually invalid.
> >
> > Using pg_basebackup is *not* a bad idea, it's a good tool and works just
> > fine if used properly for a lot of environments. The biggest concern
> > with pg_basebackup is making sure that the WAL is streamed with the
> > backup and kept (using --xlog-method=stream) and that you're checking
> > the results of the command to make sure it didn't error out.
>
> What I understand here is Alvaro telling that a shell script mimicking
> pg_basebackup behavior is a bad idea, as Raghavendra seems to mean.

Erm, apologies for misunderstanding then, I was only reading what was
included in the reply and figured the comment made about "implemented a
pg_basebackup utility through shell script" was simple misphrasing for
"implemented a shell script using pg_basebackup."

> And that's right to not encourage people to do that. pg_basebackup is
> a robust and excellent tool, maintained with the core code and we've
> made things better with the latest releases, like improving. It is
> used everyday in my internal company stuff and we don't regret it, so
> use it :)

I agree that people shouldn't be trying to write their own filesystem
backup utility. Having a shell script which calls pg_basebackup is
a reasonable approach, though not what I'd typically recommend except
for very small systems.

> Note also a couple of things which have changed in Postgres 10 to ease
> pg_basebackup's use:
> 1) --xlog-method (actually renamed to --wal-method) uses the stream
> method by default.

Right, which is great.

> 2) By default, again, a temporary replication slot is used so as you
> don't need to worry about WAL segment rotation caused by segments.

Yeah, this is a pretty important piece as older versions did have the
risk that WAL could end up being reused before pg_basebackup got it.
That said, pg_basebackup would complain and throw an error if any of the
WAL was missing, which is why it's important to make sure to check for
any errors coming back from the pg_basebackup run, and to always restore
the backup to test both the restore procedure and that the backup was
done properly and successfully.

Thanks!

Stephen