Cant connect to PostgreSQL database

Question

I have problem connecting to database. I recive message that role "Darko" does not exists. This is the sequence of my commands:

/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -l logfile 
Server starting /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "darko". 
This user must also own the server process. 
The database cluster will be initialized with locales COLLATE: en_US.UTF-8 TYPE: en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: hr_HR.UTF-8 NUMERIC: hr_HR.UTF-8 TIME: en_US.UTF-8 
The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". 
Data page checksums are disabled. initdb: directory "/usr/local/pgsql/data" exists but is not empty 
If you want to create a new database system, either remove or empty the directory "/usr/local/pgsql/data" or run initdb with an argument other than "/usr/local/pgsql/data". /usr/local/pgsql/bin/psql psql: 
FATAL: role "darko" does not exist

Nick · Accepted Answer · 2017-06-23 16:00:47Z

2

You're trying to initialize Postgres cluster (main directory where all your Postgres data and logs will be stored) in "/usr/local/pgsql/data" directory.

Create another directory, anywhere and pass full path to it to -D option of initdb.

It will initialize your Postgres cluster.

During this process, a default database role will be created. If you run initdb under your regular OS user ("darko") -- it will create database user with the same name. But usually people run posgres tools (including initdb) under a separate OS user, "postgres" -- and if you will do it (like "sudo -u postgres initdb -D ..."), initdb will create database role "postgres" instead. (BTW, in Postgres terminology, database user and database role are the same things).

Then, once initbd has successfully created your Postgres cluster, you can check it using "ls" -- directory must contain subdirectories such as "base", "global", "pg_xlog", config files, etc.

Then you need to run Postgres, it's done using pg_ctl command, and you need to pass path to your cluster, again with -D option:

pg_ctl -D /path/to/cluster

If it runs successfully, open another terminal tab/window and try to connect using psql and corresponding database role (either "darko" or "postgres", based on your previous decision):

psql -U darko template1

"template1" is a database name which is always present, it serves as a template for all future databases you'll create.

Also, before you try to start Postgres with pg_ctl, it's worth to check if you have already some Postgres running:

ps ax | grep postgres

-- if you have another Postgres running, you can shut it down using "pg_ctl stop" with corresponding -D option pointing to the proper cluster directory.

Example (Ubuntu):

$ ps ax | grep postgres | grep D
21996 ?        S     23:37 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf

To stop it gracefully, just run:

/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main stop

edited Jun 23, 2017 at 16:00

answered Jun 23, 2017 at 15:55

Nick

2,54315 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

piga Over a year ago

I need to be able to use postgresql database in my rails app.

piga Over a year ago

Ok, thanks. This is summary of my actions based on your explanation. 1. I killed all postgres proceses. 2. run initdb in some directory. 3. started server for that cluster. 4. then you can connect with database running **psql "database_name". My problem was that I didn't include database_name argument. Thanks

Arkhena · Accepted Answer · 2017-06-23 15:06:01Z

0

initdb is a tool used to create a cluster (see PostgreSQL documentation for cluster definition in this context). It seems your allready did that as your directory isn't empty. Don't do it anymore.

Then, you're trying to connect with psql. By default, it's trying a socket connection with the same name for database user as your OS user. As you didn't create a role named 'darko', it fails.

What you have to do is trying to connect as postgres user. Try this to connect:

sudo -u postgres psql

You should be abble to connect with that line (assuming you didn't change the pg_hba.conf file) and then you should be abble to create your users and databases.

Here's the documentation page for psql.

answered Jun 23, 2017 at 15:06

Arkhena

2802 silver badges10 bronze badges

2 Comments

piga Over a year ago

I tried sudo -u postgres psql but I got this message: psql: FATAL: role "postgres" does not exist. I didn't change pg_hba.conf

piga Over a year ago

I made new directory for initdb, then I run initdb for that directory, then I started server for that directory. Then I tried /usr/local/pgsql/bin/psql -U darko "postgres" and succeded. Thanks

Collectives™ on Stack Overflow

Cant connect to PostgreSQL database

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related