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>
exit

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>;
\q

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 (
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

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 {
        log.Fatalln(err)
    }
    var recipes []Recipe
    _ = db.Select(&recipes, "select * from recipes order by title")
    log.Printf("recipes=%v", recipes)
    _ = db.Close()
}

All done!

← Back to Posts