Back to Details

postgresql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

PostgreSQL Administration

PostgreSQL 管理

Installation & Setup

安装与配置

bash
undefined
bash
undefined

On Linux (Ubuntu/Debian)

On Linux (Ubuntu/Debian)

sudo apt-get install postgresql postgresql-contrib
sudo apt-get install postgresql postgresql-contrib

On macOS

On macOS

brew install postgresql@15
brew install postgresql@15

Docker installation

Docker installation

docker run --name postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres:15
docker run --name postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres:15

Start and enable PostgreSQL

Start and enable PostgreSQL

sudo systemctl start postgresql sudo systemctl enable postgresql
undefined
sudo systemctl start postgresql sudo systemctl enable postgresql
undefined

Connection Basics

连接基础

bash
undefined
bash
undefined

Connect to default database

Connect to default database

psql -U postgres
psql -U postgres

Connect to specific database

Connect to specific database

psql -U postgres -d mydb -h localhost -p 5432
psql -U postgres -d mydb -h localhost -p 5432

List databases

List databases

\l
\l

List tables in current database

List tables in current database

\dt
\dt

Get table info

Get table info

\d table_name
\d table_name

Quit psql

Quit psql

\q
undefined
\q
undefined

User & Role Management

用户与角色管理

sql
-- Create a new role
CREATE ROLE developer WITH LOGIN PASSWORD 'secure_password';

-- Create superuser role
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin_password';

-- Grant privileges on database
GRANT CONNECT ON DATABASE mydb TO developer;

-- Grant privileges on schema
GRANT USAGE ON SCHEMA public TO developer;

-- Grant privileges on tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developer;

-- Grant privileges on specific table
GRANT SELECT ON employees TO developer;

-- Make role a database owner
ALTER DATABASE mydb OWNER TO developer;

-- Revoke privileges
REVOKE INSERT, UPDATE ON employees FROM developer;

-- Drop role
DROP ROLE developer;
sql
-- Create a new role
CREATE ROLE developer WITH LOGIN PASSWORD 'secure_password';

-- Create superuser role
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin_password';

-- Grant privileges on database
GRANT CONNECT ON DATABASE mydb TO developer;

-- Grant privileges on schema
GRANT USAGE ON SCHEMA public TO developer;

-- Grant privileges on tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developer;

-- Grant privileges on specific table
GRANT SELECT ON employees TO developer;

-- Make role a database owner
ALTER DATABASE mydb OWNER TO developer;

-- Revoke privileges
REVOKE INSERT, UPDATE ON employees FROM developer;

-- Drop role
DROP ROLE developer;

Configuration & Tuning

配置与调优

bash
undefined
bash
undefined

PostgreSQL configuration file

PostgreSQL configuration file

sudo nano /etc/postgresql/15/main/postgresql.conf
sudo nano /etc/postgresql/15/main/postgresql.conf

Key configuration parameters:

Key configuration parameters:

Memory

Memory

shared_buffers = 256MB # 25% of RAM for dedicated server effective_cache_size = 1GB # 50-75% of RAM work_mem = 64MB # RAM per operation
shared_buffers = 256MB # 25% of RAM for dedicated server effective_cache_size = 1GB # 50-75% of RAM work_mem = 64MB # RAM per operation

Connections

Connections

max_connections = 200 superuser_reserved_connections = 3
max_connections = 200 superuser_reserved_connections = 3

Write-Ahead Log

Write-Ahead Log

wal_level = replica max_wal_senders = 3 wal_keep_segments = 64
wal_level = replica max_wal_senders = 3 wal_keep_segments = 64

Query planning

Query planning

random_page_cost = 1.1 # For SSD log_min_duration_statement = 1000 # Log slow queries
undefined
random_page_cost = 1.1 # For SSD log_min_duration_statement = 1000 # Log slow queries
undefined

Backup & Recovery

备份与恢复

bash
undefined
bash
undefined

Full database backup (text format)

Full database backup (text format)

pg_dump -U postgres -d mydb -f mydb_backup.sql
pg_dump -U postgres -d mydb -f mydb_backup.sql

Binary backup (faster, compressed)

Binary backup (faster, compressed)

pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

Backup specific table

Backup specific table

