Clobbered default database in postgreSQL

Question

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.

• Craig Ringer: "clobbered"? "all databases are utterly unusable"? How exactly did it "clobber" the postgres database? Do you mean you DROPped it? And how exactly are all DBs unusable? Exact error messages and precise description of effects please. Dropping postgres doesn't affect the other databases, so it's not clear what the problem is. You also forgot to mention your PostgreSQL version.
• Bill: An interesting side effect is that in phppgadmin everything shows an "estimated row count" of 0. It appeared to me at the time that insert queries that had once worked in my code were going into a black hole.

Answer 1

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.

• Bill: This explained a lot and helped.. thank you for your time!
• Craig Ringer: Also, if you've somehow managed to drop all databases (which shouldn't be possible) you can still start PostgreSQL up in single-user recovery mode against template0.

Answer 2

You might try connecting to another database in psql, dropping the postgres database, and simply recreating it, which should make a copy based on template1.

drop database postgres;
create database postgress;

• B. VB.: Hm.., well I did this and all that happened is that I end up with an empty postgres database. I read the link, but I see nothing along the lines of template1 anywhere
• banderkat: The default postgres database is empty of relations. More documentation on templates here.