PostgreSQL 9.3 – Cannot Run After Drop Table

postgresqlpostgresql-9.3

I've deleted database from PostgreSQL and after this I can do nothing.
Here what i receive with createuser

$ createuser -D new_user
createuser: could not connect to database postgres: FATAL:  role "postgres" does not exist

And this when try to run psql

$ psql
psql: FATAL:  role "postgres" does not exist

I've tried above with root, postgres, custom users. Still same output

Also I searched for pg_hba.conf like this
find / -name 'pg_hba.conf'
and got this

/var/lib/postgres-xc/DN2/pg_hba.conf
/var/lib/postgres-xc/DN1/pg_hba.conf
/var/lib/postgres-xc/CN/pg_hba.conf
/etc/postgres-xc/coordinator/CN/pg_hba.conf
/etc/postgres-xc/datanode/DN2/pg_hba.conf
/etc/postgres-xc/datanode/DN1/pg_hba.conf
/etc/postgresql/9.3/main/pg_hba.conf

I've changed connection method to trustfor localhost and IPv4 in /etc/postgresql/9.3/main/pg_hba.conf.

What should I do get rid of this error?

Does connection method in other files above should be edited too?

Best Answer

Main problem was that I could not run psql command under no user. Output I received was:

psql: FATAL:  role "any role" does not exist

any role means that I tried to run psql under each user on my system(root,postgresql,my_user)

After all I haven't figured out what the problem was, I decided to reinstall PostgreSQL.

And this process turned out rather hard to.

So if you have problems with deleting and reinstalling PostgreSQL, read on.

Here what I had to do.

echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

now we get error about missing postgresql-common to fix it we need to install above package lets determine its version

sudo apt-cache policy postgresql-common

and install it

sudo apt-get install postgresql-common=151.pgdg12.4+1

we can get error that postgresql-client-common not installed so we need to do the same determine it version

sudo apt-cache policy postgresql-client-common

and install

sudo apt-get install postgresql-client-common=151.pgdg12.4+1

after this we can install postgresql like this

sudo apt-get install postgresql-9.3 postgresql-contrib-9.3

Related Solutions

Postgresql – pg_upgrade unrecognized configuration parameter “unix_socket_directory”

I hacked the problem by running (as root):

mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
     /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run pg_upgrade as intended, then undo the hack:

    mv -f /usr/bin/pg_ctl{-orig,}

The problem is that pg_upgrade executes the program pg_ctrl with arguments that specify files in the old "unix_socket_directory" rather than the new "unix_socket_directories" (note the second is plural). This hack renames the original /usr/bin/pg_ctl to /usr/bin/pg_ctl-orig, and then creates a shell script in its place that simply calls the original pg_ctl program, passing all arguments with any strings "unix_socket_directory" changed to "unix_socket_directories".

In bash, one can change a portion of a string, say from bar to baz in a variable $foo, by using ${foo/bar/baz} (note this does not change the variable, but rather returns the variable's modified contents). Arrays can also be used with ${x/y/z} to retrieve an array with all of its contents replaced, all at once. The variable $@ is an array that contains all arguments passed to the program/script/function, so the new pg_ctl script executes the old one with all arguments changed from the old directory name to the new one.

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?

Best Answer

Related Solutions

Postgresql – pg_upgrade unrecognized configuration parameter “unix_socket_directory”

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

Related Question