Back to Details

postgresql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

PostgreSQL

PostgreSQL

Administer, optimize, and secure PostgreSQL databases in development and production environments.
在开发和生产环境中管理、优化和保护PostgreSQL数据库。

When to Use

使用场景

  • You need a reliable, ACID-compliant relational database.
  • Your application requires advanced features such as JSONB, full-text search, or CTEs.
  • You are setting up streaming replication or point-in-time recovery.
  • You need to tune an existing PostgreSQL deployment for better throughput.
  • 你需要一个可靠的、符合ACID标准的关系型数据库。
  • 你的应用需要JSONB、全文搜索或CTEs等高级功能。
  • 你正在设置流式复制或时间点恢复。
  • 你需要调优现有的PostgreSQL部署以提升吞吐量。

Prerequisites

前提条件

  • Linux server (Debian/Ubuntu or RHEL-based) or Docker.
  • Root or sudo access for package installation.
  • Familiarity with SQL fundamentals.
  • Linux服务器（Debian/Ubuntu或基于RHEL的系统）或Docker。
  • 拥有安装软件包的Root或sudo权限。
  • 熟悉SQL基础知识。

Installation and Setup

安装与配置

bash
undefined
bash
undefined

Debian / Ubuntu

Debian / Ubuntu

sudo apt update sudo apt install -y postgresql postgresql-contrib
sudo apt update sudo apt install -y postgresql postgresql-contrib

RHEL / Amazon Linux

RHEL / Amazon Linux

sudo dnf install -y postgresql15-server postgresql15-contrib sudo postgresql-setup --initdb sudo systemctl enable --now postgresql
sudo dnf install -y postgresql15-server postgresql15-contrib sudo postgresql-setup --initdb sudo systemctl enable --now postgresql

Verify

Verify

psql --version sudo systemctl status postgresql
undefined
psql --version sudo systemctl status postgresql
undefined

Initial User and Database Setup

初始用户与数据库配置

bash
undefined
bash
undefined

Switch to the postgres system user

Switch to the postgres system user

sudo -u postgres psql

```sql
-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;
sudo -u postgres psql

```sql
-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

psql Commands Reference

psql命令参考

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY
\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

Configuration Tuning

配置调优

Edit 
/etc/postgresql/15/main/postgresql.conf
(path varies by OS and version).
ini
undefined
编辑 
/etc/postgresql/15/main/postgresql.conf
（路径因操作系统和版本而异）。
ini
undefined

Connection settings

Connection settings

listen_addresses = '*' max_connections = 200
listen_addresses = '*' max_connections = 200

Memory — adjust to ~25% of total RAM for shared_buffers

Memory — adjust to ~25% of total RAM for shared_buffers

shared_buffers = 4GB effective_cache_size = 12GB work_mem = 16MB maintenance_work_mem = 512MB
shared_buffers = 4GB effective_cache_size = 12GB work_mem = 16MB maintenance_work_mem = 512MB

WAL / write performance

WAL / write performance

wal_buffers = 64MB checkpoint_completion_target = 0.9 min_wal_size = 1GB max_wal_size = 4GB
wal_buffers = 64MB checkpoint_completion_target = 0.9 min_wal_size = 1GB max_wal_size = 4GB

Planner

Planner

random_page_cost = 1.1 # lower for SSD effective_io_concurrency = 200 # for SSD
random_page_cost = 1.1 # lower for SSD effective_io_concurrency = 200 # for SSD

Logging

Logging

log_min_duration_statement = 250 # log queries slower than 250 ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on

```bash
log_min_duration_statement = 250 # log queries slower than 250 ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on

```bash

Reload configuration without restart

Reload configuration without restart

sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

Some settings (shared_buffers, max_connections) require a full restart

Some settings (shared_buffers, max_connections) require a full restart

sudo systemctl restart postgresql
undefined
sudo systemctl restart postgresql
undefined

pg_hba.conf — Client Authentication

pg_hba.conf — 客户端认证

undefined
undefined

/etc/postgresql/15/main/pg_hba.conf

/etc/postgresql/15/main/pg_hba.conf

TYPE DATABASE USER ADDRESS METHOD

TYPE DATABASE USER ADDRESS METHOD

local all postgres peer host mydb myapp 10.0.0.0/8 scram-sha-256 host all all 0.0.0.0/0 reject

```bash
sudo systemctl reload postgresql
local all postgres peer host mydb myapp 10.0.0.0/8 scram-sha-256 host all all 0.0.0.0/0 reject

```bash
sudo systemctl reload postgresql

