Install Postgresql#

First let update

sudo dnf update

Then install postgresql on amazon linux 2023

sudo dnf install postgresql15.x86_64 postgresql15-server

Let create a new database cluster

sudo postgresql-setup --initdb

Start and enable service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Finally check the running service

sudo systemctl status postgresql

Sample Database#

Download

wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip

Create and load the downloaded database

psql
create database dvdrental;
\q

Then restore and load

pg_restore --dbname=dvdrental --verbose dvdrental.tar

Finally query

psql

Select a database

\c dvdrental;

Then list table

\dt

Simple query

select * from actor limit 10;

Setup Authentication#

Update password

su - postgres
psql -c "ALTER USER postgres WITH PASSWORD 'Mike@865525';"

Backup configuration

cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bak

Listen on address

vim /var/lib/pgsql/data/postgresql.confg

And update the listening address

# default
listen_addresses = 'localhost'
listen_addresses = '*'

Update authentication method from ident to md5 which requires password when a user want to access postgresql

/var/lib/pgsql/data/pg_hba.conf

Replace ident by m5 and 127.0.0.1/32 to 0.0.0.0/0, and replace ::1/128 to ::0/0

Create User#

First connect to the postgresql server as a postgresql user by running the following command

sudo -u postgres psql

Then list database

\l

Create an user which is actually a ROLE

CREATE USER demo WITH PASSWORD 'Mike@865525';

Create a database

CREATE DATABASE database_name;

Grant access

GRANT ALL PRIVILEGES ON DATABASE dvdrental TO demo;

Grant at table level, please go into dvdrental database first, then grant access per table or schema

\c dvdreltal
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO demo;
GRANT ALL PRIVILEGES ON TABLE dvdrental.actor TO demo;
GRANT ALL PRIVILEGES ON TABLE actor TO demo;

Check the result

\l

Here is the result permissions look like

Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 dvdrental | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =Tc/postgres         +
           |          |          |         |         |            |                 | postgres=CTc/postgres+
           |          |          |         |         |            |                 | demo=CTc/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres

Finally access the database given the user and using a psql client

psql -h localhost -U demo -d dvdrental
psql -h server-ip-address --U username  -d database_name

User Management#

List user, there is a superuser by default

\du

Check by psql terminal

psql -h localhost -p 5432 -U demo -d dvdrental

Postresql Drive#

Let use a simple github.com/lib/pq to work with postgresql

  • Connection string
  • Simple scan
package main


import (
	"database/sql"
	"fmt"
	"log"


	_ "github.com/lib/pq"
)


type Actor struct {
	actor_id int
	first_name string
	last_name string
	last_update string
}


func main()  {
	fmt.Println("Hello")


	connStr := "host=localhost port=5432 user=demo dbname=dvdrental password='Mike@865525' sslmode=disable"


	db, error := sql.Open("postgres", connStr)


	if (error != nil){
		log.Fatal(error)
	}


	rows, error := db.Query("SELECT * FROM actor LIMIT 10;")


	if error != nil {
		log.Fatal(error)
	}


	defer rows.Close()


	var actors [] Actor


	for rows.Next() {
		var actor Actor
		if err := rows.Scan(&actor.actor_id, &actor.first_name, &actor.last_name, &actor.last_update); err != nil {
            fmt.Println(err)
        }
		fmt.Println(actor)
        actors= append(actors, actor)
	}
	fmt.Println(actors)
}

Go Gorm#

Let use Gorm as a ORM to work with postgresql

  • Naming convention
  • TableName and existing tables

First, follow name convention the first character for a column should be cap

type Actor struct {
	Actor_id string `gorm:"PrimaryKey"`
	First_name string
	Last_name string
	Last_update string
}

To query existing table we should know how to customize TableName

type Tabler interface {
	TableName() string
}


func (Actor) TableName() string {
	return "actor"
}

Here is a full example

package main


import (
	"fmt"


	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)


type Actor struct {
	Actor_id string `gorm:"PrimaryKey"`
	First_name string
	Last_name string
	Last_update string
}


type Tabler interface {
	TableName() string
}


func (Actor) TableName() string {
	return "actor"
}


func main() {
	dsn := "host=localhost user=demo password='Mike@865525' dbname=dvdrental port=5432 sslmode=disable"
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			NoLowerCase: false,
			SingularTable: true,
		},
	})


	if err != nil {
		fmt.Println("ERROR")
	}


	var actors []Actor


	db.Limit(2).Find(&actors)


	for _, actor := range actors {
		fmt.Println(actor.Actor_id)
		fmt.Printf(actor.First_name)
		fmt.Println(actor.Last_name)
		fmt.Println(actor.Last_update)
	}


	var actor Actor


	db.First(&actor)


	fmt.Println(actor.Actor_id)
	fmt.Printf(actor.First_name)
	fmt.Println(actor.Last_name)
	fmt.Println(actor.Last_update)
}

Reference#