Table of Contents

1. PostgreSQL Administration Command Reference
   1. 1. PostgreSQL Installation
      1. RHEL / Rocky Linux / AlmaLinux / Fedora
      2. Debian / Ubuntu
   2. 2. Connecting to PostgreSQL
      1. Connect as the local PostgreSQL administrator
      2. Connect to a specific database
      3. Connect with a PostgreSQL username
      4. Connect to a remote PostgreSQL server
      5. Force a password prompt
      6. Connect using a PostgreSQL URI
   3. 3. Useful psql Commands
      1. Show connection information
      2. List databases
      3. Connect to another database
      4. List PostgreSQL users and roles
      5. List schemas
      6. List tables
      7. List tables in all schemas
      8. Describe a table
      9. Show detailed table information
      10. List indexes
      11. List sequences
      12. List views
      13. Show command history
      14. Run a SQL file
      15. Toggle expanded output
      16. Show query execution time
      17. Quit psql
   4. 4. Database and User Setup
      1. Create a PostgreSQL user
      2. Create a login role
      3. Create a database
      4. Create a database owned by a specific user
      5. Create a database from the shell
      6. Create a user from the shell
      7. Change a user’s password
      8. Change database ownership
      9. Allow a user to connect to a database
      10. Grant all database privileges
   5. 5. Schema Setup and Permissions
      1. Create a schema
      2. Create a schema only if it does not exist
      3. Change schema ownership
      4. Grant access to a schema
      5. Allow object creation in a schema
      6. Grant access to all existing tables
      7. Grant access to all existing sequences
      8. Set default privileges for future tables
      9. Set default privileges for future sequences
   6. 6. Basic Table Creation
      1. Create a simple table
      2. Create a table only if it does not exist
      3. Example auction table
      4. Create a foreign key
      5. Create an index
      6. Create a multi-column index
      7. Create a unique index
   7. 7. Common Data Commands
      1. Insert a row
      2. Insert multiple rows
      3. Query all rows
      4. Query selected columns
      5. Filter rows
      6. Sort results
      7. Limit results
      8. Update rows
      9. Delete rows
      10. Count rows
      11. Count rows by value
   8. 8. Modifying Tables
      1. Add a column
      2. Add a column with a default
      3. Rename a column
      4. Change a column data type
      5. Set a default value
      6. Remove a default value
      7. Add a NOT NULL constraint
      8. Remove a NOT NULL constraint
      9. Add a check constraint
      10. Drop a constraint
      11. Rename a table
      12. Change table ownership
      13. Drop a column
      14. Drop a table
      15. Drop a table only if it exists
      16. Drop a table and dependent objects
   9. 9. Transactions
      1. Start a transaction
      2. Commit changes
      3. Undo uncommitted changes
      4. Example safe update
   10. 10. Inspecting Database Objects
       1. Show table columns using SQL
       2. Show table constraints
       3. Show indexes for a table
       4. Show database sizes
       5. Show table sizes
       6. Show active connections
2. Database Backups
   1. 11. Dump the Full Database
      1. Custom-format backup
      2. Plain SQL full database backup
      3. Compressed plain SQL backup
      4. Timestamped backup filename
   2. 12. Dump Schema Only
      1. Schema-only custom-format dump
      2. Schema-only plain SQL dump
      3. Dump one PostgreSQL schema only
      4. Dump schema without ownership statements
   3. 13. Dump Data Only
      1. Dump all table data without schema definitions
      2. Dump data using custom format
   4. 14. Dump Specific Tables
      1. Dump one table with schema and data
      2. Dump one table’s data only
      3. Dump multiple tables
      4. Exclude a table
      5. Exclude table data but keep its schema
   5. 15. Dump All PostgreSQL Databases
      1. Dump only PostgreSQL roles and tablespaces
