如何备份完整PostGIS数据库并将几何列转换为WKT格式？

Backing up your PostGIS database while turning geometry columns into WKT (Well-Known Text) is totally manageable—you just need to create a temporary copy of your data where spatial columns are transformed to plain text, then dump that copy. Here's a step-by-step breakdown:

1. Create a Temporary Schema for Converted Data #

First, we’ll set up a new schema to hold tables where geometry columns are replaced by their WKT equivalents. This keeps your original production database untouched.

CREATE SCHEMA IF NOT EXISTS backup_schema;

2. Automatically Convert All Tables to WKT Format #

Instead of writing manual queries for every table, use a PL/pgSQL script to loop through your entire database. It will convert geometry columns to WKT and copy all tables (including non-spatial ones) to the temporary schema.

Run this script in your PostgreSQL database (adjust the public schema if your tables live elsewhere):

DO $$
DECLARE
    rec RECORD;
    table_cols TEXT;
BEGIN
    -- Ensure backup schema exists
    IF NOT EXISTS (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'backup_schema') THEN
        EXECUTE 'CREATE SCHEMA backup_schema';
    END IF;

    -- Process tables with geometry columns
    FOR rec IN 
        SELECT DISTINCT f_table_schema, f_table_name
        FROM geometry_columns
        WHERE f_table_schema = 'public' -- Update this to your target schema
    LOOP
        -- Build column list: transform geometry columns to WKT, keep others as-is
        SELECT string_agg(
            CASE WHEN c.column_name = g.f_geometry_column 
                 THEN 'ST_AsText(' || quote_ident(c.column_name) || ') AS ' || quote_ident(c.column_name)
                 ELSE quote_ident(c.column_name)
            END, ', '
        ) INTO table_cols
        FROM information_schema.columns c
        LEFT JOIN geometry_columns g 
            ON c.table_schema = g.f_table_schema 
            AND c.table_name = g.f_table_name 
            AND c.column_name = g.f_geometry_column
        WHERE c.table_schema = rec.f_table_schema
          AND c.table_name = rec.f_table_name;

        -- Create converted table in backup schema
        EXECUTE format(
            'CREATE TABLE backup_schema.%I AS SELECT %s FROM %I.%I',
            rec.f_table_name,
            table_cols,
            rec.f_table_schema,
            rec.f_table_name
        );
    END LOOP;

    -- Process tables without geometry columns (copy directly)
    FOR rec IN 
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'public' -- Update this to your target schema
          AND table_type = 'BASE TABLE'
          AND NOT EXISTS (
              SELECT 1 FROM geometry_columns
              WHERE f_table_schema = information_schema.tables.table_schema
                AND f_table_name = information_schema.tables.table_name
          )
    LOOP
        -- Copy table to backup schema
        EXECUTE format(
            'CREATE TABLE backup_schema.%I AS SELECT * FROM %I.%I',
            rec.table_name,
            rec.table_schema,
            rec.table_name
        );
    END LOOP;
END $$;

3. Dump the Converted Schema #

Now that your temporary schema has all data with WKT instead of PostGIS geometry types, use pg_dump to create a portable backup. Run this command from your terminal:

# Plain text dump
pg_dump -d your_database_name -n backup_schema -f wkt_backup.sql

# Compressed dump (recommended for large databases)
pg_dump -d your_database_name -n backup_schema | gzip > wkt_backup.sql.gz

Replace your_database_name with the name of your PostgreSQL database.

4. Clean Up (Optional) #

Once you have your backup, you can drop the temporary schema to free up space:

DROP SCHEMA backup_schema CASCADE;

Important Notes #

• Preserving SRID: If you need to keep the spatial reference ID (SRID) of your geometry data, replace ST_AsText with ST_AsEWKT in the script. EWKT includes the SRID (e.g., SRID=4326;POINT(1 2)).
• Constraints & Indexes: The script copies table data and column types but doesn’t include indexes, primary keys, or foreign keys. If you need those in your backup, generate schema DDL separately, modify it to replace geometry types with text, then apply it before restoring the data.
• Multiple Schemas: If your database uses multiple schemas, adjust the WHERE f_table_schema = 'public' clauses in the script to loop through all relevant schemas (or remove the condition entirely to process all schemas).

This approach gives you a portable backup that works with any standard PostgreSQL database—no PostGIS required.

内容的提问来源于stack exchange，提问作者nevrome