学习使用PG经历之数据表迁移(2)

数据的迁移在我们的开发中是必须的，我们开发测试在本地测试数据库中，但是正式上线我们要把表、序列，视图等要复杂到正式数据库中，这其中存在第一次完全初始化，还有后面我们新增一些对象，那么我们怎么把测试数据的表和数据复制到正式数据库呢？

先学会备份和恢复相关的两个命令

======================================================================

可以把它们理解成一对工具：pgdump 负责把数据库“导出成文件”，pgrestore 负责把这个文件“恢复到另一个数据库”。

pg\_dump：PostgreSQL 自带的逻辑备份工具，可以导出整库、某个 Schema、某几张表，也可以只导结构或只导数据。
pg\restore：专门恢复 pgdump 生成的非纯文本格式备份文件，比如自定义格式 -Fc 或目录格式 -Fd。
如果 pgdump 导出的是纯文本 SQL（-Fp 或不写 -F），恢复时不用 pgrestore，直接用 psql -f 执行即可。

常用格式

参数	作用
`-U 用户名`	连接数据库的用户
`-h 主机`	数据库主机，本地可省略
`-p 端口`	端口，默认 5432
`-d 数据库名`	要导出或导入的目标库
`-F c`	自定义压缩格式，推荐配合 `pg_restore`
`-F p`	纯文本 SQL，可用 `psql -f` 恢复
`-t 表名`	只导出指定表
`-n 模式名`	只导出指定 Schema
`-s`	只导结构
`-a`	只导数据
`-O`	不输出 OWNER，避免所有者问题
`-x`	不输出权限 GRANT/REVOKE
`-f 文件`	导出到指定文件

1. 导出整库

自定义格式，适合后面用 pg_restore：

pg_dump -U postgres -d test_db -F c -f test_db.dump

如果希望正式库导入后不受测试库 OWNER 影响：

pg_dump -U postgres -d test_db -F c -O -x -f test_db.dump

2. 只导出指定表

pg_dump -U postgres -d test_db -t public.test -F c -f test.dump

导出多张表可以重复 -t：

pg_dump -U postgres -d test_db -t public.users -t public.orders -F c -f tables.dump

3. 只导结构或只导数据

只导结构：

pg_dump -U postgres -d test_db -s -F c -f test_schema.dump

只导数据：

pg_dump -U postgres -d test_db -a -F c -f test_data.dump

4. 用 pg\_restore 恢复

先确保目标库已经存在，例如：

CREATE DATABASE prod_db;

然后恢复：

pg_restore -U postgres -d prod_db test_db.dump

如果希望恢复时不恢复原库的所有者和权限：

pg_restore -U postgres -d prod_db --no-owner --no-acl test_db.dump

如果只想恢复某张表：

pg_restore -U postgres -d prod_db -t public.test test_db.dump

5. 如果导出的是纯文本 SQL

pg_dump -U postgres -d test_db -F p -f test_db.sql

恢复时不用 pg_restore：

psql -U postgres -d prod_db -f test_db.sql

你这种“测试库迁正式库”的推荐写法

pg_dump -U postgres -d test_db -n public -O -x -F c -f public.dump

pg_restore -U prod_owner -d prod_db --no-owner --no-acl public.dump

这样导出的文件不会携带测试库的 OWNER 和权限；导入时用正式库账号执行，表的所有者就会变成正式库用户。

一个小提醒

pgrestore 只能恢复 pgdump 用 非纯文本格式 生成的文件，比如 -Fc、-Fd、-Ft。
如果你导出时没有加 -F，默认是纯文本 SQL，这时应该用：

psql -U postgres -d prod_db -f backup.sql

而不是 pg_restore。

需求：我现在有两台服务器，一个是本地，一个是172.26.1.99上，都有pg实例，172.26.1.99 上的数据库名称为aikb，本地的为pitlog，我现在需求是把172.26.1.99上的aikb下的东西复制到本地的pitlog数据库上，两台服务器的用户名不一样权限不一样，我怎么处理比较好。

==========================================================================================

两台服务器用户名、权限不一样时，处理这类迁移的核心原则是：导出时不绑定源端用户，导入时用本地目标用户创建对象，最后再把权限补成你本地需要的样子。 所以推荐用 pgdump + pgrestore，并加上 --no-owner --no-privileges。

推荐方案：pg\dump + pg\restore

假设：

远程库：172.26.1.99，数据库 aikb，用户 remote_user
本地库：localhost，数据库 pitlog，用户 local_user

在本地服务器执行：

# 1. 从远程导出，不保留 owner 和权限
pg_dump \
  -h 172.26.1.99 \
  -p 5432 \
  -U remote_user \
  -d aikb \
  -Fc \
  -b \
  -v \
  --no-owner \
  --no-privileges \
  -f aikb.dump

