PostgreSQL的操作

# 安装
## 非root安装
没有root权限安装 PostgreSQL（本地用户目录）
### 🧰 步骤一：准备工作
确保系统已安装以下依赖（如无权限，可跳过系统级依赖，后续用源码解决）：
```
# 如果你能用 apt，先尝试安装依赖（可选）
apt-get install build-essential libreadline-dev zlib1g-dev
```
如果无权限，可以手动编译依赖（见后文）。
### 📥 步骤二：下载源码
```
cd ~
#wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz
wget https://ftp.postgresql.org/pub/source/v16.9/postgresql-16.9.tar.gz
tar -xzf postgresql-16.9.tar.gz
cd postgresql-16.9
```
### ⚙️ 步骤三：配置安装路径（无 root）
```
./configure --prefix=$HOME/postgres --without-readline --without-zlib
```
 --prefix=$HOME/postgres  表示安装到用户目录下的  ~/postgres ，无需 root。

### 🛠️ 步骤四：编译并安装
```
make -j$(nproc)
make install
```
### 🗂️ 步骤五：初始化数据库
```
mkdir -p ~/postgres/data
~/postgres/bin/initdb -D ~/postgres/data
```
### 🚀 步骤六：启动 PostgreSQL 服务
```
~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile start
```
### 🔐 步骤七：创建用户和数据库（可选）
```
~/postgres/bin/createuser -s $USER
~/postgres/bin/createdb $USER
```
### 🔗 步骤八：连接数据库
`~/postgres/bin/psql`

### ✅ 添加到环境变量（可选）
将以下内容添加到  ~/.bashrc  或  ~/.zshrc ：
```
export PATH=$HOME/postgres/bin:$PATH
export PGDATA=$HOME/postgres/data
# 然后执行：
source ~/.bashrc
```
🧪 验证安装
bash
psql --version
# 输出例如：psql (PostgreSQL) 15.4

## root安装【失败】
```
sudo apt-get install postgresql postgresql-contrib
```

# 数据库基本操作
安装路径按前面无 root 方式举例为：
```
~/postgres/bin/psql
~/postgres/data/
```
通过`psql` 进入数据库操作！

## 创建用户
```sql
-- 创建用户
CREATE USER secure_user WITH PASSWORD 'MyStrongPass!';

-- 可选：赋予创建数据库、超级用户等权限（按需）
-- ALTER USER secure_user CREATEDB;
```

## ✅ 启动数据库（若尚未启动）
```bash
~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile start
# 重启
~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile restart
```

## ✅ 用超级用户登录
```bash
~/postgres/bin/psql postgres
```

## 修改用户密码
```sql
ALTER ROLE lvlh PASSWORD 'NewStrongPass!';
```
或者 ` ~/postgres/bin/psql -c "ALTER ROLE lvlh PASSWORD 'NewStrongPass!';"`

## 删除旧数据库
```sql
-- 强制断开其他会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'genome_variants_db';

-- 删除数据库
DROP DATABASE IF EXISTS genome_variants_db;
```

## 创建数据库
```sql
CREATE DATABASE genome_variants_db;
\c genome_variants_db
```
## 创建不同角色（用户）
```sql
-- 全权用户：owner，可读写
CREATE ROLE admin_user LOGIN PASSWORD 'AdminPass123';

-- 只读用户
CREATE ROLE read_only_user LOGIN PASSWORD 'ReadPass123';
```

## ✅ 将数据库属主改为全权用户
```sql
ALTER DATABASE genome_variants_db OWNER TO admin_user;
```

## ✅ 切换到目标数据库，为只读用户授权

```sql
\c genome_variants_db
```

6.1 把现有所有表/序列/视图的只读权限一次性赋予

```sql
-- 对现有对象
GRANT CONNECT ON DATABASE genome_variants_db TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user;
```

6.2 以后新建的对象也自动拥有只读权限（一劳永逸）

```sql
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON SEQUENCES TO read_only_user;
```

## ✅ 退出 psql
`quit`或者`\q`
```sql
\q
```

