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

# It by default will try to connect to a database that is the same name as the user,
# if this database doesn't exist an error will be raised.
psql -U <user_name>

# Optionally specify the database
psql -U <user_name> -d <database_name>

Manage

Databases

Listing

psql

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

Dumping

# Dump specific database
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
> dump.sql

Importing

# Import to specific database schema
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
  -f dump.sql

Schemas

Listing

psql

-- List all schemas in the current database
\dn

Dumping

# Dump specific database schema
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
  -n <schema_name>
> dump.sql

Importing

# Import to specific database schema
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
  # If schema is not specified it will use 'public'
  -n <schema_name>
  -f dump.sql

Tables

Listing

psql

-- 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

Creating tables

-- Create a table "like" another (with same columns, etc)
CREATE TABLE table2 LIKE table1;

Dumping

# Dump specific database schema table
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
  # If schema is not specified it will use 'public'
  -n <schema_name>
  -t <table_name>
> dump.sql

Importing

# Import to specific database schema table
pg_dump
  -h <host_name>
  -U <user_name>
  -d <database_name>
  # If schema is not specified it will use 'public'
  -n <schema_name>
  -t <table_name>
  -f dump.sql

Export to csv

COPY table_name TO 'location\file_name.csv' DELIMITER ',' CSV HEADER;

Indexes

psql

-- 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

psql

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

Functions

psql

-- List all user-defined functions
\df

-- Describe a function
\df+ function_name

-- Functions from specific schema
\df schema_name.*

Views

psql

-- List all views
\dv

Roles/Users

Listing

psql

-- List roles
\du

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

Permissions