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.
3 Answers
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
Try to use:
sudo su - postgres
Then createdb using:
createdb name_of_db;
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.
