PostgreSQL

先安装 PostgreSQL

以下是在 Ubuntu 上安装 PostgreSQL 并进行类似 MySQL 配置的完整步骤:

1. 安装 PostgreSQL

# 更新包列表
sudo apt update

# 安装 PostgreSQL
sudo apt install postgresql postgresql-contrib

# 查看 PostgreSQL 状态
sudo systemctl status postgresql

2. 配置远程访问

# 编辑 PostgreSQL 配置文件
sudo vim /etc/postgresql/*/main/postgresql.conf

# 找到并修改以下行（约 59 行）: 
listen_addresses = '*'

# 编辑客户端认证配置
sudo vim /etc/postgresql/*/main/pg_hba.conf

# 在文件末尾添加（允许所有 IP 访问）: 
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5

3. 重启 PostgreSQL 服务

# 重启服务使配置生效
sudo systemctl restart postgresql

# 允许防火墙端口（如果需要）
sudo ufw allow 5432

4. PostgreSQL 命令对应 MySQL 的操作

连接到 PostgreSQL（默认使用 postgres 用户）

sudo -u postgres psql

在 PostgreSQL 中执行以下命令:

-- 创建用户（相当于 MySQL 的 CREATE USER）
CREATE USER test WITH PASSWORD 'test';

-- 创建数据库（PostgreSQL 创建数据库时指定编码）
CREATE DATABASE test 
    WITH 
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TEMPLATE = template0;

-- 或者更简单的创建数据库
CREATE DATABASE test;

-- 授予所有权限（相当于 MySQL 的 GRANT ALL PRIVILEGES）
GRANT ALL PRIVILEGES ON DATABASE test TO test;

-- 连接到 test 数据库
\c test

-- 授予用户对数据库中所有表的权限（需要在具体数据库中执行）
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO test;

-- 授予用户创建表的权限
GRANT CREATE ON SCHEMA public TO test;

-- 设置默认权限，使得未来创建的表也自动有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON TABLES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public  
    GRANT ALL ON SEQUENCES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON FUNCTIONS TO test;

-- 查看用户权限（相当于 MySQL 的 SHOW GRANTS）
\du test

-- 查看数据库权限
\l test

5. 完整脚本示例

创建脚本文件 setup_postgres.sh:

#!/bin/bash

# 切换到 postgres 用户执行以下命令
sudo -u postgres psql <<EOF
-- 删除用户如果存在
DROP USER IF EXISTS test;

-- 删除数据库如果存在
DROP DATABASE IF EXISTS test;

-- 创建用户并设置密码
CREATE USER test WITH PASSWORD 'test';

-- 创建数据库
CREATE DATABASE test 
    WITH 
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TEMPLATE = template0;

-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE test TO test;

-- 连接到数据库并设置模式权限
\c test

-- 授予模式权限
GRANT CREATE ON SCHEMA public TO test;

-- 授予现有对象权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO test;

-- 设置未来对象的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON TABLES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public  
    GRANT ALL ON SEQUENCES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT ALL ON FUNCTIONS TO test;
EOF

echo "PostgreSQL 用户和数据库设置完成！"

6. 连接测试

# 使用新用户连接测试
psql -h localhost -U test -d test -W

# 或者指定 IP 连接
psql -h [服务器IP] -U test -d test -W

重要区别说明:

1. 权限系统不同: PostgreSQL 使用基于角色的权限系统
2. 没有 FLUSH PRIVILEGES: PostgreSQL 权限更改立即生效
3. 编码设置: 创建数据库时指定编码
4. 模式概念: PostgreSQL 有 schema 概念（默认是 public）

7. 快速重置数据库（类似 MySQL 的 DROP DATABASE）

-- 在 psql 中执行
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'test'
  AND pid <> pg_backend_pid();

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
GRANT ALL PRIVILEGES ON DATABASE test TO test;

这样就完成了类似 MySQL 的安装和配置过程！

---

配置密码

PostgreSQL 的认证机制和 MySQL 不同。默认情况下，PostgreSQL 使用操作系统用户认证或 peer authentication。以下是详细解释和解决方案:

