PostgreSQL

PostgreSQL is an advanced Relational Database Management System (RDBMS) designed for applications that require consistency, scalability, performance, and long-term reliability. It is ideal for applications that need secure data storage, complex relationships between entities, transactional consistency, and the ability to scale from small internal tools to large production platforms without changing database technology.

Installation

Arch Linux

yay postgresql
sudo -u postgres initdb -D /var/lib/postgres/data
sudo systemctl start postgresql

Docker

docker run --name postgres \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=my_database \
  -p 5432:5432 \
  -d postgres:16

Check https://hub.docker.com/_/postgres to select the best release If you're undecided, the best thing is always to take the last LTS (Long-Term Support) or the latest

Run a docker-compose with that image or run single container with that image with. Docker usually have a dedicated 5432 port to be used.

First Usage

sudo -u postgres psql

Create a database:

CREATE DATABASE <nome_db>;

Create a new user:

CREATE USER app_user WITH PASSWORD 'strongpassword';

Grant permissions:

GRANT ALL PRIVILEGES ON DATABASE company_data TO app_user;

Exit:

\q

Connect with your new user:

psql -U app_user -d company_data -h localhost

Basic Example

Create a table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Insert data:

INSERT INTO employees (name, email)
VALUES ('Alice Smith', 'alice@company.com');

Query data:

SELECT * FROM employees;