Running a Postgres database from scratch

This tutorial is aimed at those that wish to create a Postgres relational database. This assumes you have basic background knowledge of SQL. Check out here.

This tutorial assumes you are using linux/macOS. I have not covered additional support for windows.

Install postgres via brew (macOS)

brew update && brew install postgres

Install postgres via apt (linux distro)

sudo apt-get install postgresql

Check if the following directory was created (macOS only):

ls -lrt /usr/local/var/postgres

If the previous directory doesn’t exist, execute the following:

export PG_PATH="/usr/local/var/postgres"
sudo mkdir $PG_PATH
sudo chmod 775 $PG_PATH
sudo chown $(whoami) $PG_PATH

The above sets up the directory where all your postgres data will be stored. We are making sure that our current os user has access to the directory.

Initializing the database

initdb $PG_PATH

Time to initialize our database. Here we use a command to create a database cluster that will hold all future databases we create. By default, the command creates two databases: postgres, template1. The former database is the default that is used by users and third-party applications. Useful for creating database users as it has superuser and create role attributes. Think of it as a root database account with superpower priviledges. The template1 database is a blueprint that is copied for every new database that is created. Anything you install into template1 database will be copied over to new databases.

Starting the postgres server

Once we have successfully created the database cluster, the only requirement that remains is to start the postgres server. This is a prerequisite before initiate connections to any database instance.

pg_ctl -D $PG_PATH -l myDB.log start

The above does the following:

• Uses our data directory to create a background process. The server is running on port 5432 by default
• All the events emitted from the process is logged to the myDB.log file
• Creates the myDB.log file in your current directory
• You can override the default server configuration by editing /usr/local/var/postgres/postgresql.conf

Accessing our database cluster

Hopefully, you have successfully started the database. Let’s check using the psql command-line tool — utility tool which acts as a frontend to postgres server.

psql -d postgres

Here we connect to the database postgres. You should now be in the postgres interactive terminal. Execute the following command to list all the databases in our cluster.

postgres=# \l

You should see at least 2 rows present; postgres and template1 databases. These are two databases that were created during the initialisation step.

Creating a database

First and foremost it’s best practice to create a different user that wil be the owner of the database. Let’s create a new DB user using SQL. It’s good practice to use SQL directly as that is the domain language relational databases use to execute operations.

CREATE USER myuser;

Above we create the myuser user. Let’s check if the user exists by issuing the following command in the postgres interactive terminal:

\du

You should see a row with the myuser user. Yay! Let’s now create a new database and assign our new user as the owner.

CREATE DATABASE userdb WITH OWNER=myuser;

As you can see we have successfully created the userdb database and assigned our previously created user as the owner. We seem to be getting more comfortable with working with our database so let’s dive into the deep end.

Creating a table

Before we can create a table we need to create a schema within our database. A schema is a namespace that is useful for organizing our tables. We create and store tables within schemas. Schemas are analogous to directories except you cannot have nested ones. Execute the following commands below to create your table:

\c myusers myuser
CREATE SCHEMA users AUTHORIZATION myuser;

First, we connect to our myusers database using the myuser user. This will use the underlying connection to postgres. Afterward, we create a schema called users and restrict access to the user we created before. We want to ensure that no one else has access to read/write to this schema. The next step is to create a table to store customers in using the following SQL:

CREATE TABLE customers (
    id char(5) CONSTRAINT firstkey PRIMARY KEY,
    first_name varchar(40) NOT NULL,
    age INTEGER NOT NULL,
    CONSTRAINT age check (age >= 0)
);

Here we create a table with three fields: id, first_name, and age. We have set the id as the primary key. When creating tables you should ensure that you have a uniquely identifiable column. This is beneficial to identify singular rows that are represented by entities. If you run \dt you should have a similar output as below.

Cleanup

If you wish to stop the postgres server execute the following command:

pg_ctl -D $PG_PATH stop

Hooray! For those that stayed until the end, we managed to install postgres, create a database cluster, run a server, create users, schemas, and a table to store information in.