PostgreSQL

Connection

Connecting to databases

Meta Commands

psql meta commands

10commands

Backup/Restore

pg_dump & pg_restore

11commands

Import/Export

Data import & export

Administration

Database administration

9commands

Query Analysis

Query optimization

Server

Server management

Performance

Performance tuning

17commands

Replication

Replication & HA

Users & Roles

User management, roles, grants & permissions

28commands

Run PostgreSQL with Docker

$ docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres --name postgres postgres:alpine

Start PostgreSQL server in a Docker container on port 5432

Connect to PostgreSQL

$ docker exec -it postgres psql -U postgres

Open interactive psql session in the running container

List databases

$ psql -U postgres -c '\l'

List all databases in PostgreSQL

Connect to database

$ psql -h localhost -U postgres -d mydb

Connect with host, user, and database.

Connect with URL

$ psql postgresql://user:pass@localhost:5432/mydb

Connect using connection string.

Connect with SSL

$ psql "sslmode=require host=db.example.com dbname=mydb user=app"

Connect with SSL required.

Execute and exit

$ psql -c "SELECT version();" -d postgres

Run single command and exit.

Run SQL file

$ psql -f script.sql -d mydb

Execute SQL file against database.

Connection info

$ \conninfo

Display current connection info (in psql).

List databases

$ \l

List all databases.

Connect to database

$ \c dbname

Switch to another database.

List tables

$ \dt

List tables in current schema.

List all tables

$ \dt *.*

List tables in all schemas.

Describe table

$ \d tablename

Show table structure.

List schemas

$ \dn

List all schemas.

List indexes

$ \di

List indexes.

List functions

$ \df

List functions.

List roles

$ \du

List users/roles.

Show search path

$ SHOW search_path;

Display current schema search path.

Dump database

$ pg_dump -U postgres mydb > backup.sql

Export database to SQL file.

Dump compressed

$ pg_dump -Fc -U postgres mydb > backup.dump

Export in custom compressed format.

Dump table

$ pg_dump -t users -U postgres mydb > users.sql

Export specific table.

Dump schema only

$ pg_dump -s -U postgres mydb > schema.sql

Export schema without data.

Dump data only

$ pg_dump -a -U postgres mydb > data.sql

Export data without schema.

Dump all databases

$ pg_dumpall -U postgres > all_dbs.sql

Export all databases including roles.

Restore SQL

$ psql -U postgres mydb < backup.sql

Restore from SQL file.

Restore custom format

$ pg_restore -U postgres -d mydb backup.dump

Restore from custom format dump.

Restore parallel

$ pg_restore -j 4 -U postgres -d mydb backup.dump

Parallel restore with 4 jobs.

Physical backup with progress

$ pg_basebackup -D /backup/dir -Ft -z -P

Create compressed tar backup with progress display.

Streaming basebackup

$ pg_basebackup -D /backup -F p -X stream

Physical backup with streaming WAL.

Copy to CSV

$ \copy users TO 'users.csv' WITH CSV HEADER

Export table to CSV file.

Copy from CSV

$ \copy users FROM 'users.csv' WITH CSV HEADER

Import data from CSV file.

COPY (server-side)

$ COPY users TO '/tmp/users.csv' WITH CSV HEADER;

Server-side export (requires superuser).

Query to file

$ \o output.txt

Send query results to file.

Import with options

$ \copy users FROM 'data.csv' DELIMITER ',' NULL 'NULL' CSV

Import with custom delimiter and null handling.

Create database

$ CREATE DATABASE mydb;

Create a new database.

Create user

$ CREATE USER appuser WITH PASSWORD 'secret';

Create a new user with password.

Grant privileges

$ GRANT ALL PRIVILEGES ON DATABASE mydb TO appuser;

Grant database privileges to user.

Active connections

$ SELECT * FROM pg_stat_activity;

View all active connections.

Kill connection

$ SELECT pg_terminate_backend(pid);

Terminate a backend process by PID.

Database size

$ SELECT pg_size_pretty(pg_database_size('mydb'));

Get database size in human-readable format.

Table sizes

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

List tables by size.

Vacuum analyze

$ VACUUM ANALYZE tablename;

Clean up and update statistics.

Reindex table

$ REINDEX TABLE tablename;

Rebuild table indexes.

Explain query

$ EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

Analyze query execution plan.

Long running queries

$ SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

Find slow running queries.

Index usage

$ SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

View index usage statistics.

Table stats

$ SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

View table statistics.

Lock info

$ SELECT * FROM pg_locks WHERE NOT granted;

View blocked locks.

Timing on

$ \timing on

Enable query timing display.

Start PostgreSQL server

$ pg_ctl start -D /var/lib/postgresql/data

Start the PostgreSQL server instance.

Stop server fast

$ pg_ctl stop -D /var/lib/postgresql/data -m fast

Stop server, abort connections, shutdown cleanly.

Reload configuration

$ pg_ctl reload -D /var/lib/postgresql/data

Reload server configuration without restart.

Replication status

$ SELECT * FROM pg_stat_replication;

Monitor replication status and lag.

Check if replica

$ SELECT pg_is_in_recovery();

Check if server is in recovery (replica) mode.

Stream WAL logs

$ pg_receivewal -D /wal/archive --slot slot_name

Stream WAL logs to archive directory.

View replication slots

$ SELECT * FROM pg_replication_slots;

List all replication slots and their status.

Create logical publication

$ CREATE PUBLICATION my_pub FOR TABLE users;

Create publication for logical replication.

Discussion

Loading comments...

PostgreSQL

PostgreSQL database administration with psql. Query data, manage users, backups, and server configuration.

103 commands

Browse by Topic

Getting Started

Quick setup and installation

Connection

Connecting to databases

Meta Commands

psql meta commands

10commands

Backup/Restore

pg_dump & pg_restore

11commands

Import/Export

Data import & export

Administration

Database administration

9commands

Query Analysis

Query optimization

Server

Server management

Performance

Performance tuning

17commands

Replication

Replication & HA