1. PostgreSQL 默认用户情况

• 默认超级用户: postgres
• 默认无密码: 安装时不设置密码，通过系统认证
• 默认只能本地连接: 需要通过系统用户切换

2. 连接问题的几种解决方案

方案一: 使用系统用户身份连接（推荐）

# 通过系统用户 postgres 连接（无需密码）
sudo -u postgres psql

# 或者直接切换到 postgres 用户
sudo su - postgres
psql

方案二: 为 postgres 用户设置密码

在 PostgreSQL 命令行中:

-- 登录到 postgres 用户
sudo -u postgres psql

-- 修改 postgres 用户密码
ALTER USER postgres WITH PASSWORD 'new_password';

方案三: 使用你已经创建的 test 用户

如果已创建 test 用户，这样连接:

# 方式1: 指定用户名和数据库
psql -h 10.0.0.18 -U test -d test -W

# 方式2: 使用连接字符串
psql postgresql://test:test@10.0.0.18/test

3. 完整创建用户并设置密码的流程

# 1. 以系统 postgres 用户身份登录
sudo -u postgres psql

# 2. 在 PostgreSQL 中执行以下命令

-- 创建新用户并设置密码（相当于 MySQL 的 create user）
CREATE USER test WITH PASSWORD 'test';

-- 创建数据库
CREATE DATABASE test;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE test TO test;

-- 退出
\q

4. 测试连接的不同方式

本地连接（不指定主机）

# 方式1: 使用 postgres 系统用户
sudo -u postgres psql

# 方式2: 为新用户创建 .pgpass 文件
echo "localhost:5432:test:test:test" >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h localhost -U test -d test

远程连接

# 方式1: 交互式输入密码
psql -h 10.0.0.18 -U test -d test -W

# 方式2: 通过环境变量设置密码
PGPASSWORD=test psql -h 10.0.0.18 -U test -d test

# 方式3: 使用连接字符串
psql "host=10.0.0.18 port=5432 dbname=test user=test password=test"

5. 常见问题排查

检查用户是否已创建

sudo -u postgres psql -c "\du"

检查数据库权限

sudo -u postgres psql -c "\l"

检查认证配置是否正确

# 查看 pg_hba.conf 配置
sudo grep -A 5 -B 5 "test" /etc/postgresql/*/main/pg_hba.conf

# 或查看整个认证配置
sudo cat /etc/postgresql/*/main/pg_hba.conf

6. 快速重置和创建脚本

创建 setup_postgres_user.sh:

#!/bin/bash

# 设置变量
DB_USER="test"
DB_PASS="test"
DB_NAME="test"
PG_HOST="localhost"

echo "正在设置 PostgreSQL 用户和数据库..."

# 以 postgres 用户执行 SQL 命令
sudo -u postgres psql <<EOF
-- 终止连接到 test 数据库的所有会话
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '${DB_NAME}'
  AND pid <> pg_backend_pid();

-- 删除用户和数据库（如果存在）
DROP USER IF EXISTS ${DB_USER};
DROP DATABASE IF EXISTS ${DB_NAME};

-- 创建用户和数据库
CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASS}';
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};

-- 输出创建信息
\echo "用户 ${DB_USER} 和数据库 ${DB_NAME} 创建成功！"
EOF

echo "设置完成！"
echo "连接命令: "
echo "本地连接: psql -h ${PG_HOST} -U ${DB_USER} -d ${DB_NAME} -W"
echo "远程连接: psql -h [服务器IP] -U ${DB_USER} -d ${DB_NAME} -W"

给脚本执行权限并运行:

chmod +x setup_postgres_user.sh
./setup_postgres_user.sh

7. 重要提示

1. PostgreSQL 没有 FLUSH PRIVILEGES 命令 - 权限更改立即生效
2. 认证方式多样 - 可以配置为密码认证、证书认证、系统认证等
3. 默认端口 5432 - 与 MySQL 的 3306 不同
4. 连接字符串格式 - postgresql://user:password@host:port/database

现在你应该可以成功连接了。如果还有问题，请告诉我具体的错误信息。