使用Golang脚本仅获取Postgres数据库模式的方法

在Golang中获取PostgreSQL数据库的完整DDL脚本，可以通过查询系统目录表来实现。以下是几种常用方法：

方法1：使用pg_dump命令（推荐）

package main

import (
    "fmt"
    "os/exec"
    "strings"
)

func getSchemaDDL(host, port, user, password, dbname string) (string, error) {
    // 设置连接字符串
    connStr := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s",
        host, port, user, password, dbname)
    
    // 执行pg_dump命令，仅导出模式（不包含数据）
    cmd := exec.Command("pg_dump", 
        "--schema-only",      // 仅导出模式
        "--no-owner",         // 不包含所有者信息
        "--no-privileges",    // 不包含权限信息
        "-d", connStr)
    
    output, err := cmd.Output()
    if err != nil {
        return "", fmt.Errorf("pg_dump执行失败: %v", err)
    }
    
    return string(output), nil
}

func main() {
    ddl, err := getSchemaDDL("localhost", "5432", "postgres", "password", "mydb")
    if err != nil {
        panic(err)
    }
    fmt.Println(ddl)
}

方法2：直接查询系统表（纯Go实现）

package main

import (
    "database/sql"
    "fmt"
    "strings"
    _ "github.com/lib/pq"
)

type TableDDL struct {
    TableName string
    DDL       string
}

func getDatabaseSchema(db *sql.DB) ([]TableDDL, error) {
    var tables []TableDDL
    
    // 获取所有用户表的DDL
    query := `
        SELECT 
            schemaname || '.' || tablename as table_name,
            'CREATE TABLE ' || schemaname || '.' || tablename || ' (' || 
            string_agg(column_def, ', ') || 
            COALESCE(', ' || condef, '') || ');' as ddl
        FROM (
            SELECT 
                n.nspname as schemaname,
                c.relname as tablename,
                a.attname || ' ' || 
                pg_catalog.format_type(a.atttypid, a.atttypmod) || 
                CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END as column_def,
                con.contype,
                'CONSTRAINT ' || con.conname || ' ' || 
                pg_catalog.pg_get_constraintdef(con.oid) as condef
            FROM pg_catalog.pg_class c
            JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
            LEFT JOIN pg_catalog.pg_constraint con ON con.conrelid = c.oid AND a.attnum = ANY(con.conkey)
            WHERE c.relkind = 'r' 
                AND n.nspname NOT IN ('pg_catalog', 'information_schema')
                AND a.attnum > 0
                AND NOT a.attisdropped
            ORDER BY c.relname, a.attnum
        ) t
        GROUP BY schemaname, tablename, condef
    `
    
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    for rows.Next() {
        var table TableDDL
        if err := rows.Scan(&table.TableName, &table.DDL); err != nil {
            return nil, err
        }
        tables = append(tables, table)
    }
    
    return tables, nil
}

func getFunctionsDDL(db *sql.DB) ([]string, error) {
    var functions []string
    
    query := `
        SELECT 
            'CREATE OR REPLACE FUNCTION ' || 
            n.nspname || '.' || p.proname || '(' || 
            pg_catalog.pg_get_function_arguments(p.oid) || ')\n' ||
            'RETURNS ' || pg_catalog.pg_get_function_result_string(p.oid) || '\n' ||
            'LANGUAGE ' || l.lanname || '\n' ||
            'AS $$\n' || 
            pg_catalog.pg_get_functiondef(p.oid) || 
            '\n$$;' as function_ddl
        FROM pg_proc p
        LEFT JOIN pg_namespace n ON n.oid = p.pronamespace
        LEFT JOIN pg_language l ON l.oid = p.prolang
        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    `
    
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    for rows.Next() {
        var ddl string
        if err := rows.Scan(&ddl); err != nil {
            return nil, err
        }
        functions = append(functions, ddl)
    }
    
    return functions, nil
}

