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

The following bug has been logged on the website:

Bug reference:      14963
Logged by:          Raghav Rao
Email address:      raghavendrajsv@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Centos
Description:

pg_xlog file the log files are keep on increasing day by day. Due to this
disk is full unable to proceed further. Please help me.

checkpoint_segments = 50
wal_keep_segments = 80

> archive_mode = on
hmm. archive_command was configured according enabled archive_mode? Please show current archive_command value. And try
tocheck postgresql logs, it may contains some number fatal errors "archive command failed with exit code"
 

regards, Sergei

Hello
Empty archive_command with archive_mode = on prohibits purge old WAL.
If you will use archive_mode - you must provide correct command in archive_command (change archive_command need only
reload).Documentation can be found here:
https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL
Otherwise you must disable archive_mode (need restart) or use archive_command with any command returning 0. For example
archive_command= '/bin/true'. This is useful if you wish enable WAL archiving later without restart postgresql.
 

regards, Sergei

Raghavendra Rao J S V wrote:
> Thank you very much for your explanation.
> 
> 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.

I suggest you use a battle-tested tool such as https://pgbarman.org/ to
do the backups for you.  It comes with instructions about what to use in
archive_command, how to take base backups, simple mechanism to remove
old backups, easy to use restore operations, etc.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro, Raghavendra,

* Alvaro Herrera (alvherre@alvh.no-ip.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.

Of course, with any backup solution, it's essential that backups be
fully restored and tested to make sure that they work.

There are certainly other tools out there for doing backups of PG with
their own pros and cons and while I wouldn't recommend that people try
to write their own backup software, using pg_basebackup isn't bad and we
shouldn't be telling people that.

Thanks!

Stephen

On Thu, Dec 14, 2017 at 10:35 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Alvaro Herrera (alvherre@alvh.no-ip.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.
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 :)

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.
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.
3) Default parameters of postgresql.conf and pg_hba.conf have been
changed to ease the use of those tools, like max_wal_senders and
max_replication_slots with non-0 defaults, replication connections
allowed.

> Of course, with any backup solution, it's essential that backups be
> fully restored and tested to make sure that they work.

"Consider yourself as not having a backup unless you have tested that
you are able to restore it".

> There are certainly other tools out there for doing backups of PG with
> their own pros and cons and while I wouldn't recommend that people try
> to write their own backup software, using pg_basebackup isn't bad and we
> shouldn't be telling people that.

pg_basebackup has hundreds man-hours behind, if not more. Any custom
and quickly-written script will never match its quality.
-- 
Michael

Michael,

* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Thu, Dec 14, 2017 at 10:35 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * Alvaro Herrera (alvherre@alvh.no-ip.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