如何将Docker容器中的PostgreSQL数据库导出为XML或JSON格式文件

PostgreSQL doesn’t have a direct equivalent to pg_dumpall for exporting entire clusters to XML or JSON out of the box, but we can use its built-in JSON/XML functions combined with psql (via Docker exec) to get the job done. Below are step-by-step methods for both formats:

Export to JSON Format #

1. Export a Single Table (Line-Delimited JSON Objects) #

This is the simplest approach for individual tables, producing one JSON object per row:

docker exec -i database psql -U postgres -c "COPY (SELECT * FROM your_table_name) TO STDOUT WITH (FORMAT json);" > your_table_export.json

2. Export a Single Table (Single JSON Array) #

If you prefer all rows wrapped in a single JSON array (easier to parse in some tools):

docker exec -i database psql -U postgres -c "COPY (SELECT json_agg(t) FROM your_table_name t) TO STDOUT;" > your_table_array.json

3. Export All Tables in All Databases (Automated Script) #

To replicate the "full cluster" behavior of pg_dumpall, use this shell script to iterate through all non-template databases and their tables:

#!/bin/bash

# Fetch list of non-template databases
databases=$(docker exec database psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;")

for db in $databases; do
    # Clean up whitespace from database name
    db_trimmed=$(echo "$db" | xargs)
    # Fetch list of public tables in the current database
    tables=$(docker exec database psql -U postgres -d "$db_trimmed" -t -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';")
    
    for table in $tables; do
        table_trimmed=$(echo "$table" | xargs)
        # Export table to a JSON file named {db}_{table}.json
        docker exec -i database psql -U postgres -d "$db_trimmed" -c "COPY (SELECT * FROM $table_trimmed) TO STDOUT WITH (FORMAT json);" > "${db_trimmed}_${table_trimmed}.json"
    done
done

Save this as export_all_json.sh, make it executable with chmod +x export_all_json.sh, then run it.

Export to XML Format #

PostgreSQL provides dedicated functions like row_to_xml and table_to_xml to convert table data to XML structures.

1. Export a Single Table (Row-by-Row XML Fragments) #

Export each row as an individual XML element:

docker exec -i database psql -U postgres -c "COPY (SELECT row_to_xml(t) FROM your_table_name t) TO STDOUT;" > your_table_rows.xml

2. Export a Single Table (Full Schema + Data as XML) #

Generate a complete XML document that includes both the table schema and all its data:

docker exec -i database psql -U postgres -c "COPY (SELECT table_to_xml('your_table_name', true, true, '')) TO STDOUT;" > your_table_full.xml

The table_to_xml parameters mean:

• true: Include column names as XML attributes
• true: Include data types as XML attributes
• Empty string: No XML namespace

3. Export All Tables in All Databases (Automated Script) #

Adjust the JSON script to use XML functions for full cluster export:

#!/bin/bash

databases=$(docker exec database psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;")

for db in $databases; do
    db_trimmed=$(echo "$db" | xargs)
    tables=$(docker exec database psql -U postgres -d "$db_trimmed" -t -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';")
    
    for table in $tables; do
        table_trimmed=$(echo "$table" | xargs)
        docker exec -i database psql -U postgres -d "$db_trimmed" -c "COPY (SELECT table_to_xml('$table_trimmed', true, true, '')) TO STDOUT;" > "${db_trimmed}_${table_trimmed}.xml"
    done
done

Key Notes #

• Replace your_table_name with your actual table name, and database with your Docker container’s name.
• Ensure the postgres user has read access to all tables you’re exporting.
• For large tables, these commands may take time and consume disk space—monitor your system resources during the process.

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