Postgresql – How to update the database generated pg_basebackup through WAL files

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually.

Safely removing WAL

If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless:

• archive_mode is on, but archive_command is failing, so the server keeps on retrying the archive attempts until they succeed or the admin intervenes;
• It's still preserved by wal_keep_segments
• (in 9.4) It's still needed by a replication slot.

If none of those apply, a CHECKPOINT (either automatic, or manually issued via SQL) will remove all WAL that's not currently required. So you don't have to delete it by hand.

In unusual circumstances you might need to use pg_archivecleanup, like if you've run out of disk space due to WAL accumulation after archiving has failed for a long time. You might decide to accept that you'll have to re-create your replicas as a result of throwing away WAL they still need and use pg_archivecleanup to free space to get the master running.

But you should never delete WAL segments by hand.

Recovering with archived WAL

If you kept the archived WAL somewhere else, you might just be able to copy the files back into pg_xlog, or create a recovery.conf with a restore_command to do so. See the manual on PITR and log shipping for details.

Recovering without archived WAL

If you don't have a copy of those WAL files somewhere else, you should restore from a backup if you have a recent backup, because you have corrupted your database.

If you do not have a backup, follow the instructions in the corruption wiki page and only once you have made a complete copy of the current state of the database, as a last resort only, use pg_resetxlog to throw away the transaction logs and force the DB to start with incomplete transactions.

You must then pg_dump the database, stop it, initdb a new one, and restore to it. Do not keep using the database you damaged. Never keep using a database you used pg_resetxlog on, and never use it except as a last resort.

The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived). You can force the system to archive some files to verify manually if you want - to do this run the following:

select pg_start_backup('testing_archiving');
select pg_stop_backup();

Once pg_stop_backup() is finished, you should see a few files in the archive directory.

Postgres uses the archive status folder to record "notes" about archiving attempts and so on with regard to the WAL files.