3. Database Restores
   1. 16. Restore a Plain SQL Backup
      1. Restore a compressed SQL backup
   2. 17. Restore a Custom-Format Backup
      1. Restore into an existing database
      2. Drop existing objects before restoring
      3. Restore without original ownership
      4. Restore and create the database from the dump
      5. Restore only the schema
      6. Restore only the data
      7. List backup contents
      8. Restore in parallel
   3. 18. Restore All Databases and Roles
   4. 19. Recreate a Database Before Restore
4. Docker PostgreSQL
   1. 20. Basic Docker Compose PostgreSQL Service
   2. 21. Start and Inspect the Container
   3. 22. Connect to PostgreSQL Inside Docker
   4. 23. Back Up a Docker PostgreSQL Database
      1. Custom-format backup to the host
      2. Plain SQL backup to the host
      3. Schema-only Docker backup
      4. Data-only Docker backup
      5. Timestamped Docker backup
   5. 24. Restore a Docker PostgreSQL Database
      1. Restore a plain SQL file
      2. Restore a custom-format dump
      3. Restore through standard input
   6. 25. PostgreSQL Initialization Scripts in Docker
5. PostgreSQL Authentication and Configuration
   1. 26. Find PostgreSQL Configuration Files
   2. 27. Allow Password Authentication
   3. 28. Listen on Network Interfaces
6. Password Handling
   1. 29. Use a .pgpass File
   2. 30. Use PGPASSWORD Temporarily
7. Backup Automation
   1. 31. Simple Backup Script
   2. 32. Cron Backup Example
   3. 33. systemd Service and Timer
8. Maintenance and Troubleshooting
   1. 34. Analyze and Vacuum
      1. Run from the shell
   2. 35. Reindex
   3. 36. Check Long-Running Queries
   4. 37. Check Locks
   5. 38. Common Error: Peer Authentication Failed
   6. 39. Common Error: Server and Client Version Mismatch
   7. 40. Common Error: Could Not Change Directory
9. Roles and Security
   1. 41. Role Management
      1. Create a read-only role
      2. Assign the role to a user
      3. Remove a role from a user
      4. Remove login access
      5. Restore login access
      6. Drop a user or role
10. Migration Workflow
    1. 42. Recommended Schema Change Workflow
11. Quick Reference
    1. 43. Frequently Used Commands
       1. Connect locally
       2. List databases
       3. List users
       4. List tables
       5. Describe a table
       6. Full database dump
       7. Schema-only dump
       8. Data-only dump
       9. Restore custom dump
       10. Restore plain SQL
       11. Docker full database dump
       12. Docker schema dump
       13. Docker restore
12. Backup Checklist

---

PostgreSQL Administration Command Reference

A practical reference for setting up PostgreSQL, creating databases and users, inspecting schemas, modifying databases, and performing backups and restores.

Replace example values such as hydra_auctions, hydra_user, localhost, and file paths before running commands.

---

1. PostgreSQL Installation

RHEL / Rocky Linux / AlmaLinux / Fedora

Install the PostgreSQL client and server packages:

sudo dnf install postgresql postgresql-server

Initialize the database cluster:

sudo postgresql-setup --initdb

Enable and start PostgreSQL:

sudo systemctl enable --now postgresql

Check service status:

sudo systemctl status postgresql

Check installed versions:

psql --version
postgres --version
pg_dump --version

Debian / Ubuntu

sudo apt update
sudo apt install postgresql postgresql-client
sudo systemctl enable --now postgresql

---

2. Connecting to PostgreSQL

Connect as the local PostgreSQL administrator

sudo -u postgres psql

Connect to a specific database

sudo -u postgres psql -d hydra_auctions

Connect with a PostgreSQL username

psql -U hydra_user -d hydra_auctions

Connect to a remote PostgreSQL server

psql -h 192.168.1.50 -p 5432 -U hydra_user -d hydra_auctions

Force a password prompt

psql -h localhost -U hydra_user -d hydra_auctions -W

Connect using a PostgreSQL URI

psql "postgresql://hydra_user:password@localhost:5432/hydra_auctions"

Avoid putting real passwords directly in shell history when possible.

---

3. Useful psql Commands

These commands are entered from inside the psql prompt.

Show connection information

\conninfo

List databases

\l

