青蛙小白
目录

PostgreSQL

客户端软件

本地GUI:

  • HeidiSQL - Windows
  • Wine + HeidiSQL - MacOS

Web部署:

资料

连接管理

查看系统最大连接数:

postgres=# show max_connections;
 max_connections 
-----------------
 500
(1 row)

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

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

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

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

select count(1) from pg_stat_activity;
 count 
-------
    42
(1 row)

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

-- 查看所有活跃查询
SELECT pid, usename, query FROM pg_stat_activity WHERE state = 'active';

-- 查看长时间运行的查询
SELECT pid, now() - query_start as duration, query FROM pg_stat_activity  WHERE state != 'idle';

-- 终止特定查询（需要权限）
SELECT 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实例

备份所有:

pg_dumpall -f dump.sql

还原:

psql < dump.sql

pg_dumpall导出的是sql文本格式

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

备份和还原单个数据库

导出某个数据库:

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

几个关键参数的解释:

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

示例：

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

还原单个数据库的示例：

  1. 创建数据库和用户：

    psql -h 127.0.0.1 -p 5432 -U postgres

CREATE DATABASE "foodb";
CREATE USER foodb_user WITH PASSWORD 'xxx';
GRANT ALL PRIVILEGES ON DATABASE foodb TO foodb_user;

\c "foodb" postgres;
GRANT ALL PRIVILEGES ON SCHEMA public TO foodb_user;

  2. 还原数据库

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

还原时在创建某些对象时需要超级权限?

可在还原前将用户设置为SUPERUSER，还原后再去掉。

ALTER ROLE foodb_user WITH SUPERUSER;
ALTER ROLE foodb_user WITH NOSUPERUSER;

例如，只有SUPERUSER才能创建扩展，例如使用pgvector。如果在还原时使用普通用户，会抱下面的错误:

pg_restore: from TOC entry 2; 3079 21011 EXTENSION vector (no owner)
pg_restore: error: could not execute query: ERROR:  permission denied to create extension "vector"
HINT:  Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA public;

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

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

TODO待整理…

Archives

评论