Postgresql
使用 PostGIS 將 PostgreSQL 9.5 升級到 9.6 - pg_upgrade
我正在嘗試將 9.5 伺服器升級到 9.6,而 pg_upgrade 給了我以下錯誤:
Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* system OID user data types ok Checking for contrib/isn with bigint-passing mismatch ok Checking for roles starting with 'pg_' ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries fatal Your installation references loadable libraries that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file: loadable_libraries.txt
其中包含:
could not load library "$libdir/rtpostgis-2.2": ERROR: could not access file "$libdir/rtpostgis-2.2": No such file or directory
當我啟動伺服器並執行“CREATE EXTENSION postgis”然後停止伺服器時,pg_upgrade 會給我一個關於非空數據庫的錯誤:
New cluster database "postgres" is not empty
所以,我陷入了第 22 個問題 - 如果不安裝擴展,我就無法升級,但一旦我這樣做,它就會認為數據庫不為空。
任何提示如何解決這個問題?
問題是舊集群中的postgis擴展版本與新集群中的**庫版本不一樣。所以當它試圖遷移數據庫時,它找不到舊版本的擴展。
要解決這個問題,對於每個
my_database
安裝了 postgis 擴展的程序,執行:psql my_database postgres -c 'ALTER EXTENSION postgis UPDATE TO "2.3.2";'
在舊集群中。然後執行 pg_update。
您的 9.6 安裝可能具有 postgis 2.3 和相應的 rtpostgis-2.3 庫。如果您在 9.6 安裝中有 postgis 2.2 的庫,那麼 pg_upgrade 應該可以正常工作。
如果您需要 postgis 2.3,您可能必須使用 pg_dumpall 和 pg_restore。