PostgreSQL实例搭建全攻略 从安装到配置一步到位的实用指南

威震华夏关云长

引言

PostgreSQL是一款功能强大的开源对象关系数据库系统，它以其稳定性、丰富的功能集和对标准的遵从性而闻名。作为世界上最先进的开源数据库系统之一，PostgreSQL提供了完整的关系数据库功能，并且支持SQL标准以及许多扩展。本指南将带领您从零开始，一步步完成PostgreSQL的安装与配置，帮助您快速搭建一个稳定、高效的数据库环境。

系统要求

在开始安装PostgreSQL之前，确保您的系统满足以下基本要求：

硬件要求

• CPU: 至少1 GHz的处理器，推荐2 GHz或更高
• 内存: 最小512MB RAM，推荐2GB或更多（取决于数据库大小和并发用户数）
• 硬盘空间: 至少1GB可用空间用于安装，实际数据存储空间需求取决于您的数据库大小
• 磁盘类型: SSD硬盘可显著提高I/O性能，特别是对于高负载的数据库

软件要求

• 操作系统:Windows: Windows 7及更高版本（包括Windows Server版本）Linux: 大多数现代Linux发行版，如Ubuntu 16.04+, CentOS 7+, Debian 8+macOS: macOS 10.12及更高版本
• Windows: Windows 7及更高版本（包括Windows Server版本）
• Linux: 大多数现代Linux发行版，如Ubuntu 16.04+, CentOS 7+, Debian 8+
• macOS: macOS 10.12及更高版本
• 其他软件:对于Linux系统，可能需要安装一些开发工具和库对于从源代码编译安装，需要C编译器（如GCC）和开发库
• 对于Linux系统，可能需要安装一些开发工具和库
• 对于从源代码编译安装，需要C编译器（如GCC）和开发库

• Windows: Windows 7及更高版本（包括Windows Server版本）
• Linux: 大多数现代Linux发行版，如Ubuntu 16.04+, CentOS 7+, Debian 8+
• macOS: macOS 10.12及更高版本

• 对于Linux系统，可能需要安装一些开发工具和库
• 对于从源代码编译安装，需要C编译器（如GCC）和开发库

安装前的准备工作

在正式安装PostgreSQL之前，需要进行一些准备工作，以确保安装过程顺利进行。

创建专用用户（Linux/Unix系统）

在Linux或Unix系统上，建议创建一个专用的系统用户来运行PostgreSQL服务：

2. # 创建postgres用户
3. sudo useradd -r postgres
5. # 设置密码（可选）
6. sudo passwd postgres

复制代码

更新系统包

确保您的系统包是最新的：

Ubuntu/Debian系统:

2. sudo apt update
3. sudo apt upgrade -y

复制代码

CentOS/RHEL系统:

2. sudo yum update -y

复制代码

检查磁盘空间

确保有足够的磁盘空间用于安装和数据存储：

2. # 检查磁盘空间
3. df -h

复制代码

检查端口占用

PostgreSQL默认使用5432端口，确保该端口未被其他服务占用：

2. # 检查端口占用
3. sudo netstat -tulnp | grep 5432

复制代码

在不同操作系统上的安装步骤

Windows系统安装

1. 下载安装包访问PostgreSQL官方网站（https://www.postgresql.org/download/windows/）下载最新的Windows安装程序。
2. 运行安装程序双击下载的安装包，启动安装向导。
3. 安装步骤选择安装目录（默认为C:\Program Files\PostgreSQL\<version>）选择要安装的组件（通常保持默认选择即可）设置数据目录（用于存储数据库数据）设置postgres超级用户的密码选择端口号（默认为5432）选择区域设置（默认为Default locale）
4. 选择安装目录（默认为C:\Program Files\PostgreSQL\<version>）
5. 选择要安装的组件（通常保持默认选择即可）
6. 设置数据目录（用于存储数据库数据）
7. 设置postgres超级用户的密码
8. 选择端口号（默认为5432）
9. 选择区域设置（默认为Default locale）
10. 配置环境变量安装程序通常会自动配置环境变量，但如果没有，您可以手动添加：将PostgreSQL的bin目录（如C:\Program Files\PostgreSQL\<version>\bin）添加到系统的PATH环境变量中。
11. 将PostgreSQL的bin目录（如C:\Program Files\PostgreSQL\<version>\bin）添加到系统的PATH环境变量中。
12. 验证安装打开命令提示符，运行以下命令验证安装：

下载安装包访问PostgreSQL官方网站（https://www.postgresql.org/download/windows/）下载最新的Windows安装程序。

运行安装程序双击下载的安装包，启动安装向导。

安装步骤

• 选择安装目录（默认为C:\Program Files\PostgreSQL\<version>）
• 选择要安装的组件（通常保持默认选择即可）
• 设置数据目录（用于存储数据库数据）
• 设置postgres超级用户的密码
• 选择端口号（默认为5432）
• 选择区域设置（默认为Default locale）

配置环境变量安装程序通常会自动配置环境变量，但如果没有，您可以手动添加：

• 将PostgreSQL的bin目录（如C:\Program Files\PostgreSQL\<version>\bin）添加到系统的PATH环境变量中。

