Restore produces an out of memory error

kerrywales · Post by **kerrywales** » Wed 19 May 2010 21:38

Can someone help me please. I am carrying out what I thought would be a simple task. That is to create a backup and restore program for my databases. My test database produces a backup.dump file of 220Mb.

For completeness, at the end of the message I show both the backup and restore functions using the Devart library. This also shows how it is being Backed up. But it is the Restore that I am having an issue with

I have messed around with the code for 5 days with various technique permutations and still have not found a working configuration.

Thank you for your help.

The goal is to produce a backup which is zipped up in a secure file. I am using the free DotNetZip mechanism compress and password protect the backup. (I can recommend this mechanism to others). I have got to the point where this is not causing me any issues. I am backing up the data and the schema independently of each other. My working backup function is as follows:

public bool DoDumpNetZip()
{
bool result = false;
try
{
//I put the connection in a class called from my form
Connect myConnection = new Connect(userName, Pass, port, hostName, dbName);
if (myConnection.Login()) //my own method of the class to login
{
Devart.Data.PostgreSql.PgSqlDump DataBackup = new Devart.Data.PostgreSql.PgSqlDump();

//get connection is a method returning the connection
DataBackup.Connection = myConnection.getConnection();

DataBackup.IncludeBlob = true;
DataBackup.CreateConstraints = false;
DataBackup.GenerateHeader = true;

DataBackup.Mode = Devart.Data.PostgreSql.DumpMode.Data;

//The biggest backup stream tested so far is around 300Mb
//and it worked – well it has produced a file
MemoryStream myStream = new MemoryStream();
DataBackup.Backup(myStream);

//This is my class to create a password protected zip file
CSLZip myZip = new CSLZip();
result = myZip.ZipFromStream(myStream, destination + backupName + ".zip");
myStream.Close();

//now backup the schema and add it the created zip file
DataBackup.Mode = Devart.Data.PostgreSql.DumpMode.Schema;
myStream = new MemoryStream();
DataBackup.Backup(myStream);
result = myZip.ZipFromStreamAdd(myStream, destination + backupName + ".zip","schema.backup");

myConnection.Logout();

}
}
catch (Exception executeCommand) { MessageBox.Show(executeCommand.Message); };
return result;
}

//The above code works fine. As far as I can see and the files appear to be correctly created.

Now to the Restore.
The goal is NOT to have to restore to a blank new database as this requires postgresql rights the the program user will not have, but to restore data to the existing structure.

Issues:
Restore will not restore data from the dump into tables with existing data without errosrs.

If the data rows are deleted the Restore gives errors regarding the constraints.

The version below
a. truncates all the data in the tables
b. disable triggers so the constraints are not an issue

Now I get the OutOfMemory error and or a total OS dump of the system.

Using the Task Monitor on my Vista Ultimate 3Gb RAM laptop which has a local copy of postgresql 8.4 with the latest Devart (build 4.90.124). At the point of the Restore method is called it shows Memory in use is 1.25Gb out of 3. The dump file is 220Mb in size.
All I see in the Task Manager is the Memory grow and grow till I get an Out of Memory error. The restore function is:

//Calls to unzip the backup.dump (data backup) is done before calling this
public bool DoRestoreFromFile(string myFile)
{
bool result = false;
Devart.Data.PostgreSql.PgSqlCommand command1 = null;
Devart.Data.PostgreSql.PgSqlCommand command2 = null;
try
{
CSLConnect myConnection = new CSLConnect(userName, Pass, port, hostName, dbName);

DataTable tables = null;
if (myConnection.Login())
{
tables = myConnection.getConnection().GetSchema("Tables", new string[] { "public" });
myConnection.Logout();
};

//Clear down the datatables except TblAdmin
if (myConnection.Login())
{
foreach (DataRow row in tables.Rows)
{
string tname = row["name"].ToString();
if (tname.ToUpper() != "TBLADMIN")
{
//If I don’t disable the triggers then the restore data fails
//because of constraints. I use TRUNCATE as it VACUUM’s as part
//of its process
//I would prefer NOT to use raw SQL call but feel I have
//no choice
command2 = new Devart.Data.PostgreSql.PgSqlCommand
("ALTER TABLE \"" + tname + "\" DISABLE TRIGGER ALL", myConnection.getConnection());
command1 = new Devart.Data.PostgreSql.PgSqlCommand
("TRUNCATE TABLE \"" + tname + "\" RESTART IDENTITY CASCADE", myConnection.getConnection());
try
{
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
}
catch (Exception ex) { MessageBox.Show(ex.Message); };
}
}
myConnection.getConnection().Commit();
myConnection.Logout();
};

if (myConnection.Login())
{
Devart.Data.PostgreSql.PgSqlDump DataRestore = new Devart.Data.PostgreSql.PgSqlDump();

DataRestore.Connection = myConnection.getConnection();

//It is during the next call that I get Out of Memory
DataRestore.Restore(myFile);

//Never get to this section of code so I don’t know
//if it makes sense yet

DataRestore.Connection.Commit();
myConnection.Logout();
}

if (myConnection.Login())
{

foreach (DataRow row in tables.Rows)
{
string tname = row["name"].ToString();
command1 = new Devart.Data.PostgreSql.PgSqlCommand
("ALTER TABLE \"" + tname + "\" ENABLE TRIGGER ALL", myConnection.getConnection());
try
{
command1.ExecuteNonQuery();
}
catch { };

};
myConnection.Logout();
};

result = true;
}
catch (Exception executeCommand) { MessageBox.Show(executeCommand.Message); };
return result;
}

