Thread: Pg_xlog increase due to postgres crash (disk full)

On 01/22/2013 03:12 AM, Cliff de Carteret wrote:
> My database crashed a couple of days ago during an upgrade several
> seconds after committing a large transaction to the database. Eventually
> we found out that this was due to the disk being full as the transaction
> had created several gigs of data. A day or so later the disk is full
> again and PostgreSQL crashes due to the pg_xlog file taking up all of
> the disk space. I have cleaned up the drive to have so extra space which
> allows PostgreSQL to start again but the xlogs are still increasing. I
> have two errors in my pg_log:
>
> "WARNING: transaction log file "00000001000000A800000078" could not be
> archived: too many failures" and
>
> "LOG: archive command failed with exit code 1
> DETAIL: The failed archive command was: test ! -f
> /opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078 && cp
> pg_xlog/00000001000000A800000078
> /opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078"

I am not seeing it below, but just to be complete is wal_keep_segments
set to something greater than 0?


Just to be clear which xlogs are filling up, the ones in the original
data directory or in the archive directory?



>
> Postgres version 9.0.3 conf:
>
>   *
>
>
>     wal_level = hot_standby
>     archive_mode = true
>     archive_command = 'test ! -f
>     /opt/postgres/remote_pgsql/wal_archive/%f && cp %p
>     /opt/postgres/remote_pgsql/wal_archive/%f' # command to use to
>     archive a logfile segment
>     archive_timeout = 1800
>     max_wal_senders = 1
>     max_standby_archive_delay = 900s
>     max_standby_streaming_delay = 900s
>     default_statistics_target = 50 # pgtune wizard 2010-11-18
>     maintenance_work_mem = 480MB # pgtune wizard 2010-11-18
>     constraint_exclusion = on # pgtune wizard 2010-11-18
>     checkpoint_completion_target = 0.9 # pgtune wizard 2010-11-18
>     effective_cache_size = 5632MB # pgtune wizard 2010-11-18
>     work_mem = 48MB # pgtune wizard 2010-11-18
>     wal_buffers = 8MB # pgtune wizard 2010-11-18
>     checkpoint_segments = 16 # pgtune wizard 2010-11-18
>     shared_buffers = 1920MB # pgtune wizard 2010-11-18
>     max_connections = 80 # pgtune wizard 2010-11-18
>
>

--
Adrian Klaver
adrian.klaver@gmail.com

On 01/22/2013 06:59 AM, Cliff de Carteret wrote:
> Hello, thanks for your response.
>
> The xlogs that are filling up are in the original data directory and the
> wal_keep_segments is commented out on my master and slave postgresql.conf

It is expected that there may a certain increase in WAL files.
  In particular: checkpoint_segments = 16

http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS
"
checkpoint_segments (integer)
Maximum number of log file segments between automatic WAL checkpoints
(each segment is normally 16 megabytes). The default is three segments.
Increasing this parameter can increase the amount of time needed for
crash recovery. This parameter can only be set in the postgresql.conf
file or on the server command line.
"

For more information that explains the above see:
http://www.postgresql.org/docs/9.2/static/wal-configuration.html

More questions:

Does it look like any WAL files in the pg_xlog directory are being recycled?

Re your archive error from your previous post, is your archive directory
full?

     Assuming the archive directory is remote, is it reachable?

>
> Cliff
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Cliff de Carteret wrote:

> The current setup has been working successfully for several years
> until the recent database crash

What file does the server log say it is trying to archive? What
error are you getting? Does that filename already exist on the
archive (or some intermediate location used by the archive command
or script)?

-Kevin

On 01/22/2013 07:57 AM, Cliff de Carteret wrote:
> There are no files in the pg_xlog directory from before the database
> crash except one file which ends in .backup, there are 759 files. In the
> pg_xlog/archive_status folder there are 755 files.
> The local ../wal_archive folder has no files in it at all whereas the
> remote wal_archive folder has 147 files present.

What is this local wal_archive directory?
 From a previous post:

"LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: test ! -f
/opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078 && cp
pg_xlog/00000001000000A800000078
/opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078

I see only /opt/postgres/remote_pgsql/wal_archive which I assumed was
the remote. I am not sure where the local one fits in?

>
> The remote archive folder is not full and has ~14G whereas the local
> archive folder was previously full but now it has been moved so it has 100G+
>
> The archive directory is a mount and as the user postgres I can copy a
> file into it successfully.
>
> The current setup has been working successfully for several years until
> the recent database crash

So what did you do to get the database running again?

>
>



--
Adrian Klaver
adrian.klaver@gmail.com

[Please keep the list copied, and put your reply in-line instead
of at the top.]

Cliff de Carteret wrote:
> On 22 January 2013 16:07, Kevin Grittner <kgrittn@mail.com> wrote:
>
>> Cliff de Carteret wrote:
>>
>>> The current setup has been working successfully for several years
>>> until the recent database crash
>>
>> What file does the server log say it is trying to archive? What
>> error are you getting? Does that filename already exist on the
>> archive (or some intermediate location used by the archive command
>> or script)?

> The sever log is (repeated constantly):
>
> LOG: archive command failed with exit code 1
> DETAIL: The failed archive command was: test ! -f
> /opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078 && cp
> pg_xlog/00000001000000A800000078
> /opt/postgres/remote_pgsql/wal_archive/00000001000000A800000078
> WARNING: transaction log file "00000001000000A800000078" could not be
> archived: too many failures
>
> The file 00000001000000A800000078 exists in the remote archive's
> wal_archive directory. I read a post saying to copy the file over to the
> archive and then delete the .ready file to get postgres to move onto the
> next file but this ended up logging out saying that a log file was missing.
> There are more recent files in this directory but they end at the point
> where I reverted all of the changes I made last night when time was running
> out and the database had to be put back to a known state.

I would have deleted (or renamed) the copy in the archive
directory. Archiving should have then resumed and cleaned up the
pg_xlog directory.

-Kevin

Cliff de Carteret wrote:

> I have now deleted the copy on the remote wal_archive folder and the
> archiving is now functioning and sending the logs from the local to the
> remote folder. The remote database does not startup and the following is in
> the log:
>
> LOG: database system was shut down in recovery at 2013-01-22 10:54:48 GMT
> LOG: entering standby mode
> LOG: restored log file "00000001000000AB00000051" from archive
> LOG: invalid resource manager ID in primary checkpoint record
> PANIC: could not locate a valid checkpoint record
> LOG: startup process (PID 22350) was terminated by signal 6: Aborted
> LOG: aborting startup due to startup process failure
>
> 00000001000000AB00000051 is in my remote database's pg_xlog folder

Any chance that there was a pg_start_backup() call on the master without
a matching pg_stop_backup() call?

-Kevin