验证安装打开命令提示符，运行以下命令验证安装：

2. psql --version

复制代码

如果显示PostgreSQL的版本信息，则表示安装成功。

Linux系统安装

1. 添加PostgreSQL仓库“`bash创建仓库配置文件sudo sh -c ‘echo “debhttp://apt.postgresql.org/pub/repos/apt$(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’

添加PostgreSQL仓库“`bash

sudo sh -c ‘echo “debhttp://apt.postgresql.org/pub/repos/apt$(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’

# 导入仓库签名密钥
   wget –quiet -O -https://www.postgresql.org/media/keys/ACCC4CF8.asc| sudo apt-key add -

# 更新包列表
   sudo apt update

2. 2. **安装PostgreSQL**
3.    ```bash
4.    # 安装PostgreSQL和贡献包
5.    sudo apt install postgresql postgresql-contrib -y

复制代码

1. 启动并启用服务“`bash启动PostgreSQL服务sudo systemctl start postgresql

启动并启用服务“`bash

sudo systemctl start postgresql

# 设置开机自启
   sudo systemctl enable postgresql

2. 4. **验证安装**
3.    ```bash
4.    # 检查服务状态
5.    sudo systemctl status postgresql
6.    
7.    # 切换到postgres用户并连接数据库
8.    sudo -u postgres psql

复制代码

1.

2. 安装PostgreSQL仓库# 安装PostgreSQL仓库RPM包
3. sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

复制代码

2. 安装PostgreSQL# 安装PostgreSQL服务器和贡献包
sudo yum install -y postgresql13-server postgresql13-contrib
3. 初始化数据库# 初始化数据库
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
4. 启动并启用服务“`bash启动PostgreSQL服务sudo systemctl start postgresql-13

安装PostgreSQL仓库

2. # 安装PostgreSQL仓库RPM包
3. sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

复制代码

安装PostgreSQL

2. # 安装PostgreSQL服务器和贡献包
3. sudo yum install -y postgresql13-server postgresql13-contrib

复制代码

初始化数据库

2. # 初始化数据库
3. sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

复制代码

启动并启用服务“`bash

sudo systemctl start postgresql-13

# 设置开机自启
   sudo systemctl enable postgresql-13

2. 5. **验证安装**
3.    ```bash
4.    # 检查服务状态
5.    sudo systemctl status postgresql-13
6.    
7.    # 切换到postgres用户并连接数据库
8.    sudo -u postgres psql

复制代码

macOS系统安装

1. 安装Homebrew如果您尚未安装Homebrew，可以通过以下命令安装：/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
2. 安装PostgreSQL# 安装PostgreSQL
brew install postgresql
3. 启动PostgreSQL服务# 启动PostgreSQL服务
brew services start postgresql
4. 验证安装“`bash检查服务状态brew services list

安装Homebrew如果您尚未安装Homebrew，可以通过以下命令安装：

2. /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

复制代码

安装PostgreSQL

2. # 安装PostgreSQL
3. brew install postgresql

复制代码

启动PostgreSQL服务

2. # 启动PostgreSQL服务
3. brew services start postgresql

复制代码

验证安装“`bash

brew services list

# 连接数据库
   psql postgres

2. #### 使用Postgres.app安装
4. 1. **下载Postgres.app**
5.    访问https://postgresapp.com/下载最新版本的Postgres.app。
7. 2. **安装Postgres.app**
8.    将下载的文件拖动到Applications文件夹。
10. 3. **启动Postgres.app**
11.    双击Applications文件夹中的Postgres.app图标启动应用程序。
13. 4. **验证安装**
14.    打开终端，运行以下命令：
15.    ```bash
16.    # 添加Postgres.app的bin目录到PATH
17.    export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin
18.    
19.    # 连接数据库
20.    psql -h localhost

复制代码

基本配置

初始化数据库集群

在Linux系统上，如果使用包管理器安装，数据库集群通常会自动初始化。但如果您需要手动初始化，可以执行以下命令：

2. # 切换到postgres用户
3. sudo -u postgres initdb -D /var/lib/pgsql/data

复制代码

配置文件详解

PostgreSQL的主要配置文件位于数据目录中，最重要的两个文件是postgresql.conf和pg_hba.conf。

这个文件包含PostgreSQL服务器的基本配置参数。以下是一些常用参数的说明：

2. # 连接设置
3. listen_addresses = 'localhost'  # 监听的IP地址，'*'表示所有IP
4. port = 5432                    # 监听的端口
5. max_connections = 100           # 最大连接数
7. # 内存设置
8. shared_buffers = 128MB         # 共享内存缓冲区大小
9. effective_cache_size = 4GB     # 系统可用于磁盘缓存的内存估计量
10. work_mem = 4MB                 # 排序操作使用的内存
11. maintenance_work_mem = 64MB    # 维护操作使用的内存
13. # 日志设置
14. logging_collector = on         # 启用日志收集器
15. log_directory = 'log'          # 日志目录
16. log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # 日志文件名格式
17. log_statement = 'all'          # 记录所有SQL语句
18. log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  # 日志行前缀
20. # 查询性能
21. shared_preload_libraries = 'pg_stat_statements'  # 预加载库
22. pg_stat_statements.max = 10000    # 跟踪的最大语句数
23. pg_stat_statements.track = all   # 跟踪所有语句

