PostgreSQL #

客户端软件 #

本地GUI:

HeidiSQL - Windows
Wine + HeidiSQL - MacOS

Web部署:

pgadmin4

资料 #

《PostgreSQL 内参：深入解析运行原理》 -《PostgreSQL 14 Internal》的翻译版。

连接管理 #

查看系统最大连接数:

1postgres=# show max_connections;
2 max_connections 
3-----------------
4 500
5(1 row)

max_connections是一个静态参数，在postgresql.conf中配置，只能在服务器启动时设置，修改配置需重启服务。

查看为超级用户保留的用户数:

1postgres=# show superuser_reserved_connections;
2 superuser_reserved_connections 
3--------------------------------
4 3
5(1 row)

查看当前正在使用的连接总数:

1select count(1) from pg_stat_activity;
2 count 
3-------
4    42
5(1 row)

pg_stat_activity是PostgreSQL的一个系统视图，用于监控数据库当前的活动连接和会话信息。它提供了非常详细的实时数据库活动信息。

1-- 查看所有活跃查询
2SELECT pid, usename, query FROM pg_stat_activity WHERE state = 'active';
3
4-- 查看长时间运行的查询
5SELECT pid, now() - query_start as duration, query FROM pg_stat_activity  WHERE state != 'idle';
6
7-- 终止特定查询（需要权限）
8SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 'PID';

pg_terminate_backend是PostgreSQL中用于强制终止数据库连接的函数。它会向指定的数据库后端进程发送终止信号。

备份和还原 #

官方文档Backup and Restore

备份PostgreSQL有三种不同的方法：

SQL Dump
文件系统级备份 - (限制:数据库服务器必须关闭才能获得可用的备份)
持续归档和时间点还原(PITR)

基于SQL Dump的备份和还原 #

备份和还原整个PostgreSQL实例 #

备份所有:

1pg_dumpall -f dump.sql

还原:

1psql < dump.sql

pg_dumpall导出的是sql文本格式
pg_dumpall不支持导出二进制格式, 只能导出为纯文本格式。 pg_dumpall主要用于导出整个PostgreSQL实例的所有数据库、全局对象（如角色和权限）以及配置信息。如果要数据库数据为二进制格式，需要使用pg_dump来逐个导出每个数据库，pg_dump 支持多种格式（包括二进制格式），而pg_dumpall适用于导出整个PostgreSQL实例的所有数据库及其元数据。

备份和还原单个数据库 #

导出某个数据库:

1pg_dump -h <远程服务器IP或域名> -p <端口号> -U <用户名> \
2    -d <数据库名> -F c -b -v -f <导出文件路径>

几个关键参数的解释:

1-F, --format=c|d|t|p  output file format (custom, directory, tar,
2                        plain text (default))
3-b, --large-objects   include large objects in dump
4-v, --verbose         verbose mode
5-O, --no-owner        skip restoration of object ownership in
6                      ALTER OWNER commands to set ownership

示例：

1pg_dump -h 192.168.100.202 -p 5432 -U foo \
2    -d foodb --no-owner -F c -b -v -f foodb.dump

还原单个数据库的示例：

创建数据库和用户：

1psql -h 127.0.0.1 -p 5432 -U postgres
2
3CREATE DATABASE "foodb";
4CREATE USER foodb_user WITH PASSWORD 'xxx';
5GRANT ALL PRIVILEGES ON DATABASE foodb TO foodb_user;
6
7\c "foodb" postgres;
8GRANT ALL PRIVILEGES ON SCHEMA public TO foodb_user;

还原数据库

1pg_restore -d foodb --no-owner --role=foodb_user -v foodb.dump

还原时在创建某些对象时需要超级权限?
可在还原前将用户设置为SUPERUSER，还原后再去掉。
1ALTER ROLE foodb_user WITH SUPERUSER;
2ALTER ROLE foodb_user WITH NOSUPERUSER;
例如，只有SUPERUSER才能创建扩展，例如使用pgvector。如果在还原时使用普通用户，会抱下面的错误:
1pg_restore: from TOC entry 2; 3079 21011 EXTENSION vector (no owner)
2pg_restore: error: could not execute query: ERROR:  permission denied to create extension "vector"
3HINT:  Must be superuser to create this extension.
4Command was: CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

持续归档和时间点还原(PITR) #

PostgreSQL的增量备份和时间点还原(PITR)

TODO待整理…