package main import ( "database/sql" "fmt" "log" "os" "time" "strings" "flag" "path/filepath" _ "github.com/lib/pq" ) var ( printDebugMsg = false // schemas allSchemasQueryTemp = `select schema_name, 'create schema '||schema_name||' authorization '||schema_owner||';' as schema_ddl from information_schema.schemata where schema_name not like 'pg_%' and schema_name not in ('information_schema') order by 1` oneSchemaQueryTemp = `select schema_name, 'create schema '||schema_name||' authorization '||schema_owner||';' as schema_ddl from information_schema.schemata where schema_name = '${SCHEMANAME}'` // functions dumpFuncsQueryTemp = ` select s.proc_name, pg_get_functiondef(s.oid)||' '||proc_volatile||' COST '||s.procost||';' as proc_source_code, proc_identity, 'ALTER FUNCTION '||s.proc_identity||' OWNER TO '||s.owner||';' as proc_grant from ( select p.oid, p.pronamespace::regnamespace::text as schemaname, p.pronamespace::regnamespace::text||'.'||p.proname as proc_name, p.pronamespace::regnamespace::text||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||')' as proc_identity, (select rolname from pg_roles r where r.oid = p.proowner) as owner, case when p.provolatile::text='v'::text then 'volatile'::text when p.provolatile::text='i'::text then 'immutable'::text when p.provolatile::text='s'::text then 'stable'::text else p.provolatile::text end as proc_volatile, p.procost from pg_proc p ) s where s.schemaname not in ('pg_catalog','information_schema') ${SCHEMACOND} ${FUNCNAMECOND} order by s.schemaname, s.proc_name` funcSchemaCondition = ` and schemaname = '${SCHEMANAME}' ` funcNameCondition = ` and proc_name = '${OBJNAME}' ` // views dumpViewsQueryTemp = `select schemaname||'.'||viewname as viewname, 'create or replace view '||schemaname||'.'||viewname||' as '||definition as def, schemaname||'.'||viewname as viewidentity, '' as viewgrant from pg_views where 1=1 ${SCHEMACOND} ${VIEWNAMECOND}` viewNameCondition = ` and viewname = '${OBJNAME}' ` viewSchemaCondition = ` and schemaname = '${SCHEMANAME}' ` // tables dumpTableQueryTemp = ` select tl.table_schema, tl.table_name, coalesce(ddl.table_exists, 0) as table_exists, coalesce(ddl.table_is_child, 0) as table_is_child, coalesce(ddl.table_inherits, '') as table_inherits, coalesce(ddl.table_structure, '') as table_structure, coalesce(ddl.table_constraints, '') as table_constraints, coalesce(ddl.table_tablespace, '') as table_tablespace, coalesce(ddl.table_indexes, '') as table_indexes from ( select table_schema, table_name, table_type, (select count(*) from pg_inherits where inhrelid=(x.table_schema||'.'||x.table_name)::regclass) as child from information_schema.tables x ) tl join lateral (select (select count(*) from information_schema.tables t where table_schema||'.'||table_name=tl.table_schema||'.'||tl.table_name) as table_exists, (select count(*) from pg_inherits where inhrelid=(tl.table_schema||'.'||tl.table_name)::regclass) as table_is_child, (select 'INHERITS ('|| inhparent::regclass||')' from pg_inherits where inhrelid=(tl.table_schema||'.'||tl.table_name)::regclass) as table_inherits, (with srcdata as (SELECT a.attname ||' '|| format_type(a.atttypid, a.atttypmod) ||case when a.attnotnull then ' NOT NULL' else '' end as _def FROM pg_attribute a WHERE attrelid=(tl.table_schema||'.'||tl.table_name)::regclass and a.attstattarget = -1 order by attnum) select string_agg(_def,', ') as _ddl from srcdata) as table_structure, (with srcdata as (select 'CONSTRAINT '||conname||' '||pg_catalog.pg_get_constraintdef(oid) as _ddl from pg_constraint where conrelid=(tl.table_schema||'.'||tl.table_name)::regclass order by case contype when 'p' then '1' when 'f' then '2'||conname else '3' end ) select string_agg(_ddl,', ') as _ddl from srcdata) as table_constraints, (with srcdata as (select case when tablespace is not null then 'TABLESPACE '||t.tablespace else ' ' end as _ddl from pg_tables t where schemaname||'.'||tablename = (tl.table_schema||'.'||tl.table_name)) select string_agg(_ddl,', ') as _ddl from srcdata ) as table_tablespace, (with srcdata as (select indexdef||case when tablespace is not null then ' tablespace '||tablespace else '' end||';' as _ddl from pg_indexes where schemaname||'.'||tablename = (tl.table_schema||'.'||tl.table_name)) select string_agg(_ddl,' ') as _ddl from srcdata) as table_indexes) ddl on true where upper(table_type) like '%TABLE%' ${SCHEMACOND} ${TABLENAMECOND} ${WITHCHILDCOND} order by 1, 2` tableNameCondition = ` and table_name='${OBJNAME}' ` tableWithChildCondition = ` and child=1 ` tableNoChildCondition = ` and child=0 ` tableSchemaCondition = ` and table_schema='${SCHEMANAME}' ` ) type pgdata struct { objName string objSource string objIdentity string objGrant string } type schemadata struct { schName string schSource string } type pgtable struct { tableSchema string tableName string tableExists int tableIsChild int tableInherits string tableStructure string tableConstraints string tableTablespace string tableIndexes string } func main() { _, prgName := filepath.Split(os.Args[0]) usageHint := fmt.Sprintf(` USAGE: %s --schema=schemaname | --all_schemas [ --functions | --views | --tables [ --withchildren ] | --all_objects ] [ --name=object_name ] [--debug ] [ --help ] Program dumps source codes of postgresql functions in separate files with extension ".sql". These files can be directly implemented back into database to install functions. `, prgName) paramSchema := flag.String("schema", "", "name of pg schema in database to dump functions from") paramObjName := flag.String("name", "", "function name to be dump") paramAllSchemas := flag.Bool("all_schemas", false, "dumps functions from all schemas in database") paramDebug := flag.Bool("debug", false, "print debug messages") paramHelp := flag.Bool("help", false, "print help") paramAllObjects := flag.Bool("all_objects", false, "dump all objects source code") paramFuncs := flag.Bool("functions", false, "dump functions source code") paramTables := flag.Bool("tables", false, "dump tables source code (only parents or single tables)") paramWithChildTables := flag.Bool("withchildren", false, "dump tables source code - with children (inhereted) tables") paramViews := flag.Bool("views", false, "dump views source code") flag.Parse() pgSchema := *paramSchema pgDumpAllSchemas := *paramAllSchemas pgObjName := *paramObjName printDebugMsg = *paramDebug printHelp := *paramHelp dumpViews := *paramViews dumpFuncs := *paramFuncs dumpTabs := *paramTables dumpWithChildTables := *paramWithChildTables dumpAllObjects := *paramAllObjects debugMsg(fmt.Sprintf("pgSchema: %s", pgSchema)) debugMsg(fmt.Sprintf("pgDumpAllSchemas: %v", pgDumpAllSchemas)) debugMsg(fmt.Sprintf("pgObjName: %s", pgObjName)) debugMsg(fmt.Sprintf("printDebugMsg: %v", printDebugMsg)) debugMsg(fmt.Sprintf("printHelp: %v", printHelp)) debugMsg(fmt.Sprintf("dumpViews: %v", dumpViews)) debugMsg(fmt.Sprintf("dumpFuncs: %v", dumpFuncs)) debugMsg(fmt.Sprintf("dumpTabs: %v", dumpTabs)) debugMsg(fmt.Sprintf("dumpAllObjects: %v", dumpAllObjects)) if printHelp == true || (pgSchema == "" && pgDumpAllSchemas == false) { log.Fatal(usageHint) } var err error var createSchemaQuery string pgTableSchemaCondition := "" pgViewSchemaCondition := "" pgFuncSchemaCondition := "" if pgSchema != "" { pgTableSchemaCondition = strings.Replace(tableSchemaCondition, "${SCHEMANAME}", pgSchema, -1) pgViewSchemaCondition = strings.Replace(viewSchemaCondition, "${SCHEMANAME}", pgSchema, -1) pgFuncSchemaCondition = strings.Replace(funcSchemaCondition, "${SCHEMANAME}", pgSchema, -1) createSchemaQuery = strings.Replace(oneSchemaQueryTemp, "${SCHEMANAME}", pgSchema, -1) } else { createSchemaQuery = allSchemasQueryTemp } var pgFuncNameCondition, pgViewNameCondition, pgTableNameCondition string if pgObjName != "" { pgFuncNameCondition = strings.Replace(funcNameCondition, "${OBJNAME}", pgObjName, -1) pgViewNameCondition = strings.Replace(viewNameCondition, "${OBJNAME}", pgObjName, -1) pgTableNameCondition = strings.Replace(tableNameCondition, "${OBJNAME}", pgObjName, -1) } else { pgFuncNameCondition = "" pgViewNameCondition = "" pgTableNameCondition = "" } pgURI := requireEnvVar("POSTGRES_URI") checkValue("postgresql URI", pgURI, true, false) pgDB, err := sql.Open("postgres", pgURI) if err != nil { log.Fatalln("Cannot connect into postgresql db: ", err) } defer func() { if errClose := pgDB.Close(); err != nil { log.Println(curTime(), "closing source database:", errClose.Error()) } }() if err = pgDB.Ping(); err != nil { log.Fatalln("Cannot ping postgresql db: ", err) } var sd schemadata sch, err := pgDB.Query(createSchemaQuery) if err != nil { log.Fatalln("cannot not run query:", err) } if sch != nil { for sch.Next() { if err = sch.Scan(&sd.schName, &sd.schSource); err != nil { log.Fatalln("cannot get schema ", &sd.schName, " DDL:", err) } } fmt.Println(sd.schName) createSchemaSQLFile(sd) } if dumpTabs == true || dumpAllObjects == true { pgTabsQuery := strings.Replace(dumpTableQueryTemp, "${SCHEMACOND}", pgTableSchemaCondition, -1) pgTabsQuery = strings.Replace(pgTabsQuery, "${TABLENAMECOND}", pgTableNameCondition, -1) if dumpWithChildTables == true { pgTabsQuery = strings.Replace(pgTabsQuery, "${WITHCHILDCOND}", tableWithChildCondition, -1) } else { pgTabsQuery = strings.Replace(pgTabsQuery, "${WITHCHILDCOND}", tableNoChildCondition, -1) } debugMsg(fmt.Sprint("pgTabsQuery: ", pgTabsQuery)) dumpTables(pgDB, pgTabsQuery, dumpWithChildTables) } if dumpFuncs == true || dumpAllObjects == true { pgFuncQuery := strings.Replace(dumpFuncsQueryTemp, "${SCHEMACOND}", pgFuncSchemaCondition, -1) pgFuncQuery = strings.Replace(pgFuncQuery, "${FUNCNAMECOND}", pgFuncNameCondition, -1) debugMsg(fmt.Sprint("pgFuncQuery: ", pgFuncQuery)) dumpObjects(pgDB, pgFuncQuery, "functions") } if dumpViews == true || dumpAllObjects == true { pgViewsQuery := strings.Replace(dumpViewsQueryTemp, "${SCHEMACOND}", pgViewSchemaCondition, -1) pgViewsQuery = strings.Replace(pgViewsQuery, "${VIEWNAMECOND}", pgViewNameCondition, -1) debugMsg(fmt.Sprint("pgViewsQuery: ", pgViewsQuery)) dumpObjects(pgDB, pgViewsQuery, "views") } fmt.Println(curTime(), "ALL DONE") } func dumpTables(pgDB *sql.DB, pgQuery string, dumpWithChildTables bool) { rows, err := pgDB.Query(pgQuery) if err != nil { log.Fatalln("cannot not run query:", pgQuery, " ERROR: ", err) } var count int var t pgtable var f pgdata if rows != nil { for rows.Next() { if err = rows.Scan( &t.tableSchema, &t.tableName, &t.tableExists, &t.tableIsChild, &t.tableInherits, &t.tableStructure, &t.tableConstraints, &t.tableTablespace, &t.tableIndexes, ); err != nil { log.Fatalln("cannot parse data:", err) } count++ if (dumpWithChildTables == false) || ((dumpWithChildTables == true) && (t.tableIsChild == 0)) { f.objIdentity = t.tableSchema + "." + t.tableName f.objName = f.objIdentity f.objSource = "CREATE TABLE " + f.objName + `( ` if t.tableIsChild == 0 { f.objSource = f.objSource + t.tableStructure if t.tableConstraints != "" { f.objSource = f.objSource + `, ` + t.tableConstraints } f.objSource = f.objSource + ")" } else { if t.tableConstraints != "" { f.objSource = f.objSource + t.tableConstraints } f.objSource = f.objSource + ")" if t.tableInherits == "" { log.Fatal("table ", f.objName, " is child table but source code for inheritance is empty") } else { f.objSource = f.objSource + ` ` + t.tableInherits } } if t.tableTablespace != "" { f.objSource = f.objSource + ` ` + t.tableTablespace } f.objSource = f.objSource + ";" if t.tableIndexes != "" { f.objSource = f.objSource + ` ` + t.tableIndexes } fmt.Println(count, ":", f.objName) debugMsg(fmt.Sprint("DDL:", f.objSource)) createObjSQLFile(f) } } } fmt.Println(curTime(), count, " tables DONE") } func dumpObjects(pgDB *sql.DB, pgQuery string, description string) { rows, err := pgDB.Query(pgQuery) if err != nil { log.Fatalln("cannot not run query:", pgQuery, " ERROR: ", err) } var f pgdata var count int if rows != nil { for rows.Next() { if err = rows.Scan( &f.objName, &f.objSource, &f.objIdentity, &f.objGrant, ); err != nil { log.Fatalln("cannot parse data:", err) } count++ fmt.Println(count, ":", f.objName) createObjSQLFile(f) } } fmt.Println(curTime(), count, " ", description, " DONE") } func createSchemaSQLFile(s schemadata) { sname := s.schName + ".sql" so, err := os.Create(sname) if err != nil { log.Fatal("Cannot create file ", sname) } defer func() { if err := so.Close(); err != nil { panic(err) } }() if _, err = so.Write([]byte(s.schSource)); err != nil { panic(err) } } func createObjSQLFile(f pgdata) { fname := f.objIdentity + ".sql" debugMsg(fmt.Sprint("fname:", fname)) fo, err := os.Create(fname) if err != nil { log.Fatal("Cannot create file ", fname) } defer func() { if err := fo.Close(); err != nil { panic(err) } }() if _, err = fo.Write([]byte(f.objSource)); err != nil { panic(err) } } func curTime() string { return time.Now().UTC().Format(time.RFC3339) + ":" } func requireEnvVar(s string) string { env, ok := os.LookupEnv(s) if !ok { log.Fatalln(curTime(), s, "isn't defined") } return (env) } func checkValue(name string, value string, required bool, printit bool) { if value == "" && required == true { log.Fatal("ERROR: variable ", name, " cannot be empty!") } if printit == true { fmt.Println(curTime(), name, ": ", value) } } func debugMsg(t string) { if printDebugMsg == true { fmt.Println(curTime(), t) } }