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

On Mon, Dec 11, 2017 at 7:18 AM, <raghavendrajsv@gmail.com> wrote:

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 = 8
​0

This is not a bug report. Please use "pgsql-general@lists.postgresql.org"
for usage assistance requests. Stating your version and describing what
kinds of backups you are performing are necessary for help on this topic.

If you have limited drive space the choice of 80 for "wal_keep_segments" in
not advisable - though it is only a minimum and if your backup routine is
not working properly the behavior you are seeing can result.

David J.

Hi David,

We are using pg_basebackup to take the backup of the database. The version
of PostgreSQL is *9.2.9. *My Postgres database configs are as below.

wal_buffers = 16MB
max_wal_senders = 1
wal_level = archive
archive_mode = on
checkpoint_segments = 50
wal_keep_segments = 80
checkpoint_completion_target = 0.9
synchronous_commit = on

Do we have any command to minimize the wall files. Day by day number of wal
files are increasing due to that slowly disk will be full.

Regards,
Raghavendra Rao

On Mon, Dec 11, 2017 at 8:06 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, Dec 11, 2017 at 7:18 AM, <raghavendrajsv@gmail.com> wrote:

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 = 8
​0

This is not a bug report. Please use "pgsql-general@lists.postgresql.org"
for usage assistance requests. Stating your version and describing what
kinds of backups you are performing are necessary for help on this topic.

If you have limited drive space the choice of 80 for "wal_keep_segments"
in not advisable - though it is only a minimum and if your backup routine
is not working properly the behavior you are seeing can result.

David J.

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

archive_mode = on

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

regards, Sergei

Hi Sergie/David,

*archive_command and archive_timeout values are as below in configuration
file. *

#archive_command = '' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p
/mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables

Below error is there in several times in log file.

WARNING,01000,"archive_mode enabled, yet archive_command is not
set",,,,,,,,,""

Please help me what to do and what to set to resolve the same.

Regards,
Raghavendra Rao

On Tue, Dec 12, 2017 at 1:14 PM, Sergei Kornilov <sk@zsrv.org> wrote:

archive_mode = on

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

regards, Sergei

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

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

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.

We don't have any building standby server. In order to take the backup of
the database through pg_basebackup utility, we have enable below things.

wal_level = archive
archive_mode = on
max_wal_senders = 1

Through your update, I understand we haven't enabled archive_command
(#archive_command
= '') due to this number of wal files are keep on growing in pg_xlog. How
to meet my requirement? Please guide me .

Regards,
Raghavendra Rao

On Tue, Dec 12, 2017 at 2:32 PM, Francisco Olarte <folarte@peoplecall.com>
wrote:

Raghavendra :

On Tue, Dec 12, 2017 at 9:52 AM, Raghavendra Rao J S V
<raghavendrajsv@gmail.com> wrote:

Below error is there in several times in log file.
WARNING,01000,"archive_mode enabled, yet archive_command is not
set",,,,,,,,,""

This tells it all.

Archive mode tells postgres you want to keep all the log files, for
backup purposes or building standby or whatever.

To implement this postgres calls your archive_command to copy the log
files, and if it fails logs and KEEPS the file, assuming it will
succeed later, but it doesn't delete them, as postgres tries very hard
to avoid loosing any data.

In your case you should probably disable archive mode, since you are
not archiving. Or maybe implement archive command ( see the section on
archiving on the docs ).

Also note your are hitting this behaviour for archive_command,
explicitly written in the docs: "If archive_command is an empty string
(the default) while archive_mode is enabled, WAL archiving is
temporarily disabled, but the server continues to accumulate WAL
segment files in the expectation that a command will soon be provided.
"

This is for situation like a change in log storage, postgres
accumulates logs until you finish the switch, then you restart with a
new working archive command and all the logs are copied and deleted.

Francisco Olarte.

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425

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

Thanks for the support.

On 14-Dec-2017 7:05 PM, "Stephen Frost" <sfrost@snowman.net> 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.
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