Install & Management

Install

Linux Ubuntu

Ubuntu apt includes PostgreSQL by default, install it with:

apt install postgresql

Docker Images

Docker Composer

compose.yaml

postgres:
  image: postgres:16-alpine
  environment:
    POSTGRES_DB: db-name
    POSTGRES_USER: db-root-user
    POSTGRES_PASSWORD: db-root-pass
    # OR (if using secrets)
    POSTGRES_PASSWORD_FILE: /run/secrets/root_pass
  secrets:
    - root_pass
  healthcheck:
    test: ['CMD', 'pg_isready', '-U', 'db-root-user']
    interval: 10s
    timeout: 5s
    retries: 5
  ports:
    - '5432:5432'
  volumes:
    - ./init:/docker-entrypoint-initdb.d
    - postgres:/var/lib/postgresql/data
  # To add pg_stat_statement
  command: >
    postgres
    -c shared_preload_libraries=pg_stat_statements
    -c pg_stat_statements.track=all
    -c pg_stat_statements.max=10000
    -c track_activity_query_size=2048

./init/enable_pg_stat_statement.sql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Execute

Linux & Docker Images

psql 

Manage

Databases

-- List all databases
\l
-- or
\list

Schemas

-- List all schemas in the current database
\dn

Tables

-- List all tables in the current schema
\dt

-- List all tables in the current schema with extra/detailed information
\dt+

-- List tables from a specific schema
\dt schema_name.*

-- Describe a table (structure)
\d table_name

Indexes

-- List all indexes in the current schema
\di

-- List indexes for a specific table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';

Constraints

-- List all constraints on a table
SELECT conname, contype, conrelid::regclass
FROM pg_constraint
WHERE conrelid = 'your_table'::regclass;

Functions

-- List all user-defined functions
\df

-- Describe a function
\df+ function_name

-- Functions from specific schema
\df schema_name.*

Views

-- List all views
\dv

Roles/Users

-- List roles
\du

-- List users (same as roles in Postgres)
SELECT * FROM pg_user;