## 【用于创建本地库】实操：
需要创建一个数据库 名为genome_variants_db，并设置2个用户，一个lvlh具有所有权限，一个tester只有读取查询的权限；
`~/postgres/bin/psql postgres`超级管理员登录
```sql
-- 1) 创建数据库（如已存在可忽略）
CREATE DATABASE genome_variants_db;

-- 2) 创建两个登录角色
CREATE ROLE lvlh       LOGIN PASSWORD 'meifen1028';
CREATE ROLE tester     LOGIN PASSWORD 'Tester_123';

-- 3) 将数据库属主改为全权用户
ALTER DATABASE genome_variants_db OWNER TO lvlh;

-- 4) 切换到目标数据库
\c genome_variants_db

-- 5) 为只读用户授予已有对象的查询权限
GRANT CONNECT ON DATABASE genome_variants_db TO tester;
GRANT USAGE ON SCHEMA public TO tester;
GRANT SELECT ON ALL TABLES    IN SCHEMA public TO tester;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tester;

-- 6) 为今后新建的对象自动赋予只读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES    TO tester;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON SEQUENCES TO tester;
```

### 初始化数据库`genome_variants_db`:

- 用已经写好的SQL文件，psql操作
```sql
\i variants_optimized.sql
```
- 直接命令行操作
`psql -U lvlh -d genome_variants_db -f variants_final.sql`

## ✅ 局域网/本机测试连接
- 全权用户

```bash
  psql -h localhost -U admin_user -d genome_variants_db
```

- 只读用户

```bash
psql -h localhost -U read_only_user -d genome_variants_db
```

只读用户尝试 `INSERT`/`UPDATE`/`DELETE` 会被拒绝。

## 🔐 额外安全（可选）
- 若只允许局域网 IP，可在 `pg_hba.conf` 里把 `192.168.0.0/16` 对应的行改成：

```
  host genome_variants_db admin_user 192.168.0.0/16 md5
  host genome_variants_db read_only_user 192.168.0.0/16 md5
```

- 修改后重启：
```bash
~/postgres/bin/pg_ctl restart -D ~/postgres/data
```

# 局域网连接
要在局域网内（比如同一 Wi-Fi 或公司内网）让其他机器连接你这台 PostgreSQL，需要三步：
## ✅ 步骤 1：让 PostgreSQL 监听局域网 IP
编辑配置文件：
`vim ~/postgres/data/postgresql.conf`

找到并修改：
```
listen_addresses = '*'          # 默认是 localhost，改成 '*' 表示监听所有网卡
port = 5432                     # 确保端口是你想要的（默认 5432）
```
保存后退出，重启 PostgreSQL：
`~/postgres/bin/pg_ctl restart -D ~/postgres/data`

## ✅ 步骤 2：允许局域网 IP 连接（修改认证配置）
编辑：
`vim ~/postgres/data/pg_hba.conf`

在文件末尾新增一行（IPv4 局域网）：
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.0.0/16          md5
```
说明：
保存后重启 PostgreSQL：
`~/postgres/bin/pg_ctl restart -D ~/postgres/data`

## ✅ 步骤 3：开放防火墙端口（如有）
如果你机器有  ufw  防火墙：
`sudo ufw allow 5432/tcp`
如果没有 root 权限，可以跳过这一步；只要系统没防火墙就行。

## ✅ 步骤 4：从另一台机器连接测试
在局域网另一台电脑上（已安装  psql  或其他客户端），运行：
`psql -h 你的局域网IP -p 5432 -U your_unix_user -d genome_variants_db`

例如：
psql -h 192.168.1.100 -U alice -d genome_variants_db

系统会提示输入密码（如果你设置了）。
## 🔍 如何查看本机局域网 IP？
`ip a | grep 'inet 192.168'`

# 连接错误问题
## 用户未给数据库设置密码
> 用户  lvlh  没有设置过密码，而  pg_hba.conf  要求  md5 （密码）认证，于是登录失败。

会出现如下报错：
```
2025-08-06 14:30:18.814 CST [26203] FATAL:  password authentication failed for user "lvlh"
2025-08-06 14:30:18.814 CST [26203] DETAIL:  User "lvlh" has no password assigned.
Connection matched pg_hba.conf line 99: "host    all             all             192.168.0.0/16          md5"
```

进入数据库设置密码即可（测试设置了test12345）：
```
~/postgres/bin/psql -U lvlh -d genome_variants_db
```