Logo

菜单

修炼者
修炼者
发布于 2026-06-18 / 11 阅读
0
0

PostgreSQL

从源码构建并启动

下载、编译、安装

可以从如下URL，https://ftp.postgresql.org/pub/latest/下载最新的代码，或者切换到源文件目录下载指定版本代码。


# 下载指定版本源码
 wget https://ftp.postgresql.org/pub/latest/postgresql-18.4.tar.gz

# 更新源，导入密钥
wget -O - https://apt.llvm.org/llvm-snapshot.gpg.key | sudo apt-key add -
# 添加llvm18源
echo "deb http://apt.llvm.org/jammy/ llvm-toolchain-jammy-18 main" | sudo tee /etc/apt/sources.list.d/llvm18.list
sudo apt update
# 完整依赖
sudo apt install gcc make libreadline-dev zlib1g-dev flex bison libssl-dev llvm-18-dev clang-18 lld-18

# 解压并编译
tar zxvf postgresql-18.4.tar.gz
cd postgresql-18.4/
# 带调试符号编译，插件开发专用
./configure --prefix=/home/gauss/soft/pg18 CFLAGS="-g -O0" --with-llvm
make -j$(nproc)

# 安装
sudo make install

配置并启动


cd /home/gauss/soft/pg18
mkdir data
chmod 700 data
# 使用gauss作为postgresql用户，使用data作为数据库目录
$ tree -L 1
.
├── bin
├── data
├── include
├── lib
└── share

# 初始化数据库
/home/gauss/soft/pg18/bin/initdb -D /home/gauss/soft/pg18/data --encoding=UTF8 --locale=C.UTF-8

# 启动数据库

# 启动
/home/gauss/soft/pg18/bin/pg_ctl start -D /home/gauss/soft/pg18/data -l /home/gauss/soft/pg18/log/pg.log
# 查看状态
/home/gauss/soft/pg18/bin/pg_ctl status -D /home/gauss/soft/pg18/data
# 停止
/home/gauss/soft/pg18/bin/pg_ctl stop -D /home/gauss/soft/pg18/data -m fast

测试链接


psql -d postgres

# 进入后，可修改密码
ALTER USER gauss WITH PASSWORD '123456';
# 创建数据库 
CREATE DATABASE gauss OWNER gauss;

配置为服务

gauss@gauss-pc-win:~$ cat /etc/systemd/system/pg18.service
[Unit]
Description=PostgreSQL 18 (run as gauss)
After=network.target

[Service]
Type=forking
User=gauss
Group=gauss
ExecStart=/home/gauss/soft/pg18/bin/pg_ctl start -D /home/gauss/soft/pg18/data -l /home/gauss/soft/pg18/log/pg18.log
ExecStop=/home/gauss/soft/pg18/bin/pg_ctl stop -D /home/gauss/soft/pg18/data -m fast
ExecReload=/home/gauss/soft/pg18/bin/pg_ctl reload -D /home/gauss/soft/pg18/data
Restart=on-failure
RestartSec=5
LimitNOFILE=65535
Environment=PATH=/home/gauss/soft/pg18/bin:$PATH
Environment=LD_LIBRARY_PATH=/home/gauss/soft/pg18/lib:$LD_LIBRARY_PATH
Environment=PGDATA=/home/gauss/soft/pg18/data

[Install]
WantedBy=multi-user.target

# 启动服务
sudo systemctl daemon-reload
sudo systemctl enable pg18
sudo systemctl start pg18
sudo systemctl status pg18

令远程访问


gauss@gauss-pc-win:~/soft/pg18/data$ vi postgresql.conf
listen_addresses = '*'

PSQL

PSQL是PostgreSQL数据库的交互式控制台工具

查看系统版本


gauss=# SELECT version();
 PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit

查看所有数据库

\list

设计DDL

可使用Enterprise Architecture设计实体关系，生成的DDL可以借助LLM优化。

数据库

创建

-- PostgreSQL 18.4：创建数据库，使用 UTF8 编码
-- 该环境当前可用的区域设置为 C.UTF-8，因此直接使用它可避免 locale 报错

CREATE DATABASE db_refining_realm
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'C.UTF-8'
       LC_CTYPE = 'C.UTF-8'
       TEMPLATE = template0;

CREATE DATABASE db_refining_realm

# 解释如下：
创建一个名叫 db_refining_realm 的数据库。
WITH ENCODING = 'UTF8'

