Sorry guys - Still cannot restore from PgSqlDump backup

[kerrywales]

Using build 146 I am backing up to file. As previous discussed in other thread

Devart.Data.PostgreSql.PgSqlDump DataBackup = new Devart.Data.PostgreSql.PgSqlDump();
DataBackup.Connection = myConnection.getConnection();
DataBackup.IncludeBlob = true;
DataBackup.CreateConstraints = true;
DataBackup.IncludeDrop = false;
DataBackup.IncludeUsers = false;
DataBackup.GenerateHeader = true;
DataBackup.Mode = Devart.Data.PostgreSql.DumpMode.All;
string name = "data.backup";
DataBackup.Backup(name);

backups up the whole db. This produces a 215Mb file.

Now to test the restore:

Test core code is

Devart.Data.PostgreSql.PgSqlDump DataRestore = new Devart.Data.PostgreSql.PgSqlDump();
DataRestore.Connection = myConnection.getConnection();
if (File.Exists(myFile))
{
DataRestore.Restore(myFile);
DataRestore.Connection.Commit();
}
myConnection.Logout();
}


My Backup program before Restore starts is showing a memory working set of 28Mb and Task Mgr also says I am using 26% of phys memory.

I start the restore and watch task manager.

1st attempt laptop blue screened after about 15 mins. Wasn't watching Task Manager this time around.
2nd attempt I saw the Memory Working Set grow to 1.6Gb and 84% of phys memory in use. Then I get an error.

Two issues:

1. Why so much memory need? How big must a machine be to use your dump method?

2. The error was:

"Error in File Restore or Update on Table "TblClientName" violates foreign key contraint "TblName_TblClientName""

Since the backup contains everything needed to restore why the error?

I am using basically the same dataset as I sent/uploaded to you guys when I reported the error in build 140.


If I should be doing something different in the restore please let me know.

HELPPPPPPP!!

I want to use your mechanism.

Kerry

[kerrywales]

Just to add to the first part.
I thought I would try and backup the schema and data separately and then restore the schema followed by restoring the data.

Restoring the schema seems to be ok. The structure gives me 120 tables and 120 sequences but the data fails on the first insert.

Lokks as though this is due to the data backup contaning ID references to other tables which have yet to be restored.

I suppose what we need is the ability to restore data ignoring the constraints and then building them at the end when all the core data is restored.

AHHHHHHHH!!!!

[kerrywales]

More comments to add. Tried Build 152.

Same painful result.

Have a workaround for some of issues.
For the schema backup I set the following

Code: Select all

Devart.Data.PostgreSql.PgSqlDump SchemaBackup = new Devart.Data.PostgreSql.PgSqlDump();
SchemaBackup.Mode = Devart.Data.PostgreSql.DumpMode.Schema;
SchemaBackup.GenerateHeader = false;
SchemaBackup.CreateConstraints = true;

For the data backup I use:

Code: Select all

Devart.Data.PostgreSql.PgSqlDump DataBackup = new Devart.Data.PostgreSql.PgSqlDump();
DataBackup.IncludeBlob = true;
DataBackup.CreateConstraints = false;
DataBackup.IncludeDrop = false;
DataBackup.IncludeUsers = false;
DataBackup.GenerateHeader = true;

I then restore the schema into a blank database.
Before restoring the data I run the following code:

Code: Select all

Devart.Data.PostgreSql.PgSqlCommand command1 = null;
Devart.Data.PostgreSql.PgSqlCommand command2 = null;
try
{
   DataTable tables = null;
   if (myConnection.Login())
   {
      tables = myConnection.getConnection().GetSchema("Tables", new string[] { "public" });
      myConnection.Logout();
   };
   
   if (myConnection.Login())
   {
      foreach (DataRow row in tables.Rows)
      {
         string tname = row["name"].ToString();
         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("Error disabling triggers: "+ex.Message); };
      }
      myConnection.getConnection().Commit();
      myConnection.Logout();
   };
}
catch (Exception executeCommand) { MessageBox.Show("Error in file restore: " + executeCommand.Message); };

After the data restore I loop through the tables again enabling the triggers and then I run a VACUUM.

I did a restore on my test DB and I got no errors.
Hurrahhhh.

Flushed with success I thought I would try it with one of my live databases.

This produces a 245Mb data backup file. And this is not the largest!!!

I started the restore.

It got to 88% physical memory used, and the app was using 1.8Gb of Working Set memory when I got an Out of Memory Exception.

Why on earth does the restore method need so much space?

Kerry

[StanislavK]

