如何在Debian上使用PostgreSQL进行开发 - 问答

1. 更新系统并安装PostgreSQL
在Debian上使用PostgreSQL前，需先更新系统包列表以确保依赖项最新。通过APT包管理器安装PostgreSQL及附加工具（如postgresql-contrib，提供额外功能如全文搜索、UUID生成等）：

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

安装完成后，PostgreSQL服务会自动启动，可通过sudo systemctl status postgresql验证服务状态（显示“active (running)”即为正常）。

2. 访问PostgreSQL命令行工具（psql）
默认情况下，PostgreSQL创建了一个名为postgres的超级用户。切换至该用户并进入psql终端（PostgreSQL的交互式SQL命令行）：

sudo -i -u postgres
psql

退出psql终端可使用\q命令，退出postgres用户则用exit。

3. 创建数据库与用户（角色）
PostgreSQL采用“角色”管理用户权限，需为用户分配数据库所有权。以下命令创建一个名为mydb的数据库、一个名为myuser的用户（密码为mypassword），并授予其对数据库的所有权限：

-- 在psql终端中执行
CREATE DATABASE mydb;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
\q  -- 退出psql

注意：避免使用弱密码（如123456），建议使用包含大小写字母、数字和符号的复杂密码。

4. 配置PostgreSQL以允许远程访问（可选）
若需从其他机器访问PostgreSQL，需修改以下两个配置文件：

修改监听地址：编辑postgresql.conf（路径为/etc/postgresql/<版本>/main/postgresql.conf），找到listen_addresses并将其设置为'*'（允许所有IP连接）或指定IP（如'192.168.1.100'）：
```
sudo nano /etc/postgresql/15/main/postgresql.conf
# 将 listen_addresses = 'localhost' 改为 listen_addresses = '*'
```
配置客户端认证：编辑pg_hba.conf（路径为/etc/postgresql/<版本>/main/pg_hba.conf），在文件末尾添加允许远程连接的规则（如允许192.168.1.0/24网段通过密码认证访问所有数据库）：
```
sudo nano /etc/postgresql/15/main/pg_hba.conf
# 添加以下行（method改为scram-sha-256更安全）
host    all             all             192.168.1.0/24            scram-sha-256
```

修改完成后，重启PostgreSQL服务使配置生效：

sudo systemctl restart postgresql

若系统启用了防火墙（如ufw），需允许PostgreSQL默认端口（5432）的流量：

sudo ufw allow 5432/tcp
```。


**5. 基本数据库操作（psql常用命令）**  
连接到指定数据库（如`mydb`）：  
```bash
psql -U myuser -d mydb -h localhost

常用命令：

\l：列出所有数据库；
\c mydb：连接到mydb数据库；
\dt：列出当前数据库中的所有表；
\q：退出psql。

6. 备份与恢复数据库

备份数据库：使用pg_dump工具导出数据库结构和数据为SQL脚本（如备份mydb到/backups目录）：
```
sudo -u postgres pg_dump -U postgres -W -F c -b -v -f "/backups/mydb_backup.backup" mydb
```
（-W提示输入密码，-F c表示压缩格式，-b包含大对象，-v显示详细信息）
恢复数据库：使用psql工具将备份文件导入目标数据库（如恢复到mydb）：
```
sudo -u postgres psql -U postgres -d mydb -f "/backups/mydb_backup.backup"
```。
```

7. 安全设置

修改默认密码：首次安装后，postgres用户的默认密码是随机生成的，需及时修改：
```
sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'your_new_password';
\q
```
使用强密码策略：在postgresql.conf中设置password_encryption = 'scram-sha-256'（默认已启用），强制用户使用加密密码。

8. 扩展开发（可选）
若需扩展PostgreSQL功能（如创建自定义函数、数据类型），可通过以下步骤进行：

安装开发库：PostgreSQL扩展开发需要libpq-dev库（包含头文件和工具）：
```
sudo apt install libpq-dev -y
```
创建扩展目录：新建扩展目录（如my_extension），并创建control文件（描述扩展信息）和c文件（实现功能）：
```
mkdir -p my_extension/src
touch my_extension/my_extension.control
touch my_extension/src/my_extension.c
```

编写扩展代码：在my_extension.control中填写扩展元数据（如名称、版本），在src/my_extension.c中编写C语言函数（如简单的加法函数）：

// my_extension.c
#include "postgres.h"
#include "fmgr.h"
PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_numbers);

Datum add_numbers(PG_FUNCTION_ARGS) {
    int32 arg1 = PG_GETARG_INT32(0);
    int32 arg2 = PG_GETARG_INT32(1);
    PG_RETURN_INT32(arg1 + arg2);
}

编译与安装扩展：使用gcc编译C代码为共享库（.so文件），并将库文件复制到PostgreSQL的lib目录：

gcc -fpic -I/usr/include/postgresql/server/ -shared -o my_extension.so my_extension/src/my_extension.c
sudo cp my_extension.so /usr/lib/postgresql/15/lib/

加载扩展：在psql中创建扩展，即可使用自定义函数：

CREATE EXTENSION my_extension;
SELECT add_numbers(1, 2);  -- 返回3
```。

0 赞

0 踩