Re: pg_xlog and standby

On Jan 23, 2008, at 9:28 AM, Roberto Scattini wrote:

> hello everybody:
>
> im trying to reconfigure a warm-standby server. the problem is that
> for some reason, one day the standby server stopped recovering the
> archives. this leaded to a full disk on that server, so i turned off
> (commented) the archive_command on the main server.
> i want to restart the procedure described in
> http://www.postgresql.org/docs/8.1/interactive/backup-
> online.html#BACKUP-PITR-RECOVERY
> but i dont know how to "safely clean" the main server $DATA/pg_xlog/
> dir.
> with "safely clean" i mean how do i know which archives can i delete
> (or move somewhere) without disrupting the normal operation of the
> server.
>
> im using postgres 8.2.5 from source on debian etch.
>
> thanks in advance!

You don't. The main server should not be keeping archived WAL files
directly in pg_xlog/. As it queues WAL files to be archived it puts
them in pg_xlog/archive_status/ with file names suffixed with .ready,
once they are archived that suffix changes to .done after which, at
some point (I'm not sure how long/many) they are removed.

Now, if you took your standby server offline, but didn't disable your
archive_command then you've basically been accumulating WALs with
the .ready prefix in the archive_status directory that, if you're
going to start from scratch with your standby, you can safely
delete. Just make sure you have a couple of WAL files successfully
archived (suffix has changed to .done in the archive_status dir and
you've verified that they've reached whatever directory your standby
expects them to be in) before call pg_start_backup() and starting
your new base backup.

IMO, the most important point to be had here is DO NOT delete WALs
that sit directly under pg_xlog/. Mistakes with the rest can be
worked with, you could run into serious problems with your primary
when deleting WALs directly under pg_xlog/.

Also, do you know why your standby stopped recovering? I'd say you
should make sure you know why and how, otherwise you run the risk of
the same thing happening again.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com