Installation

Ubuntu

apt install postgresql

This will install the database and a bunch of tools to connect/manage database.

Important concepts

Before going any further, let's explore the concepts of resources (user, database, table, etc) defined by PostgresSQL.

Role name
- AKA username. You need to present a role name whenever you connect to the PostgresSQL (like username when logging into a computer).
Database
- You also need to choose a database to load, whenever you connect to the PostgresSQL. A database is like a collection of tables.
Table
- The SQL table we are familiar about. A table can contain rows of data. A table has a data schema.

Default configuration

After install, some resources will be created by default, and a default configuration file will be used that controls access over network.

Default role name: postgres
Default database: postgres

This is important later

Configuration file location (Ubuntu)

Main configuration: /etc/postgresql/<version>/main/postgresql.conf
- Defines listen address, port, max connection allowed, etc
Network access: /etc/postgresql/<version>/main/pg_hba.conf
- Defines network access, allow/deny list of IP addresses

Recommended change

Change listen address

Open /etc/postgresql/<version>/main/postgresql.conf, and edit this line to:

listen_addresses = '*'

This allows connection from other computers in the network.

Change network access

For example, if your PostgresSQL service runs on machine A, and you want to access it on machine B which is on the same LAN. You need to open and edit /etc/postgresql/<version>/main/pg_hba.conf, and add

# Allow LAN
host    all             all             192.168.0.0/24          trust

My pg_hba.conf looks like this

# Database administrative login by Unix domain socket
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
# Allow Docker network
host    all             all             172.17.0.0/16           trust
# Allow Localhost
host    all             all             127.0.0.0/24            trust
# Allow LAN
host    all             all             192.168.0.0/24          trust
# IPv6 local connections:
(unchanged)

I didn't care about security so I set trust to all of the rules.
Look up document to see if you need something more secure! https://www.postgresql.org/docs/current/auth-methods.html

Restart service to apply change.

(Ubuntu) sudo systemctl restart postgresql

Connect to DB

Finally, time to connect to the PostgresSQL and actually do something!

To do that, we use a command line tools that comes with the install: psql

If you simply run psql without any arguments, you will probably get an error message, saying role does not exist.

psql: error: connection to server on socket "xxx" failed: FATAL:  role "user1" does not exist

This is because, by default, psql will try to login using current user $USER, try to connect to a database the same name as current user.

You can specify which user or database to connect to
psql -U <username> -d <database>
Run psql --help for more

postgres user

Like we mentioned before, a role name and a database called postgres will be created by default.

So to connect to database, lets use this role name.

psql -U postgres
# or
sudo -u postgres psql

Now we are in the system, we can create other users.

Create user and database

Launch psql, and type

CREATE USER <role name>;

Also you need to create a matching database

CREATE DATABASE <role name>;

Now you can connect to db using this name

psql -U <role name>

Some helper command for psql

View all roles: \du
View all databases: \list or \l for short

Now you can finally create tables and add data and do SQLs.

Connection string

You probably need to provide a connection string for applications to connect to database.

See: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

PostgresSQL Getting Started