What to Do with WAL Files for Postgres Slave Reset

postgresqlpostgresql-9.1

So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error:

FATAL:  could not receive data from WAL stream: FATAL:  requested WAL segment 00000001000018F70000008A has already been removed

From the reading around this I've done, it appears that the only solution is to re-sync the slave with pg_start_backup() et al. Based on this, I have a few questions.

  1. Is there a better way of fixing the slave that I've simply missed or overlooked?
  2. Do I need to clear out the WAL files on the slave and/or master prior or during the backup?
  3. Does pg_start_backup lock the database during this time?

As requested, the log file can be found: http://pastebin.com/9F8vJh6R, have removed the rest of the file as its just 5 hours of the same repeated error

Many thanks

Best Answer

The message:

requested WAL segment 00000001000018F70000008A has already been removed

Means that the master hasn't kept enough history to bring the standby back up to date. Since you are using version 9.1, you can use pg_basebackup to create a new slave. We use a command like:

pg_basebackup -h masterhost -U postgres -D path --progress --verbose -c fast

This doesn't lock the master, and you don't have to rsync or call pg_start_backup() and friends.

Related Question