Thank you for investigating.
For other poor souls trying this I though I would let you know where I have got to and what I have found.
1. Make sure that ALL tables are owned by the same user and that ALL constraints are owned by the same user. It is possible to have constraints set up between two tables owned by different users which pg_dump and pg_restore do not mind but pgsqldump does.
2. Do not dump the data out as data only the restore moans like mad.
3. Do dump the information in separate files.
4. Check that people who manage the database do NOT put in funny characters. In doing this exercise I identified that a user had a constraint name with a '"' (double quotes) contained within it. Again pg_dump & pg_restore was happy with this. The Devart mechanism was not.
I use Views, indexes and constraints to define relationships within the DB. The restoring works once the name of the constraint was modified (see previous comment).
Extracts from my backup/restore test code:
Code: Select all
var dataBackup = new PgSqlDump()
{
Connection = myConnection.getConnection(),
QuoteIdentifier = true,
IncludeBlob = true,
IncludeDrop = false,
IncludeUsers = false,
GenerateHeader = false,
ObjectTypes = PgSqlDumpObjects.All ^ PgSqlDumpObjects.Users ^ PgSqlDumpObjects.Languages
^ PgSqlDumpObjects.Views ^ PgSqlDumpObjects.Indexes ^ PgSqlDumpObjects.Constraints,
Mode = DumpMode.All
};
string name = Path.Combine(Destination, "dump_data.backup");
dataBackup.Backup(name);
Code: Select all
var viewsBackup = new PgSqlDump()
{
Connection = myConnection.getConnection(),
QuoteIdentifier = true,
GenerateHeader = false,
ObjectTypes = PgSqlDumpObjects.Views
};
name = Path.Combine(Destination, "dump_views.backup");
viewsBackup.Backup(name);
Code: Select all
var indiciesBackup = new PgSqlDump()
{
Connection = myConnection.getConnection(),
QuoteIdentifier = true,
GenerateHeader = false,
ObjectTypes = PgSqlDumpObjects.Indexes
};
name = Path.Combine(Destination, "dump_indicies.backup");
indiciesBackup.Backup(name);
Code: Select all
var constraintsBackup = new PgSqlDump()
{
Connection = myConnection.getConnection(),
QuoteIdentifier = true,
GenerateHeader = false,
ObjectTypes = PgSqlDumpObjects.Constraints
};
name = Path.Combine(Destination, "dump_constraints.backup");
constraintsBackup.Backup(name);
Restoring means I
1. Create an empty database of correct ownership etc
2. Restore in this order (I also zip up the backups):
Code: Select all
DoDevartRestore("Data", openWhat.InitialDirectory, openWhat.FileName, "dump_data.backup");
DoDevartRestore("Indicies", openWhat.InitialDirectory, openWhat.FileName, "dump_indicies.backup");
DoDevartRestore("Constraints", openWhat.InitialDirectory, openWhat.FileName, "dump_constraints.backup");
DoDevartRestore("Views", openWhat.InitialDirectory, openWhat.FileName, "dump_views.backup");
Code: Select all
private bool DoDevartRestore(string myMessage, string myDirectory, string zipFile, string myFile)
{
bool result = false;
try
{
...
string myFilePath = Path.Combine(myDirectory, myFile);
try
{
if (File.Exists(myFilePath))
File.Delete(myFilePath);
}
catch { };
...
RunDevartRestore();
result = true;
}
}
catch { };
return result;
}
Code: Select all
public void RunDevartRestore()
{
try
{
CSLConnect myConnection = new CSLConnect(teUser.Text,tePassword.Text, tePort.Text, teHost.Text, teDB.Text);
if (myConnection.Login())
{
using (PgSqlDump DataRestore = new PgSqlDump { Connection = myConnection.getConnection() })
{
if (File.Exists(RestoreString))
{
...
DataRestore.Restore(RestoreString);
myConnection.Logout();
File.Delete(RestoreString);
}
}
}
}
catch (Exception ex)
{
Message = ex.Message;
}
}
Timings
I have a pg_dump & pg_restore program with a wrapper as well. I tested the routine with the same database.
pg_dump & pg_restore to do both took less than 5 mins
Using the devart method it took 7 minutes to backup and 1 hr 10 mins to restore