rootninja

If your dump was performed with the “–clean” option, you can skip the reinitializing of your databases to avoid duplicating data. This option, used with the “pg_dumpall” command will cause the restore to clean out all the objects in the database before performing the restore. If you didn’t include this option, then just delete the data directory and reinitialize the database as shown below.

* Stop the postmaster service.

PGDATA=/var/lib/pgsql/data/ pg_ctl stop
or “/etc/init.d/postgres stop” if you have fancy init scripts.

* Delete the data directory

rm -rf /var/lib/pgsql/data

* Run initdb

/etc/init.d/postgres initdb

* Give yourself some access

edit pg_hba.conf
change “ident” to “md5″ for local connections
If you want to connect using external network interfaces or names that resolve to external ip’s, then add a new line for your subnet.

host    all         all         192.168.1.1/32          md5

I use “md5″ for all interfaces. Don’t use “trust” on this or any interface unless you really dont care about security.

* Start the database

PGDATA=/var/lib/pgsql/data/ pg_ctl start
or “/etc/init.d/postgres start” if you have fancy init scripts.

* Restore the data

psql -U postgres -f pg_dumpall_file postgres

The dump file is a plain text list of commands that restores all objects, or specific parts of the database if just used dump instead of dumpall.

You can also become the postgres user even if you don’t know the password or it isn’t even set. This allows you to just run “psql” without any arguments and connect right away.

$ psql

Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit