#数据存储/PGSQL

1 概念

1.1 PGSQL 与 MySQL

  • 在开源方面，PostgreSQL 是一个完全开源的数据库管理系统，MySQL 分为两个版本：开源版本和商业版本。
  • 数据类型方面，PostgreSQL 提供了丰富的内置数据类型，包括数组、JSON、HSTORE（键值对）等。
  • PostgreSQL 对 SQL 标准的支持较好，支持更多高级 SQL 功能。
  • 性能方面，PostgreSQL 支持表分区、并行查询和内置的复制功能，使其适用于大规模应用。

1.2 schema

在 PostgreSQL 中，schema 是一个命名空间，它可以包含数据库对象的名称，如表、视图、索引、数据类型、函数以及运算符。在一个数据库中可以存在多个 schema，它们都有各自的命名空间。这使得多个用户可以在同一个数据库中使用相同的表名，因为它们在不同的 schema 中。

相关 SQL 命令：

  • CREATE SCHEMA：创建一个新的 schema，
  • DROP SCHEMA：删除一个 schema
  • ALTER SCHEMA：更改一个已存在的 schema 的属性

2 建立服务器环境

1
2
3
$ docker pull postgres:11.0
$ mkdir /exports/project/pv/data/ -p
$ docker run --rm --name pv_postgres -v /exports/project/pv/data/:/var/lib/postgresql/data -e POSTGRES_PASSWORD=123456 -p 5432:5432 -d postgres:11.0

此后即可操作：

  • 使用 Navicat 连接；
  • 创建数据库（注意：字符集按 sql 中的设置）
  • 创建 schema
  • 用 sql 导入数据

3 用命令行连接服务

1
2
3
$ psql -h ip -p 5432 -U username
或
$ psql -h ip -p 5432 -U username -d 库名

4 备份/恢复 pgsql 数据库

4.1 备份库

1
2
3
$ pg_dump -U {db_user} -h {db_host} -p {db_port} {db_name} > {backup_file}
如：
$ pg_dump -U postgres -h tencent.xyan666.com -p 5433 knowjourney > data_240703.sql

4.2 恢复库

1
2
> createdb -U postgres knowjourney_bak
> psql -U postgres -d knowjourney_bak -f /exports/knowjourney/data/bak/data_240703.sql

4.3 备份表

1
$ pg_dump -U postgres -h tencent.xyan666.com -p 5433 -d knowjourney -t public.store_record -f store_record_240716.sql

4.4 恢复表

1
$ psql -U postgres -h tencent.xyan666.com -p 5433 -d knowjourney -f store_record_240716.sql

5 操作数据库

5.1.1 查看数据库

1
\l

5.1.2 新建数据库

1
CREATE DATABASE 库名;

注意这里有 ';' 则执行之前所有命令,否则不执行

5.1.3 连接数据库

1
\c 数据库名;

5.1.4 查看当前库中的数据表

1
\dt

5.1.5 查看表结构

1
\d 表名;

实在找不到建表语句，可以把表结构贴到 chatGPT，让他写对应的建表语句

5.1.6 表改名

1
ALTER TABLE current_table_name RENAME TO new_table_name;

5.1.7 库改名

1
ALTER DATABASE current_db_name RENAME TO new_db_name;

5.1.8 退出

1
\q

5.1.9 查看 SCHEMA 命名空间

1
\dn

5.1.10 查看所有表

即使使用最高权限的 postgres 用户，命令行连接也可能看不到某些 schema 对应的表，使用以下命令可以看到命名空间中的表。

1
select * from pg_tables;

5.1.11 访问表

1
select * from SCHEMA名.表名;

5.1.12 操作 json 字段中的内容

1
2
3
UPDATE store_translate
SET word = info ->> 'regular_word'
WHERE info ? 'regular_word';

6 使用 sqlalchemy 连接 pgsql

6.1 安装

1
2
$ pip install sqlalchemy
$ pip install psycopg2

6.2 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

db_url = "postgresql://postgres:123456@192.168.10.106:5432/data"
engine = create_engine(db_url)
Session = sessionmaker(bind=engine)
session = Session()

result = session.execute(text("select * from pg_type;"))
for row in result:
    print(row)
session.commit()
session.close()
engine.dispose()

7 注意事项

  • where 中注意用单引号
  • 设置搜索路径：SET search_path TO data;（设定命名空间）
  • 表名：注意大小写，不加引号直接转成小写，对于大写表名，需要加引号
  • 在 pgvector 中，如果要使用 vector，请进入数据库，设置 CREATE EXTENSION vector;

8 参考

postgresql数据库中多个Schemas互相访问

PostgreSQL命令行工具psql常用命令

psql基本命令