pg_dump -U postgres -d mydb -t employees -f employees_backup.sql
pg_dump -U postgres -d mydb -t employees -f employees_backup.sql

Backup all databases

Backup all databases

pg_dumpall -U postgres -f all_databases.sql
pg_dumpall -U postgres -f all_databases.sql

Restore from backup

Restore from backup

psql -U postgres -d mydb -f mydb_backup.sql
psql -U postgres -d mydb -f mydb_backup.sql

Restore from binary dump

Restore from binary dump

pg_restore -U postgres -d mydb mydb_backup.dump
undefined
pg_restore -U postgres -d mydb mydb_backup.dump
undefined

Maintenance Operations

维护操作

sql
-- VACUUM (reclaim space)
VACUUM;                    -- Full vacuum

-- VACUUM ANALYZE (reclaim space & update stats)
VACUUM ANALYZE;

-- ANALYZE (update table statistics)
ANALYZE;

-- Check database integrity
REINDEX DATABASE mydb;

-- Show database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database;

-- Show table sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname != 'pg_catalog'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
sql
-- VACUUM (reclaim space)
VACUUM;                    -- Full vacuum

-- VACUUM ANALYZE (reclaim space & update stats)
VACUUM ANALYZE;

-- ANALYZE (update table statistics)
ANALYZE;

-- Check database integrity
REINDEX DATABASE mydb;

-- Show database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database;

-- Show table sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname != 'pg_catalog'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Monitoring

监控

sql
-- Active connections
SELECT * FROM pg_stat_activity WHERE state != 'idle';

-- Database statistics
SELECT * FROM pg_stat_database WHERE datname = 'mydb';

-- Table statistics
SELECT * FROM pg_stat_user_tables;

-- Index statistics
SELECT * FROM pg_stat_user_indexes;

-- Cache hit ratio
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
sql
-- Active connections
SELECT * FROM pg_stat_activity WHERE state != 'idle';

-- Database statistics
SELECT * FROM pg_stat_database WHERE datname = 'mydb';

-- Table statistics
SELECT * FROM pg_stat_user_tables;

-- Index statistics
SELECT * FROM pg_stat_user_indexes;

-- Cache hit ratio
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

Performance Tuning

性能调优

sql
-- Check slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Find missing indexes
SELECT * FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 1000;

-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary > 50000;
sql
-- Check slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Find missing indexes
SELECT * FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND n_live_tup > 1000;

-- Analyze query plan
EXPLAIN ANALYZE
SELECT * FROM employees WHERE salary > 50000;

Replication Setup

复制配置

bash
undefined
bash
undefined

On primary server - enable replication in postgresql.conf

On primary server - enable replication in postgresql.conf

wal_level = replica max_wal_senders = 3 wal_keep_segments = 64
wal_level = replica max_wal_senders = 3 wal_keep_segments = 64

Create replication user

Create replication user

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'rep_password';
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'rep_password';

On standby - base backup from primary

On standby - base backup from primary

pg_basebackup -h primary_host -D /var/lib/postgresql/15/main -U replicator -v -P -W
pg_basebackup -h primary_host -D /var/lib/postgresql/15/main -U replicator -v -P -W

Create recovery.conf on standby

Create recovery.conf on standby

standby_mode = 'on' primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'
undefined
standby_mode = 'on' primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'
undefined

High Availability with pgBouncer

基于pgBouncer的高可用

bash
undefined
bash
undefined

Install pgBouncer

Install pgBouncer

sudo apt-get install pgbouncer
sudo apt-get install pgbouncer

Configuration - /etc/pgbouncer/pgbouncer.ini

Configuration - /etc/pgbouncer/pgbouncer.ini

[databases] mydb = host=primary_host port=5432 dbname=mydb
[pgbouncer] listen_port = 6432 max_client_conn = 1000 default_pool_size = 25 reserve_pool_size = 5
undefined
[databases] mydb = host=primary_host port=5432 dbname=mydb
[pgbouncer] listen_port = 6432 max_client_conn = 1000 default_pool_size = 25 reserve_pool_size = 5
undefined

Next Steps

后续步骤

Learn advanced security features including row-level security and SSL/TLS configuration in the 
postgresql-security
skill.
学习高级安全功能，包括行级安全和SSL/TLS配置，请查看
postgresql-security
技能。