/*------------------------------------------------------------------------- * * pg_dump.c-- * pg_dump is an utility for dumping out a postgres database * into a script file. * * pg_dump will read the system catalogs in a database and * dump out a script that reproduces * the schema of the database in terms of * user-defined types * user-defined functions * tables * indices * aggregates * operators * * the output script is SQL that is understood by Postgres95 * * Copyright (c) 1994, Regents of the University of California * * * IDENTIFICATION * $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.11 1996/10/07 21:17:01 scrappy Exp $ * * Modifications - 6/10/96 - dave@bensoft.com - version 1.13.dhb * * Applied 'insert string' patch from "Marc G. Fournier" * Added '-t table' option * Added '-a' option * Added '-da' option * * Modifications - 6/12/96 - dave@bensoft.com - version 1.13.dhb.2 * * - Fixed dumpTable output to output lengths for char and varchar types! * - Added single. quote to twin single quote expansion for 'insert' string * mode. * * Modifications - 7/26/96 - asussman@vidya.com * * - Fixed ouput lengths for char and varchar type where the length is variable (-1) * *------------------------------------------------------------------------- */ #include #include #include #include /* for MAXHOSTNAMELEN on most */ #ifdef PORTNAME_sparc_solaris #include /* for MAXHOSTNAMELEN on some */ #endif #include "postgres.h" #include "access/htup.h" #include "libpq-fe.h" #include "pg_dump.h" extern char *optarg; extern int optind, opterr; /* global decls */ int g_verbose; /* verbose flag */ int g_last_builtin_oid; /* value of the last builtin oid */ FILE *g_fout; /* the script file */ PGconn *g_conn; /* the database connection */ int dumpData; /* dump data using proper insert strings */ int attrNames; /* put attr names into insert strings */ char g_opaque_type[10]; /* name for the opaque type */ /* placeholders for the delimiters for comments */ char g_comment_start[10]; char g_comment_end[10]; static void usage(const char* progname) { fprintf(stderr, "%s - version 1.13.dhb.2\n\n",progname); fprintf(stderr, "usage: %s [options] [dbname]\n",progname); fprintf(stderr, "\t -f filename \t\t script output filename\n"); fprintf(stderr, "\t -H hostname \t\t server host name\n"); fprintf(stderr, "\t -p port \t\t server port number\n"); fprintf(stderr, "\t -v \t\t verbose\n"); fprintf(stderr, "\t -d[a] \t\t dump data as proper insert strings\n"); fprintf(stderr, "\t \t\t (if 'a' then attribute names also)\n"); fprintf(stderr, "\t -S \t\t dump out only the schema, no data\n"); fprintf(stderr, "\t -a \t\t dump out only the data, no schema\n"); fprintf(stderr, "\t -t table \t\t dump for this table only\n"); fprintf(stderr, "\t -o \t\t dump object id's (oids)\n"); fprintf(stderr, "\n if dbname is not supplied, then the DATABASE environment name is used\n"); fprintf(stderr, "\n"); fprintf(stderr, "\tpg_dump dumps out postgres databases and produces a script file\n"); fprintf(stderr, "\tof SQL commands to regenerate the schema\n"); fprintf(stderr, "\tThe SQL output is designed for import into Postgres95\n"); exit(1); } static void exit_nicely(PGconn* conn) { PQfinish(conn); exit(1); } int main(int argc, char** argv) { int c; const char* progname; const char* filename; const char* dbname; int schemaOnly; int dataOnly; const char *pghost = NULL; const char *pgport = NULL; const char *tablename; int oids; TableInfo *tblinfo; int numTables; dbname = NULL; filename = NULL; tablename = NULL; g_verbose = 0; oids = 0; strcpy(g_comment_start,"-- "); g_comment_end[0] = '\0'; strcpy(g_opaque_type, "opaque"); dataOnly = schemaOnly = dumpData = attrNames = 0; progname = *argv; while ((c = getopt(argc, argv,"f:H:p:t:vSDd:ao")) != EOF) { switch(c) { case 'f': /* output file name */ filename = optarg; break; case 'H' : /* server host */ pghost = optarg; break; case 'p' : /* server port */ pgport = optarg; break; case 'v': /* verbose */ g_verbose = 1; break; case 'S': /* dump schema only */ schemaOnly = 1; break; case 'd': /* dump data as proper insert strings */ dumpData = 1; attrNames = strstr(optarg,"a") ? 1 : 0; break; case 't': /* Dump data for this table only */ tablename = optarg; break; case 'a': /* Dump data only */ dataOnly = 1; break; case 'o': /* Dump oids */ oids = 1; break; default: usage(progname); break; } } /* open the output file */ if (filename == NULL) { g_fout = stdout; } else { g_fout = fopen(filename, "w"); if (g_fout == NULL) { fprintf(stderr,"%s: could not open output file named %s for writing\n", progname, filename); exit(2); } } /* find database */ if (!(dbname = argv[optind]) && !(dbname = getenv("DATABASE")) ) { fprintf(stderr, "%s: no database name specified\n",progname); exit (2); } g_conn = PQsetdb(pghost, pgport, NULL, NULL, dbname); /* check to see that the backend connection was successfully made */ if (PQstatus(g_conn) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.\n", dbname); fprintf(stderr,"%s",PQerrorMessage(g_conn)); exit_nicely(g_conn); } g_last_builtin_oid = findLastBuiltinOid(); if (oids) setMaxOid(g_fout); if (!dataOnly) { if (g_verbose) fprintf(stderr, "%s last builtin oid is %d %s\n", g_comment_start, g_last_builtin_oid, g_comment_end); tblinfo = dumpSchema(g_fout, &numTables, tablename); } else tblinfo = dumpSchema(NULL, &numTables, tablename); if (!schemaOnly) { if (g_verbose) fprintf(stderr,"%s dumping out the contents of each table %s\n", g_comment_start, g_comment_end); dumpClasses(tblinfo, numTables, g_fout, tablename, oids); } if (!dataOnly) /* dump indexes at the end for performance */ dumpSchemaIdx(g_fout, &numTables, tablename, tblinfo, numTables); fflush(g_fout); fclose(g_fout); PQfinish(g_conn); exit(0); } /* * getTypes: * read all base types in the system catalogs and return them in the * TypeInfo* structure * * numTypes is set to the number of types read in * */ TypeInfo* getTypes(int *numTypes) { PGresult *res; int ntups; int i; char query[MAXQUERYLEN]; TypeInfo *tinfo; int i_oid; int i_typowner; int i_typname; int i_typlen; int i_typprtlen; int i_typinput; int i_typoutput; int i_typreceive; int i_typsend; int i_typelem; int i_typdelim; int i_typdefault; int i_typrelid; int i_typbyval; res = PQexec(g_conn, "begin"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"BEGIN command failed\n"); exit_nicely(g_conn); } PQclear(res); /* find all base types */ /* we include even the built-in types because those may be used as array elements by user-defined types */ /* we filter out the built-in types when we dump out the types */ sprintf(query, "SELECT oid, typowner,typname, typlen, typprtlen, typinput, typoutput, typreceive, typsend, typelem, typdelim, typdefault, typrelid,typbyval from pg_type"); res = PQexec(g_conn,query); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"getTypes(): SELECT failed"); exit_nicely(g_conn); } ntups = PQntuples(res); tinfo = (TypeInfo*)malloc(ntups * sizeof(TypeInfo)); i_oid = PQfnumber(res,"oid"); i_typowner = PQfnumber(res,"typowner"); i_typname = PQfnumber(res,"typname"); i_typlen = PQfnumber(res,"typlen"); i_typprtlen = PQfnumber(res,"typprtlen"); i_typinput = PQfnumber(res,"typinput"); i_typoutput = PQfnumber(res,"typoutput"); i_typreceive = PQfnumber(res,"typreceive"); i_typsend = PQfnumber(res,"typsend"); i_typelem = PQfnumber(res,"typelem"); i_typdelim = PQfnumber(res,"typdelim"); i_typdefault = PQfnumber(res,"typdefault"); i_typrelid = PQfnumber(res,"typrelid"); i_typbyval = PQfnumber(res,"typbyval"); for (i=0;i '%d'::oid", g_last_builtin_oid); res = PQexec(g_conn, query); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"getFuncs(): SELECT failed"); exit_nicely(g_conn); } ntups = PQntuples(res); *numFuncs = ntups; finfo = (FuncInfo*)malloc(ntups * sizeof(FuncInfo)); i_oid = PQfnumber(res,"oid"); i_proname = PQfnumber(res,"proname"); i_proowner = PQfnumber(res,"proowner"); i_prolang = PQfnumber(res,"prolang"); i_pronargs = PQfnumber(res,"pronargs"); i_proargtypes = PQfnumber(res,"proargtypes"); i_prorettype = PQfnumber(res,"prorettype"); i_proretset = PQfnumber(res,"proretset"); i_prosrc = PQfnumber(res,"prosrc"); i_probin = PQfnumber(res,"probin"); for (i=0;i 0 order by attnum",tblinfo[i].oid); res = PQexec(g_conn, q); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"getTableAttrs(): SELECT failed"); exit_nicely(g_conn); } ntups = PQntuples(res); i_attname = PQfnumber(res,"attname"); i_typname = PQfnumber(res,"typname"); i_attlen = PQfnumber(res,"attlen"); tblinfo[i].numatts = ntups; tblinfo[i].attnames = (char**) malloc( ntups * sizeof(char*)); tblinfo[i].typnames = (char**) malloc( ntups * sizeof(char*)); tblinfo[i].attlen = (int*) malloc(ntups * sizeof(int)); tblinfo[i].inhAttrs = (int*) malloc (ntups * sizeof(int)); tblinfo[i].parentRels = NULL; tblinfo[i].numParents = 0; for (j=0;j 0) tblinfo[i].attlen[j] = tblinfo[i].attlen[j] - 4; tblinfo[i].inhAttrs[j] = 0; /* this flag is set in flagInhAttrs()*/ } PQclear(res); } } /* * getIndices * read all the user-defined indices information * from the system catalogs return them in the InhInfo* structure * * numIndices is set to the number of indices read in * * */ IndInfo* getIndices(int *numIndices) { int i; char query[MAXQUERYLEN]; PGresult *res; int ntups; IndInfo *indinfo; int i_indexrelname; int i_indrelname; int i_indamname; int i_indproc; int i_indkey; int i_indclassname; /* find all the user-defined indices. We do not handle partial indices. We also assume that only single key indices skip 'Xinx*' - indices on inversion objects this is a 5-way join !! */ res = PQexec(g_conn, "begin"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"BEGIN command failed\n"); exit_nicely(g_conn); } PQclear(res); sprintf(query, "SELECT t1.relname as indexrelname, t2.relname as indrelname, i.indproc, i.indkey[0], o.opcname as indclassname, a.amname as indamname from pg_index i, pg_class t1, pg_class t2, pg_opclass o, pg_am a where t1.oid = i.indexrelid and t2.oid = i.indrelid and o.oid = i.indclass[0] and t1.relam = a.oid and i.indexrelid > '%d'::oid and t2.relname !~ '^pg_' and t1.relname !~ '^Xinx' ;", g_last_builtin_oid); res = PQexec(g_conn, query); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"getIndices(): SELECT failed"); exit_nicely(g_conn); } ntups = PQntuples(res); *numIndices = ntups; indinfo = (IndInfo*)malloc(ntups * sizeof (IndInfo)); i_indexrelname = PQfnumber(res,"indexrelname"); i_indrelname = PQfnumber(res,"indrelname"); i_indamname = PQfnumber(res,"indamname"); i_indproc = PQfnumber(res,"indproc"); i_indkey = PQfnumber(res,"indkey"); i_indclassname = PQfnumber(res,"indclassname"); for (i=0;i 0) ? "," : "", typname); } sprintf(q,"%s ) RETURNS %s%s AS '%s' LANGUAGE '%s';\n", q, finfo[i].retset ? " SETOF " : "", findTypeByOid(tinfo, numTypes, finfo[i].prorettype), (finfo[i].lang) ? finfo[i].probin : finfo[i].prosrc, (finfo[i].lang) ? "C" : "SQL"); fputs(q,fout); } /* * dumpOprs * writes out to fout the queries to recreate all the user-defined operators * */ void dumpOprs(FILE* fout, OprInfo* oprinfo, int numOperators, TypeInfo *tinfo, int numTypes) { int i; char q[MAXQUERYLEN]; char leftarg[MAXQUERYLEN]; char rightarg[MAXQUERYLEN]; char commutator[MAXQUERYLEN]; char negator[MAXQUERYLEN]; char restrict[MAXQUERYLEN]; char join[MAXQUERYLEN]; char sortop[MAXQUERYLEN]; for (i=0;i 0) ? ", " : "", tblinfo[i].attnames[j]); /* stored length can be -1 (variable) */ if (tblinfo[i].attlen[j] > 0) sprintf(q, "%s(%d)", q, tblinfo[i].attlen[j]); actual_atts++; } else if (!strcmp(tblinfo[i].typnames[j],"varchar")) { sprintf(q, "%s%s%s %s", q, (actual_atts > 0) ? ", " : "", tblinfo[i].attnames[j], tblinfo[i].typnames[j]); /* stored length can be -1 (variable) */ if (tblinfo[i].attlen[j] > 0) sprintf(q, "%s(%d)", q, tblinfo[i].attlen[j]); actual_atts++; } else { sprintf(q, "%s%s%s %s", q, (actual_atts > 0) ? ", " : "", tblinfo[i].attnames[j], tblinfo[i].typnames[j]); actual_atts++; } } } strcat(q,")"); if (numParents > 0) { sprintf(q, "%s inherits ( ",q); for (k=0;k0) ? ", " : "", parentRels[k]); } strcat(q,")"); } switch(tblinfo[i].relarch[0]) { case 'n': archiveMode = "none"; break; case 'h': archiveMode = "heavy"; break; case 'l': archiveMode = "light"; break; default: fprintf(stderr, "unknown archive mode\n"); archiveMode = "none"; break; } sprintf(q, "%s archive = %s;\n", q, archiveMode); fputs(q,fout); } } } /* * dumpIndices: * write out to fout all the user-define indices */ void dumpIndices(FILE* fout, IndInfo* indinfo, int numIndices, TableInfo* tblinfo, int numTables, const char *tablename) { int i; int tableInd; const char *attname; /* the name of the indexed attribute */ char *funcname; /* the name of the function to comput the index key from*/ int indkey; char q[MAXQUERYLEN]; PGresult *res; for (i=0;iconn, copybuf, COPYBUFSIZ); if (copybuf[0] == '\\' && copybuf[1] == '.' && copybuf[2] == '\0') { copydone = true; /* don't print this... */ } else { fputs(copybuf, fout); switch (ret) { case EOF: copydone = true; /*FALLTHROUGH*/ case 0: fputc('\n', fout); break; case 1: break; } } } fprintf(fout, "\\.\n"); PQclear(res); PQendcopy(res->conn); } else { sprintf(query, "select * from %s;\n", classname); res = PQexec(g_conn, query); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"dumpClasses(): command failed"); exit_nicely(g_conn); } tuple=0; while(tuple < PQntuples(res)) { fprintf(fout, "insert into %s ", classname); if (attrNames) { actual_atts = 0; sprintf(q, "("); for (j=0;j 0) ? "," : "", tblinfo[i].attnames[j]); actual_atts++; } } sprintf(q,"%s%s",q, ") "); fprintf(fout, q); } fprintf(fout, "values ("); field=0; do { switch(PQftype(res,field)) { case 21: case 22: case 23: /* int types */ case 810: case 910: /* oldint types */ case 1005: case 1006: case 1007: /* _int types */ case 700: case 701: /* float types */ case 1021: case 1022: /* _float types */ fprintf(fout, "%s", PQgetvalue(res,tuple,field)); break; default: /* Before outputing string value, expand all single quotes to twin single quotes - dhb - 6/11/96 */ expsrc=PQgetvalue(res,tuple,field); expdest=expandbuf; while (*expsrc) { *expdest++=*expsrc; if (*expsrc == (char)0x27) /*sing. quote*/ *expdest++ = *expsrc; expsrc++; } *expdest=*expsrc; /* null term. */ fprintf(fout, "'%s'", expandbuf); break; } field++; if(field != PQnfields(res)) fprintf(fout, ","); } while(field < PQnfields(res)); fprintf(fout, ");\n"); tuple++; } PQclear(res); } } } } /* * dumpTuples -- * prints out the tuples in ASCII representation. The output is a valid * input to COPY FROM stdin. * * We only need to do this for POSTGRES 4.2 databases since the * COPY TO statment doesn't escape newlines properly. It's been fixed * in Postgres95. * * the attrmap passed in tells how to map the attributes copied in to the * attributes copied out */ void dumpTuples(PGresult *res, FILE *fout, int* attrmap) { int j, k; int m, n; char **outVals = NULL; /* values to copy out */ n = PQntuples(res); m = PQnfields(res); if ( m > 0 ) { /* * Print out the tuples but only print tuples with at least * 1 field. */ outVals = (char**)malloc(m * sizeof(char*)); for (j = 0; j < n; j++) { for (k = 0; k < m; k++) { outVals[attrmap[k]] = PQgetvalue(res, j, k); } for (k = 0; k < m; k++) { char *pval = outVals[k]; if (k!=0) fputc('\t', fout); /* delimiter for attribute */ if (pval) { while (*pval != '\0') { /* escape tabs, newlines and backslashes */ if (*pval=='\t' || *pval=='\n' || *pval=='\\') fputc('\\', fout); fputc(*pval, fout); pval++; } } } fputc('\n', fout); /* delimiter for a tuple */ } free (outVals); } } /* * setMaxOid - * find the maximum oid and generate a COPY statement to set it */ void setMaxOid(FILE *fout) { char query[255]; PGresult *res; Oid max_oid; res = PQexec(g_conn, "CREATE TABLE pgdump_oid (dummy int4)"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"Can not create pgdump_oid table\n"); exit_nicely(g_conn); } PQclear(res); res = PQexec(g_conn, "INSERT INTO pgdump_oid VALUES (0)"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"Can not insert into pgdump_oid table\n"); exit_nicely(g_conn); } max_oid = atol(PQoidStatus(res)); if (max_oid == 0) { fprintf(stderr,"Invalid max id in setMaxOid\n"); exit_nicely(g_conn); } PQclear(res); res = PQexec(g_conn, "DROP TABLE pgdump_oid;"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"Can not drop pgdump_oid table\n"); exit_nicely(g_conn); } PQclear(res); if (g_verbose) fprintf(stderr, "%s maximum system oid is %d %s\n", g_comment_start, max_oid, g_comment_end); fprintf(fout, "CREATE TABLE pgdump_oid (dummy int4);\n"); fprintf(fout, "COPY pgdump_oid WITH OIDS FROM stdin;\n"); fprintf(fout, "%-ld\t0\n", max_oid); fprintf(fout, "\\.\n"); fprintf(fout, "DROP TABLE pgdump_oid;\n"); } /* * findLastBuiltInOid - * find the last built in oid * we do this by looking up the oid of 'template1' in pg_database, * this is probably not foolproof but comes close */ int findLastBuiltinOid(void) { PGresult* res; int ntups; int last_oid; res = PQexec(g_conn, "SELECT oid from pg_database where datname = 'template1';"); if (res == NULL || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"pg_dump error in finding the template1 database"); exit_nicely(g_conn); } ntups = PQntuples(res); if (ntups != 1) { fprintf(stderr,"pg_dump: couldn't find the template1 database. You are really hosed\nGiving up\n"); exit_nicely(g_conn); } last_oid = atoi(PQgetvalue(res, 0, PQfnumber(res, "oid"))); PQclear(res); return last_oid; } /* * checkForQuote: * checks a string for quote characters and quotes them */ char* checkForQuote(const char* s) { char *r; char c; char *result; int j = 0; r = malloc(strlen(s)*3 + 1); /* definitely long enough */ while ( (c = *s) != '\0') { if (c == '\'') { r[j++] = '\''; /* quote the single quotes */ } r[j++] = c; s++; } r[j] = '\0'; result = strdup(r); free(r); return result; }