跳到主要内容

PostgreSQL 基本维护

信息
2024年8月15日 · ·

本文提供 PostgreSQL 的安装、启停、卸载、用户管理、库表创建及一些基本维护操作的指引,帮助你直接上手 PostgreSQL 数据库的应用与管理。

Cover

安装 PostgreSQL 15

针对不同的操作系统,安装、启动、停止和卸载 PostgreSQL 的方法虽然基本相似,但会有一些细微差异。以下是不同系统上的详细指南:

macOS(14.6)

使用 Homebrew 安装(推荐)

  • 安装 Homebrew,如果你还没有安装 Homebrew,可以在终端运行以下命令:
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
  • 安装 PostgreSQL 15,使用 Homebrew 安装 PostgreSQL:
brew install postgresql@15

安装完成提示:

This formula has created a default database cluster with:
initdb --locale=C -E UTF-8 /opt/homebrew/var/postgresql@15
For more details, read:
https://www.postgresql.org/docs/15/app-initdb.html

postgresql@15 is keg-only, which means it was not symlinked into /opt/homebrew,
because this is an alternate version of another formula.

If you need to have postgresql@15 first in your PATH, run:
echo 'export PATH="/opt/homebrew/opt/postgresql@15/bin:$PATH"' >> ~/.zshrc

For compilers to find postgresql@15 you may need to set:
export LDFLAGS="-L/opt/homebrew/opt/postgresql@15/lib"
export CPPFLAGS="-I/opt/homebrew/opt/postgresql@15/include"

For pkg-config to find postgresql@15 you may need to set:
export PKG_CONFIG_PATH="/opt/homebrew/opt/postgresql@15/lib/pkgconfig"

To start postgresql@15 now and restart at login:
brew services start postgresql@15
Or, if you don't want/need a background service you can just run:
LC_ALL="C" /opt/homebrew/opt/postgresql@15/bin/postgres -D /opt/homebrew/var/postgresql@15
  • 启动 PostgreSQL,安装完成后,可以启动 PostgreSQL 服务:
brew services start postgresql@15

或者手动启动:

pg_ctl -D /opt/homebrew/var/postgresql@15 start
  • 停止 PostgreSQL,如果需要停止 PostgreSQL 服务:
brew services stop postgresql@15

或者手动停止:

pg_ctl -D /opt/homebrew/var/postgresql@15 stop
  • 卸载 PostgreSQL,如果需要卸载 PostgreSQL:
brew uninstall postgresql@15

从 PostgreSQL 官方下载

  • 下载 PostgreSQL 安装包,从 PostgreSQL 官方网站下载适用于 macOS 的安装包,选择 PostgreSQL 15 并下载 appropriate .dmg 或 .pkg 文件后,按照安装向导执行安装过程。

  • 启动 PostgreSQL,如果你通过官方包安装,可以使用以下命令启动服务(可能需要管理员权限):

sudo su - postgres
pg_ctl -D /var/lib/postgresql/data start
exit

或者通过 macOS 的 LaunchDaemon 启动:

sudo launchctl load -w /Library/LaunchDaemons/com.edb.launchd.postgresql-15.plist
  • 停止 PostgreSQL,可以使用以下命令停止服务:
sudo su - postgres
pg_ctl -D /var/lib/postgresql/data stop
exit

或者通过 macOS 的 LaunchDaemon 停止:

sudo launchctl unload -w /Library/LaunchDaemons/com.edb.launchd.postgresql-15.plist
  • 卸载 PostgreSQL,找到 PostgreSQL 安装包中提供的卸载脚本,通常位于安装目录,执行以下命令进行卸载:
sudo /Library/PostgreSQL/15/uninstall-postgresql

验证安装

无论通过哪种方法安装 PostgreSQL,都可以通过以下命令检查 PostgreSQL 是否正确安装及其版本:

psql --version

注意事项

  • 权限:有些操作可能需要超用户权限(例如使用 sudo)。
  • 路径:根据你的具体安装情况,PostgreSQL 数据目录的路径可能会有所不同。确保使用正确的路径来管理 PostgreSQL 服务。
  • 环境变量:安装后可能需要将 PostgreSQL 的二进制目录添加到你的环境变量中,如 $PATH。可以在 ~/.zshrc~/.bash_profile 中进行配置:
    export PATH="/usr/local/opt/postgresql@15/bin:$PATH"

Debian 系统

安装 PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib

启动 PostgreSQL

sudo systemctl start postgresql
sudo systemctl enable postgresql # 开机启动

停止 PostgreSQL

sudo systemctl stop postgresql

卸载 PostgreSQL

sudo apt remove postgresql postgresql-contrib
sudo apt purge postgresql postgresql-contrib
sudo apt autoremove

CentOS 7

安装 PostgreSQL

添加 PostgreSQL 仓库并安装:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-centos13-13-2.noarch.rpm
sudo yum install -y postgresql13-server postgresql13-contrib

初始化 PostgreSQL 数据库并启动服务:

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13 # 开机启动

