Let'sGoFurther - Chapter 5: Database Setup and Configuration

 

zzh@ZZHPC:/zdata/Github/greenlight$ docker pull postgres

zzh@ZZHPC:/zdata/Github/greenlight$ docker run --name postgres17 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=root -d postgres:latest

 

zzh@ZZHPC:~$ docker exec -it postgres17 psql -U root
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

root=# SELECT current_user;
 current_user 
--------------
 root
(1 row)

root=# CREATE DATABASE greenlight;
CREATE DATABASE
root=# \c greenlight
You are now connected to database "greenlight" as user "root".
greenlight=#

 

 

greenlight=# CREATE ROLE greenlight WITH LOGIN PASSWORD 'greenlight';
CREATE ROLE

greenlight=# ALTER DATABASE greenlight OWNER TO greenlight;
ALTER DATABASE

greenlight=# CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION
greenlight=#

Once that’s successfully done, you can type exit or \q to close the terminal-based front-end and revert to being your normal operating system user.

 

Connecting as the new user

zzh@ZZHPC:~$ docker exec -it postgres17 psql --dbname=greenlight --username=greenlight
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

greenlight=> SELECT current_user;
 current_user 
--------------
 greenlight
(1 row)

greenlight=> exit

 

You can check where your postgresql.conf file lives with the following SQL query:

zzh@ZZHPC:~$ docker exec -it postgres17 psql -U root
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.

root=# SHOW config_file;
               config_file                
------------------------------------------
 /var/lib/postgresql/data/postgresql.conf
(1 row)

root=#

This article provides a good introduction to some of the most important PostgreSQL settings, and guidance on what values are reasonable to use as a starting point. If you’re interested in optimizing PostgreSQL, I recommend giving this a read.

Alternatively, you can use this web-based tool to generate suggested values based on your available system hardware. A nice feature of this tool is that it also outputs ALTER SYSTEM SQL statements, which you can run against your database to change the settings instead of altering your postgresql.conf file manually.

zzh@ZZHPC:/zdata/Github/greenlight$ go get github.com/jackc/pgx/v5
go: downloading github.com/jackc/pgx/v5 v5.7.1
go: downloading github.com/jackc/pgx v3.6.2+incompatible
go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761
go: downloading golang.org/x/text v0.20.0
go: added github.com/jackc/pgpassfile v1.0.0
go: added github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761
go: added github.com/jackc/pgx/v5 v5.7.1

 

DSN:

postgres://greenlight:greenlight@localhost/greenlight?sslmode=disable

 

File: cmd/api/main.go:

func createDBConnPool(cfg config) (*pgxpool.Pool, error) {
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    config, err := pgxpool.ParseConfig(cfg.db.dsn)
    if err != nil {
        return nil, err
    }

    config.MaxConns = int32(cfg.db.maxOpenConns)
    config.MaxConnIdleTime = cfg.db.maxIdleTime

    p, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, err
    }

    err = p.Ping(ctx)
    if err != nil {
        p.Close()
        return nil, err
    }

    return p, nil
}

 

 

flag.StringVar(&cfg.db.dsn, "db-dsn", os.Getenv("GREENLIGHT_DB_DSN"), "PostgreSQL DSN")

 

 

 

type config struct {
    port int
    env  string
    db   struct {
        dsn          string
        maxOpenConns int
        maxIdleTime  time.Duration
    }
}

 

    flag.IntVar(&cfg.db.maxOpenConns, "db-max-open-conns", 25, "PostgreSQL max open connections")
    flag.DurationVar(&cfg.db.maxIdleTime, "db-max-idle-time", 15*time.Minute, "PostgreSQL max connection idle time")