37

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.

Alejandro Veintimilla
  • 10,743
  • 23
  • 91
  • 180
  • i think you have to create a user role called `root` in your `PostgreSQL` or `Postgres` not `Postgre`.....`create role root login ` – Vivek S. Jul 31 '14 at 04:33

4 Answers4

48

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
  • Your last command doesn't work for me. However, `sudo postgres` works (see also http://stackoverflow.com/a/28783632/952234) – Yaroslav Nikitenko Dec 07 '15 at 18:11
  • 6
    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 – Léo Léopold Hertz 준영 May 14 '17 at 09:24
16

Try to use:

sudo su - postgres

Then createdb using:

createdb name_of_db;

Ezrqn Kemboi
  • 897
  • 1
  • 12
  • 19
  • 1
    `postgres@3a733ae6fd45:~$ createdb name_of_db;` `createdb: error: could not connect to database template1: FATAL: role "postgres" does not exist` – Eugene Gr. Philippov Jun 30 '21 at 21:47
  • @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 Jul 02 '21 at 04:19
  • 1
    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! – Eugene Gr. Philippov Jul 03 '21 at 12:48
  • Ezrqn Kemboi: also, afaik you don't need to wrap `su` with `sudo`. – Eugene Gr. Philippov Jul 03 '21 at 12:51
  • Ezrqn Kemboi: I have successfully logged in with that explicitly `docker-compose.yml`-declared role which was non-`postgres` non-`root`. – Eugene Gr. Philippov Jul 03 '21 at 12:57
  • Happy to hear that @EugeneGr.Philippov. Since you are using docker, these comments will be good for people who face the same issue in the future. – Ezrqn Kemboi Jul 04 '21 at 10:04
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

Flimm
  • 136,138
  • 45
  • 251
  • 267
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.

Justin Gross
  • 31
  • 4