getting started with postgres on os x with homebrew



~4 min read


793 words

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:

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:

  1. Create a database to connect to
  2. Create a user other than your superuser to handle routine management.1
  3. Adjust roles for the new user

Below is a quick tutorial on how to do all of those:

Create The Database

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:

FATAL: database <user> does not exist psql: FATAL: database <user> does not exist

Create The User

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 =#).

Creating User In The Bash Shell

createuser <username> --pwprompt

The --pwprompt is to secure the new user with a password.

Creating A User Within The PSQL Shell 3

<database_name>=# create user <username> [with password '<password>']

Roles And Permissions

At this point, you should be able to see your new role using the \du command within the psql shell: Roles & Permissions

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.

Alter The Role

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 createdb and 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;

Alter role

Update: If you forgot to set a password when you created the user, you can do it now by altering the user.5

<database_name>=# alter role onething_admin with password '[new_password]';

Wrap Up

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.

Regular user prompt


Related Posts
  • Postgres Interactive Shell, Start, Stop, And Docker

  • Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!