Large pg_xlog
Postgresql 9.3
checkpoint_segments=6
wal_keep_segments=300
Machine is master for 2, asynchronous, slaves.
pg_xlog in /var/lib/postgresql/9.3/main/pg_xlog
NFS mount with WAL archiving in /opt/backups/walarchives/HostName
During a load of a file, using copy, the pg_xlog grew to almost 120GB
and one of the slaves fell behind (at another data center). Since I am
not using replication slots I expected WAL to be wall_keep_segments x
16MB = 4GB. Any idea why it grew so large?
Once I rsync wal archives to the slave and it catches up then WAL goes down.
Any ideas why pg_xlog is going so high?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27 September 2016 01:52:26 CEST, Francisco Reyes <lists@natserv.net> wrote:
Any ideas why pg_xlog is going so high?
archive_command failed? If that happens the wal's will not deleted, you should see error-messages in the log.
--
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 09/26/2016 08:08 PM, Andreas Kretschmer wrote:
archive_command failed? If that happens the wal's will not deleted,
you should see error-messages in the log.
The only times archive command failed I believe is because the volume
where pg_xlog is ran out of space.
FATAL: archive command was terminated by signal 3: Quit
There are 2 of those today. So that does not seem to be the cause.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Sep 27, 2016 at 9:26 AM, Francisco Reyes <lists@natserv.net> wrote:
The only times archive command failed I believe is because the volume where
pg_xlog is ran out of space.
FATAL: archive command was terminated by signal 3: QuitThere are 2 of those today. So that does not seem to be the cause.
What are the files in pg_xlog/archive_status? Do see a lot of .ready
entries? Perhaps you could de-bloat things by using archive_command =
'/bin/true' (REM on Windows as far as I recall).
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 09/26/2016 09:05 PM, Michael Paquier wrote:
What are the files in pg_xlog/archive_status? Do see a lot of .ready
entries? Perhaps you could de-bloat things by using archive_command =
'/bin/true' (REM on Windows as far as I recall).
There is no archive_status
ll 9.3/main/pg_xlog/ |grep arch
postgres@s0pdb03:~$
As for changing archive_command to /bin/true, don't see how that would
help. My archive command is pointing to a completely different folder.
Just try anyway, changed archive_command to bin true. Files in pg_xlog
keep growing. Currently at over 5K files. Isn't wal_keep_segments the
upper limit of how many wal files would be retained in Postgresql 9.3?
wal_keep_segments=300
checkpoint_segments=6
Yet have over 5K files, and more getting created, in my pg_xlog folder.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Sep 29, 2016 at 2:25 PM, Francisco Reyes <lists@natserv.net> wrote:
There is no archive_status
ll 9.3/main/pg_xlog/ |grep arch
postgres@s0pdb03:~$
Meh. There must be a folder named archive_status in pg_xlog. Or
something is broken with your installation. Don't you have in your log
files something like that?
LOG: could not create archive status file
"pg_xlog/archive_status/000000010000000000000001.ready": No such file
or directory
That's what you would get if archive_status was for some reason
missing, and pg_xlog would just keep the WAL segments around until its
partition gets full.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 09/29/2016 01:36 AM, Michael Paquier wrote:
something is broken with your installation. Don't you have in your log
files something like that?
LOG: could not create archive status file
"pg_xlog/archive_status/000000010000000000000001.ready": No such file
That was the problem.
FATAL: could not open archive status directory
"pg_xlog/archive_status": No such file or directory
Very puzzled on what could have deleted the folder. I am the only person
that works on these machines and I would never do any work, much less
deletions, inside pg_xlog.
Thanks for the help.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general