CODEWITHSUNDEEP
postgresqlconnectionrole
piga
piga

Reputation: 23

Cant connect to PostgreSQL database

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

Upvotes: 2

Views: 4393

Answers (2)

Nick
Nick

Reputation: 2513

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

Upvotes: 2

Arkhena
Arkhena

Reputation: 280

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.

Upvotes: 0

Related Questions