# 1. 安装新版本
sudo apt install postgresql-17

# 2. 停止所有 PostgreSQL 服务
sudo systemctl stop postgresql

# 3. 检查兼容性
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --check

# 4. 执行升级
/usr/lib/postgresql/17/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --link  # 使用硬链接，速度极快（但两版本共享数据目录）

# 5. 更新统计信息
analyze_new_cluster.sh

# 6. 启动新版本
sudo systemctl start postgresql-17

sudo apt install pgloader

# 或 Docker
docker pull dimitri/pgloader:latest

# 命令行
pgloader mysql://user:pass@mysql-host/mydb postgresql://user:pass@pg-host/mydb

# 配置文件（mysql.load）
LOAD DATABASE
    FROM mysql://user:pass@mysql-host/mydb
    INTO postgresql://user:pass@pg-host/mydb

WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '512MB'

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type year to integer drop typemod

EXCLUDE TABLE NAMES LIKE 'temp_%', 'log_%'

BEFORE LOAD DO
    $$ CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; $$

AFTER LOAD DO
    $$ ANALYZE; $$;

# pgloader 也支持 Oracle（需要 Oracle Instant Client）
pgloader oracle://user:pass@oracle-host:1521/ORCL \
    postgresql://user:pass@pg-host/mydb

# 安装
sudo apt install ora2pg

# 初始化配置模板
ora2pg --project_base /opt/migration --init_project myproject

# 编辑配置 /opt/migration/myproject/ora2pg.conf
# ORACLE_DSN    dbi:Oracle:host=oracle-host;sid=ORCL;port=1521
# ORACLE_USER   system
# ORACLE_PWD    manager
# PG_DSN        dbi:Pg:dbname=mydb;host=pg-host;port=5432
# PG_USER       postgres
# PG_PWD        password

# 只迁移 Schema
ora2pg -c ora2pg.conf --type TABLE --dump_schema > schema.sql

# 迁移数据
ora2pg -c ora2pg.conf --type COPY --dump_data > data.sql

# 迁移 PL/SQL → PL/pgSQL
ora2pg -c ora2pg.conf --type FUNCTION --dump_as_html > functions.html

# 生成迁移报告
ora2pg -c ora2pg.conf --estimate_cost

-- COPY 命令（最快）
COPY employees (name, email, age, salary)
FROM '/tmp/employees.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL '');

-- 从 STDIN（编程方式）
\COPY employees (name, email) FROM STDIN WITH CSV HEADER

-- 使用 fdw 导入
CREATE FOREIGN TABLE csv_import (
    name TEXT, email TEXT
) SERVER csv_server
OPTIONS (filename '/tmp/data.csv', format 'csv', header 'true');

INSERT INTO employees (name, email)
SELECT name, email FROM csv_import;

-- 导出为 CSV
COPY (SELECT * FROM employees WHERE department = '工程部')
TO '/tmp/engineering.csv' WITH (FORMAT csv, HEADER true);

-- \COPY（客户端导出）
\COPY employees TO '/tmp/employees.csv' WITH CSV HEADER

-- 行数对比（源库和目标库分别执行）
SELECT table_name, row_count
FROM (
    SELECT 'employees' AS table_name, count(*) AS row_count FROM employees
    UNION ALL
    SELECT 'orders', count(*) FROM orders
    UNION ALL
    SELECT 'products', count(*) FROM products
) sub
ORDER BY table_name;

-- 校验和验证（对比特定列）
SELECT
    count(*) AS total_rows,\    sum(hashtext(row::text)) AS checksum
FROM employees;

-- 使用 pg_comparator 扩展进行跨库对比
-- 或手动编写脚本：
-- 1. 源库 count(*) 导出
-- 2. 目标库 count(*) 对比
-- 3. 抽样对比关键字段