Backup and Restore

备份与恢复

Logical Backups with pg_dump

使用pg_dump进行逻辑备份

bash
undefined
bash
undefined

Plain SQL backup

Plain SQL backup

pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql
pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql

Custom compressed format (recommended)

Custom compressed format (recommended)

pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

Backup a single table

Backup a single table

pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump
pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump

Restore from custom format

Restore from custom format

pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

Restore plain SQL

Restore plain SQL

psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql
undefined
psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql
undefined

Physical Backups with pg_basebackup

使用pg_basebackup进行物理备份

bash
undefined
bash
undefined

Full base backup (used for PITR and replica seeding)

Full base backup (used for PITR and replica seeding)

pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F)
--wal-method=stream --checkpoint=fast --progress --verbose
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F)
--wal-method=stream --checkpoint=fast --progress --verbose

Verify the backup

Verify the backup

pg_verifybackup /backups/base_2025-01-15
undefined
pg_verifybackup /backups/base_2025-01-15
undefined

Streaming Replication

流式复制

Primary Server

主服务器

sql
-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';
ini
undefined
sql
-- Create replication user
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'repl_secret';
ini
undefined

postgresql.conf on primary

postgresql.conf on primary

wal_level = replica max_wal_senders = 5 wal_keep_size = 1GB
undefined
wal_level = replica max_wal_senders = 5 wal_keep_size = 1GB
undefined

pg_hba.conf on primary

pg_hba.conf on primary

host replication replicator 10.0.0.0/8 scram-sha-256
undefined
host replication replicator 10.0.0.0/8 scram-sha-256
undefined

Replica Server

副本服务器

bash
undefined
bash
undefined

Stop PostgreSQL on the replica

Stop PostgreSQL on the replica

sudo systemctl stop postgresql
sudo systemctl stop postgresql

Remove existing data directory

Remove existing data directory