数据库使用 UTF-8 字符编码。
这样可以正常存储中文、英文、符号等字符。
LC_COLLATE = 'C.UTF-8'

定义字符串排序规则。
这里表示按 Unicode 规则进行比较，比较稳妥，也适合多语言环境。
LC_CTYPE = 'C.UTF-8'

定义字符分类规则。
例如判断某个字符是不是字母、数字、中文字符等。
TEMPLATE = template0

以 PostgreSQL 自带的基础模板 template0 来创建新数据库。
这样新库更干净，不会继承别的数据库的额外设置。

切换

从数据库切换到另一个数据库

\c db_refining_realm

# 查看当前数据库名
SELECT current_database();

模式

将通用的实体放在public，其余建立专门的模式存放，便于名字空间管理。

创建模式

-- 创建业务schema，指定所有者
-- biz 商业
CREATE SCHEMA IF NOT EXISTS biz AUTHORIZATION gauss;
-- ext 扩展
CREATE SCHEMA IF NOT EXISTS ext AUTHORIZATION gauss;

-- hzc 汉字词
CREATE SCHEMA IF NOT EXISTS hzc AUTHORIZATION gauss;
-- eng 英语吧
CREATE SCHEMA IF NOT EXISTS eng AUTHORIZATION gauss;
-- spt 运动吧
CREATE SCHEMA IF NOT EXISTS spt AUTHORIZATION gauss;
-- muc 音乐吧
CREATE SCHEMA IF NOT EXISTS muc AUTHORIZATION gauss;

-- job 作业吧
CREATE SCHEMA IF NOT EXISTS job AUTHORIZATION gauss;
-- xlz 修炼吧
CREATE SCHEMA IF NOT EXISTS xlz AUTHORIZATION gauss;

模式搜索顺序

# 查看当前数据库的模式搜索顺序
SHOW search_path;

# 数据库角度，修改搜索顺序
-- 优先业务schema，再扩展，最后公共
ALTER DATABASE gauss SET search_path TO biz_user,ext,common,public;

-- 会话角度，临时修改
SET search_path = biz_user,ext,common,public;

-- 用户角度，登录生效
ALTER ROLE gauss SET search_path TO biz_user,ext,common,public;

表

这里基于PostgreSQL当前最佳实践，对一些规则进行约定。

唯一索引

CONSTRAINT uk_t_user_uid_user_source_id UNIQUE (f_uid, f_user_source_id)

public.t_user

存放公用的数据，例如用户、团队、关系。

/* ---------------------------------------------------- */
/*  各种支持来源的用户唯一id							      */
/*  gauss@2026-6-27，7457222@qq.com 				    */
/*  DBMS       : PostgreSQL 						    */
/* ---------------------------------------------------- */

/* Drop Tables */

DROP TABLE IF EXISTS t_user CASCADE;

/* Create Tables */

CREATE TABLE t_user
(
    f_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    f_uid varchar(128) NOT NULL,
    f_user_source_id integer NOT NULL,
    f_auth_code varchar(128) NOT NULL,
    f_name varchar(50),
    f_motto varchar(128),
    f_create_time timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_last_login_time timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_t_user_uid_user_source_id UNIQUE (f_uid, f_user_source_id)
);

DROP TRIGGER IF EXISTS trg_t_user_set_modify_time ON public.t_user;
CREATE TRIGGER trg_t_user_set_modify_time
BEFORE UPDATE ON public.t_user
FOR EACH ROW
EXECUTE FUNCTION public.set_modify_time();

CREATE INDEX ix_t_user_user_source_id ON t_user (f_user_source_id);

ALTER TABLE t_user ADD CONSTRAINT fk_t_user_t_user_source
    FOREIGN KEY (f_user_source_id) REFERENCES t_user_source (f_id) ON DELETE RESTRICT ON UPDATE NO ACTION;

角色与授权

角色

db_refining_realm=# CREATE ROLE anonymous NOLOGIN;
CREATE ROLE
db_refining_realm=# CREATE ROLE app_user NOLOGIN;
CREATE ROLE
db_refining_realm=# CREATE ROLE app_admin NOLOGIN;
CREATE ROLE
db_refining_realm=# CREATE ROLE postgraphile NOLOGIN;
CREATE ROLE
db_refining_realm=# grant app_user ,app_admin to postgraphile;
GRANT ROLE

授权

-- 仅授权 login 函数供匿名访问
GRANT EXECUTE ON FUNCTION public.login(INTEGER, TEXT, TEXT, BIGINT) TO anonymous;
GRANT USAGE ON SCHEMA public TO anonymous;

