首页

pgAdmin 的使用

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

参考文献: Regina Obe Leo Hsu 著, 丁奇鹏 译《PostgreSQL即学即用》

目录

简介

pgAdmin简介

pgAdmin 是 PostgreSQL 官方的图形化管理工具, 目前的版本是 pgAdmin 4.

启动时的图片

设置中文界面

设置中文界面

点击 Configure pgAdmin,

将语言改为 Chinese (Simplified), 并点击保存按钮.

点击 Refresh 按钮.

刷新后就可看到中文界面了.

注册服务器

注册服务器

若要使用其他角色登录服务器, 我们首先需要在 pgAdmin 中注册.

右键 Servers, 点击【注册】==>【服务器】, 弹出下面的“注册-服务器”对话框.

填写服务器的名称, 例如这里是供角色 haife 登录的, 故名称取为 PostgreSQL_for_haife.

点击“连接”选项页，填写主机名称/地址、端口、要连接的数据库、用户名、密码等信息。然后点击【保存】。

此时在对象浏览器中就可以看到此服务器了.

对象浏览器

对象浏览器

点击左侧对象浏览器中的 Servers 可以看到所有的数据库. 这里可能需要输入 postgres 的密码. 如果保存密码, 则下次就不用再输入了.

以数据库 postgresql_book 为例, 这里有三个架构. 每个架构内有很多对象, 例如下面展开的是 public 架构下的对象.

查询工具

查询工具

按 Alt+Shift+Q 可打开查询工具.

输入 SELECT * FROM test_scores;, 按 F5 或上面的图标执行查询. 下图是查询结果.

数据库的创建、删除、备份和还原

数据库的创建、删除、备份和还原

假设已经在 PostgreSQL 中导入了 testbar 数据库的很多数据. 为安全起见, 首先将其备份. 备份使用 pg_dump 命令.

pg_dump -U haife -d testbar -f testbar_20251013_GBK.sql --encoding=GBK --verbose

PS D:\work\cs\Database\postgres\testbar> pg_dump -U haife -d testbar -f testbar_20251013_GBK.sql --encoding=GBK --verbose                                                                                                                       pg_dump: 最后的内置 OID 是 16383
pg_dump: 读扩展
pg_dump: 识别扩展成员
pg_dump: 读取模式
pg_dump: 读取用户定义表
pg_dump: 读取用户定义函数
pg_dump: 读取用户定义类型
pg_dump: 读取过程语言
pg_dump: 读取用户定义聚集函数
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的访问方法
pg_dump: 读取用户定义操作符集
pg_dump: 读取用户定义操作符
pg_dump: 读取用户定义的文本搜索解析器
pg_dump: 读取用户定义的文本搜索模板
pg_dump: 读取用户定义的文本搜索字典
pg_dump: 读取用户定义的文本搜索配置
pg_dump: 读取用户定义外部数据封装器
pg_dump: 读取用户定义的外部服务器
pg_dump: 正在读取缺省权限
pg_dump: 读取用户定义的校对函数
pg_dump: 读取用户定义的字符集转换
pg_dump: 读取类型转换
pg_dump: 读取转换
pg_dump: 读取表继承信息
pg_dump: 读取事件触发器
pg_dump: 查找扩展表
pg_dump: 正在查找关系继承
pg_dump: 正在读取感兴趣表的列信息
pg_dump: 在子表里标记继承字段
pg_dump: reading partitioning data
pg_dump: 读取索引
pg_dump: 在分区表中标记索引
pg_dump: 读取扩展统计信息
pg_dump: 读取约束
pg_dump: 读取触发器
pg_dump: 读取重写规则
pg_dump: 读取策略
pg_dump: reading row-level security policies
pg_dump: 读取发布
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: 读取订阅
pg_dump: reading subscription membership of tables
pg_dump: 正在读取大对象
pg_dump: 读取从属数据
pg_dump: 正在保存encoding = GBK
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: 创建TABLE "public.bars"
pg_dump: 创建TABLE "public.beers"
pg_dump: 创建TABLE "public.drinkers"
pg_dump: 创建TABLE "public.frequents"
pg_dump: 创建TABLE "public.likes"
pg_dump: 创建TABLE "public.sells"
pg_dump: 为表"public.bars"处理数据
pg_dump: 正在转储表"public.bars"的内容
pg_dump: 为表"public.beers"处理数据
pg_dump: 正在转储表"public.beers"的内容
pg_dump: 为表"public.drinkers"处理数据
pg_dump: 正在转储表"public.drinkers"的内容
pg_dump: 为表"public.frequents"处理数据
pg_dump: 正在转储表"public.frequents"的内容
pg_dump: 为表"public.likes"处理数据
pg_dump: 正在转储表"public.likes"的内容
pg_dump: 为表"public.sells"处理数据
pg_dump: 正在转储表"public.sells"的内容
pg_dump: 创建CONSTRAINT "public.bars bars_pkey"
pg_dump: 创建CONSTRAINT "public.beers beers_name_key"
pg_dump: 创建CONSTRAINT "public.drinkers drinkers_pkey"
pg_dump: 创建CONSTRAINT "public.frequents frequents_pkey"
pg_dump: 创建CONSTRAINT "public.likes likes_pkey"
pg_dump: 创建CONSTRAINT "public.sells sells_pkey"

