PostgreSQL 安装、常用命令及优化

一、安装 PostgreSQL

（一）二进制安装方式

1. 下载二进制包

首先，访问 PostgreSQL 官方下载页面（PostgreSQL: File Browser），选择合适的版本。以 PostgreSQL 14.8 为例，在 Linux 系统上可以使用以下命令下载：

bash

wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz

2. 解压文件

使用以下命令解压下载的压缩包：

bash

tar -zxvf postgresql-14.8.tar.gz
cd postgresql-14.8

3. 配置编译选项

在编译之前，需要配置一些编译选项。可以使用 ./configure 命令进行配置，以下是一个基本的配置示例：

bash

./configure --prefix=/usr/local/pgsql

• --prefix：指定 PostgreSQL 的安装路径，这里设置为 /usr/local/pgsql，你可以根据需要修改。

4. 编译和安装

配置完成后，执行以下命令进行编译和安装：

bash

make
sudo make install

编译和安装过程可能需要一些时间，取决于系统性能。

5. 创建用户和组

为 PostgreSQL 创建一个专用的用户和组，以提高安全性：

bash

sudo groupadd postgres
sudo useradd -g postgres postgres

6. 初始化数据库

切换到 postgres 用户，并初始化数据库：

bash

sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

• -D：指定数据库的数据目录，这里设置为 /usr/local/pgsql/data。

7. 启动服务

创建一个启动脚本，方便后续启动和管理 PostgreSQL 服务。在 /etc/systemd/system 目录下创建 postgresql.service 文件：

bash

sudo vim /etc/systemd/system/postgresql.service

添加以下内容：

plaintext

[Unit]
Description=PostgreSQL 14 database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p 5432"
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
TimeoutSec=0

[Install]
WantedBy=multi-user.target

保存并退出文件，然后重新加载 systemd 配置：

bash

sudo systemctl daemon-reload

启动 PostgreSQL 服务并设置开机自启：

bash

sudo systemctl start postgresql
sudo systemctl enable postgresql

（二）在 CentOS/RHEL 系统上使用包管理器安装

• 添加 PostgreSQL 仓库

bash

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

将 EL - 7 替换为你实际的 CentOS/RHEL 版本（如 EL - 8）。

• 安装 PostgreSQL
  以 PostgreSQL 14 为例：

bash

sudo yum install -y postgresql14-server

• 初始化数据库

bash

sudo /usr/pgsql - 14/bin/postgresql - 14 - setup initdb

• 启动并设置开机自启

bash

sudo systemctl start postgresql - 14
sudo systemctl enable postgresql - 14

（三）在 Ubuntu/Debian 系统上使用包管理器安装

• 添加 PostgreSQL 仓库

bash

sudo sh -c 'echo "deb http://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 -

• 安装 PostgreSQL
  以 PostgreSQL 14 为例：

bash

sudo apt update
sudo apt install -y postgresql - 14

• 启动并设置开机自启

bash

sudo systemctl start postgresql
sudo systemctl enable postgresql

（四）在 macOS 系统上安装

• 使用 Homebrew 安装

bash

brew install postgresql

• 启动 PostgreSQL 服务

bash

brew services start postgresql

二、配置 PostgreSQL

• 切换到 postgres 用户

bash

sudo -u postgres psql

• 创建新用户和数据库

sql

CREATE USER your_user WITH PASSWORD 'your_password';
CREATE DATABASE your_database OWNER your_user;

• 退出 psql

sql

\q

三、PostgreSQL 常用命令及使用方式

（一）服务管理命令

1. 启动服务

• Linux（systemd）：

bash

sudo systemctl start postgresql

• macOS（Homebrew）：

bash

brew services start postgresql

2. 停止服务

• Linux（systemd）：

bash

sudo systemctl stop postgresql

• macOS（Homebrew）

bash

brew services stop postgresql

3. 重启服务

• Linux（systemd）：

bash

sudo systemctl restart postgresql

• macOS（Homebrew）：

bash

brew services restart postgresql

4. 查看服务状态

• Linux（systemd）：

bash

sudo systemctl status postgresql

• macOS（Homebrew）：

bash

brew services info postgresql

（二）psql 客户端命令

1. 连接数据库

• 以 postgres 用户连接到默认数据库：

bash

sudo -u postgres psql

• 以指定用户连接到指定数据库：

bash

psql -U your_user -d your_database

• 连接到远程数据库：

bash

psql -h remote_host -p 5432 -U your_user -d your_database

2. 数据库操作

• 创建数据库：

sql

CREATE DATABASE new_database;

• 删除数据库：

sql

DROP DATABASE existing_database;

• 查看所有数据库：

sql

\l

3. 表操作

• 创建表：

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

• 删除表：

