Grant Harper

Postgres Quick Start software

Setting up a database server is the kind of infrequent operation you seldom do as an application developer, so you always forget. Yes, I know you can read the documentation to figure this out, but that takes longer than having a quick reference guide on what you need to do to get up and running quickly.

OS: Ubuntu 18

Install Postgres
This will create a postgres user that you can use to login to the database initially.

sudo apt-get install postgres

Create a user that matches your unix username for ease of use

sudo su postgres
createuser --superuser <your-username>

Create a new database using your new superuser privileges

createdb <database-name>

Create a user that can access this database

psql postgres
create user <app-user> with encrypted password '<app-user-password>';
grant all privileges on database <database-name> to <app-user>;

Note: you probably don't want an app user that is leveraged by untrusted clients to have privileges to create and drop tables, so you can create a new user that is used for say, REST API database reads and writes that has more restricted privileges.

To access the database with your new app user

psql --host=localhost --dbname=<database-name> --username=<app-user> --password

Then provide the password when prompted

Create a table and enter some data (of course about recipes!)

create table recipes
id bigserial primary key,
title text not null
insert into recipes (title) values ('Bread');

Extra Credit: Connect an application written in Go

package main

import (
_ ""

type Recipe struct {
ID int64 `db:"id"`
Title string `db:"title"`

func main() {
db, err := sqlx.Connect("postgres", "user=<app-user> dbname=<database-name> password=<app-user-password> sslmode=disable")
if err != nil {
var recipes []Recipe
_ = db.Select(&recipes, "select * from recipes order by title")
log.Printf("recipes=%v", recipes)
_ = db.Close()

All done!

← Back to Posts