November 24, 2019
Before you can connect to a Postgres database from the command line, it needs to be running.
If it’s been a while, it’s easy to forget how to do that.
When installing via Homebrew, there’s a useful reminder:
postgresql To migrate existing data from a previous major version of PostgreSQL run: brew postgresql-upgrade-database To have launchd start postgresql now and restart at login: brew services start postgresql Or, if you don't want/need a background service you can just run: pg_ctl -D /usr/local/var/postgres start
If this is the first time you’re starting Postgres, the next things you may want to do are:
Below is a quick tutorial on how to do all of those:
Once the postgres service is started, the first thing we’ll need is a database. We can create the database using
createdb from bash:2
$ createdb <database_name>
After we have our first database, we can login and create databases from within the postgres shell, however, the first one needs to happen from the bash shell - or you’ll get the error:
does not exist psql: FATAL: database does not exist
Now that we have a database, we will want to create the second (non super) user. For that, we have the
createuser command from the terminal.3 Or, if we log into the database (using the name of the database we just created) we can create the user within the
psql shell (which you’ll know you’re in because instead of the prompt beginning with
$ for bash or
# for zsh, you’ll see
$ createuser <username> --pwprompt
--pwprompt is to secure the new user with a password.
<database_name>=# create user <username> [with password '<password>']
The thing to notice is that this does not apply a role to the user - which will limit the ability for them to do much within the database once they login.
Postgres recommends having a user who can create databases and roles but is not a super user.
To do that, we’ll want to provide the attributes of
createrole to our new role without making them a superuser.
From within the Postgres shell, we can use the
ALTER ROLE command:4
<database_name>=# alter role onething_admin CREATEDB; <database_name>=# alter role onething_admin createrole;
Update: If you forgot to set a password when you created the user, you can do it now by altering the user.5
Confirm that your new user is created as expected by logging into them.
Exit the Postgres shell and try logging in as your new user.
If the command prompt is
= - terminating with an
> instead of the
#, then you know you’re not signed in as a super user.
createuser- checkout the createuser man page or for Postgresql documentation on create user for guidance on creating users within the Postgres shell.
alter user- we have PostgreSQL: Documentation: 9.1: ALTER ROLE
5 I found this discussion on the difference between user and role (and group) on StackOverflow really insightful. The punchline for me was:
In modern versions of Postgres, the two concepts [users and groups] have been merged: a “role” can have the ability to login, the ability to “inherit” from other roles (like a user being a member of a group, or a group being a member of another group), and access to database objects. […] Postgres still accepts commands using the old terminology, such as CREATE USER and CREATE GROUP which are both aliases for CREATE ROLE.
Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!