备份具有几何转换的完整 Postgis 数据库

Question

我有一个启用PostGIS的PostgreSQL数据库，在生产中不再需要它。我想备份它，但 PostGIS 的几何列应该转换为简单、长期稳定的文本格式，如 WKT。

我知道ST_AsText功能。

SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;

但是，如何应用它来备份包含多个具有几何形状的表和许多没有几何形状的表的完整数据库？

Answer 1

这是我最终应用的备份策略：

1. 使用pg_dump对 PostGIS -Postgres 数据库进行正常转储。纯文本格式以提高可读性。

2.CSV备份所有表

为此，我创建了初始数据库("la_as_text")的副本，并使用以下脚本将所有几何列转换为文本(感谢@Jim琼斯)。这对我的情况非常具体，但我还是决定像这样发布它。以防万一有人像我对依赖于几何列和 gist 索引的视图所做的那样在即将到来的问题中运行。如果将数据类型更改为文本，则视图将不起作用，并且文本列也对 gist 索引无效。

#!/bin/bash
# has to be run by a user who has owner permissions for the database
rm delete_views.txt delete_views_mod.txt alter_script.txt alter_script_mod.txt
# delete all views - not necessary in long-term-backup and cause problems when altering geometry columns 
echo "select 'drop view ' ||table_name|| ';' from information_schema.views where table_schema not in ('pg_catalog', 'information_schema') and table_name "'!'"~ '^pg_';" | psql la_as_text >> delete_views.txt
cat delete_views.txt | tail -n +3 | head -n -2 >> delete_views_mod.txt
cat delete_views_mod.txt | psql la_as_text
# delete one particular gist index that depends on a geometry column -- text can't be indexed with gist 
echo "drop index idx_combined_points_the_geom;" | psql la_as_text
# change data type geometry (EWKB) to human readable text (WKT)
echo "select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text USING ST_AsText('||column_name||');' from information_schema.columns where table_schema = 'public' and udt_name = 'geometry';" | psql la_as_text >> alter_script.txt
cat alter_script.txt | tail -n +3 | head -n -2 >> alter_script_mod.txt
cat alter_script_mod.txt | psql la_as_text

由于缺少几何数据类型，生成的数据库缺少许多初始功能，但它是人类可读的。

我没有使用正常的转储，而是将所有单独的表导出为";"- 使用以下脚本分隔的文本文件：

#!/bin/bash
SCHEMA="public"
DB="la_as_text"
psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |
while read TBL; do
psql -c "copy $SCHEMA.$TBL to stdout with csv delimiter ';'" $DB > $TBL.csv
done

我非常有信心，如果有必要，将来可以重建此备份。