复制代码

这个文件控制客户端认证，即哪些用户可以从哪些主机连接到哪些数据库。以下是一个示例配置：

2. # TYPE  DATABASE        USER            ADDRESS                 METHOD
4. # 允许本地用户使用Unix域套接字连接
5. local   all             all                                     trust
7. # 允许本地用户通过TCP/IP连接
8. host    all             all             127.0.0.1/32            trust
9. host    all             all             ::1/128                 trust
11. # 允许特定IP段的用户使用密码连接
12. host    all             all             192.168.1.0/24         md5
14. # 允许所有用户使用SSL加密连接
15. hostssl all             all             0.0.0.0/0               md5

复制代码

连接与认证配置

1. 修改配置文件后重载配置修改配置文件后，需要重载配置使更改生效：

2. # Linux系统
3.    sudo systemctl reload postgresql
4.    
5.    # 或者使用pg_ctl
6.    sudo -u postgres pg_ctl reload -D /var/lib/pgsql/data

复制代码

1. 创建用户和数据库“`sql
– 连接到PostgreSQL
sudo -u postgres psql

– 创建新用户
   CREATE USER myuser WITH PASSWORD ‘mypassword’;

– 创建新数据库并指定所有者
   CREATE DATABASE mydb WITH OWNER myuser;

– 授予用户数据库权限
   GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

2. 3. **测试连接**
3.    ```bash
4.    # 使用新创建的用户连接数据库
5.    psql -h localhost -U myuser -d mydb

复制代码

安全设置

用户与权限管理

1.

2. 角色和权限管理“`sql
3. – 创建角色
4. CREATE ROLE readaccess;

复制代码

– 授予连接权限
   GRANT CONNECT ON DATABASE mydb TO readaccess;

– 授予使用模式权限
   GRANT USAGE ON SCHEMA public TO readaccess;

– 授予表读取权限
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

– 创建用户并分配角色
   CREATE USER readonly WITH PASSWORD ‘readonlypass’;
   GRANT readaccess TO readonly;

2. 2. **限制超级用户访问**
3.    ```sql
4.    -- 创建一个具有有限权限的管理员用户
5.    CREATE USER admin WITH PASSWORD 'adminpass' CREATEDB CREATEROLE;
6.    
7.    -- 授予对特定数据库的完全访问权限
8.    GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;

复制代码

1.

2. 定期审查权限“`sql
3. – 查看用户角色
4. SELECT usename AS role_name,
5.       CASE
6.          WHEN usesuper AND usecreatedb THEN
7.             CAST(‘superuser, create database’ AS pg_catalog.text)
8.          WHEN usesuper THEN
9.             CAST(‘superuser’ AS pg_catalog.text)
10.          WHEN usecreatedb THEN
11.             CAST(‘create database’ AS pg_catalog.text)
12.          ELSE
13.             CAST(” AS pg_catalog.text)
14.       END role_attributes
15. FROM pg_catalog.pg_user
16. ORDER BY role_name;

复制代码

– 查看数据库权限
   SELECT datname as database,

2. usename as user,
3.       privilege_type as privilege

复制代码

FROM pg_database
   JOIN pg_user ON pg_user.usesysid = pg_database.datdba
   JOIN (

2. SELECT d.datname as database,
3.           u.usename as user,
4.           a.datacl as access
5.    FROM pg_database d
6.    JOIN pg_user u ON u.usesysid = d.datdba
7.    WHERE d.datname not in ('template0', 'template1')

复制代码

) a ON a.database = d.datname AND a.user = u.usename
   CROSS JOIN unnest(string_to_array(replace(replace(a.access, ‘{’, “), ‘}’, “), ‘,’)) AS privilege_type;

2. ### 防火墙配置
4. 1. **Ubuntu/Debian系统（使用UFW）**
5.    ```bash
6.    # 允许PostgreSQL端口
7.    sudo ufw allow 5432/tcp
8.    
9.    # 或者限制特定IP访问
10.    sudo ufw allow from 192.168.1.0/24 to any port 5432

复制代码

1. CentOS/RHEL系统（使用Firewalld）“`bash启用防火墙sudo systemctl enable firewalld
sudo systemctl start firewalld

CentOS/RHEL系统（使用Firewalld）“`bash

sudo systemctl enable firewalld
sudo systemctl start firewalld

# 添加PostgreSQL服务
   sudo firewall-cmd –permanent –add-service=postgresql
   sudo firewall-cmd –reload

# 或者限制特定IP访问
   sudo firewall-cmd –permanent –add-rich-rule=‘rule family=“ipv4” source address=“192.168.1.0/24” service name=“postgresql” accept’
   sudo firewall-cmd –reload

