目录

一、安装

二、验证安装

三、数据备份

四、重要说明

---

需求：docker安装postgresql14,并且挂载数据卷。

一、安装

# 创建必要的目录
sudo mkdir -p /docker/postgresql/14/data
sudo mkdir -p /docker/postgresql/14/conf
sudo mkdir -p /docker/postgresql/14/init-scripts

# 设置目录权限
sudo chown -R 1000:1000 /docker/postgresql/14/
sudo chmod -R 755 /docker/postgresql/14/

# 创建自定义配置文件
sudo tee /docker/postgresql/14/conf/postgresql.conf > /dev/null << EOF
# PostgreSQL Configuration File

# Connection Settings
listen_addresses = '*'
port = 5432
max_connections = 100

# Memory Settings
shared_buffers = 128MB
work_mem = 4MB
maintenance_work_mem = 64MB

# WAL Settings
wal_level = replica
synchronous_commit = on

# Performance Settings
random_page_cost = 1.1
effective_cache_size = 1GB

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000

# Locale and Formatting
datestyle = 'iso, mdy'
timezone = 'UTC'
client_encoding = utf8

# Additional Settings
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
EOF

# 创建客户端认证配置
sudo tee /docker/postgresql/14/conf/pg_hba.conf > /dev/null << EOF
# PostgreSQL Client Authentication Configuration File

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

# Custom rules for docker network
host    all             all             172.0.0.0/8             md5
host    all             all             192.168.0.0/16          md5
EOF

# 运行 PostgreSQL 14 容器
docker run -d \
  --name postgresql-14 \
  --restart unless-stopped \
  -p 5432:5432 \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=Admin123456 \
  -e POSTGRES_DB=myapp \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -e TZ=Asia/Shanghai \
  -v /docker/postgresql/14/data:/var/lib/postgresql/data \
  -v /docker/postgresql/14/conf/postgresql.conf:/etc/postgresql/postgresql.conf \
  -v /docker/postgresql/14/conf/pg_hba.conf:/etc/postgresql/pg_hba.conf \
  -v /docker/postgresql/14/init-scripts:/docker-entrypoint-initdb.d \
  postgres:14 \
  -c 'config_file=/etc/postgresql/postgresql.conf' \
  -c 'hba_file=/etc/postgresql/pg_hba.conf'

二、验证安装

# 检查容器状态
docker ps | grep postgresql

# 查看日志
docker logs postgresql-14

# 连接测试
docker exec -it postgresql-14 psql -U admin -d myapp

# 在容器内执行 SQL
docker exec -it postgresql-14 psql -U admin -d myapp -c "SELECT version();"

三、数据备份

# 备份数据库
docker exec postgresql-14 pg_dump -U admin myapp > /docker/backup/myapp_$(date +%Y%m%d).sql

# 备份整个数据目录
sudo tar -czf /docker/backup/postgresql_data_$(date +%Y%m%d).tar.gz -C /docker/postgresql/14/data .

四、重要说明

1. 数据持久化：所有数据都保存在主机目录中，容器重启不会丢失数据

2. 配置文件：自定义配置覆盖了默认配置，便于管理

3. 权限控制：通过 pg_hba.conf 管理客户端连接权限

4. 初始化：init-scripts 目录中的 SQL 文件会在首次启动时自动执行

5. 时区设置：根据需求调整 TZ 环境变量