-- 授权 app_user 对当前 schema 中已有表的查询与更新权限
GRANT USAGE ON SCHEMA public TO app_user, app_admin;
GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;

-- 授权 app_admin 对当前 schema 中表、序列和函数的全部权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO app_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO app_admin;

函数

login

/**
 * 验证用户登录凭据并生成JWT,基于t_user、t_team_member表，具体如下：
 * 0. 检查ts时间戳与当前时间戳的差值应该在10s内，如果不是，应该抛异常
 * 1. 从t_user按照login_source和login_name查找用户，如果找到继续，否则抛异常
 * 2. 如找到的记录t_user.f_auth_code不为空且内容以pwd:打头，则校验密码是否匹配，如果匹配继续，否则抛异常
 * 3. 查找t_team_member表，判断用户是否属于1号团队并有1号角色，如果有就是管理员，否则就是普通用户
 * 4. 生成JWT，包含role/exp/用户标识，返回给客户端
 */
CREATE OR REPLACE FUNCTION public.login(
  -- 用户来源
  login_source INTEGER,
  -- 用户的登录名
  login_name TEXT,
  -- 用户的登录密码，客户端传入明文密码摘要，算法为SHA-256(SHA-256('jygt_202501131242_'+password)+ts)，后端按 pwd: 前缀规则校验
  login_pwd TEXT,
  -- ts 秒级别的时间戳
  ts BIGINT
) RETURNS JSONB AS $$
DECLARE
  v_user public.t_user;
  v_is_admin BOOLEAN;
  v_role TEXT;
  v_jwt TEXT;
  v_auth_code TEXT;
  v_login_pwd_wanted TEXT;