2. 3. **Windows系统**
3.    - 打开"Windows Defender 防火墙"
4.    - 点击"高级设置"
5.    - 选择"入站规则"
6.    - 点击"新建规则"
7.    - 选择"端口"，点击"下一步"
8.    - 选择"TCP"和"特定本地端口"，输入"5432"，点击"下一步"
9.    - 选择"允许连接"，点击"下一步"
10.    - 选择适用的配置文件，点击"下一步"
11.    - 输入规则名称（如"PostgreSQL"），点击"完成"
13. ### SSL加密连接
15. 1. **生成SSL证书**
16.    ```bash
17.    # 切换到postgres用户
18.    sudo -u postgres -i
19.    
20.    # 创建证书目录
21.    mkdir ~/certs
22.    cd ~/certs
23.    
24.    # 生成私钥
25.    openssl genrsa -out server.key 2048
26.    
27.    # 生成证书签名请求
28.    openssl req -new -key server.key -out server.csr
29.    
30.    # 生成自签名证书
31.    openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
32.    
33.    # 设置适当的权限
34.    chmod 600 server.key

复制代码

1. 配置PostgreSQL使用SSL编辑postgresql.conf文件：ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
2. 强制SSL连接编辑pg_hba.conf文件，将host条目改为hostssl：# 强制特定用户使用SSL连接
hostssl all             all             0.0.0.0/0               md5
3. 重载配置sudo systemctl reload postgresql
4. 验证SSL连接“`bash使用SSL连接psql “sslmode=require host=localhost user=myuser dbname=mydb”

配置PostgreSQL使用SSL编辑postgresql.conf文件：

2. ssl = on
3. ssl_cert_file = 'server.crt'
4. ssl_key_file = 'server.key'

复制代码

强制SSL连接编辑pg_hba.conf文件，将host条目改为hostssl：

2. # 强制特定用户使用SSL连接
3. hostssl all             all             0.0.0.0/0               md5

复制代码

重载配置

2. sudo systemctl reload postgresql

复制代码

验证SSL连接“`bash

psql “sslmode=require host=localhost user=myuser dbname=mydb”

# 检查连接是否加密
   SELECT * FROM pg_stat_ssl;

2. ## 性能优化
4. ### 内存配置
6. 1. **共享缓冲区（shared_buffers）**
7.    这是PostgreSQL用于缓存数据的内存区域。通常设置为系统内存的25%左右。
9.    ```ini
10.    # 在postgresql.conf中设置
11.    shared_buffers = 4GB  # 对于16GB RAM的系统

复制代码

1. 工作内存（work_mem）用于排序、哈希连接等操作的内存。根据查询复杂度和并发连接数调整。

2. # 在postgresql.conf中设置
3.    work_mem = 16MB

复制代码

1. 维护工作内存（maintenance_work_mem）用于VACUUM、CREATE INDEX等维护操作的内存。

2. # 在postgresql.conf中设置
3.    maintenance_work_mem = 512MB

复制代码

1. 有效缓存大小（effective_cache_size）告诉PostgreSQL系统可用于磁盘缓存的内存量，帮助查询优化器做出更好的决策。

2. # 在postgresql.conf中设置
3.    effective_cache_size = 12GB  # 对于16GB RAM的系统

复制代码

磁盘I/O优化

1. 使用SSD存储将数据目录放在SSD上可以显著提高I/O性能。
2. 调整检查点参数# 在postgresql.conf中设置
checkpoint_segments = 32  # PostgreSQL 9.5及以下版本
max_wal_size = 4GB       # PostgreSQL 9.6及以上版本
min_wal_size = 1GB       # PostgreSQL 9.6及以上版本
checkpoint_completion_target = 0.9
3. 启用WAL归档# 在postgresql.conf中设置
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'
4. 表空间管理将频繁访问的表和索引放在单独的表空间中，可能位于更快的存储设备上。

使用SSD存储将数据目录放在SSD上可以显著提高I/O性能。

调整检查点参数

2. # 在postgresql.conf中设置
3. checkpoint_segments = 32  # PostgreSQL 9.5及以下版本
4. max_wal_size = 4GB       # PostgreSQL 9.6及以上版本
5. min_wal_size = 1GB       # PostgreSQL 9.6及以上版本
6. checkpoint_completion_target = 0.9

复制代码

启用WAL归档

2. # 在postgresql.conf中设置
3. wal_level = replica
4. archive_mode = on
5. archive_command = 'cp %p /path/to/archive/%f'

复制代码

表空间管理将频繁访问的表和索引放在单独的表空间中，可能位于更快的存储设备上。

2. -- 创建表空间
3.    CREATE TABLESPACE fastspace LOCATION '/path/to/fast/storage';
4.    
5.    -- 在新表空间中创建表
6.    CREATE TABLE mytable (
7.        id serial primary key,
8.        name varchar(100)
9.    ) TABLESPACE fastspace;

复制代码

查询优化

1.

2. 收集统计信息“`sql
3. – 更新表统计信息
4. ANALYZE mytable;

复制代码

– 更新所有表的统计信息
   ANALYZE;

2. 2. **使用EXPLAIN分析查询**
3.    ```sql
4.    -- 分析查询执行计划
5.    EXPLAIN ANALYZE SELECT * FROM mytable WHERE name = 'test';

复制代码

1.