停止 PostgreSQL

sudo systemctl stop postgresql-13

卸载 PostgreSQL

sudo yum remove postgresql13-server postgresql13-contrib
sudo rm -rf /var/lib/pgsql/13/data /var/lib/pgsql/13/backups

CentOS 8

安装 PostgreSQL

添加 PostgreSQL 仓库并安装:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql13-server postgresql13-contrib

初始化 PostgreSQL 数据库并启动服务:

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13 # 开机启动

停止 PostgreSQL

sudo systemctl stop postgresql-13

卸载 PostgreSQL

sudo dnf remove postgresql13-server postgresql13-contrib
sudo rm -rf /var/lib/pgsql/13/data /var/lib/pgsql/13/backups

Windows 11

安装 PostgreSQL

从 PostgreSQL 官方网站下载 Windows 版本 并运行安装程序,按照向导提示进行安装。选择合适的安装目录,并记住你设置的数据库超级用户(通常是 postgres)的密码。

启动 PostgreSQL

安装程序会自动启动 PostgreSQL 服务。如果你需要手动启动,可以通过 Windows 服务管理器:

  • 打开任务管理器,切换到“服务”选项卡,点击“打开服务”。
  • 在服务列表中找到 PostgreSQL
  • 右键点击选择“启动”。

停止 PostgreSQL

通过 Windows 服务管理器停止服务:

  • 在服务列表中找到 PostgreSQL
  • 右键点击选择“停止”。

卸载 PostgreSQL

  • 打开“控制面板”。
  • 选择“程序和功能”。
  • 在列表中找到 PostgreSQL,右键点击选择“卸载”。
  • 按照卸载向导完成卸载过程。

常用操作

检查数据库状态

通过以下命令检查 PostgreSQL 服务的状态:

brew services info postgresql@15  # 使用 Homebrew 安装时

初次连接 PostgreSQL

psql 客户端连接到默认的 postgres 数据库:

psql postgres

修改初始密码(假设初始用户是 postgres),在 psql 提示符下,执行以下命令:

-- 在 psql 提示符下输入以下命令查看现有角色:
\du
-- 修改当前 role 的密码(将 {role} 替换成上一步查到的 role 名称,比如 postgres)
\password {role}

然后系统会提示你输入并确认新密码。

创建新的用户

psql 提示符下,执行以下命令:

CREATE USER new_username WITH PASSWORD 'new_password';

为新用户赋予权限(可选,根据需要):

-- 如果你希望给新用户超级用户权限,执行以下命令:
ALTER USER new_username WITH SUPERUSER;

-- 如果只需要赋予创建数据库的权限:
ALTER USER new_username WITH CREATEDB;

完成上述步骤之后,你就可以使用新用户进行数据库操作了。需要注意的是,为保证安全,请根据具体需求合理分配权限。

连接到数据库

使用 psql 命令连接到数据库:

psql -h localhost -U myuser -d mydatabase

通过 psql 连接并查看当前连接的数据库、用户等信息:

psql -h localhost -U myuser -d mydatabase -c '\conninfo'

创建数据库

# 在 PostgreSQL 交互式终端中执行
CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

备份和恢复数据库

使用 pg_dumppg_restore 进行数据库备份和恢复:

# 备份数据库
pg_dump -U myuser -h localhost -d mydatabase > mydatabase_backup.sql

# 恢复数据库
psql -U myuser -h localhost -d newdatabase < mydatabase_backup.sql

或者使用压缩格式:

# 备份数据库
pg_dump -U myuser -h localhost -d mydatabase | gzip > mydatabase_backup.sql.gz

# 恢复数据库
gunzip -c mydatabase_backup.sql.gz | psql -U myuser -h localhost -d newdatabase

配置文件

  • postgresql.conf:主要配置文件,用于设置内存、连接选项等。通常位于数据目录下。
  • pg_hba.conf:用于配置客户端认证。

注意事项

文件权限

确保 PostgreSQL 数据目录和配置文件拥有正确的文件权限。通常,数据目录应只对 PostgreSQL 用户可读写。

chmod 700 /var/lib/postgresql/data
chown -R postgres:postgres /var/lib/postgresql/data

配置文件

PostgreSQL 的两个主要配置文件是 postgresql.confpg_hba.conf。调整这些文件以优化性能和安全性。

  • postgresql.conf:通常位于 /etc/postgresql/15/main/ 或安装目录下的 data 文件夹。该文件包含服务器的主要配置选项,例如连接数、内存限制和日志记录。
  • pg_hba.conf:用于设置客户端认证方法和策略,确保只允许授权用户和客户端连接到数据库。

防火墙和网络配置

确保 PostgreSQL 所在服务器的防火墙允许所需端口(默认端口为 5432)的访问。同时,确保网络连接稳定,尤其是在远程服务器上运行 PostgreSQL 时。

例如,在 ufw 防火墙上打开 5432 端口:

sudo ufw allow 5432/tcp