sql

DROP TABLE users;

• 查看所有表：

sql

\dt

• 查看表结构：

sql

\d users

4. 用户和权限操作

• 创建用户：

sql

CREATE USER new_user WITH PASSWORD 'new_password';

• 删除用户：

sql

DROP USER existing_user;

• 授予权限：

sql

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

• 撤销权限：

sql

REVOKE ALL PRIVILEGES ON DATABASE your_database FROM your_user;

5. 数据操作

• 插入数据：

sql

INSERT INTO users (name, age) VALUES ('John', 30);

• 查询数据：

sql

SELECT * FROM users;

• 更新数据：

sql

UPDATE users SET age = 31 WHERE name = 'John';

• 删除数据：

sql

DELETE FROM users WHERE name = 'John';

6. 其他 psql 命令

• 退出 psql：

sql

\q

• 查看帮助信息：

sql

\?

（三）备份与恢复命令

1. 备份数据库

• 使用 pg_dump 备份单个数据库：

bash

pg_dump -U your_user -d your_database > backup.sql

• 备份所有数据库：

bash

pg_dumpall -U your_user > all_backup.sql

2. 恢复数据库

• 恢复单个数据库：

bash

psql -U your_user -d your_database < backup.sql

• 恢复所有数据库：

bash

psql -U your_user -f all_backup.sql postgres

四、优化 PostgreSQL

（一）内存优化

• 修改 postgresql.conf 文件
  找到 shared_buffers 参数，它表示 PostgreSQL 用于缓存数据的共享内存大小。一般可以设置为系统内存的 25% - 50%。例如，系统有 8GB 内存，可以设置为：

plaintext

shared_buffers = '2GB'

• 调整 work_mem
work_mem 控制每个排序操作和哈希表使用的内存量。根据实际情况适当增加，例如：

plaintext

work_mem = '64MB'

• 调整 maintenance_work_mem
maintenance_work_mem 用于 VACUUM、CREATE INDEX 等维护操作。可以设置为几百兆到几 GB，例如：

plaintext

maintenance_work_mem = '512MB'

（二）磁盘 I/O 优化

• 使用快速存储设备
  尽量使用 SSD 磁盘，其读写速度远高于传统 HDD 磁盘，可以显著提高 PostgreSQL 的性能。
• 优化磁盘调度算法
  对于 Linux 系统，可以为 PostgreSQL 数据目录所在的磁盘选择合适的 I/O 调度算法。例如，对于 SSD 磁盘，可以使用 noop 调度算法：

bash

echo noop > /sys/block/sda/queue/scheduler

将 sda 替换为实际的磁盘设备名。

• 调整 fsync 参数
  fsync 参数控制 PostgreSQL 是否将数据同步写入磁盘。如果对数据安全性要求不是特别高，可以将其设置为 off 以提高性能，但这样在系统崩溃时可能会丢失部分数据：

plaintext

fsync = off

（三）网络优化

• 调整 listen_addresses
  在 postgresql.conf 中设置 listen_addresses 参数，指定 PostgreSQL 监听的 IP 地址。如果需要远程访问，可以设置为 '*'：

plaintext

listen_addresses = '*'

• 修改 pg_hba.conf
  在 pg_hba.conf 文件中添加远程访问规则，例如允许所有 IP 地址以密码认证方式访问：

plaintext

host    all             all             0.0.0.0/0            md5

修改后需要重新加载配置：

bash

sudo systemctl reload postgresql - 14

（四）查询优化

• 创建合适的索引
  分析查询语句，为经常用于 WHERE 子句、JOIN 条件和 ORDER BY 子句的列创建索引。例如：

sql

CREATE INDEX idx_column_name ON your_table (column_name);

• 使用 EXPLAIN 分析查询
  使用 EXPLAIN 关键字分析查询语句的执行计划，找出可能的性能瓶颈：

sql

EXPLAIN SELECT * FROM your_table WHERE column_name = 'value';

• 定期进行 VACUUM 和 ANALYZE
  VACUUM 用于回收已删除行占用的空间，ANALYZE 用于更新表的统计信息，以帮助查询优化器生成更优的执行计划：

sql

VACUUM ANALYZE your_table;

（五）日志优化

• 调整日志级别
  在 postgresql.conf 中设置 log_statement 参数，控制日志记录的详细程度。例如，设置为 'ddl' 只记录数据定义语言（DDL）语句：

plaintext

log_statement = 'ddl'

• 定期清理日志文件
  可以编写脚本定期清理 PostgreSQL 的日志文件，避免占用过多磁盘空间。例如：

bash

find /var/log/postgresql -name "postgresql-*.log" -mtime +7 -delete

根据实际业务需求和系统资源情况，还可以进一步调整优化参数。