BEGIN
  IF ts IS NULL OR abs(extract(EPOCH FROM clock_timestamp())::BIGINT - ts) > 100 THEN
    RAISE EXCEPTION 'Timestamp is invalid' USING ERRCODE = '10001';
  END IF;

  SELECT * INTO v_user
  FROM public.t_user AS u
  WHERE u.f_user_source_id = login_source
    AND u.f_uid = login_name
  LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Invalid user ' USING ERRCODE = '10002';
  END IF;

  v_auth_code := trim(coalesce(v_user.f_auth_code, ''));
  IF v_auth_code <> '' AND v_auth_code LIKE 'pwd:%' THEN
    -- f_auth_code存放的是没有时间戳的摘要，需要与时间戳结合计算，获得当前正确的login_pwd，才能进行校验
    v_login_pwd_wanted := encode(digest(substring(v_auth_code FROM 5)||ts,'sha256'),'hex');
    IF login_pwd IS NULL OR login_pwd IS DISTINCT FROM v_login_pwd_wanted THEN
      RAISE EXCEPTION 'Invalid password, expected hash: %', v_login_pwd_wanted USING ERRCODE = '10003';
    END IF;
  END IF;

  SELECT EXISTS (
    SELECT 1
    FROM public.t_team_member AS tm
    WHERE tm.f_member_user_id = v_user.f_id
      AND tm.f_team_id = 1
      AND tm.f_role_id = 1
  ) INTO v_is_admin;

  v_role := CASE WHEN v_is_admin THEN 'app_admin' ELSE 'app_user' END;

  v_jwt := public.sign(
    json_build_object(
      'aud', 'postgraphile',
      'role', v_role,
      'id', v_user.f_id,
      'exp', extract(EPOCH FROM NOW() + INTERVAL '7 days')
    )::JSON,
    current_setting('jwt.secret')
  );

  RETURN jsonb_build_object(
    'token', v_jwt,
    'user', jsonb_build_object(
      'id', v_user.f_id,
      'uid', v_user.f_uid,
      'name', v_user.f_name,
      'motto', v_user.f_motto,
      'role', v_role
    )
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

使用时可以借助pgcrypto扩展，获得验证

db_refining_realm=# select public.login(6,'7457222@qq.com','c82f394f942d6a1161c6088d13f758edf5699aed207751755aad7f425e7cca49',1783504261);                                                                                                                                                                                                    login

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
 {"user": {"id": 1, "uid": "7457222@qq.com", "name": "Gauss", "role": "app_admin", "motto": "人是逐渐变化的"}, "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhdWQiIDogInBvc3Rnc
mFwaGlsZSIsICJyb2xlIiA6ICJhcHBfYWRtaW4iLCAiaWQiIDogMSwgImV4cCIgOiAxNzg0MTA5MTE1LjI2MjE1MX0.hFM0XwSLuxJQwUcX-b3ZzoVpEb2kDiY77IKaKZdklLM"}
(1 row)

扩展

pgcrypto

基于源码构建PostgreSQL需要增加openssl的支持，单线程编译可避免冲突。contrib下可看到pgcrypt相关的源码。

db_refining_realm=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION


SELECT replace(
  encode(digest('jygt_202501131242_test', 'sha256'), 'hex'),
  '0', 'A'
);
                             replace
------------------------------------------------------------------
 A2Abeec9aaf931e3c3A1fA661342A61a1e7f27A3d7d228adA789aA3865bc4dce
(1 row)

SELECT encode(digest('jygt_202501131242_test', 'sha256'), 'hex');

#  一个典型的用于用户登录验证的摘要算法
db_refining_realm=# select  extract(EPOCH from clock_timestamp())::BIGINT  as ts,encode(digest(substring(f_auth_code FROM 5)||extract(EPOCH from clock_timestamp())::BIGINT,'sha256'),'hex') from t_user where f_id =1;
     ts     |                              encode
------------+------------------------------------------------------------------
 1783504313 | 2bbc729be6d86ceba7e7839f321034eb9d888d17c8556eb700012e95b85bcb45
(1 row)

pgjwt

pgjwt需要下载源码

# 下载pgjwt源码
git clone https://github.com/michelp/pgjwt.git
make install

db_refining_realm=# CREATE EXTENSION IF NOT EXISTS pgjwt;
CREATE EXTENSION

db_refining_realm=# SELECT
  e.extname AS ext_name,
  np.nspname AS func_schema,
  p.proname AS func_name,
  pg_get_function_arguments(p.oid) AS args
FROM pg_extension e
JOIN pg_depend d ON d.refobjid = e.oid AND d.deptype = 'e'
JOIN pg_proc p ON p.oid = d.objid
JOIN pg_namespace np ON p.pronamespace = np.oid
WHERE e.extname = 'pgjwt';
 ext_name | func_schema |    func_name    |                              args
----------+-------------+-----------------+-----------------------------------------------------------------
 pgjwt    | public      | url_encode      | data bytea
 pgjwt    | public      | url_decode      | data text
 pgjwt    | public      | algorithm_sign  | signables text, secret text, algorithm text
 pgjwt    | public      | sign            | payload json, secret text, algorithm text DEFAULT 'HS256'::text
 pgjwt    | public      | verify          | token text, secret text, algorithm text DEFAULT 'HS256'::text
 pgjwt    | public      | try_cast_double | inp text
(6 rows)
# 生成token
db_refining_realm=# SELECT sign(
  '{"uid":10001,"iss":"backend"}'::json,
  '你的JWT密钥'::text,
  'HS256'::text
) AS token;
                                                          token
--------------------------------------------------------------------------------------------------------------------------
 eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1aWQiOjEwMDAxLCJpc3MiOiJiYWNrZW5kIn0.DegiZUqRcUa__cD-RtXywUsaWVI5OSzg0YEnaXwA0KY
(1 row)

# 验证token
db_refining_realm=# select verify('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1aWQiOjEwMDAxLCJpc3MiOiJiYWNrZW5kIn0.DegiZUqRcUa__cD-RtXywUsaWVI5OSzg0YEnaXwA0KY'::text,'你的JWT密钥'::text)
;
                                     verify
---------------------------------------------------------------------------------
 ("{""alg"":""HS256"",""typ"":""JWT""}","{""uid"":10001,""iss"":""backend""}",t)
(1 row)

Postgraphile

Postgraphile是一nodejs开源项目，可以基于postgresql的数据库结构直接生成graphql风格的web服务，实现数据库表、视图等的增删改查。但是5.0相对于4.0有太大改动，这里尝试5.0的订阅和过滤实在是难受

安装与调试


$ nvm list
->     v24.14.1
default -> lts/* (-> v24.14.1)

$ npm install -g  postgraphile

$ export GRAPHILE_ENV=development

$ postgraphile --version
5.0.3


$ postgraphile   --preset postgraphile/presets/amber   \
    -c "postgres://gauss:123456@localhost:5432/db_refining_realm" \
    --schema biz,xlz,hzc,eng,spt,muc,job,ext,public   \
    --watch   \
    --host 0.0.0.0   \
    --port 5000

增强过滤能力

通过增加插件

Flink

评论