or:

\list

Connect to another database

\c hydra_auctions

List PostgreSQL users and roles

\du

List schemas

\dn

List tables

\dt

List tables in all schemas

\dt *.*

Describe a table

\d auctions

Show detailed table information

\d+ auctions

List indexes

\di

List sequences

\ds

List views

\dv

Show command history

\s

Run a SQL file

\i /path/to/schema.sql

Toggle expanded output

Useful for wide rows:

\x

Show query execution time

\timing

Quit psql

\q

---

4. Database and User Setup

Create a PostgreSQL user

Run from psql as an administrator:

CREATE USER hydra_user WITH PASSWORD 'replace-with-secure-password';

Create a login role

CREATE ROLE hydra_user
WITH LOGIN
PASSWORD 'replace-with-secure-password';

Create a database

CREATE DATABASE hydra_auctions;

Create a database owned by a specific user

CREATE DATABASE hydra_auctions
OWNER hydra_user;

Create a database from the shell

sudo -u postgres createdb hydra_auctions

Create a user from the shell

sudo -u postgres createuser --pwprompt hydra_user

Change a user’s password

ALTER USER hydra_user
WITH PASSWORD 'new-secure-password';

Change database ownership

ALTER DATABASE hydra_auctions
OWNER TO hydra_user;

Allow a user to connect to a database

GRANT CONNECT ON DATABASE hydra_auctions TO hydra_user;

Grant all database privileges

GRANT ALL PRIVILEGES ON DATABASE hydra_auctions TO hydra_user;

Database-level privileges do not automatically grant full access to existing tables.

---

5. Schema Setup and Permissions

Create a schema

CREATE SCHEMA hydra
AUTHORIZATION hydra_user;

Create a schema only if it does not exist

CREATE SCHEMA IF NOT EXISTS hydra
AUTHORIZATION hydra_user;

Change schema ownership

ALTER SCHEMA public
OWNER TO hydra_user;

Grant access to a schema

GRANT USAGE ON SCHEMA public TO hydra_user;

Allow object creation in a schema

GRANT CREATE ON SCHEMA public TO hydra_user;

Grant access to all existing tables

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO hydra_user;

Grant access to all existing sequences

Required for SERIAL and identity columns:

GRANT USAGE, SELECT, UPDATE
ON ALL SEQUENCES IN SCHEMA public
TO hydra_user;

Set default privileges for future tables

Run this as the role that will create the tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES TO hydra_user;

Set default privileges for future sequences

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE
ON SEQUENCES TO hydra_user;

---

6. Basic Table Creation

Create a simple table

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Create a table only if it does not exist

CREATE TABLE IF NOT EXISTS users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Example auction table

