PostgreSQL 的安装和基本配置

PostgreSQL 的安装和基本配置

PostgreSQL 的安装和配置过程可以在多种操作系统（Linux、Windows、macOS）上进行。以下是通用步骤和具体操作的详细说明。

---

1. 安装 PostgreSQL

1.1 在 Linux 上安装

常见的 Linux 发行版（如 Ubuntu、CentOS 等）都提供 PostgreSQL 的官方软件包。

Ubuntu/Debian：

1. 添加 PostgreSQL 官方仓库：

   sudo apt update
   sudo apt install wget ca-certificates -y
   wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
   

2. 安装 PostgreSQL：

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

3. 检查服务状态：

   sudo systemctl status postgresql
   

CentOS/RHEL：

1. 安装 PostgreSQL 官方 Yum 仓库：

   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：

   sudo yum install -y postgresql15 postgresql15-server
   

3. 初始化数据库并启动服务：

   sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
   sudo systemctl enable postgresql-15
   sudo systemctl start postgresql-15
   

---

1.2 在 Windows 上安装

1. 下载 PostgreSQL 安装包：

   • 前往 PostgreSQL 官网 下载对应版本的安装程序。

2. 执行安装程序：

   • 双击安装程序，按提示完成安装。
   • 在安装过程中会要求设置超级用户 postgres 的密码，请记住该密码。

3. 验证安装：

   • 打开命令行工具（例如 cmd 或 PowerShell），运行以下命令：

     psql -U postgres
     

---

1.3 在 macOS 上安装

使用 Homebrew 安装：

1. 更新 Homebrew：

   brew update
   

2. 安装 PostgreSQL：

   brew install postgresql
   

3. 启动服务：

   brew services start postgresql
   

4. 验证安装：

   psql postgres
   

---

2. 基本配置

2.1 配置文件位置

PostgreSQL 的主要配置文件存储在以下路径：

• postgresql.conf：
  • 数据库的核心配置文件（端口、内存分配、日志等）。
  • 默认路径：/etc/postgresql/{version}/main/（Ubuntu）或 /var/lib/pgsql/{version}/data/（CentOS）。
  • listen_addresses = '*'
• pg_hba.conf：
  • 负责访问控制（认证方法、允许的 IP 地址等）。
  • 默认路径与 postgresql.conf 相同。
  • host all all 0.0.0.0/0 md5
• postgresql.conf：
  • 默认路径：/var/lib/pgsql/12/data/postgresql.conf
  • listen_addres = '*'

---

2.2 常见配置项

修改监听地址：

• 默认情况下，PostgreSQL 只监听本地连接 (localhost)。
• 如果需要支持远程连接，请修改 postgresql.conf：

  listen_addresses = '*'
  

• 重启服务使更改生效：

  sudo systemctl restart postgresql
  

配置访问权限：

• 编辑 pg_hba.conf，允许特定的 IP 地址访问：

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    all             all             192.168.1.0/24         md5
  

• md5 表示使用密码验证。

设置端口号：

• 默认端口是 5432，可以在 postgresql.conf 中修改：

  port = 5433
  

---

2.3 初始化数据库用户和权限

1. 切换到 postgres 用户：

   sudo -i -u postgres
   

2. 创建新用户：

   createuser myuser -P
   

   • 添加 -P 参数会提示设置密码。

3. 创建新数据库：

   createdb mydb -O myuser
   

4. 通过 psql 工具登录：

   psql -U myuser -d mydb
   

---

3. 性能优化配置

3.1 内存相关配置

• shared_buffers：

  • 用于设置数据库缓存大小。
  • 通常设置为系统总内存的 25%~40%：

    shared_buffers = 2GB
    

• work_mem：

  • 单个排序操作或哈希表的内存大小。
  • 默认较小，可以根据查询复杂性调整：

    work_mem = 64MB
    

3.2 并发配置

• max_connections：

  • 最大并发连接数：

    max_connections = 200
    

• effective_cache_size：

  • 数据库可以利用的操作系统文件系统缓存大小。
  • 通常设置为总内存的 50%~75%：

    effective_cache_size = 4GB
    

3.3 日志配置

• 启用查询日志：
  • 在 postgresql.conf 中设置：

    logging_collector = on
    log_statement = 'all'
    log_directory = 'pg_log'
    log_filename = 'postgresql-%Y-%m-%d.log'
    

---

4. 备份与恢复

4.1 备份数据库

• 逻辑备份：

  pg_dump -U myuser -F c -b -v -f mydb.backup mydb
  

• 物理备份：
  使用 pg_basebackup 进行数据目录级别的备份：

  pg_basebackup -D /backup/dir -F tar -z -P -U postgres
  

4.2 恢复数据库

• 从逻辑备份恢复：

  pg_restore -U myuser -d mydb -v mydb.backup
  

• 从物理备份恢复：

  • 停止 PostgreSQL 服务，将备份解压到数据目录，然后重启服务。

---

5. 常见问题和解决

远程连接失败

1. 检查 listen_addresses 是否配置为 '*'。
2. 确保 pg_hba.conf 中配置了允许的 IP 地址。
3. 检查防火墙是否开放了 PostgreSQL 端口（默认为 5432）。

用户权限不足

• 确保用户具有必要权限：

  GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
  

服务启动失败

• 检查日志文件（通常位于 /var/log/postgresql/ 或 pg_log）。