pg_restore导入错误的解决思路

pg_restore导入错误的解决思路

引用

CSDN

1.

https://blog.csdn.net/qq_40033445/article/details/140521717

背景

在使用PostgreSQL数据库进行部署时，通过pg_dump导出数据后，使用pg_restore导入时遇到了错误，导致很多表未能成功导入。部分错误截图如下：

排查问题

由于开发中使用了PostgreSQL插件PostGIS中的地理类型，因此使用SQL导出会遇到很多问题。于是采用pg_dump进行导出，导出命令如下：

pg_dump -h localhost -p 15431 -U postgres  -F c -b -v -d test1 -x -O -f D:\databases\test1.dump

1.本地试验（没有带PostGIS插件）

使用不带PostGIS插件的镜像进行测试，使用Docker环境：

docker run --name some-postgres -e POSTGRES_PASSWORD=dev@local -v E:\docker-postgis:/var/lib/postgresql/data -p 15432:5432  -d postgres:12.0-alpine

首先创建数据库，然后创建用户，之后给用户授予此新建数据库的权限，截图如下：

下图可知此镜像没有PostGIS插件

导入命令：

pg_restore -h localhost -p 5432 -U test -d test -c -v -x -O /var/lib/postgresql/data/theamtic_map.dump

报错：

之后给用户授予superuser权限：

ALTER USER test WITH SUPERUSER;

再继续导入，发现报错减少。

2.带PostGIS插件的镜像试验

同样创建用户，创建数据库，给用户授予操作数据库所有的权限，报错如下：

之后修改用户为superuser：

alter user test with superuser;

导入：

pg_restore -h localhost -p 5432 -U test  -d test -c -v -x -O /var/lib/postgresql/data/theamtic_map.dump

导入成功

报的95个错误是因为新的数据库，有些索引不存在

查询可知PostGIS插件存在

总结

pg_restore导入成功，需要满足两个条件：一是安装的PostgreSQL需要包含PostGIS插件，二是创建的用户需要具有superuser权限。

部分查看和创建语句

# 没有PostGIS插件的镜像
docker run --name some-postgres -e POSTGRES_PASSWORD=dev@local -v E:\docker-postgis:/var/lib/postgresql/data -p 15432:5432  -d postgres:12.0-alpine

# 登录数据库
psql -U postgres -h localhost -p 5432

# 创建数据库
create database test;

# 创建用户
CREATE USER test WITH PASSWORD 'dev@local';

# 给用户授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE dbname TO user;

# 给用户授予superuser
ALTER USER your_username WITH SUPERUSER;

# 查看用户
\du

# 切换用户
\c mydatabase myuser

# 列出所有数据库
\l

# 查询PostGIS版本
select postgis_version();

# 查询PostgreSQL版本
select version();

# 查看PostgreSQL data目录
SHOW data_directory;

# 导入数据库
pg_restore -h localhost -p 5432 -U test -d test -c -v -x -O /var/lib/postgresql/data/theamtic_map.dump

# 有PostGIS的镜像
docker run -d --name fct-postgis-12-3.0 -p 54321:5432 -e POSTGRES_PASSWORD=dev@local  -v E:\docker-postgis2:/data geographica/postgis:latest