func main() {
    connStr := "host=localhost port=5432 user=postgres password=password dbname=mydb sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()
    
    // 获取表结构
    tables, err := getDatabaseSchema(db)
    if err != nil {
        panic(err)
    }
    
    // 获取函数定义
    functions, err := getFunctionsDDL(db)
    if err != nil {
        panic(err)
    }
    
    // 输出完整的DDL
    var builder strings.Builder
    builder.WriteString("-- 表结构定义\n")
    for _, table := range tables {
        builder.WriteString(table.DDL)
        builder.WriteString("\n\n")
    }
    
    builder.WriteString("-- 函数定义\n")
    for _, fn := range functions {
        builder.WriteString(fn)
        builder.WriteString("\n\n")
    }
    
    fmt.Println(builder.String())
}

方法3：使用第三方库（简化操作）

package main

import (
    "database/sql"
    "fmt"
    "github.com/davecgh/go-spew/spew"
    _ "github.com/lib/pq"
    "github.com/xo/dburl"
)

func getFullSchemaDDL(dbURL string) (string, error) {
    // 解析数据库URL
    u, err := dburl.Parse(dbURL)
    if err != nil {
        return "", err
    }
    
    db, err := sql.Open(u.Driver, u.DSN)
    if err != nil {
        return "", err
    }
    defer db.Close()
    
    // 获取所有对象
    var ddl strings.Builder
    
    // 获取扩展
    rows, err := db.Query(`
        SELECT 'CREATE EXTENSION IF NOT EXISTS ' || extname || ';'
        FROM pg_extension
        WHERE extname NOT LIKE 'pg_%'
    `)
    if err == nil {
        for rows.Next() {
            var ext string
            rows.Scan(&ext)
            ddl.WriteString(ext + "\n")
        }
        rows.Close()
    }
    
    // 获取序列
    rows, err = db.Query(`
        SELECT 'CREATE SEQUENCE ' || schemaname || '.' || sequencename || ';'
        FROM pg_sequences
        WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
    `)
    if err == nil {
        for rows.Next() {
            var seq string
            rows.Scan(&seq)
            ddl.WriteString(seq + "\n")
        }
        rows.Close()
    }
    
    return ddl.String(), nil
}

func main() {
    dbURL := "postgres://postgres:password@localhost:5432/mydb?sslmode=disable"
    
    ddl, err := getFullSchemaDDL(dbURL)
    if err != nil {
        panic(err)
    }
    
    spew.Dump(ddl)
}

方法4：使用information_schema（标准SQL）

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

func getSchemaFromInformationSchema(db *sql.DB) error {
    // 获取所有表
    query := `
        SELECT 
            table_schema,
            table_name,
            column_name,
            data_type,
            is_nullable,
            column_default
        FROM information_schema.columns
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
        ORDER BY table_schema, table_name, ordinal_position
    `
    
    rows, err := db.Query(query)
    if err != nil {
        return err
    }
    defer rows.Close()
    
    for rows.Next() {
        var schema, table, column, dataType, nullable, defaultValue sql.NullString
        err := rows.Scan(&schema, &table, &column, &dataType, &nullable, &defaultValue)
        if err != nil {
            return err
        }
        
        fmt.Printf("表: %s.%s, 列: %s, 类型: %s, 可空: %s, 默认值: %s\n",
            schema.String, table.String, column.String, dataType.String, 
            nullable.String, defaultValue.String)
    }
    
    return nil
}

func main() {
    db, err := sql.Open("postgres", 
        "host=localhost port=5432 user=postgres password=password dbname=mydb sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    
    err = getSchemaFromInformationSchema(db)
    if err != nil {
        panic(err)
    }
}

依赖安装

go get github.com/lib/pq
go get github.com/xo/dburl
go get github.com/davecgh/go-spew/spew

方法1是最简单直接的方式，利用系统工具生成完整的DDL。方法2提供了更细粒度的控制，可以自定义输出格式。方法3使用第三方库简化了URL处理。方法4使用标准SQL但可能无法获取所有PostgreSQL特有的对象定义。