PostgreSQL Admin 101 Thread

postgresql-admin-101.md

Links

Threads

• 20250114 - Installation
• 20250115 - pg_hba.conf
• 20250116 - Terminologies
• 20250118 - Dockerizing PostgreSQL
• 20250124 - Postmaster, max_connections
• 20250127 - Intro to Replication, Physical, Streaming, Async Replication
• 20250128 - Tips on username/application_name/comment

References

• How to Install PostgreSQL on Ubuntu 24.04
• KloudDB Diagrams
• PostgreSQL Process Architecture
• Amazon RDS Read Replica

Commands

Docker Cheatsheet:

# Stop and delete container
docker rm -f container-name

# Enter the container using psql
docker exec -it lab-pg17 psql -U postgres

# Build the docker image
docker build -t image-name .

# Create new container
docker run --name lab-pg17 \
  -v /path/to/postgres-data:/var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=secret \
  -p 5433:5432 \
  -d lab-pg17

Meta commands

\conninfo -- show connection's info
\d -- list tables/relations
\db -- list tablespaces
\du -- list users
\dx -- list extensions
\l -- show databases

PostgreSQL config files:

show hba_file; -- pg_hba.conf
show config_file; -- postgresql.conf

Reload without restarting when changing something that loads at the start:

select pg_reload_conf();

See user passwords:

select * from pg_shadow;

Change a user's password

ALTER USER amree WITH PASSWORD 'password';
\password user

See the current password encryption (md5 or scram-sha-256)

show password_encryption;