sudo rm -rf /var/lib/postgresql/15/main/*
sudo rm -rf /var/lib/postgresql/15/main/*

Base backup from primary

Base backup from primary

sudo -u postgres pg_basebackup
-h 10.0.0.1 -U replicator
-D /var/lib/postgresql/15/main
--wal-method=stream --checkpoint=fast --progress
sudo -u postgres pg_basebackup
-h 10.0.0.1 -U replicator
-D /var/lib/postgresql/15/main
--wal-method=stream --checkpoint=fast --progress

Create standby signal file

Create standby signal file

sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

```ini
sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

```ini

postgresql.conf on replica

postgresql.conf on replica

primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=repl_secret' hot_standby = on

```bash
sudo systemctl start postgresql
primary_conninfo = 'host=10.0.0.1 port=5432 user=replicator password=repl_secret' hot_standby = on

```bash
sudo systemctl start postgresql

Verify Replication

验证复制状态

sql
-- On primary
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

-- On replica
SELECT pg_is_in_recovery();           -- should return true
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();
sql
-- On primary
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

-- On replica
SELECT pg_is_in_recovery();           -- should return true
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();

Monitoring Queries

监控查询

sql
-- Active connections by state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

-- Long-running queries (> 30 seconds)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

-- Table bloat and dead tuples
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Index usage statistics
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT ROUND(
  100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2
) AS cache_hit_pct
FROM pg_stat_database;

-- Database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;
sql
-- Active connections by state
SELECT state, COUNT(*)
FROM pg_stat_activity
GROUP BY state;

-- Long-running queries (> 30 seconds)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

-- Table bloat and dead tuples
SELECT relname,
       n_live_tup,
       n_dead_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Index usage statistics
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

-- Cache hit ratio (should be > 99%)
SELECT ROUND(
  100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2
) AS cache_hit_pct
FROM pg_stat_database;

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

Docker Compose Setup

Docker Compose配置

yaml
undefined
yaml
undefined

docker-compose.yml

docker-compose.yml

version: "3.9"
services: postgres: image: postgres:16-alpine restart: unless-stopped ports: - "5432:5432" environment: POSTGRES_USER: myapp POSTGRES_PASSWORD: secret POSTGRES_DB: mydb volumes: - pg_data:/var/lib/postgresql/data - ./init.sql:/docker-entrypoint-initdb.d/init.sql command: > postgres -c shared_buffers=256MB -c work_mem=8MB -c maintenance_work_mem=128MB -c effective_cache_size=768MB -c log_min_duration_statement=250 healthcheck: test: ["CMD-SHELL", "pg_isready -U myapp -d mydb"] interval: 10s timeout: 5s retries: 5
pgbouncer: image: edoburu/pgbouncer:latest restart: unless-stopped ports: - "6432:6432" environment: DATABASE_URL: postgres://myapp:secret@postgres:5432/mydb POOL_MODE: transaction MAX_CLIENT_CONN: 500 DEFAULT_POOL_SIZE: 40 depends_on: postgres: condition: service_healthy
volumes: pg_data:

```bash
docker compose up -d
psql -h 127.0.0.1 -p 6432 -U myapp mydb
version: "3.9"
services: postgres: image: postgres:16-alpine restart: unless-stopped ports: - "5432:5432" environment: POSTGRES_USER: myapp POSTGRES_PASSWORD: secret POSTGRES_DB: mydb volumes: - pg_data:/var/lib/postgresql/data - ./init.sql:/docker-entrypoint-initdb.d/init.sql command: > postgres -c shared_buffers=256MB -c work_mem=8MB -c maintenance_work_mem=128MB -c effective_cache_size=768MB -c log_min_duration_statement=250 healthcheck: test: ["CMD-SHELL", "pg_isready -U myapp -d mydb"] interval: 10s timeout: 5s retries: 5
pgbouncer: image: edoburu/pgbouncer:latest restart: unless-stopped ports: - "6432:6432" environment: DATABASE_URL: postgres://myapp:secret@postgres:5432/mydb POOL_MODE: transaction MAX_CLIENT_CONN: 500 DEFAULT_POOL_SIZE: 40 depends_on: postgres: condition: service_healthy
volumes: pg_data:

```bash
docker compose up -d
psql -h 127.0.0.1 -p 6432 -U myapp mydb

Maintenance Tasks

维护任务

bash
undefined
bash
undefined

Manual VACUUM and ANALYZE

Manual VACUUM and ANALYZE

sudo -u postgres psql -d mydb -c "VACUUM ANALYZE;"
sudo -u postgres psql -d mydb -c "VACUUM ANALYZE;"

Reindex a bloated index

Reindex a bloated index

sudo -u postgres psql -d mydb -c "REINDEX INDEX CONCURRENTLY idx_orders_user_id;"
sudo -u postgres psql -d mydb -c "REINDEX INDEX CONCURRENTLY idx_orders_user_id;"

Check for unused indexes

Check for unused indexes

sudo -u postgres psql -d mydb -c " SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;"
undefined
sudo -u postgres psql -d mydb -c " SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;"
undefined

Troubleshooting

故障排查

SymptomLikely CauseFix
FATAL: too many connections
Connection limit reachedIncrease 
max_connections
or add PgBouncer
Slow SELECT on large tableMissing index or stale statisticsRun 
EXPLAIN ANALYZE
; add index; run 
ANALYZE
High CPU from autovacuumLarge number of dead tuplesTune 
autovacuum_vacuum_cost_delay
; run manual 
VACUUM
Replication lag increasingReplica under-provisioned or network bottleneckCheck 
pg_stat_replication
; increase 
wal_keep_size
could not access file "base/..."
Disk full or corrupt data directoryFree disk space; restore from 
pg_basebackup
FATAL: password authentication failed
Wrong credentials or pg_hba.conf mismatchVerify pg_hba.conf entries and reload
症状可能原因解决方法
FATAL: too many connections
连接数达到上限增加
max_connections
或添加PgBouncer
大表上的SELECT查询缓慢缺少索引或统计信息过时执行
EXPLAIN ANALYZE
；添加索引；执行
ANALYZE
自动清理（autovacuum）导致CPU占用过高大量死元组调优
autovacuum_vacuum_cost_delay
；手动执行
VACUUM
复制延迟增加副本资源不足或网络瓶颈查看
pg_stat_replication
；增加
wal_keep_size
could not access file "base/..."
磁盘已满或数据目录损坏释放磁盘空间；从
pg_basebackup
恢复
FATAL: password authentication failed
凭证错误或pg_hba.conf配置不匹配验证pg_hba.conf条目并重新加载配置

Related Skills

相关技能

  • mysql - Alternative relational database
  • database-backups - Automated backup strategies
  • redis - Caching layer to reduce database load
  • planetscale - Managed MySQL-compatible alternative
  • mysql - 可选的关系型数据库
  • database-backups - 自动化备份策略
  • redis - 缓存层，用于降低数据库负载
  • planetscale - 托管式MySQL兼容替代方案