0

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?

3

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)

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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