Postgres not starting after deleting pg_xlog files

Question

Using Debian Wheezy, Postgresql 9.3

My database went down because the section in which it stores WAL files was full. So, I deleted everything inside ./pg_xlog/ because I didn’t know what they were (yes, incredibly stupid from me). Now the Postgres service will not start, although the problem is according to syslog:

 00000: could not open tablespace directory "pg_tblspc/16386/PG_9.3_201306121": File or directory not found LOCAL: RelationCacheInitFileRemoveInDir, relcache.c:4895 00000: Primary checkpoint record is invalid LOCAL: ReadCheckpointRecord, xlog.c:6543 00000: Secondary checkpoint record is invalid LOCAL: ReadCheckpointRecord, xlog.c:6547 PANIC: XX000: could not locate a valid checkpoint record LOCAL: StartupXLOG, xlog.c:5228 

I'm not quite sure the problem is that it cannot find the correct pg_tblspc or the complete absence of WAL checkpoint files. The actual path to the database storage location is /dados/PG_9.3_201306121 . What can I do to start the service again?

EDIT1: Well, I was able to bring this news back online. Some databases have been corrupted. I managed to execute DROPDB two of them (they could not even connect to them without forcibly restarting the service). I tried to do this with the other that got damaged, but the error was again related to xlog. I tried to do a clean recovery on it, but the recovery was incomplete. Then I created a new database and tried to restore the old backup of this database. It was also incomplete.

Now I can’t refuse any databases and not create new ones, I always get xlog flush request not satisfied . I tried running pg_resetxlog , but it didn't seem to do anything. Other that the error shows: cannot write to block 1 of pg_tblspc/16385/PG_9.3_201306121/36596452/11773 , write error may be permanent .

EDIT2: Part of the problem above was with this 11773 file. I renamed it to 11773.corrupt, and now the database allows me to create and drop again.

Answer 1

Postgres not starting after deleting pg_xlog files

Um, yes. Do not do that.

What can I do to start the service again?

Well, you damaged your database. Restore from backups. You have backups, right? Preferred is a convenient PITR archive, for example, from PgBarman, where you can restore up to 5 minutes ago. No?

OK, first copy the damaged copy. https://wiki.postgresql.org/wiki/Corruption

Now. If you are lucky, pg_resetxlog will deliver you and run enough to successfully execute the pg_dump database, so you can move the old damaged datadir aside, initdb new and restore the database to it.

If you're out of luck, pg_dump will fail, or you will receive recovery failures due to things like duplicate primary keys. In the latter case, you may have to manually repair the dump. If pg_dump fails, the appropriate action will depend on why it does not work.

So yes. Do not delete pg_xlog .

There is a discussion in the PostgreSQL community about renaming pg_xlog to something that makes it more obvious that it is an important database component, and hopefully this will be done in version 9.7.