PostgreSQL – How to Recover Clobbered Default Database

postgresql

I created a script to drop a particular database and then reload just the structure. I created it using phppgsql.

I'm still new to pgsql, so I simply ran the script using \i myCreateScript.sql with the psql client. What happened was that it clobbered the default postgres database, and now all databases are utterly unusable. I didn't figure out until later that I was supposed to do \c myDatabase first to set the current database.

So.. What do I do now? I'm running on a linux server. Should I purge postgres and re-install? Is there another way to recover from this?

Thanks in advance.

Best Answer

Your question is very unclear (see comment) but I'm going to make a guess at it anyway.

I think you DROPped the postgres database. You're used to always connecting to PostgreSQL as the postgres superuser using the postgres database, probably via sudo and peer authentication with something like:

sudo -u postgres psql

where you don't specify a database name to connect to. So with no postgres database, you don't know how to connect anymore. You get an error like:

psql: FATAL:  database "postgres" does not exist

If so, you just need to connect to a different database and re-create the postgres database. It only exists as a convenient scratch area - the system doesn't actually care if the postgres database exists or not, and doesn't use it for anything.

Something like:

sudo -u postgres psql template1 -c 'CREATE DATABASE postgres;'

will do the trick.

If you dropped the template1 database as well, you could still recover by connecting to any other database on the server and re-creating template1 from the locked, protected template0 database; see the documentation on template databases.

Key things to understand:

  • You don't have to connect to the postgres database. It doesn't get used for anything except a default place to connect to when using the postgres user. If you DROP it the system doesn't care.

  • psql connects by default to the database with the same name as the current user. You can just pick another one with psql databasename.

  • psql connects by default with the username of the current unix user. You can just pick another one with (eg) psql -U someuser. This will work fine if pg_hba.conf is configured to permit the connection.

  • While the postgres user is the default superuser, you can create another one, and you don't have to rely on peer authentication to use it either; you can use md5 password auth or whatever you want. So while you're probably used to just using sudo -u postgres psql you can actually set things up in a variety of flexible ways. I frequently create a database superuser for my regular unix user account (CREATE USER myuser WITH SUPERUSER ENCRYPTED PASSWORD 'blah';) when I'm on a scratch/development box, so I'm not messing about with sudo all the time.

Related Question