Thread: 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?
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.
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.
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: Quit > > There 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
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.
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
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.