If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is public
)
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
If I remember correctly the user postgres
has no DB password set on Ubuntu by default. That means, that you can login to that account only by using the postgres
OS user account.
Assuming, that you have root
access on the box you can do:
sudo -u postgres psql
If that fails with a database "postgres" does not exists
error, then you are most likely not on a Ubuntu or Debian server :-) In this case simply add template1
to the command:
sudo -u postgres psql template1
If any of those commands fail with an error psql: FATAL: password authentication failed for user "postgres"
then check the file /etc/postgresql/8.4/main/pg_hba.conf
: There must be a line like this as the first non-comment line:
local all postgres ident
For newer versions of PostgreSQL ident
actually might be peer
. That's OK also.
Inside the psql
shell you can give the DB user postgres
a password:
ALTER USER postgres PASSWORD 'newPassword';
You can leave the psql
shell by typing CtrlD or with the command \q
.
Now you should be able to give pgAdmin a valid password for the DB superuser and it will be happy too. :-)
Best Answer
The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.
Connect to the default database with user postgres:
Set the password for user postgres, then exit psql (Ctrl-D):
Edit the
pg_hba.conf
file:and change "peer" to "md5" on the line concerning postgres:
To know what version of postgresql you are running, look for the version folder under
/etc/postgresql
. Also, you can use Nano or other editor instead of VIM.Restart the database :
(Here you can check if it worked with
psql -U postgres
).Create a user having the same name as you (to find it, you can type
whoami
):The options tell postgresql to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you're asked to type the password that will be encrypted, and -d so that you can do a
createdb
.Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).
Again, edit the
pg_hba.conf
file (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:Restart (like in step 4), and check that you can login without -U postgres:
Note that if you do a mere
psql
, it will fail since it will try to connect you to a default database having the same name as you (i.e.whoami
). template1 is the admin database that is here from the start.Now
createdb <dbname>
should work.