没有Postgis功能的转储或还原Postgresql数据库 [英] Dump or restore Postgresql databases without Postgis functions

问题描述

我想从Postgresql 8.4转储4个数据库以迁移到Postgresql 9.1.

I want to dump 4 databases from postgresql 8.4 to migrate to Postgresql 9.1.

我在带有PgRouting的旧Postgresql 8.4上使用PostGis，因此每个数据库都有大约1000个函数.每次导出所有数据库时，所有功能都写入转储中.还原备份文件时，在Postgresql 9.1上创建postgis或pgrouting扩展时会遇到一些冲突.

I use PostGis on the old Postgresql 8.4 with PgRouting so each database has around 1000 functions. Each time I export all databases, all functions are written in the dump. When I restore the backup file, I get some conflicts when I create an extension of postgis or pgrouting on Postgresql 9.1

总有没有在不导出功能的情况下在8.4上转储数据库(创建数据库，创建模式，创建表和数据)的方法吗?还是在没有在备份文件上创建功能的情况下还原9.1上的数据库?

Is there anyway to dump databases on 8.4 (create dbs, create schemas, create tables and data) without exporting fucntions as well? Or is there anyway to restore the databases on 9.1 without creating the functions on the backup file?

推荐答案

带有PostGIS的数据库需要遵循特定的更新过程.您将需要遵循此处记录的过程.

Databases with PostGIS need to follow a specific update procedure. You will need to follow a "hard upgrade" procedure documented here.

在8.4数据库上:

pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

在9.1数据库上，也许从这些开始(如果您还没有这样做的话):

And on the 9.1 database, maybe start with these (if you haven't already done so):

createdb [yourdatabase]
psql -d [yourdatabase] -c "CREATE EXTENSION postgis;"

然后使用特殊的postgis_restore.pl脚本还原数据:

Then restore the data using a special postgis_restore.pl script:

perl utils/postgis_restore.pl "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

这篇关于没有Postgis功能的转储或还原Postgresql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！