# 2. 导入到本地 pitlog，所有对象归属 local_user
pg_restore \
  -h 127.0.0.1 \
  -p 5432 \
  -U local_user \
  -d pitlog \
  -v \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  aikb.dump

这里的关键参数：

参数	作用
`--no-owner`	不恢复源库对象归属，避免远程用户不存在导致报错
`--no-privileges`	不恢复源库 GRANT/REVOKE，避免权限不一致
`-Fc`	自定义格式，支持 `pg_restore` 并行恢复
`--clean --if-exists`	导入前清理已有对象，适合覆盖导入

恢复完成后，所有表、序列、视图等对象会归属于当前连接用户 local_user。

如果本地 local\_user 权限不够

如果 local_user 不是超级用户，但又有权限写 pitlog，可以这样：

pg_restore \
  -h 127.0.0.1 \
  -p 5432 \
  -U local_user \
  -d pitlog \
  -v \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  aikb.dump

如果仍然报权限错误，可以临时换成本地超级用户执行恢复：

pg_restore \
  -h 127.0.0.1 \
  -p 5432 \
  -U postgres \
  -d pitlog \
  -v \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  aikb.dump

恢复完后再把所有权交给本地业务用户：

REASSIGN OWNED BY postgres TO local_user;

注意：REASSIGN OWNED 只对当前数据库生效，所以要先 \c pitlog 再执行。

导入后补权限

如果本地应用还需要读写权限，可以补一下：

GRANT ALL ON SCHEMA public TO local_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO local_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO local_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO local_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO local_user;

网络和认证怎么处理

如果本地能直接连 172.26.1.99:5432，可以直接用上面的命令。

密码可以用环境变量传，例如：

export PGPASSWORD='remote_password'

或者配置 ~/.pgpass，避免明文写在命令里。

如果本地连不上远程 PG，可以先在远程服务器导出：

pg_dump -h 127.0.0.1 -p 5432 -U remote_user -d aikb -Fc -b -v \
  --no-owner --no-privileges -f /tmp/aikb.dump

然后把 aikb.dump 传到本地服务器，再在本地执行 pg_restore。

什么时候不建议用这个方案

如果数据量很大、要求不停机，可以考虑逻辑复制，但需要源库开启 wal_level=logical，改动更大。
如果只是临时同步几张表，也可以用 postgresfdw 或 pgdump -t 指定表。

你可以先确认三件事：本地 pitlog 是否已经存在、local_user 是否有 CREATE/USAGE 权限、本地能否直接访问 172.26.1.99:5432。确认后我可以帮你把命令改成完全可直接执行的版本。

安装时存在Stack Builder，为何找不到地方？

===================================================================

Stack Builder：【建议保留】 这是一个辅助工具，相当于一个“软件商店”。安装完成后，你可以通过它方便地一键安装额外的扩展组件（比如处理地理信息的 PostGIS）或数据库连接驱动。如果你以后需要用到高级功能，它会非常有用

别担心，Stack Builder 并没有“丢失”，它只是没有自动弹出来，或者是没有添加到快捷方式里。

你可以通过以下几种方式轻松找到并启动它：

方法一：在安装目录中手动查找（最准确）

PostgreSQL 默认安装在 C:\Program Files 目录下。请按照以下步骤寻找：

打开资源管理器，进入 C:\Program Files\PostgreSQL\{你的版本号} （例如 15、16 或 17）。
进入其中的 bin 文件夹。
在里面往下翻找，你会发现一个名为 stackbuilder.exe 的应用程序。
双击它即可直接打开 Stack Builder。

为了方便以后使用，你可以右键点击这个 stackbuilder.exe -> “发送到” -> “桌面快捷方式”。

方法二：在 Windows 开始菜单中查找

点击屏幕左下角的 Windows 开始菜单（或按下键盘上的 Windows 徽标键）。
在搜索框中直接输入 Stack Builder。
如果搜索结果中出现了带有 PostgreSQL 图标的应用程序，直接点击打开即可。

方法三：通过安装日志确认安装状态

如果你在上述路径中确实找不到 stackbuilder.exe 这个文件，可能是安装过程中出现了意外（极少数情况）。

你可以打开文件资源管理器，在地址栏输入：%temp% 并回车。
寻找名为 install-postgresql.log 的日志文件并打开。
搜索关键词 stackbuilder，确认日志中显示该组件是否被成功复制到了你的安装目录中。

建议先尝试方法一，绝大多数情况下它都静静地躺在安装目录的 bin 文件夹里。如果找到了并成功打开，你可以直接关掉这个窗口，以后需要安装 PostGIS 等扩展时再来点击它即可。