2. 创建适当的索引“`sql
3. – 创建B-tree索引
4. CREATE INDEX idx_mytable_name ON mytable (name);

复制代码

– 创建复合索引
   CREATE INDEX idx_mytable_name_id ON mytable (name, id);

– 创建部分索引
   CREATE INDEX idx_mytable_active ON mytable (id) WHERE active = true;

– 创建哈希索引（适用于等值查询）
   CREATE INDEX idx_mytable_hash_name ON mytable USING hash (name);

2. 4. **配置查询计划**
3.    ```ini
4.    # 在postgresql.conf中设置
5.    seq_page_cost = 1.0        # 顺序扫描的成本因子
6.    random_page_cost = 1.1     # 随机扫描的成本因子（SSD可以设置为1.1）
7.    cpu_tuple_cost = 0.01      # 处理每行的CPU成本
8.    cpu_index_tuple_cost = 0.005  # 处理每个索引条目的CPU成本
9.    cpu_operator_cost = 0.0025   # 处理每个操作符的CPU成本

复制代码

1.

2. 使用pg_stat_statements监控查询性能“`sql
3. – 查看最耗时的查询
4. SELECT query, calls, total_time, mean_time, rows
5. FROM pg_stat_statements
6. ORDER BY total_time DESC
7. LIMIT 10;

复制代码

– 重置统计信息
   SELECT pg_stat_statements_reset();

2. ## 备份与恢复策略
4. ### 逻辑备份
6. 1. **使用pg_dump进行备份**
7.    ```bash
8.    # 备份单个数据库
9.    pg_dump -U myuser -h localhost -d mydb -f mydb_backup.sql
10.    
11.    # 备份所有数据库
12.    pg_dumpall -U postgres -h localhost -f all_databases_backup.sql
13.    
14.    # 创建压缩备份
15.    pg_dump -U myuser -h localhost -d mydb | gzip > mydb_backup.sql.gz
16.    
17.    # 创建自定义格式备份（允许选择性恢复）
18.    pg_dump -U myuser -h localhost -d mydb -Fc -f mydb_backup.dump

复制代码

1. 恢复逻辑备份“`bash恢复单个数据库psql -U myuser -h localhost -d mydb_new -f mydb_backup.sql

恢复逻辑备份“`bash

psql -U myuser -h localhost -d mydb_new -f mydb_backup.sql

# 恢复所有数据库
   psql -U postgres -h localhost -f all_databases_backup.sql

# 恢复压缩备份
   gunzip -c mydb_backup.sql.gz | psql -U myuser -h localhost -d mydb_new

# 恢复自定义格式备份
   pg_restore -U myuser -h localhost -d mydb_new mydb_backup.dump

2. ### 物理备份
4. 1. **使用pg_basebackup进行热备份**
5.    ```bash
6.    # 创建物理备份
7.    pg_basebackup -U repuser -h localhost -D /path/to/backup -Ft -z -P -Xs
8.    
9.    # 参数说明：
10.    # -U: 连接用户
11.    # -h: 主机地址
12.    # -D: 备份输出目录
13.    # -Ft: 输出格式为tar
14.    # -z: 压缩输出
15.    # -P: 显示进度
16.    # -Xs: 使用流式复制协议同步WAL

复制代码

1. 设置连续WAL归档# 在postgresql.conf中设置
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
2. 设置时间点恢复（PITR）“`bash1. 停止PostgreSQL服务sudo systemctl stop postgresql

设置连续WAL归档

2. # 在postgresql.conf中设置
3. wal_level = replica
4. archive_mode = on
5. archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

复制代码

设置时间点恢复（PITR）“`bash

sudo systemctl stop postgresql

# 2. 备份数据目录
   cp -r /var/lib/pgsql/data /var/lib/pgsql/data_backup

# 3. 恢复基础备份
   rm -rf /var/lib/pgsql/data/*
   tar -xzf /path/to/backup/base.tar.gz -C /var/lib/pgsql/data

# 4. 创建recovery.conf文件
   cat > /var/lib/pgsql/data/recovery.conf << EOF
   restore_command = ‘cp /mnt/server/archivedir/%f %p’
   recovery_target_time = ‘2023-05-15 14:30:00’
   EOF

# 5. 启动PostgreSQL服务
   sudo systemctl start postgresql

2. ### 自动化备份策略
4. 1. **创建备份脚本**
5.    ```bash
6.    #!/bin/bash
7.    
8.    # 设置变量
9.    DATE=$(date +%Y%m%d_%H%M%S)
10.    BACKUP_DIR="/path/to/backups"
11.    DB_NAME="mydb"
12.    DB_USER="myuser"
13.    
14.    # 创建备份目录
15.    mkdir -p $BACKUP_DIR
16.    
17.    # 创建逻辑备份
18.    pg_dump -U $DB_USER -h localhost -d $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz
19.    
20.    # 保留最近30天的备份，删除旧备份
21.    find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +30 -delete
22.    
23.    # 记录备份日志
24.    echo "Backup completed at $(date)" >> $BACKUP_DIR/backup_log.txt

复制代码

1. 设置定时任务“`bash编辑crontabcrontab -e

设置定时任务“`bash

