I figured others might find this useful. I use it to make a blank
database based on the master database, putting the resulting database on
the subscriber end and subscribing into the blank database. If someone
wants to they could add this to the repository.
Here is the usage:
Arguments: [-dv] [-U username] url
where -d is for a dry run, otherwise it modifies the database in place.
Note that this removes foreign keys and data so be careful. It requires the
java getopts jar + postgres jdbc jar.
Also, for whatever reason you will need to execute this SQL:
CREATE FUNCTION information_schema._pg_keypositions() RETURNS SETOF
integer
LANGUAGE sql
IMMUTABLE
AS 'select g.s
from generate_series(1,current_setting(''max_index_keys'')::int,1)
as g(s)';
before running the program.
-Brett
package org.postgresql.gborg;
import gnu.getopt.Getopt;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import org.postgresql.util.PSQLException;
/**
*
* Given a jdbc url to a database, remove all foreign keys and rows from every table
*
* @author brett
*
* Arguments: [-dv] [-U username] url
*
* where -d is for a dry run
*/
public class MakeBlankDB {
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
try {
Getopt g = new Getopt("testprog", args, "vdU:");
g.setOpterr(false); // We'll do our own error handling
int c;
boolean dryrun = false;
boolean verbose = false;
String username = "postgres";
//
while ((c = g.getopt()) != -1) {
switch (c) {
case 'd':
dryrun = true;
break;
case 'U':
username = g.getOptarg();
break;
case 'v':
verbose = true;
break;
default:
System.err.println("Unexpected token: " + (char) c);
printUsage();
System.exit(1);
break;
}
}
int idxleft = g.getOptind();
if (idxleft > args.length - 1) {
System.err.println("Expected URL");
printUsage();
System.exit(1);
}
String url = args[idxleft];
System.out.println("url = " + url);
Connection connection = getJdbcConnection(url, username, null);
DatabaseMetaData dbmd = connection.getMetaData();
if (verbose) {
System.out.println("Connection to "
+ dbmd.getDatabaseProductName() + " "
+ dbmd.getDatabaseProductVersion() + " successful.\n");
;
}
removeForeignKeysAndData(connection, verbose, dryrun);
} catch (Exception e) {
e.printStackTrace(System.err);
System.exit(1);
}
}
private static void removeForeignKeysAndData(Connection connection,
boolean verbose, boolean dryrun) throws SQLException {
DatabaseMetaData dbmd = connection.getMetaData();
String[] types = { "TABLE" };
ResultSet tableNames = dbmd.getTables(null, "public", "%", types);
connection.setAutoCommit(false);
boolean error = false;
HashMap<String, Object> fkeyremovedmap = new HashMap<String, Object>();
try {
//dbmd.getExportedKeys(catalog, schema, table)
while (tableNames.next()) {
String tableName = tableNames.getString("TABLE_NAME");
if (verbose) {
System.out.println("Found table name of " + tableName);
}
ResultSet keys = dbmd
.getExportedKeys(null, "public", tableName);
while (keys.next()) {
String fkname = keys.getString("FK_NAME");
if (verbose) {
System.out.println("\tFound foreign key " + fkname);
}
String fktablename = keys.getString("FKTABLE_NAME");
System.out.println("fktablename = " + fktablename
+ ", table name = " + tableName);
String sql = "ALTER TABLE " + fktablename
+ " DROP CONSTRAINT " + fkname;
if (verbose) {
System.out.println("sql = " + sql);
}
if (fkeyremovedmap.get(fkname) == null) {
if (!dryrun) {
Statement stmt = connection.createStatement();
try {
stmt.execute(sql);
} catch (PSQLException pe) {
throw pe;
}
}
fkeyremovedmap.put(fkname, new Object());
} else {
System.out.println("Duplicate key " + fkname);
}
}
}
tableNames = dbmd.getTables(null, "public", "%", types);
if (!dryrun) {
while (tableNames.next()) {
String tableName = tableNames.getString("TABLE_NAME");
if (verbose) {
System.out.println("Deleting from table name of "
+ tableName);
}
String sql = "DELETE FROM " + tableName;
Statement stmt = connection.createStatement();
try {
stmt.execute(sql);
} catch (PSQLException pe) {
throw pe;
}
}
}
} catch (RuntimeException e) {
error = true;
}
if (!error) {
connection.commit();
} else {
connection.rollback();
}
}
public static Connection getJdbcConnection(String url, String username,
String password) {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //load the driver
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} //connect to the db
return conn;
}
private static void printUsage() {
System.err.println("Arguments: [-dv] [-U username] url");
}
}
_______________________________________________
Slony1-general mailing list
[email protected]
http://gborg.postgresql.org/mailman/listinfo/slony1-general