查看此文件, 如果含有乱码, 则更换一下编码. 或者使用下面的命令.

pg_dump -U haife -d testbar > testbar_20251013.sql --encoding=GBK

删除 testbar 数据库

点击 testbar 数据库, 按 Alt+Shift+D 或右键点击删除.

在弹出的确认对话框中点击【删除】.

创建 testbar 数据库

点击对象浏览器中的 PostgreSQL 17 或数据库, 右键==>点击【创建】==>数据库. 若点击的是“数据库”, 则可按快捷键 Alt+Shift+N, 出现下面的对话框.

注意, 若点击的是 PostgreSQL 17, 按 Alt+Shift+N 则会打开注册服务器窗口.

填入要建的数据库名 testbar, 选择所有者为 haife.

在“定义”选项页, 编码选择 UTF8. 点击 SQL 选项页, 可以看到完整的 CREATE DATABASE 语句.

检查无误后点击【保存】.

还原 testbar 数据库中的数据

右键点击 testbar 数据库, 点击“还原中...”.

出现如下对话框

格式选择“全文本”, 文件名一栏填写刚才备份文件的绝对路径名.

然后点击【还原】。

使用 pgAdmin 备份 testbar 数据库

右键点击 testbar 数据库, 点击【备份】, 出现Backup对话框.

填写要备份的文件名，选择文件的编码以及角色。

按【备份】即启动备份程序, 右下角会有进程开始和结束的提示。

查看备份文件的内容

设置权限

设置权限

点击菜单栏的【工具】，点击【授权向导】

当然, 这里受让人可以换成其他的.

点击【完成】.

测试授权是否成功

使用刚才所选受让人的账号登录系统, 测试是否能查询 bars 表中的数据.

haifeng@localhost:5432 testbar# select * from beers;
错误:  对表 beers 权限不够
时间：32.601 ms
haifeng@localhost:5432 testbar# select * from bars;
       name       |          addr           | license
------------------+-------------------------+---------
 3DArtBar         | 上海虹梅路3338弄9－11号 | BHXGZX
 HardRock         | 上海政通路315号         | XJ8QGF
 Westside         | 上海衡山路237号         | EDS9HF
 木板房啤酒吧     | 上海子长路350号         | YJVI9A
 宝莱纳餐厅       | 上海徐汇区汾阳路150号   | SKAPXJ
 苏荷酒吧         | 扬州1912                | LYMTD5
 扬州老啤酒厂酒吧 | 广陵区南通东路128号     | 8NZBSG
(7 行记录)


时间：2.197 ms
haifeng@localhost:5432 testbar#

或者, 注册一个由 haifeng 登录的服务器. 然后登录后执行相关查询以验证.

跨数据库查询

跨数据库查询

PostgreSQL 中的数据库彼此是独立的, 因此默认不支持跨数据库查询. 例如, 在当前用户 haifeng 连接数据库 testbar 后, 尝试查询 postgresql_book 数据库下 public 架构中的 test_scores 表中的数据. 结果返回错误: “未实现跨数据库关联”。

在目标数据库中安装dblink扩展

在目标数据库（这里是 testbar）中启用 dblink 扩展. 当然这需要超级用户才能安装扩展.

haifeng@127.0.0.1:5432 testbar# CREATE EXTENSION dblink;
错误:  创建扩展 "dblink" 权限不够
HINT:  只有超级用户能创建扩展.
Time: 19.755 ms
haifeng@127.0.0.1:5432 testbar#

使用 postgres 登录 testbar

我们的目标是使 postgres 用户在 testbar 数据库中启用 dblink 扩展, 以建立到另一个数据库的连接.

下图中注册的 PostgreSQL 17 服务器就是使用 postgres 账号登录的.

打开 psql 工具

这是 pgAdmin4 中 psql 的界面, 跟操作系统下 psql 启动的界面差不多. 但在这里使用 psql 执行数据导入会慢一点, 可以尝试导入 postgresql_book 数据库, 速度显著慢于在操作系统命令行界面下直接使用 psql 导入的速度.

（使用 postgres） 启用 dblink 扩展.

在源数据库中创建连接到目标数据库的链接

这里源数据库为 postgresql_book. 现在建立名为 conn_to_testbar 的到目标数据库 testbar 的连接.

SELECT dblink_connect('conn_to_testbar', 'host=localhost dbname=testbar user=haifeng password=34klq*');

授权给 haifeng

postgresql_book 数据库的所有者是 postgres, 用户haifeng 没有权限执行查询等操作.

将 postgresql_book 数据库中 public.test_scores 表的查询权限赋予 haifeng. 注意得切换到 postgres 用户下进行权限的赋予.

使用 haifeng 连接 testbar 数据库

现在使用 haifeng 这个角色执行跨数据库查询.

然后再执行跨数据库查询.

SELECT * FROM dblink('conn_to_testbar', 'SELECT student ,score FROM test_scores') As s(name varchar(100), score numeric(5,0));

End

Thanks very much!