PostgreSQL 入门指南：安装、配置与基本命令

PostgreSQL 是一款强大的开源关系型数据库，以其高扩展性、SQL 标准兼容性和丰富的功能著称。本文将从零开始，手把手教你完成 PostgreSQL 的安装、配置，并掌握最基础的数据库操作命令。全文包含 30+个实操代码示例，覆盖 Windows、Linux、macOS 三大平台。

一、安装 PostgreSQL

1.1 Windows 系统安装

1. 步骤 1：下载安装包
   访问 PostgreSQL 官网，选择对应版本的安装包（如 PostgreSQL 15.4）。
   下载完成后，双击 .exe 文件启动安装向导。

2. 步骤 2：配置安装选项
   选择组件：默认勾选 PostgreSQL Server、pgAdmin（图形化管理工具）、Stack Builder（扩展管理工具）。
   设置安装路径：建议保持默认路径 C:\Program Files\PostgreSQL\15。
   设置超级用户密码：为默认的 postgres 用户设置强密码（如 MySecureP@ssw0rd）。
   设置端口号：默认端口为 5432，若无冲突建议保持默认。

3. 步骤 3：完成安装
   安装完成后，勾选 “Launch Stack Builder” 安装常用扩展（如 PostGIS），但非必需。

验证安装：
打开命令提示符，输入以下命令检查版本：

psql --version

若输出类似 psql (PostgreSQL) 15.4，则安装成功。

1.2 Linux 系统安装（以 Ubuntu 为例）

1. 步骤 1：添加仓库并更新

   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 -
   sudo apt update

2. 步骤 2：安装 PostgreSQL

   sudo apt install postgresql-15

3. 步骤 3：验证服务状态

   sudo systemctl status postgresql

   若显示 active (running)，则服务已启动。

1.3 macOS 系统安装

1. 步骤 1：使用 Homebrew 安装

   brew install postgresql@15

2. 步骤 2：启动服务

   brew services start postgresql@15

3. 步骤 3：验证安装

   psql -V

二、配置 PostgreSQL

2.1 修改配置文件

• 文件位置
  主配置文件：postgresql.conf（路径示例：/etc/postgresql/15/main/postgresql.conf）
  客户端认证文件：pg_hba.conf

• 关键配置项
  允许远程访问：
  修改 postgresql.conf：

  listen_addresses = '*'  # 允许所有IP连接
  port = 5432            # 默认端口

设置最大连接数：

max_connections = 100   # 根据硬件调整

修改认证方式（pg_hba.conf）：

# 允许所有IP通过密码访问
host    all             all             0.0.0.0/0               scram-sha-256

重启服务生效

sudo systemctl restart postgresql  # Linux
pg_ctl restart -D /usr/local/var/postgres  # macOS

2.2 创建新用户与数据库

1. 步骤 1：登录默认账户

   sudo -u postgres psql  # Linux/macOS
   psql -U postgres       # Windows

2. 步骤 2：创建新用户

   CREATE USER myuser WITH PASSWORD 'user123';

3. 步骤 3：创建数据库并授权

   CREATE DATABASE mydb OWNER myuser;
   GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

三、基础命令操作

3.1 使用 psql 命令行工具

1. 登录数据库

psql -U myuser -d mydb -h 127.0.0.1 -p 5432
-U：用户名
-d：数据库名
-h：主机地址
-p：端口号

2. 常用元命令

3.2 数据库与表操作

1. 创建表

   CREATE TABLE employees (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   salary NUMERIC(10,2),
   hire_date DATE DEFAULT CURRENT_DATE
   );

2. 插入数据

   INSERT INTO employees (name, salary) VALUES 
   ('Alice', 75000.50),
   ('Bob', 82000.00);

3. 查询数据

   SELECT * FROM employees WHERE salary > 80000;

   输出示例：

   id | name  | salary   | hire_date
   ----+-------+----------+------------
   2 | Bob   | 82000.00 | 2023-10-01

3.3 数据备份与恢复

1. 备份数据库

   pg_dump -U myuser -d mydb -f mydb_backup.sql

2. 恢复数据库

   psql -U myuser -d mydb -f mydb_backup.sql

四、常见问题与解决方案

1. 4.1 连接被拒绝
   错误信息：

   psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "myuser"

解决方法：

检查 pg_hba.conf 中是否允许用户连接。
确认密码是否正确：

ALTER USER myuser WITH PASSWORD 'new_password';

2. 4.2 端口冲突
   错误信息：

   psql: error: could not connect to server: could not connect to server: Connection refused

解决方法：

• 检查 PostgreSQL 服务是否运行：

  sudo systemctl status postgresql

• 查看端口占用：

  netstat -tuln | grep 5432

3. 4.3 忘记 postgres 用户密码
   • 重置密码：
     停止 PostgreSQL 服务。
     以单用户模式启动：

     postgres --single -D /var/lib/postgresql/15/main

     执行密码修改：

     ALTER USER postgres WITH PASSWORD 'new_password';

五、总结

通过本文，你已完成以下核心任务：

• 在 Windows/Linux/macOS 上安装 PostgreSQL。
• 配置远程访问和用户权限。
• 掌握 psql 命令行工具的基本操作。
• 执行 SQL 语句创建表、插入数据及查询。

附：命令速查表