求助：通过SSH将本地MySQL迁移至虚拟机PostgreSQL的脚本问题

Let's break down the issues with your current script and walk through how to get a complete migration working smoothly.

Key Problems in Your Current Script #

1. Broken Redirection + Pipe Combination
   Your line mysqldump ... > Migration.sql | ssh ... sends all of mysqldump's output to a local Migration.sql file, which means nothing gets passed through the pipe to your remote SSH session. The remote psql command ends up reading empty input, which explains why only partial database structures are created.

2. Limited Compatibility with --compatible=postgresql
   This flag only handles basic syntax tweaks—MySQL and PostgreSQL have significant differences in data types (like ENUM implementations, AUTO_INCREMENT vs SERIAL/GENERATED), constraints, and built-in functions that this flag can't resolve on its own.

3. Missing Pre-Migration Setup
   You likely haven't created the target database in PostgreSQL first, so psql can't write to a non-existent dump database (I assume that's a typo and you meant your actual target DB name).

Step-by-Step Fixed Script #

First, let's fix the pipe/redirection issue and add necessary setup steps:

#!/bin/bash
# Check if database name is provided as an argument
if [ -z "$1" ]; then
    echo "Error: Please provide the database name."
    echo "Usage: $0 <your-database-name>"
    exit 1
fi

DATABASE="$1"
MYSQL_PASSWORD="xxxxx"
REMOTE_HOST="xxx.xxx.xx.x"

# 1. Create the target database in PostgreSQL (run once, comment out after first use)
ssh root@$REMOTE_HOST "psql -U postgres -c \"CREATE DATABASE $DATABASE;\""

# 2. Stream mysqldump output directly to remote psql (no local temp file)
mysqldump -u root -p"$MYSQL_PASSWORD" \
    --compatible=postgresql \
    --default-character-set=utf8 \
    --no-tablespaces \
    --skip-lock-tables \
    "$DATABASE" | ssh root@$REMOTE_HOST "psql -U postgres -d $DATABASE"

Handling Advanced Compatibility Issues #

Even with the fixed script, you might still hit roadblocks like:

• Data type mismatches: MySQL's INT AUTO_INCREMENT won't automatically translate to PostgreSQL's SERIAL or GENERATED AS IDENTITY—you'll need to adjust table definitions manually.
• Constraint/Index Differences: Unique keys, foreign keys, and index syntax can vary between the two databases, requiring small tweaks.
• Stored Procedures/Functions: These are almost entirely incompatible—you'll need to rewrite them using PostgreSQL's syntax.

Better Alternative: Use pgloader #

For a far more robust migration that handles most compatibility issues automatically, use pgloader (a dedicated tool for migrating databases to PostgreSQL):

1. Install pgloader on your local machine or the remote VM
2. Run this command (replace credentials and hosts as needed):

   pgloader mysql://root:xxxxx@localhost/$DATABASE pgsql://postgres@$REMOTE_HOST/$DATABASE
   

   pgloader will automatically:
   • Create tables with correct PostgreSQL data types
   • Migrate data while handling encoding differences
   • Set up indexes, constraints, and sequences
   • Support incremental migrations if you need to update the remote DB later

内容的提问来源于stack exchange，提问作者Ferran Climent Cavallé