StanislavK · Post by **StanislavK** » Fri 21 May 2010 14:33

At the moment, we couldn't reproduce the issue, but we will perform code review to find possible problems. We will notify you about the results of our investigation.

Also, it would be helpful if you send us a script for creating and filling the database objects with which the issue can be reproduced.

kerrywales · Post by **kerrywales** » Mon 24 May 2010 11:37

Thank you for your response. I have sent feedback off forum.
I have found one issue in that when using a stream it truncates some data at the end. I am trying to resolve which piece of code is responsible for the loss but that is for another day.

I have gone back to basics to identify the issue. I have Postgresql 4.90.124. Set up for locale (which is UK). The Db I am using to test is using Win1252 encoding. It has 120 Tables. There are 120 Sequences. All tables have at least 1 constraint. All tables (except 1) has at least 1 index.

The backup is now:

Devart.Data.PostgreSql.PgSqlDump DataBackup = new Devart.Data.PostgreSql.PgSqlDump();
DataBackup.Connection = myConnection.getConnection();
DataBackup.CreateConstraints = true;
DataBackup.Mode = Devart.Data.PostgreSql.DumpMode.All;
DataBackup.Backup("test.backup");

This produces a file "test.backup" 220,971Kb in size

The Restore code is now:
//Login sequence here
Devart.Data.PostgreSql.PgSqlCommand command1 = new Devart.Data.PostgreSql.PgSqlCommand();
command1.Connection = myConnection.getConnection();
command1.CommandText = "DROP DATABASE IF EXISTS \"NewTest\";";
command1.ExecuteNonQuery();
command1.CommandText = "CREATE DATABASE \"NewTest\" ENCODING 'WIN1252' TEMPLATE template0;";
command1.ExecuteNonQuery();
//Logout here
Devart.Data.PostgreSql.PgSqlDump DataRestore = new Devart.Data.PostgreSql.PgSqlDump();
myConnection.getConnection().Database = "NewTest";
if (myConnection.Login())
{
DataRestore.Connection = myConnection.getConnection();
DataRestore.Restore("test.backup");
}

Using pgAdmin following the backup routine being called I see:
120 Tables created
100 Sequences created
No indexes created
No Constraints created

I get an error (presumably the 121st sequence)
"relation "TbUsers_UserID_seq" does not exist"

Do not know what is causing this error. Is it the squence which Restore is creating the supporting structure?
There appears to be rows in all the relevant tables.

Does this help?

kerrywales · Post by **kerrywales** » Mon 24 May 2010 14:15

I can add to the previous message.

I started looking at the script created cross-referencing what pgAdmin says about the db.

I noticed that for other sequences (not looked at all 120) that there was an equivalent

CREATE SEQUENCE... line
which later had an
ALTER...
line.

For TbUsers_....
there is no CREATE SEQUENCE in the backup script but there is an ALTER.

Why would it not form the CREATE SEQUENCE line?
Is there a trigger which is missing from the db definition? or is this a bug?

Kerry

StanislavK · Post by **StanislavK** » Tue 25 May 2010 17:02

We've answered you by mail.

We've found several problems with backing up PostgreSQL sequences using PgSqlDump. We will investigate them and inform you about the results of our investigation.

kerrywales · Post by **kerrywales** » Mon 28 Jun 2010 08:58

In your email you were hoping for a new build within a couple of weeks. This has not happened yet. Do you have a date when the new pgsqldump / restore modifications will become available.

Kerry

StanislavK · Post by **StanislavK** » Wed 30 Jun 2010 15:00

We plan to release the nearest build in several days. We will inform you here when it is published.

kerrywales · Post by **kerrywales** » Wed 28 Jul 2010 10:20

I have just started retesting the code with build 146 and have a minor problem.

I am testing the PgSqlDump of the schema and then using pgAdmin to run the pgscript to recreate the database.

If I backup to a file name then it does work.
If I backup to a stream, then the stream seems to have characters truncated at the end.

Comparing the output of the two (by saving the stream to a file), I appear to be missing about 670 characters in my schema.

This is the code I use to create the dump of schema to stream and then to a file.

Is this an issue with the Backup method or an issue that I have to do something with the stream?

Kerry

DataBackup.Mode = Devart.Data.PostgreSql.DumpMode.Schema;
DataBackup.GenerateHeader = false;
DataBackup.CreateConstraints = true;

myStream = new MemoryStream();
DataBackup.Backup(myStream);

string theFile = destination + "schemastream.txt";
FileStream myFStream = new FileStream(theFile, FileMode.CreateNew);
myStream.WriteTo(myFStream);
myFStream.Close();

StanislavK · Post by **StanislavK** » Fri 30 Jul 2010 17:28

Thank you for your report, we've reproduced the problem. We will investigate it and inform you about the results.

StanislavK · Post by **StanislavK** » Fri 24 Sep 2010 16:49

We have fixed the problem with backing up into a stream. The fix is available in the new 4.95.170 build of dotConnect for PostgreSQL. The build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

The detailed information about the fixes and improvements implemented in dotConnect for PostgreSQL 4.95.170 is available at
http://www.devart.com/forums/viewtopic.php?t=19070