Drop and restore PostgresSQL database without losing settings

Question

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?

Answer 1

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)