CREATE TABLE auctions (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    scan_id BIGINT NOT NULL,
    item_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    buyout BIGINT NOT NULL CHECK (buyout >= 0),
    faction VARCHAR(20),
    scanned_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Create a foreign key

ALTER TABLE auctions
ADD CONSTRAINT auctions_scan_id_fkey
FOREIGN KEY (scan_id)
REFERENCES scans(id)
ON DELETE CASCADE;

Create an index

CREATE INDEX idx_auctions_item_id
ON auctions(item_id);

Create a multi-column index

CREATE INDEX idx_auctions_item_faction
ON auctions(item_id, faction);

Create a unique index

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

---

7. Common Data Commands

Insert a row

INSERT INTO users (username, email)
VALUES ('example_user', 'user@example.com');

Insert multiple rows

INSERT INTO users (username, email)
VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com');

Query all rows

SELECT *
FROM users;

Query selected columns

SELECT id, username, created_at
FROM users;

Filter rows

SELECT *
FROM auctions
WHERE item_id = 818;

Sort results

SELECT *
FROM auctions
ORDER BY scanned_at DESC;

Limit results

SELECT *
FROM auctions
ORDER BY scanned_at DESC
LIMIT 25;

Update rows

UPDATE users
SET email = 'new-address@example.com'
WHERE username = 'example_user';

Delete rows

DELETE FROM users
WHERE username = 'example_user';

Count rows

SELECT COUNT(*)
FROM auctions;

Count rows by value

SELECT faction, COUNT(*)
FROM auctions
GROUP BY faction
ORDER BY COUNT(*) DESC;

---

8. Modifying Tables

Add a column

ALTER TABLE users
ADD COLUMN last_login TIMESTAMPTZ;

Add a column with a default

ALTER TABLE users
ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE;

Rename a column

ALTER TABLE users
RENAME COLUMN username TO account_name;

Change a column data type

ALTER TABLE auctions
ALTER COLUMN buyout TYPE NUMERIC(20, 0);

For complex conversions, use USING:

ALTER TABLE users
ALTER COLUMN id TYPE BIGINT
USING id::BIGINT;

Set a default value

ALTER TABLE users
ALTER COLUMN created_at SET DEFAULT NOW();

Remove a default value

ALTER TABLE users
ALTER COLUMN created_at DROP DEFAULT;

Add a NOT NULL constraint

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

Before doing this, verify that no existing rows contain NULL.

Remove a NOT NULL constraint

ALTER TABLE users
ALTER COLUMN email DROP NOT NULL;

Add a check constraint

ALTER TABLE auctions
ADD CONSTRAINT auctions_buyout_nonnegative
CHECK (buyout >= 0);

Drop a constraint

ALTER TABLE auctions
DROP CONSTRAINT auctions_buyout_nonnegative;

Rename a table

ALTER TABLE users
RENAME TO application_users;

Change table ownership

ALTER TABLE auctions
OWNER TO hydra_user;

Drop a column

ALTER TABLE users
DROP COLUMN last_login;

Drop a table

DROP TABLE users;

Drop a table only if it exists

DROP TABLE IF EXISTS users;

Drop a table and dependent objects

Use carefully:

DROP TABLE IF EXISTS users CASCADE;

---

9. Transactions

Transactions allow multiple changes to succeed or fail together.

Start a transaction

BEGIN;

Commit changes

COMMIT;

Undo uncommitted changes

ROLLBACK;

Example safe update

BEGIN;

UPDATE auctions
SET buyout = 0
WHERE item_id = 818;

SELECT *
FROM auctions
WHERE item_id = 818;

COMMIT;

Replace COMMIT with ROLLBACK if the results are incorrect.

---

10. Inspecting Database Objects

Show table columns using SQL

SELECT
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'auctions'
ORDER BY ordinal_position;

Show table constraints

SELECT
    constraint_name,
    constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'public'
  AND table_name = 'auctions';

Show indexes for a table

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'auctions';

Show database sizes

SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Show table sizes

SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Show active connections

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    query_start,
    query
FROM pg_stat_activity
ORDER BY query_start;

---

Database Backups

11. Dump the Full Database

A full database dump includes schema definitions and data.

Custom-format backup

Recommended for most backups:

pg_dump \
  -h localhost \
  -p 5432 \
  -U hydra_user \
  -d hydra_auctions \
  -F c \
  -f hydra_auctions.dump

Options:

• -F c: custom archive format
• -f: output file
• -d: database name
• -U: PostgreSQL username
• -h: PostgreSQL host
• -p: PostgreSQL port

Plain SQL full database backup

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  -F p \
  -f hydra_auctions.sql

A plain SQL backup can be viewed in a text editor and restored with psql.

Compressed plain SQL backup

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  | gzip > hydra_auctions.sql.gz

Timestamped backup filename

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  -F c \
  -f "hydra_auctions_$(date +%F_%H-%M-%S).dump"

---

12. Dump Schema Only

A schema-only dump contains tables, sequences, constraints, indexes, functions, and other definitions, but no table data.

Schema-only custom-format dump

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --schema-only \
  -F c \
  -f hydra_auctions_schema.dump

Schema-only plain SQL dump

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --schema-only \
  -f hydra_auctions_schema.sql

Dump one PostgreSQL schema only

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --schema=public \
  --schema-only \
  -f public_schema.sql

Dump schema without ownership statements

Useful when restoring under a different PostgreSQL user:

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --schema-only \
  --no-owner \
  --no-privileges \
  -f hydra_auctions_schema.sql

---

13. Dump Data Only

Dump all table data without schema definitions

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --data-only \
  -f hydra_auctions_data.sql

Dump data using custom format

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --data-only \
  -F c \
  -f hydra_auctions_data.dump

---

14. Dump Specific Tables

Dump one table with schema and data

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --table=auctions \
  -f auctions.sql

Dump one table’s data only

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --table=auctions \
  --data-only \
  -f auctions_data.sql

Dump multiple tables

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --table=scans \
  --table=auctions \
  -F c \
  -f auction_tables.dump

Exclude a table

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --exclude-table=temporary_data \
  -F c \
  -f hydra_without_temp.dump

Exclude table data but keep its schema

pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --exclude-table-data=logs \
  -F c \
  -f hydra_without_log_data.dump

---

15. Dump All PostgreSQL Databases

pg_dumpall creates a plain SQL file containing all databases and global objects.

sudo -u postgres pg_dumpall > all_databases.sql

Compressed version:

sudo -u postgres pg_dumpall | gzip > all_databases.sql.gz

Dump only PostgreSQL roles and tablespaces

sudo -u postgres pg_dumpall --globals-only > postgres_globals.sql

This is useful because normal pg_dump backups do not include cluster-wide roles.

---

Database Restores

16. Restore a Plain SQL Backup

Create the target database first:

sudo -u postgres createdb hydra_auctions

Restore the SQL file:

psql \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  -f hydra_auctions.sql

Restore a compressed SQL backup

gunzip -c hydra_auctions.sql.gz \
  | psql -h localhost -U hydra_user -d hydra_auctions

---

17. Restore a Custom-Format Backup

Restore into an existing database

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  hydra_auctions.dump

Drop existing objects before restoring

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --clean \
  --if-exists \
  hydra_auctions.dump

Restore without original ownership

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --no-owner \
  hydra_auctions.dump

Restore and create the database from the dump

The dump must contain database creation metadata:

pg_restore \
  -h localhost \
  -U postgres \
  --create \
  -d postgres \
  hydra_auctions.dump

Restore only the schema

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --schema-only \
  hydra_auctions.dump

Restore only the data

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --data-only \
  hydra_auctions.dump

List backup contents

pg_restore --list hydra_auctions.dump

Restore in parallel

Custom and directory formats support parallel restore:

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --jobs=4 \
  hydra_auctions.dump

---

18. Restore All Databases and Roles

sudo -u postgres psql -f all_databases.sql

For a compressed dump:

gunzip -c all_databases.sql.gz \
  | sudo -u postgres psql

---

19. Recreate a Database Before Restore

Terminate existing connections:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'hydra_auctions'
  AND pid <> pg_backend_pid();

Drop and recreate the database:

sudo -u postgres dropdb hydra_auctions
sudo -u postgres createdb -O hydra_user hydra_auctions

Restore:

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --no-owner \
  hydra_auctions.dump

---

Docker PostgreSQL

20. Basic Docker Compose PostgreSQL Service

services:
  db:
    image: postgres:16
    container_name: hydra-postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - hydra_postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  hydra_postgres_data:

Example .env file:

POSTGRES_DB=hydra_auctions
POSTGRES_USER=hydra_user
POSTGRES_PASSWORD=replace-with-secure-password

The final volumes: section declares the named volume. Docker creates and manages the actual storage location.

---

21. Start and Inspect the Container

docker compose up -d

docker compose ps

docker compose logs db

docker compose logs -f db

docker inspect hydra-postgres

---

22. Connect to PostgreSQL Inside Docker

docker exec -it hydra-postgres \
  psql -U hydra_user -d hydra_auctions

Using Docker Compose:

docker compose exec db \
  psql -U hydra_user -d hydra_auctions

---

23. Back Up a Docker PostgreSQL Database

Custom-format backup to the host

docker exec hydra-postgres \
  pg_dump -U hydra_user -d hydra_auctions -F c \
  > hydra_auctions.dump

Plain SQL backup to the host

docker exec hydra-postgres \
  pg_dump -U hydra_user -d hydra_auctions \
  > hydra_auctions.sql

Schema-only Docker backup

docker exec hydra-postgres \
  pg_dump -U hydra_user -d hydra_auctions --schema-only \
  > hydra_auctions_schema.sql

Data-only Docker backup

docker exec hydra-postgres \
  pg_dump -U hydra_user -d hydra_auctions --data-only \
  > hydra_auctions_data.sql

Timestamped Docker backup

docker exec hydra-postgres \
  pg_dump -U hydra_user -d hydra_auctions -F c \
  > "hydra_auctions_$(date +%F_%H-%M-%S).dump"

---

24. Restore a Docker PostgreSQL Database

Restore a plain SQL file

cat hydra_auctions.sql \
  | docker exec -i hydra-postgres \
      psql -U hydra_user -d hydra_auctions

Restore a custom-format dump

Copy the dump into the container:

docker cp hydra_auctions.dump \
  hydra-postgres:/tmp/hydra_auctions.dump

Restore it:

docker exec -it hydra-postgres \
  pg_restore \
  -U hydra_user \
  -d hydra_auctions \
  --clean \
  --if-exists \
  --no-owner \
  /tmp/hydra_auctions.dump

Remove the copied dump:

docker exec hydra-postgres \
  rm /tmp/hydra_auctions.dump

Restore through standard input

docker exec -i hydra-postgres \
  pg_restore \
  -U hydra_user \
  -d hydra_auctions \
  --no-owner \
  < hydra_auctions.dump

---

25. PostgreSQL Initialization Scripts in Docker

SQL and shell files placed in /docker-entrypoint-initdb.d/ run only when the PostgreSQL data directory is first initialized.

Example Compose mount:

services:
  db:
    image: postgres:16
    volumes:
      - hydra_postgres_data:/var/lib/postgresql/data
      - ./database/init:/docker-entrypoint-initdb.d:ro

Example project structure:

database/
└── init/
    ├── 01-schema.sql
    └── 02-seed-data.sql

Important:

• Initialization scripts do not run again when an existing named volume is reused.
• Removing the volume deletes the database data.
• Use migrations such as Alembic for ongoing schema changes.

Remove containers and the database volume:

docker compose down -v

This permanently deletes the database stored in the Compose volume.

---

PostgreSQL Authentication and Configuration

26. Find PostgreSQL Configuration Files

From psql:

SHOW config_file;

SHOW hba_file;

SHOW data_directory;

Common files:

postgresql.conf
pg_hba.conf
pg_ident.conf

---

27. Allow Password Authentication

Example pg_hba.conf entries:

host    all    all    127.0.0.1/32    scram-sha-256
host    all    all    ::1/128         scram-sha-256

For a trusted private network:

host    hydra_auctions    hydra_user    192.168.1.0/24    scram-sha-256

Reload PostgreSQL after changing pg_hba.conf:

sudo systemctl reload postgresql

or from SQL:

SELECT pg_reload_conf();

---

28. Listen on Network Interfaces

In postgresql.conf:

listen_addresses = '*'
port = 5432

Restart PostgreSQL:

sudo systemctl restart postgresql

Open the firewall only for required sources:

sudo firewall-cmd \
  --permanent \
  --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'

sudo firewall-cmd --reload

Do not expose PostgreSQL directly to the public internet unless it is protected by strong network controls.

---

Password Handling

29. Use a .pgpass File

Create:

nano ~/.pgpass

Format:

hostname:port:database:username:password

Example:

localhost:5432:hydra_auctions:hydra_user:replace-with-password

Set required permissions:

chmod 600 ~/.pgpass

Then run commands without an interactive password prompt:

pg_dump -h localhost -U hydra_user -d hydra_auctions -F c -f backup.dump

---

30. Use PGPASSWORD Temporarily

PGPASSWORD='replace-with-password' \
pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  -F c \
  -f hydra_auctions.dump

This can expose the password to process inspection or shell history. Prefer .pgpass, Docker secrets, or another secrets manager.

---

Backup Automation

31. Simple Backup Script

#!/usr/bin/env bash

set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
DATABASE="hydra_auctions"
USER_NAME="hydra_user"
HOST_NAME="localhost"
RETENTION_DAYS=14
TIMESTAMP="$(date +%F_%H-%M-%S)"
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_${TIMESTAMP}.dump"

mkdir -p "$BACKUP_DIR"

pg_dump \
  -h "$HOST_NAME" \
  -U "$USER_NAME" \
  -d "$DATABASE" \
  -F c \
  -f "$BACKUP_FILE"

find "$BACKUP_DIR" \
  -type f \
  -name "${DATABASE}_*.dump" \
  -mtime +"$RETENTION_DAYS" \
  -delete

echo "Backup completed: $BACKUP_FILE"

Make it executable:

chmod +x /usr/local/sbin/backup-hydra-postgres.sh

Run it:

/usr/local/sbin/backup-hydra-postgres.sh

---

32. Cron Backup Example

Edit the current user’s crontab:

crontab -e

Run every day at 2:00 AM:

0 2 * * * /usr/local/sbin/backup-hydra-postgres.sh >> /var/log/hydra-postgres-backup.log 2>&1

---

33. systemd Service and Timer

Service file:

# /etc/systemd/system/hydra-postgres-backup.service

[Unit]
Description=Back up Hydra PostgreSQL database

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/backup-hydra-postgres.sh

Timer file:

# /etc/systemd/system/hydra-postgres-backup.timer

[Unit]
Description=Run Hydra PostgreSQL backup daily

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable the timer:

sudo systemctl daemon-reload
sudo systemctl enable --now hydra-postgres-backup.timer

Check the timer:

systemctl list-timers hydra-postgres-backup.timer

Run the backup manually:

sudo systemctl start hydra-postgres-backup.service

View logs:

journalctl -u hydra-postgres-backup.service

---

Maintenance and Troubleshooting

34. Analyze and Vacuum

Update query planner statistics:

ANALYZE;

Vacuum all tables in the current database:

VACUUM;

Vacuum and analyze:

VACUUM ANALYZE;

Reclaim more disk space with an exclusive lock:

VACUUM FULL;

Use VACUUM FULL carefully because it locks and rewrites tables.

Run from the shell

vacuumdb \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  --analyze

---

35. Reindex

Reindex one table:

REINDEX TABLE auctions;

Reindex a database:

REINDEX DATABASE hydra_auctions;

---

36. Check Long-Running Queries

SELECT
    pid,
    now() - query_start AS duration,
    usename,
    datname,
    state,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

Cancel a query:

SELECT pg_cancel_backend(12345);

Terminate a connection:

SELECT pg_terminate_backend(12345);

---

37. Check Locks

SELECT
    locktype,
    database,
    relation::regclass,
    mode,
    granted,
    pid
FROM pg_locks
ORDER BY granted, pid;

---

38. Common Error: Peer Authentication Failed

Example error:

FATAL: Peer authentication failed for user "postgres"

Use the operating-system postgres account:

sudo -u postgres psql

Or connect using TCP and password authentication:

psql -h localhost -U postgres -d postgres -W

The authentication behavior is controlled by pg_hba.conf.

---

39. Common Error: Server and Client Version Mismatch

Example:

pg_dump: error: server version: 16.x
pg_dump version: 13.x

Use a pg_dump version that is the same major version as the server or newer.

Check versions:

pg_dump --version

SELECT version();

On RHEL-based systems, install the matching PostgreSQL client package or use the pg_dump binary from the matching PostgreSQL installation.

Example path:

/usr/pgsql-16/bin/pg_dump --version

Example backup:

/usr/pgsql-16/bin/pg_dump \
  -h localhost \
  -U hydra_user \
  -d hydra_auctions \
  -F c \
  -f hydra_auctions.dump

---

40. Common Error: Could Not Change Directory

Example:

could not change directory to "/root": Permission denied

This commonly happens when using:

sudo -u postgres ...

from a directory the postgres operating-system user cannot access.

Change to a readable directory first:

cd /tmp
sudo -u postgres pg_dump hydra_auctions > /tmp/hydra_auctions.sql

Or write the backup to a directory owned by postgres.

---

Roles and Security

41. Role Management

Create a read-only role

CREATE ROLE hydra_readonly;

GRANT CONNECT ON DATABASE hydra_auctions TO hydra_readonly;

GRANT USAGE ON SCHEMA public TO hydra_readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO hydra_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO hydra_readonly;

Assign the role to a user

GRANT hydra_readonly TO reporting_user;

Remove a role from a user

REVOKE hydra_readonly FROM reporting_user;

Remove login access

ALTER ROLE hydra_user NOLOGIN;

Restore login access

ALTER ROLE hydra_user LOGIN;

Drop a user or role

DROP ROLE hydra_user;

Before dropping a role, ownership and privileges may need to be reassigned:

REASSIGN OWNED BY hydra_user TO postgres;
DROP OWNED BY hydra_user;
DROP ROLE hydra_user;

---

Migration Workflow

42. Recommended Schema Change Workflow

1. Back up the database.
2. Test the change against a development or staging database.
3. Put changes in a migration file.
4. Run the migration inside a transaction when possible.
5. Verify the schema and application behavior.
6. Keep the migration file in source control.

Example SQL migration:

BEGIN;

ALTER TABLE auctions
ADD COLUMN source VARCHAR(50);

CREATE INDEX idx_auctions_source
ON auctions(source);

COMMIT;

Rollback migration:

BEGIN;

DROP INDEX IF EXISTS idx_auctions_source;

ALTER TABLE auctions
DROP COLUMN IF EXISTS source;

COMMIT;

For Python applications using SQLAlchemy, Alembic is commonly used to track and apply schema migrations.

---

Quick Reference

43. Frequently Used Commands

Connect locally

sudo -u postgres psql

List databases

\l

List users

\du

List tables

\dt

Describe a table

\d+ auctions

Full database dump

pg_dump -h localhost -U hydra_user -d hydra_auctions -F c -f hydra_auctions.dump

Schema-only dump

pg_dump -h localhost -U hydra_user -d hydra_auctions --schema-only -f hydra_schema.sql

Data-only dump

pg_dump -h localhost -U hydra_user -d hydra_auctions --data-only -f hydra_data.sql

Restore custom dump

pg_restore -h localhost -U hydra_user -d hydra_auctions --no-owner hydra_auctions.dump

Restore plain SQL

psql -h localhost -U hydra_user -d hydra_auctions -f hydra_auctions.sql

Docker full database dump

docker exec hydra-postgres pg_dump -U hydra_user -d hydra_auctions -F c > hydra_auctions.dump

Docker schema dump

docker exec hydra-postgres pg_dump -U hydra_user -d hydra_auctions --schema-only > hydra_schema.sql

Docker restore

docker exec -i hydra-postgres pg_restore -U hydra_user -d hydra_auctions --no-owner < hydra_auctions.dump

---

Backup Checklist

Before relying on a backup:

• Confirm the backup command completed successfully.
• Confirm the backup file is not empty.
• Store copies outside the PostgreSQL server or Docker host.
• Encrypt backups containing sensitive information.
• Test restores regularly.
• Keep PostgreSQL role backups when needed.
• Keep multiple generations of backups.
• Document the PostgreSQL major version used to create each backup.

Check file size:

ls -lh hydra_auctions.dump

Inspect a custom backup:

pg_restore --list hydra_auctions.dump | head

Test a restore into a temporary database:

createdb -h localhost -U hydra_user hydra_restore_test

pg_restore \
  -h localhost \
  -U hydra_user \
  -d hydra_restore_test \
  --no-owner \
  hydra_auctions.dump

Delete the test database:

dropdb -h localhost -U hydra_user hydra_restore_test