crontab -e

# 添加每天凌晨2点执行备份
   0 2 * * * /path/to/backup_script.sh
   “`

常见问题与解决方案

连接问题

1. 无法连接到服务器检查PostgreSQL服务是否运行：
“`bashLinux系统sudo systemctl status postgresql# Windows系统
net start | findstr PostgreSQL- 检查`postgresql.conf`中的`listen_addresses`设置：
```ini
listen_addresses = '*'  # 允许所有IP连接检查防火墙设置：
“`bashLinux系统sudo ufw status
sudo firewall-cmd –list-all# Windows系统
netsh advfirewall show allprofiles- 检查`pg_hba.conf`中的认证设置：
```ini
# 允许特定IP连接
host    all             all             192.168.1.0/24         md5
2. 检查PostgreSQL服务是否运行：
“`bashLinux系统sudo systemctl status postgresql
3. 检查防火墙设置：
“`bashLinux系统sudo ufw status
sudo firewall-cmd –list-all
4.

2. 连接被拒绝确保使用正确的端口号：# 使用非默认端口连接
3. psql -h localhost -p 5433 -U myuser -d mydb检查用户权限：-- 检查用户是否有连接权限
4. SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'myuser';

复制代码

5. 确保使用正确的端口号：# 使用非默认端口连接
psql -h localhost -p 5433 -U myuser -d mydb
6.

2. 检查用户权限：-- 检查用户是否有连接权限
3. SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'myuser';

复制代码

无法连接到服务器

• 检查PostgreSQL服务是否运行：
“`bashLinux系统sudo systemctl status postgresql

检查PostgreSQL服务是否运行：
“`bash

sudo systemctl status postgresql

# Windows系统
net start | findstr PostgreSQL

2. - 检查`postgresql.conf`中的`listen_addresses`设置：
3. ```ini
4. listen_addresses = '*'  # 允许所有IP连接

复制代码

• 检查防火墙设置：
“`bashLinux系统sudo ufw status
sudo firewall-cmd –list-all

检查防火墙设置：
“`bash

sudo ufw status
sudo firewall-cmd –list-all

# Windows系统
netsh advfirewall show allprofiles

