Postgres: Setup & Basics

I recently started working on a personal project that includes a database in the back end. As a primarily Frontend Engineer it had been years since I'd set up a database. Below are the basic steps to getting started with postgresql.

Installation

I'll be installing postgres using Homebrew, a package manager used for Mac/Linux. If you don't have homebrew installed here's a link. Now pop open a terminal window and lets get to work.

We'll start by updating homebrew to make sure we have the latest:

brew update

Next we'll install postgres:

brew install postgresql

Once installed lets start the service:

brew services start postgresql

Postgres is now installed and running. You can stop the service at anytime with:

brew services stop postgresql

Log into postgres and create a user

psql is a terminal based front-end for postgres. It will connect us to postgres and allow us to create databases, users, issue queries, etc..

First we have to log in to psql as the default root user:

psql postgres

We are now logged in as the root user. You should see this in the command prompt:

postgres=#

Here's a list of some common postgres commands you'll use now that we are logged in:

\q - quit/exit psql
\list - lists all databases
\du - lists all database roles
\dt - lists all database tables
\conninfo - lists info about current database connection
\c [dbname] - connect to a different database 
CREATE DATABASE [dbname]; - creates a new database
CREATE USER [username]; - creates a new user
ALTER ROLE [username] CREATEDB; - allows user to create databases
ALTER ROLE [username] CREATEROLE; - allows user to create roles
DROP DATABASE [dbname]; - deletes database
DROP USER [username]; - deletes a user

Currently we are logged in as our root user, its generally poor practice to use this user in postgres so lets create a new user that we'll use to perform our database actions and grant them some basic permissions:

postgres=# CREATE USER username WITH PASSWORD 'password';
postgres=# ALTER ROLE username CREATEDB CREATEROLE;

We've now created a new user called username with a password and given them permission to create databases and roles/users. You can check this by running \du and you should see your user listed.

Log out of the root user and log in as our new user:

postgres=# \q

psql -d postgres -U username

Note: -d indicates which database we are logging into (we are logging into the default postgres db), -U indicates what user we are logging in as.

You should now see:

postgres=>

Creating a Database and Tables

Lets create a new database:

postgres=> CREATE DATABASE dbName;

If you use the \list command you should see your new database listed. Congrats you've just created a database in postgres! From here you can add tables and data to your database directly in psql, however I'd recommend doing this in a tool like TablePlus or pgAdmin as its much easier to visual and doesnt require memorizing SQL commands. However heres a basic example in psql for a table of messages.

To create a table:

dbname=> CREATE TABLE messages (
   id SERIAL PRIMARY KEY,
   name VARCHAR(30),
   message TEXT
);

To add data to that table:

dbname=> INSERT INTO messages (name, message) VALUES ('Donald Duck', 'Qwack Qwack');

Then to check out our data:

dbname=> SELECT * FROM messages;

Result:

id |    name     |   message
----+-------------+-------------
  1 | Donald Duck | Qwack Qwack
(1 row)

Comments

[Andrew Baisden]

PostgreSQL and SQL are fairly easy to learn wish more people realised this. Good setup guide.

[Sean Tansey]

Thanks, yeah I think for the most part the set up is harder than the basics of using it. At least until you start doing some more complex SQL joins