PostgreSQL

安装

docker-compose安装

cat <<EOF > docker-compose.yaml
version: '3.8'
services:
  db:
    image: harbor.qihoo.net/finloan-infra-library/postgres:15-alpine
    restart: always
    environment:
      POSTGRES_USER: postgres
      # The password for the default postgres user.
      POSTGRES_PASSWORD: difyai123456
      # The name of the default postgres database.
      POSTGRES_DB: dify
      # postgres data directory
      PGDATA: /var/lib/postgresql/data/pgdata
	shm_size: '16gb'  # shared memory
	command: postgres -c 'max_connections=10000'
    volumes:
      - /data/postgre:/var/lib/postgresql/data
    # uncomment to expose db(postgresql) port to host
    ports:
      - "5432:5432"
    healthcheck:
      test: [ "CMD", "pg_isready" ]
      interval: 1s
      timeout: 3s
      retries: 30
EOF

docker compose up -d

运维

修改最大连接数

pg默认最大连接数是100，如果你的pod已经启动过了，需要去修改配置文件才能生效；

psql
# 不存在用户名对应的数据库时，一定要指定数据库
psql -h <host> -p <port> -U <username> -d <database> 
# 查看配置文件所在路径: /var/lib/postgresql/data/pgdata/postgresql.conf
show config_file;
exit

# 修改配置文件中的最大连接数
sed -i 's/^max_connections[ ]*=.*.*/max_connections = 10000/' /var/lib/postgresql/data/pgdata/postgresql.conf
# 重启容器
docker compose restart

# 查看活动的连接
select * from pg_stat_activity;
# 查看最大连接数
show max_connections;

修改XA事务数

pg默认XA事务数是0，即不支持两阶段提交事务。

sed -i -E 's/^[#]? *max_prepared_transactions[ ]*=.*.*/max_prepared_transactions = 200/' /var/lib/postgresql/data/pgdata/postgresql.conf
# 重启容器
docker compose restart

# 查看支持的最大XA事务数
show max_prepared_transactions;

权限管理

# 查看当前用户
select current_user;
select * from pg_user;
select * from pg_database;

create database db_1;
CREATE USER app_user WITH LOGIN PASSWORD '123456';
ALTER DATABASE db_1 OWNER TO app_user;

# 也可以直接创建数据库的时候指定owner
create database db_2 OWNER app_user;

# 建表测试下权限
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 结束db_1数据库的所有会话
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT pid FROM pg_stat_activity WHERE datname = 'db_1') LOOP
        EXECUTE 'SELECT pg_terminate_backend(' || r.pid || ')';
    END LOOP;
END $$;

# 删除db_1数据库
drop database db_1;

# 删除用户
drop user app_user;

表权限

# 查看表的属主
\dt
SELECT schemaname, tablename, tableowner FROM pg_tables WHERE tablename = '<table_name>';

# 修改表的属主
ALTER TABLE table_name OWNER TO new_owner;

常用命令

# 连接当前库
psql
# 连接指定库
psql -h localhost -p 5432 -U postgres -d dify

# 列出所有数据库
\l
# 当前链接的库
select current_database();
# 连接到其他数据库
\c otherdb
# 查看数据库的模式
\dn

# 列出所有的表
\dt
# 查看特定的表结果
\d tablename
\d+ tablename

# 显示当前连接信息
\conninfo
# 显示命令历史
\s
# 重复执行上一条命令
\g
# 保存查询结果到文件
\o filename
select * from mytable;
\o
# 执行文件中的sql
\i filename.sql