Configure PostgreSQL on your Mac from scratch

Felipe Florencio Garcia

Follow

Apr 1 · 8 min read

Here the intention is to teach how to do this on Mac only and using homebrew, if you are not interested in this way maybe could use some reference’s but I don’t think that will satisfy everything about installation.

If you don’t have Homebrew installed hit this command at your mac terminal, if you already have just skip:

/usr/bin/ruby -e "$(curl -fsSL [<https://raw.githubusercontent.com/Homebrew/install/master/install>](<https://raw.githubusercontent.com/Homebrew/install/master/install>))"

Of go to the website Homebrew.

Now that you have, let’s install by doing on terminal:

brew install postgresql

When finish let’s check if is working by typing on terminal: postgres -V

And if is installed you should see something like this, depending of the version installed:

postgres (PostgreSQL) 11.2

But still is I believe that does not satisfy your basic needs, that is have up and running, you have two approach one is have this setup to always start the postgresql server or just when you want, here I will cover only when you want, as I prefer to have control always, I do not develop 100% of the time so what’s is the point always on ;).

The command to start the database if you installed using Homebrew and have this as a service that will restart at login is:

brew services start postgresql

And following this you have more 2 must know command that are, stop and restart:

Stop: brew services stop postgresql

Restart: brew services restart postgresql

But if you do not need to have this service running all the time even when restart your machine, you can start and stop using the command below.

By now we have postgresql installed, but still can be easier right, it’s too much to always type, for this we have alias that we can create shortcut to our commands, let’s create for these 3.

Short introduction about this “alias” it’s a way to create shortcut’s to command, and your terminal will understand that you want to run that command, we have 2 ways of doing, the first it’s at file “ ~/.bash_profile” on your mac, if you don’t have just create. Or for people like me that use “zshrc” need to be at this file them *(you can load your bash profile inside your zshrc so just one place to add ;) “.

Let’s add to bash profile, add this 3 lines:

alias pg-start="pg_ctl -D /usr/local/var/postgres start"

alias pg-stop="pg_ctl -D /usr/local/var/postgres stop"

alias pg-restart="pg_ctl -D /usr/local/var/postgres restart"

For commands and any change that you make into you “~/.bash_profile and ~/.zshrc” you need restart your terminal session / window or reload these files, in order to just reload without need to close and reopen just hit: source ~/.bash_profile or source ~/.zshrc.

Now you can start and stop your server, but now you need to interact with him, and for this you need to “connect” to be able to run commands, so, start your postgresql by typing “*pg-start*", after use this command:
psql postgres

*If you try to run your alias and do not work or show a error message like this:
/Users/< You User >/Library/LaunchAgents/homebrew.mxcl.postgresql.plist: No such file or directory.
If you navigate to the LaunchAgents and do not have this file, you need to have, but why copy if you already have at homebrew (if you installed using homebrew of course).

To fix this, create a symlink between, so you will not need to copy but will have a link inside LaunchAgents folder.

First go to /usr/local/opt/postgresql/ using your terminal and verify that you have the file homebrew.mxcl.postgresql.plist.

If yes, than type this command to create the symlink:
ln -sfv /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents

Done, you should be able now to use again your alias without error.

Try again the command *pg-start* and continue.

Doing this you will see something like this:

Very important to know here, for some this could looks like that we are connected using the user “postgres”, but it’s not true, actually what we have here is that we are connected as the “superuser” to the database named “postgres”.

If you want to know who is the user connected to this database, when in the edit mode just type:

\conninfo

And you should see something like:

You are connected to database "postgres" as user "felipe.garcia" via socket in "/tmp" at port "5432".

Just a reminder that this user is my user, it will be different for you of-course ;) .

So, this means that you have your server running and you successful accessed him and now your are in the “edit mode”.

To exit this mode just type “\q" and will exit from editor mode.

Error:

Could happen that when you try to start you session using “psql" you see this message:

psql: FATAL: database <YOUR USER HERE> does not exist

It’s easy, just type in the terminal this: createdb <YOUR USER HERE>

After this you will be able to start normally again :)

Going deep

We now have the environment that we need, and for this we can connect already, but it’s really important to know how to have a minimum setup working more “professional”, and for this you will nee to know how to setup a user, it’s a very important step, when you deploy to your server, beside your local machine, it’s important to pay attention and set the right amount of privileges your API will be able to play on your database.

First let’s see how we see which user we have, start with login with your default user:

psql

If you have any problem like no user exist, just follow the step above.

Now let’s list our user’s using the command \du you should see just one user as superuser, my user, on my machine is "<felipe.garcia>" :

Ok, this is fine, as I know what i’m doing, is my machine it’s not a database on the internet, but let’s create another user, set some limited permission’s “ROLES” and change this permissions, so we will have the bases in order to be able to have a better setup latter if we need.

Create new user / role.

Let’s create a new user first the very simple way, using this command:

CREATE USER developer;

*There’s some other ways of create that you can see at the end at tips.

You now can type \du and you will see that you have your user there now.

One thing that you maybe have notice is, that we see our user in the column “Role” , actually the command “CREATE USER” is a alias for “CREATE ROLE” that you can specify more information, but for more info about, I do recommend look into the documentation, that is very good and you can easily find more informations.

Link:

• Create user: https://www.postgresql.org/docs/8.1/sql-createuser.html
• Create role: https://www.postgresql.org/docs/8.1/sql-createrole.html

Now that we have our new created user or group or role, you will see this when look at documentation, we need to change some permissions, in order to understand how to properly setup.

Let’s give this user the right only to create something inside this database, not for delete for example, for this first check which user are you connected using:

\conninfo

Need to be another one that not the one that you create, that by default is “superuser”;

If need to change you can do two ways, or exit and connect again using the right user doing:

/q
# After login again the pattern to login is: psql <DATABASE> <USER>, for me will be my user that has "root" privilegies
psql postgres felipe.garcia

Or using this command:

\c <DATABASE> <USER>

Not let’s change our user “developer” rights to not allow him to be able to create database:

ALTER ROLE developer NOCREATEDB;

This means that this user (role) don’t have the right to create a new database, for this try this:

CREATE DATABASE test;

You should see a message like this:

If you type \du you will be able to see that now he only have this right.

And this is really important, like, usually when you create a database you want to consume, you don’t want to have a user with “root” access be able to delete and create a database, you basically only want to have a user to consume that database.

Change / add role password, understand how you can make it.

But still, we are missing one another important step, if you notice, you login without have a password, and this is another important step in order to have a better setup for this user, we have 2 ways of doing this, at user (role) creation, or after, I chose do this after so you can understand better each step, for this follow this steps:

1 — Change / set user password:

ALTER USER developer WITH PASSWORD 'dev123';

2 — Change / set user login password

ALTER USER developer WITH LOGIN PASSWORD 'dev123';

How to switch user and create table:

List again using \l and now you will be able to see that developer is the owner of dev table, now let's connect to that database as developer, type this:

#\c <DATABASE> <USER>
\c dev developer

If you type now \c you will see this:

You are now connected to database "dev" as user "developer".

Let’s create any table inside this database just to see latter if we can or can not delete, type this:

CREATE TABLE test(
	id integer,
  name character(256)
);

Now to verify if we really created this, type this command to look our table:

\d test

You should see a table like this:

Additional informations, tips!

Create or drop database without being inside “psql” session.

Beside the way that we learned how to create / drop a database when we are “logged in” using “psql” we have another way of doing this, these are:

For create, type this:

createdb mydatabasename

And for dropping type this: dropdb mydatabasename

Connect to specific database with specific user

psql postgres -U newuser

To change the connected user while in “psql” mode, type:

\c <DATABASE> <USER_TO_SWITCH>

Create role (user) with password:

Create a new user inside the psql terminal, * (password must be enclosed with quotes):

CREATE ROLE newuser WITH LOGIN PASSWORD 'password';

Change user owner of a database, first you need to switch to the user that is the owner of that database, after do this:

ALTER DATABASE name OWNER TO new_owner;

Logging using specific user.

psql -U user_name -d database_name -h 127.0.0.1 -W

The “-W” flag tells PostgreSQL that we will be entering a password.

Useful commands for psql terminal:

We do have many commands and would be very difficult to have all here, you always can go to the postgres website and see / looking for them all. *(I have to say it’s very good documented).

• \list — List all of your actual databases.
• \c mydatabasename — Connect to another database.
• \d — List the relations of your currently connected database.
• \d mytablename — Shows information for a specific table.
• \du To list all users.
• \dt List tables / relations for this table.
• \q Close the session and leave psql.
• \conninfo Show connection information.