Обсуждение: archive falling behind

How can the archive process fall behind? Postgres will never reuse WAL files which are not yet archived.

Regarding your question about slowing down WAL generation, that is not possible to do, unless you slow down the application which is doing the writing into the database.

Regards,

Strahinja

On Mon, Apr 22, 2013 at 6:03 PM, German Becker <german.becker@gmail.com> wrote:

Hi I am working with version 9.1.9. The dabase is in archiving mode. The archives are then sended to a hotstandby replication sever.

I have seen that under heavy load, the archive process will fall behind the WAL generation and thuse some WAL segments won't get archived so the replication stops. To recover from this I need to do a new cold backup and send it to the replication server. Is there a way to limit the WAL generation (i.e slowing down insterts and deletes) so as to prevent the archive from falling behind?

Thanks!!

German Becker wrote:
> From my experience, postgres will delete WAL (after checkpoint) regardless if they have been archived.
> Are you saying this is abnormal?

That would be quite abnormal.
Could it be that your archive_command has exit status 0
even if something goes wrong?

What are the archive settings?

Yours,
Laurenz Albe

Here is the archive part of the config:

archive_mode = on               # allows archiving to be done

                                # (change requires restart)

archive_command = '/var/lib/postgresql/scripts/archive_copy.sh %p %f'           # command to use to archive a logfile segment

#archive_timeout = 0            # force a logfile segment switch after this

                                # number of seconds; 0 disables

The archive coommand makes a local copy and then it copies to the backup server via ssh. Both copies are md5-checked and retried up to 3 times in case of failure. 

I have seen under heavy load that some WALs are skipped, some have less size, some are corrupted (i,e, the loop fails 3 times). 

I'm not sure about the return value (checking it). What is the expected behaviour of the archiver? Will it retry de archive if archive command returns differnt than 0? Will it retain the WAL segment until it is succesfuly archived?

On Fri, Apr 26, 2013 at 9:53 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

German Becker wrote:
> From my experience, postgres will delete WAL (after checkpoint) regardless if they have been archived.
> Are you saying this is abnormal?

That would be quite abnormal.
Could it be that your archive_command has exit status 0
even if something goes wrong?

What are the archive settings?

Yours,
Laurenz Albe

German Becker wrote:
> Here is the archive part of the config:
>
> archive_mode = on               # allows archiving to be done
>                                 # (change requires restart)
> archive_command = '/var/lib/postgresql/scripts/archive_copy.sh %p %f'           # command to use to
> archive a logfile segment
> #archive_timeout = 0            # force a logfile segment switch after this
>                                 # number of seconds; 0 disables

So the problem might be in that script.

> The archive coommand makes a local copy and then it copies to the backup server via ssh. Both copies
> are md5-checked and retried up to 3 times in case of failure.

archive_command should not retry the operation, but rather
return a non-zero return code.

> I have seen under heavy load that some WALs are skipped, some have less size, some are corrupted (i,e,
> the loop fails 3 times).
> I'm not sure about the return value (checking it). What is the expected behaviour of the archiver?
> Will it retry de archive if archive command returns differnt than 0? Will it retain the WAL segment
> until it is succesfuly archived?

See http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL

archive_command should exit with zero only if the
WAL segment was archived successfully.
PostgreSQL will retry and retain the WAL segment until
archival succeeds.

Yours,
Laurenz Albe

Hi I have reverted to cp as archive command, but know under heavy load (> 150 WAL segments  in a minute) it happens that some wal segments gets corrupted:

postgres@lemur:~/9.1/main/pg_xlog$ md5sum 000000010000001000000049

f1906d2745224430f811496df466203f  000000010000001000000049

postgres@lemur:~/9.1/main/pg_xlog$ md5sum ~/backups/wal/000000010000001000000049

7e73fe759e41e427497360a815f9d3e1  /var/lib/postgresql/backups/wal/000000010000001000000049

On Fri, Apr 26, 2013 at 10:55 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

German Becker wrote:
> Here is the archive part of the config:
>
> archive_mode = on               # allows archiving to be done
>                                 # (change requires restart)
> archive_command = '/var/lib/postgresql/scripts/archive_copy.sh %p %f'           # command to use to
> archive a logfile segment
> #archive_timeout = 0            # force a logfile segment switch after this
>                                 # number of seconds; 0 disables

So the problem might be in that script.

> The archive coommand makes a local copy and then it copies to the backup server via ssh. Both copies
> are md5-checked and retried up to 3 times in case of failure.

archive_command should not retry the operation, but rather
return a non-zero return code.

> I have seen under heavy load that some WALs are skipped, some have less size, some are corrupted (i,e,
> the loop fails 3 times).
> I'm not sure about the return value (checking it). What is the expected behaviour of the archiver?
> Will it retry de archive if archive command returns differnt than 0? Will it retain the WAL segment
> until it is succesfuly archived?

See http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL

archive_command should exit with zero only if the
WAL segment was archived successfully.
PostgreSQL will retry and retain the WAL segment until
archival succeeds.

Yours,
Laurenz Albe

From: German Becker <german.becker@gmail.com>
Subject: Re: archive falling behind

> Actually this seems like a very strange filesystem /hw problem. The
> wal segments keep "changing" even after I stoped the database and
> noone is supposly accesing it:

I've seen this before.  It was bad RAM.  I bet it's a hardware problem
for you as well... either bad RAM or a flaky disk.

In my case, I found it out because I got syntax errors in the
PostgreSQL logs: SELGCT was not a valid keyword.  It turns out
that the flaky RAM was flipping bit 1 occasionally, so it would
randomly add or subtract 2 from a byte.

Regards,

David.


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin