43

Im new to Postgres and to Heroku. I am trying to pull the database from Heroku but I'm missing something simple. I did:

heroku pg:pull HEROKU_POSTGRESQL_IVORY_URL localdb

And I got the error:

createdb: database creation failed: ERROR: permission denied to create database

Then I tried the same with "sudo". and I got:

createdb: could not connect to database template1: FATAL: role "root" does not exist

So, it must be I'm missing some simple commands I can't find. Im on Linux, I have Postgres installed and working.

Improve this question
1
  • i think you have to create a user role called root in your PostgreSQL or Postgres not Postgre.....create role root login Commented Jul 31, 2014 at 4:33

4 Answers 4

Reset to default
54

createdb is a wrapper around the SQL statement CREATE DATABASE and as such it needs to connect to the database.

By default all Postgres commandline tools try to connect to the database using the current operating system user. As the error message indicates there is not user named root in the database. So you need to pass the name of the Postgres superuser in order for createdb to be able to connect. This user is usually named postgres.

Another option is to switch the Linux user to to postgres if such a Linux user exists.

I don't know Heroku and I don't know how you started createdb, but the parameter to pass a username is -U (for all Postgres command line programs). So you'd need

 createdb -U postgres name_of_new_database
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Your last command doesn't work for me. However, sudo postgres works (see also stackoverflow.com/a/28783632/952234)
The last command gives me also createdb: could not connect to database template1: FATAL: Peer authentication failed for user "postgres" in Debian 8.7. You can do sudo -u postgres psql. You can create db directly in psql: CREATE DATABASE masi which works. @YaroslavNikitenko
19

Try to use:

sudo su - postgres

Then createdb using:

createdb name_of_db;

Improve this answer

6 Comments

postgres@3a733ae6fd45:~$ createdb name_of_db; createdb: error: could not connect to database template1: FATAL: role "postgres" does not exist
@EugeneGr.Philippov, it might be an issue with how you installed your Postgres. Which operating system are you using? And what shows when you write the command psql
Ezrqn Kemboi: I later discovered that probably docker-compose with postgres creates a single role explicitly declared at the .yml descriptor and does not create any default roles like rolename root or rolename postgres if it has no explicit instruction to do so. It seems this way!
Ezrqn Kemboi: also, afaik you don't need to wrap su with sudo.
Ezrqn Kemboi: I have successfully logged in with that explicitly docker-compose.yml-declared role which was non-postgres non-root.
|
1

When it comes to heroku pg:pull specifically, you can specify the PostgreSQL user and password by specifying the PGUSER and PGPASSWORD environment variables specifically:

PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

If you want to specify the host, for example 127.0.0.1, this is how:

heroku pg:pull HEROKU_POSTGRESQL_MAGENTA postgres://127.0.0.1/mylocaldb --app sushi

See documentation for heroku pg:pull

Improve this answer

Comments

0

In some cases you need to execute createdb as the postgres user.

Run createdb (in-line) as postgres user:

sudo -u postgres createdb name_of_database

The command suggested by Ezrqn Kemboi, sudo su - postgres, switches you to the user postgres at which point you would need to exit out of that user to return to whomever you were before hand. This isn't great if you're running this in a script or some form of automation.

The command I suggested will not "switch" to the user and instead will run the command "in-place". My suggestion is more suitable for running in scripts.

Pick whichever you feel is better for your use case.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.