As for restoring data separately from schema, a possible option is to disable constraints before and enable them after inserting data. We are investigating possibilities of inserting data using PgSqlDump only (i.e., without execution of auxiliary commands), but cannot provide any timeframe for this.

As for the OutOfMemory error, we couldn't reproduce it. A program based on the code you've specified takes up to 30 Mb of memory and this amount does not increase in our environment. Could you please specify the operation system installed on your laptop, its capacity and the IDE you are using? Also, do you use dbMonitor when performing data restore?

[kerrywales]

The splitting of schema & data was purely to try and get around the memory issue.

My test/development laptop is a Toshiba Satellite Pro. I have dual core & 4 Gb RAM. Running Vista Ultimate (someone in the office has to have it for testing and I drew the short straw). Postgres is 8.4.4.1. Devart is Build 152. IDE is VS 2008. All updates are done.
Looking at status using TaskManager.
Pg is installed on laptop as is. Not tuning, no changing of conf files. Just as is.

I have no control over what sort of setup I may be restoring, hence vanilla test.

If you have a parameter activated in the conf which would be regarded as essential and sensible to set then I can look to make a condition of the applet.

I would take your advice here.

The database has a lot of char fields. This means that the dump script has a lot of space (hence a 235Mb file zips to 16M). Could there be an option to ask pgSqlDump to trim the char fields for the backup script? Help on space.

Running again Task Manager is already up to to 1Gb (This is on a test run where I have set a few conf param's to see if it would help). The starting size before processing the data.backup script was 50Mb. The data.backup script was 245Mb. I would attach a jpg showing the Task Mgr screen but there is no allowance for it.

The only time I do not see it growing in size is when I am running the restore on a db that already has populated tables. My tests are always to newly blank database

At the time of getting to the end of this message it is on 1.4Gb and 93% of phys memory in use.

Kerry[/img]

[kerrywales]

Never used or looked at dbMonitor before.

Had a quick read. Added it to app and run it.

Interesting!!!!!

Whatever it does to the restore, can we do it without the dbMonitor component?

All the code was left the same except adding two lines

dbMonitor.IsActive = true;
before starting the data restore
dbMonitor.IsActive = false;
after the restore.

TaskManager now shows a peak of 150Mb. It was incredibly slow. But it did work. I presume the component has an effect of flushing memory?

Kerry

[kerrywales]

Just thought I would feed back some timings.

Backup is of a 128 table (smallish database from pg 8.4.4.1 installation)
pg_dump backup of all (compressed) is 16Mb
pgSqlDump of all (zipped up) is 17Mb

pgSqlDump restore needs to use script. This is 249Mb.
In order for restore to work (no errors) I have to have a pgSqlMonitor component on the form. UseApp needs to be true (default) but you don't need to run dbMonitor. I set IsActive immediately before the restore method call and switch it off afterwards. If I don't I get an out of memory error.

I got the program to time the pgSqlDump.Restore command including the unzip. The zip file was on my laptop (Tosh Satellite Pro Dual Core, 4Gb RAM, 160Gb HD (high speed & bigger cache - not normal disk)) Pg is also on my laptop.
Task Manager showed program starting at 16Mb.
RAM in use at end of restore 64Mb
Peak RAM in use 143Mb
time taken 1 hour 4 mins to restore

For some reason (not really looked why) I could not restore the pg_dump file to my laptop pg, so I restored it to a pg server across the network on simple server (which is also my office file server and WAN server so it is well used). Restored from compressed file.
Task Manager showed program starting at 6Mb and it didn't change more than a few hundred k. There may have been processes elsewhere but nothing stood out on my screen.
time taken 5 minutes.

I will time the pgSqlDump restore across the network but not at the moment as it is time to go home. But I cannot see me making up 1 hour.

Kerry

[StanislavK]

Thank you for your suggestion on fixed-length character fields, we will investigate the possibility of trimming them during backup.

The dbMonitor application is designed for tracing interoperations with database performed by Devart data providers. The PgSqlMonitor class provides functionality for connecting to dbMonitor.exe. For detailed information about PgSqlMonitor, please refer to the corresponding topic of our documentation:
http://www.devart.com/dotconnect/postgr ... nitor.html
Actually, PgSqlMonitor can slow down the execution of your application as each command executed is sent to dbMonitor.exe, but it should not affect the way PgSqlDump works with memory.

Could you please specify the stack trace of the out of memory exception you are getting? Also, did you connect to a local or remote PostgreSQL server when you got this exception?

[StanislavK]

We have implemented trimming of fixed-length char fields during back up. Also, we have added the Constraints element into the PgSqlDumpObjects enumeration; using this element you can restore the database schema, insert data, and create constraints only after data is inserted.

This new functionality 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