2. - 检查`pg_hba.conf`中的认证设置：
3. ```ini
4. # 允许特定IP连接
5. host    all             all             192.168.1.0/24         md5

复制代码

连接被拒绝

• 确保使用正确的端口号：# 使用非默认端口连接
psql -h localhost -p 5433 -U myuser -d mydb
•

2. 检查用户权限：-- 检查用户是否有连接权限
3. SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'myuser';

复制代码

确保使用正确的端口号：

2. # 使用非默认端口连接
3. psql -h localhost -p 5433 -U myuser -d mydb

复制代码

检查用户权限：

2. -- 检查用户是否有连接权限
3. SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'myuser';

复制代码

性能问题

1.

2. 查询执行缓慢分析查询执行计划：EXPLAIN ANALYZE SELECT * FROM mytable WHERE condition;添加适当的索引：CREATE INDEX idx_mytable_column ON mytable (column);更新统计信息：ANALYZE mytable;调整内存参数：# 在postgresql.conf中
3. work_mem = 16MB
4. shared_buffers = 4GB

复制代码

2. 分析查询执行计划：EXPLAIN ANALYZE SELECT * FROM mytable WHERE condition;
3. 添加适当的索引：CREATE INDEX idx_mytable_column ON mytable (column);
4. 更新统计信息：ANALYZE mytable;
5. 调整内存参数：# 在postgresql.conf中
work_mem = 16MB
shared_buffers = 4GB
6.

2. 数据库响应缓慢检查活跃连接：SELECT count(*) FROM pg_stat_activity;检查长事务：SELECT * FROM pg_stat_activity WHERE (now() - xact_start) > interval '5 minutes';检查锁等待：SELECT blocked_locks.pid AS blocked_pid,
3.     blocked_activity.usename AS blocked_user,
4.     blocking_locks.pid AS blocking_pid,
5.     blocking_activity.usename AS blocking_user,
6.     blocked_activity.query AS blocked_statement,
7.     blocking_activity.query AS current_statement_in_blocking_process
8. FROM pg_catalog.pg_locks blocked_locks
9. JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
10. JOIN pg_catalog.pg_locks blocking_locks
11. ON blocking_locks.locktype = blocked_locks.locktype
12. AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
13. AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
14. AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
15. AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
16. AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
17. AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
18. AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
19. AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
20. AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
21. AND blocking_locks.pid != blocked_locks.pid
22. JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
23. WHERE NOT blocked_locks.GRANTED;

复制代码

7. 检查活跃连接：SELECT count(*) FROM pg_stat_activity;
8. 检查长事务：SELECT * FROM pg_stat_activity WHERE (now() - xact_start) > interval '5 minutes';
9.

2. 检查锁等待：SELECT blocked_locks.pid AS blocked_pid,
3.     blocked_activity.usename AS blocked_user,
4.     blocking_locks.pid AS blocking_pid,
5.     blocking_activity.usename AS blocking_user,
6.     blocked_activity.query AS blocked_statement,
7.     blocking_activity.query AS current_statement_in_blocking_process
8. FROM pg_catalog.pg_locks blocked_locks
9. JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
10. JOIN pg_catalog.pg_locks blocking_locks
11. ON blocking_locks.locktype = blocked_locks.locktype
12. AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
13. AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
14. AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
15. AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
16. AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
17. AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
18. AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
19. AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
20. AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
21. AND blocking_locks.pid != blocked_locks.pid
22. JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
23. WHERE NOT blocked_locks.GRANTED;

复制代码

查询执行缓慢

• 分析查询执行计划：EXPLAIN ANALYZE SELECT * FROM mytable WHERE condition;
• 添加适当的索引：CREATE INDEX idx_mytable_column ON mytable (column);
• 更新统计信息：ANALYZE mytable;
• 调整内存参数：# 在postgresql.conf中
work_mem = 16MB
shared_buffers = 4GB

分析查询执行计划：EXPLAIN ANALYZE SELECT * FROM mytable WHERE condition;

2. EXPLAIN ANALYZE SELECT * FROM mytable WHERE condition;

复制代码

添加适当的索引：CREATE INDEX idx_mytable_column ON mytable (column);

2. CREATE INDEX idx_mytable_column ON mytable (column);

复制代码

更新统计信息：ANALYZE mytable;

2. ANALYZE mytable;

复制代码

调整内存参数：

2. # 在postgresql.conf中
3. work_mem = 16MB
4. shared_buffers = 4GB

复制代码

数据库响应缓慢

• 检查活跃连接：SELECT count(*) FROM pg_stat_activity;
• 检查长事务：SELECT * FROM pg_stat_activity WHERE (now() - xact_start) > interval '5 minutes';
•

2. 检查锁等待：SELECT blocked_locks.pid AS blocked_pid,
3.     blocked_activity.usename AS blocked_user,
4.     blocking_locks.pid AS blocking_pid,
5.     blocking_activity.usename AS blocking_user,
6.     blocked_activity.query AS blocked_statement,
7.     blocking_activity.query AS current_statement_in_blocking_process
8. FROM pg_catalog.pg_locks blocked_locks
9. JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
10. JOIN pg_catalog.pg_locks blocking_locks
11. ON blocking_locks.locktype = blocked_locks.locktype
12. AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
13. AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
14. AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
15. AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
16. AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
17. AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
18. AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
19. AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
20. AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
21. AND blocking_locks.pid != blocked_locks.pid
22. JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
23. WHERE NOT blocked_locks.GRANTED;

复制代码

2. SELECT count(*) FROM pg_stat_activity;

复制代码

2. SELECT * FROM pg_stat_activity WHERE (now() - xact_start) > interval '5 minutes';

复制代码

2. SELECT blocked_locks.pid AS blocked_pid,
3.     blocked_activity.usename AS blocked_user,
4.     blocking_locks.pid AS blocking_pid,
5.     blocking_activity.usename AS blocking_user,
6.     blocked_activity.query AS blocked_statement,
7.     blocking_activity.query AS current_statement_in_blocking_process
8. FROM pg_catalog.pg_locks blocked_locks
9. JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
10. JOIN pg_catalog.pg_locks blocking_locks
11. ON blocking_locks.locktype = blocked_locks.locktype
12. AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
13. AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
14. AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
15. AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
16. AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
17. AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
18. AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
19. AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
20. AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
21. AND blocking_locks.pid != blocked_locks.pid
22. JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
23. WHERE NOT blocked_locks.GRANTED;

复制代码

磁盘空间问题

1.

2. 数据目录空间不足检查数据库大小：SELECT pg_database.datname AS database_name,
3.     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
4. FROM pg_database
5. ORDER BY pg_database_size(pg_database.datname) DESC;检查表大小：SELECT
6. schemaname,
7. tablename,
8. pg_size_pretty(size) AS size,
9. pg_size_pretty(total_size) AS total_size
10. FROM (
11. SELECT
12.      pg_catalog.pg_namespace.nspname AS schemaname,
13.      pg_catalog.pg_class.relname AS tablename,
14.      pg_catalog.pg_relation_size(pg_catalog.pg_class.oid) AS size,
15.      pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) AS total_size
16. FROM pg_catalog.pg_class
17. LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
18. WHERE pg_catalog.pg_class.relkind IN ('r', 'm', 'p')
19. ORDER BY pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) DESC
20. ) t;清理旧数据：
21. “`sql
22. – 删除旧数据
23. DELETE FROM mytable WHERE created_at < ‘2022-01-01’;– 清理表空间
24. VACUUM FULL mytable;- 设置表空间：
25. ```sql
26. -- 创建新表空间
27. CREATE TABLESPACE newspace LOCATION '/path/to/new/storage';
29. -- 移动表到新表空间
30. ALTER TABLE mytable SET TABLESPACE newspace;

复制代码

2.

2. 检查数据库大小：SELECT pg_database.datname AS database_name,
3.     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
4. FROM pg_database
5. ORDER BY pg_database_size(pg_database.datname) DESC;

复制代码

3.

2. 检查表大小：SELECT
3. schemaname,
4. tablename,
5. pg_size_pretty(size) AS size,
6. pg_size_pretty(total_size) AS total_size
7. FROM (
8. SELECT
9.      pg_catalog.pg_namespace.nspname AS schemaname,
10.      pg_catalog.pg_class.relname AS tablename,
11.      pg_catalog.pg_relation_size(pg_catalog.pg_class.oid) AS size,
12.      pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) AS total_size
13. FROM pg_catalog.pg_class
14. LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
15. WHERE pg_catalog.pg_class.relkind IN ('r', 'm', 'p')
16. ORDER BY pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) DESC
17. ) t;

复制代码

4.

2. 清理旧数据：
3. “`sql
4. – 删除旧数据
5. DELETE FROM mytable WHERE created_at < ‘2022-01-01’;