安全性

  • 密码策略:为数据库用户设置强密码,并定期更新。
  • SSL/TLS 加密:使用 SSL/TLS 加密数据库连接,确保传输中的数据安全。

环境变量

为了便于日常操作,可以在 shell 配置文件(如 ~/.zshrc~/.bash_profile)中添加 PostgreSQL 的环境变量:

export PATH="/usr/local/opt/postgresql@15/bin:$PATH"

然后重新加载 shell 配置文件:

source ~/.zshrc  # 或 source ~/.bash_profile

自动启动

确保 PostgreSQL 服务在系统启动时自动启动(已经通过 Homebrew 安装服务时默认是自动启动的,如果使用其他方法,需要根据系统服务管理器配置,如 launchctl, systemd 等)。

日志管理

定期检查 PostgreSQL 的日志文件,了解数据库运行状态,并迅速解决潜在问题。日志文件的位置可在 postgresql.conf 中配置。

基本维护

VACUUM

VACUUM 用于清理数据库内不再使用的数据以释放存储空间,并防止数据库膨胀。

# 手动执行 VACUUM
psql -h localhost -U myuser -d mydatabase -c 'VACUUM;'

# 建议使用 VACUUM FULL
# 但是它会锁表,因此需要在一个维护时段执行
psql -h localhost -U myuser -d mydatabase -c 'VACUUM FULL;'

ANALYZE

ANALYZE 用于收集数据库表的统计信息,这些统计信息可以帮助查询优化器生成有效的查询计划。

# 单独执行 ANALYZE
psql -h localhost -U myuser -d mydatabase -c 'ANALYZE;'

# 也可以与 VACUUM 结合使用
psql -h localhost -U myuser -d mydatabase -c 'VACUUM ANALYZE;'

重建索引

索引是提高数据库查询性能的重要工具,但它们可能会随着时间的推移而变得不再高效,通过重建索引可以重新组织索引的存储方式。

# 重建单个索引
psql -h localhost -U myuser -d mydatabase -c 'REINDEX INDEX myindex;'

# 重建特定表的所有索引
psql -h localhost -U myuser -d mydatabase -c 'REINDEX TABLE mytable;'

# 重建整个数据库的所有索引
psql -h localhost -U myuser -d mydatabase -c 'REINDEX DATABASE mydatabase;'

备份数据库

定期备份数据库是防止数据丢失的重要手段。

# 使用 pg_dump 备份数据库
pg_dump -U myuser -h localhost -d mydatabase > mydatabase_backup.sql

# 使用压缩格式备份
pg_dump -U myuser -h localhost -d mydatabase | gzip > mydatabase_backup.sql.gz

恢复数据库

通过备份文件恢复数据库。

# 恢复数据库
psql -U myuser -h localhost -d newdatabase < mydatabase_backup.sql

# 恢复压缩格式的备份
gunzip -c mydatabase_backup.sql.gz | psql -U myuser -h localhost -d newdatabase

日志管理

定期查看和管理 PostgreSQL 的日志文件,可以有助于识别和解决潜在的问题。

配置日志记录

postgresql.conf 中配置日志记录选项,例如:

# 配置日志记录
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 100MB

查看日志文件

日志文件通常存储在配置的日志目录中,例如 /var/lib/pgsql/data/pg_log//var/log/postgresql/

# 查看最新的日志文件
tail -f /var/lib/pgsql/data/pg_log/postgresql-YYYY-MM-DD.log

监控和警报

使用工具监控数据库的性能和健康状况,并设置警报以在出现问题时通知你。

  • 监控工具
    • pgAdmin:一个流行的开源 PostgreSQL 管理工具,提供图形化界面管理和监控。
    • Prometheus + Grafana:组合使用进行监控和告警。
  • Prometheus 和 Grafana 监控
    • 部署 Prometheus 和 Grafana。
    • 安装 postgres_exporter 以从 PostgreSQL 收集性能数据。
    • 配置 Prometheus 收集数据源,并在 Grafana 中创建仪表板以可视化数据库性能。

性能调优

定期检查和优化数据库性能,确保查询执行效率高,避免瓶颈。

  • 调整连接池:通过设置合理的最大连接数来优化资源使用。
  • 优化查询:使用 EXPLAIN 分析查询计划,优化慢查询。
  • 实用配置参数:调整 shared_bufferswork_mem 等配置参数以提高性能。

安全更新

确保 PostgreSQL 版本始终更新到最新的安全补丁版本,防止已知的漏洞被利用。

# 更新 Homebrew 并升级 PostgreSQL(适用于 Homebrew 安装)
brew update
brew upgrade postgresql

# 使用包管理器更新(适用于 Debian 或 CentOS 等系统)
sudo apt update && sudo apt upgrade postgresql
sudo yum update postgresql

结语

以上涵盖了 PostgreSQL 的大部分基本维护操作,掌握这些技巧是使用 PostgreSQL 数据库的基础,也是提供数据库性能和稳定性,确保数据安全等的基本保障。


PS:感谢每一位志同道合者的阅读,欢迎关注、点赞、评论!