Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted (there were errors in the Write-Ahead Log) and I'm trying to dump, re-initialize, and restore it to get it back in a stable state.

However, it seems like initdb, the recommended way to create a fresh stable database, actually creates an entire database cluster. In order to run this command, I'd first have to drop my database cluster, but the documentation for the pg_dropcluster command says it removes "all files that belong to a given PostgreSQL cluster; that includes the data directory, the log file, and all configuration files." I don't want to delete the logs and configuration files, just the data.

Since there's only one user-created database in my cluster (i.e. the one I'm trying to restore), I could just connect to the cluster, drop that database, and recreate it from the dump. But then I wouldn't be running initdb, and maybe that would leave my database cluster in some corrupted state.

Is there a way to drop and restore the data contents of a cluster without deleting all of the logs and configuration settings? Alternatively, is it OK to just drop and restore the database to recover it from corruption?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

If you are concerned about your logfile and the postgresql.conf, just do a file system backup of them before running initdb.

If your database did suffer from a harddisk corrupted then it is probably advisable to run initdb to make sure everything (including the system tables) are re-created properly.

As the configuration files are just plain text (as are the logfiles) you can simply copy the backup over into the new data directory after running initdb.

(Note: I don't know what pg_dropcluster does. It is not a standard Postgres tool)

share|improve this answer
    
OK, I guess I'll just copy the log and configuration files and copy them back after the drop and restore. Thanks for the advice on running initdb. –  Edward Jul 17 '12 at 22:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.