复制代码

5.

2. WAL日志占用过多空间检查WAL使用情况：SELECT pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;调整WAL配置：# 在postgresql.conf中
3. max_wal_size = 2GB
4. min_wal_size = 1GB
5. checkpoint_timeout = 15min归档旧WAL文件：# 设置归档脚本
6. archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

复制代码

6. 检查WAL使用情况：SELECT pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;
7. 调整WAL配置：# 在postgresql.conf中
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_timeout = 15min
8. 归档旧WAL文件：# 设置归档脚本
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

数据目录空间不足

•

2. 检查数据库大小：SELECT pg_database.datname AS database_name,
3.     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
4. FROM pg_database
5. ORDER BY pg_database_size(pg_database.datname) DESC;

复制代码

•

2. 检查表大小：SELECT
3. schemaname,
4. tablename,
5. pg_size_pretty(size) AS size,
6. pg_size_pretty(total_size) AS total_size
7. FROM (
8. SELECT
9.      pg_catalog.pg_namespace.nspname AS schemaname,
10.      pg_catalog.pg_class.relname AS tablename,
11.      pg_catalog.pg_relation_size(pg_catalog.pg_class.oid) AS size,
12.      pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) AS total_size
13. FROM pg_catalog.pg_class
14. LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
15. WHERE pg_catalog.pg_class.relkind IN ('r', 'm', 'p')
16. ORDER BY pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) DESC
17. ) t;

复制代码

•

2. 清理旧数据：
3. “`sql
4. – 删除旧数据
5. DELETE FROM mytable WHERE created_at < ‘2022-01-01’;

复制代码

2. SELECT pg_database.datname AS database_name,
3.     pg_size_pretty(pg_database_size(pg_database.datname)) AS size
4. FROM pg_database
5. ORDER BY pg_database_size(pg_database.datname) DESC;

复制代码

2. SELECT
3. schemaname,
4. tablename,
5. pg_size_pretty(size) AS size,
6. pg_size_pretty(total_size) AS total_size
7. FROM (
8. SELECT
9.      pg_catalog.pg_namespace.nspname AS schemaname,
10.      pg_catalog.pg_class.relname AS tablename,
11.      pg_catalog.pg_relation_size(pg_catalog.pg_class.oid) AS size,
12.      pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) AS total_size
13. FROM pg_catalog.pg_class
14. LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
15. WHERE pg_catalog.pg_class.relkind IN ('r', 'm', 'p')
16. ORDER BY pg_catalog.pg_total_relation_size(pg_catalog.pg_class.oid) DESC
17. ) t;

复制代码

– 清理表空间
VACUUM FULL mytable;

2. - 设置表空间：
3. ```sql
4. -- 创建新表空间
5. CREATE TABLESPACE newspace LOCATION '/path/to/new/storage';
7. -- 移动表到新表空间
8. ALTER TABLE mytable SET TABLESPACE newspace;

复制代码

WAL日志占用过多空间

• 检查WAL使用情况：SELECT pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;
• 调整WAL配置：# 在postgresql.conf中
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_timeout = 15min
• 归档旧WAL文件：# 设置归档脚本
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

检查WAL使用情况：SELECT pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;

2. SELECT pg_walfile_name(pg_current_wal_lsn()) as current_wal_file;

复制代码

调整WAL配置：

2. # 在postgresql.conf中
3. max_wal_size = 2GB
4. min_wal_size = 1GB
5. checkpoint_timeout = 15min

复制代码

归档旧WAL文件：

2. # 设置归档脚本
3. archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

复制代码

总结

本指南详细介绍了PostgreSQL的安装、配置、安全设置、性能优化以及备份恢复等方面的内容。通过遵循这些步骤，您可以成功搭建一个稳定、高效且安全的PostgreSQL数据库环境。

PostgreSQL作为一款功能强大的开源数据库系统，提供了丰富的功能和灵活的配置选项。在实际应用中，您可能需要根据具体需求调整配置参数，优化性能，并制定合适的备份策略。

随着您对PostgreSQL的深入了解，您还可以探索更多高级功能，如流复制、逻辑复制、分区表、并行查询等，以进一步提升数据库的性能和可用性。

希望本指南能够帮助您顺利完成PostgreSQL的搭建和配置工作，为您的应用提供可靠的数据存储和管理支持。