Re: pg_xlog and standby

On Jan 23, 2008, at 2:18 PM, Roberto Scattini wrote:

> On Jan 23, 2008 2:28 PM, Erik Jones <erik(at)myemma(dot)com> wrote:
>>
>> 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.
>>
>
> mmmmmmmm, ok. the problem that im having is that i have A LOT of
> archive files on pg_xlog dir, and thats because the archive_command
> keeps failing (the standby server had filled his disk with archives
> received but not proccesed), so now, i dont know how i can remove
> those files and start again...
>
>> 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/.
>>
>
> yeah, i agree. but now i have aprox 40GB of archive files in pg_xlog
> dir in the production server. :S

Watch your directory terminology. The WALs that have backed up
should be in $PGDATA/pg_xlog/archive_status/ not $PGDATA/pg_xlog/.
Since you are going to start from scratch with you're standby you're
free to delete all of the WAL files in $PGDATA/pg_xlog/
archive_status/ but leave any files directly under $PGDATA/pg_xlog
alone.

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