Issues using pgSQLDump

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Issues using pgSQLDump

Post by leeottaway » Wed 09 Jan 2008 14:22

I have just upgraded from the standard version of pgSQLDirect so I can use the backup functionality in pgSQLDump. However I'm having a couple of major issues with it:

1. Firstly I use the following code to create the backup:

oDump = New CoreLab.PostgreSql.PgSqlDump
oDump.Connection = gConCS
oDump.IncludeDrop = True
oDump.Mode = CoreLab.PostgreSql.DumpMode.All
oDump.Backup()
sw = New StreamWriter(gsAppFolderPath & "\backup.sql")
sw.WriteLine(oDump.DumpText)
sw.Close()
sw.Dispose()

When this is executed it takes around 1 second to create a 63k file which looks like it contains some schema information but nothing else. Even though there's around 400000 records in various tables I can;t see any data in the backup file.

2. How do I then restore this data? I use the following code to do so:

scr = New CoreLab.PostgreSql.PgSqlScript("CREATE DATABASE " & txtName.Text & " ENCODING = 'WIN1252';", con)
scr.Execute()
scr.Dispose()
con.Close()
con.Dispose()
con = New CoreLab.PostgreSql.PgSqlConnection("User ID=admin;Password=admin;Host=" & gsHost & ";Port=5432;Database=" & txtName.Text & ";Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;")
con.Open()

oRestore = New CoreLab.PostgreSql.PgSqlDump
oRestore.Connection = con
oRestore.Restore(gsAppFolderPath & "\backup.sql")

When I open pgAdmin although the database is created there are no tables and no data.

Help!

Lee Ottaway

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 09 Jan 2008 16:35

Try to peek into the provided example, particularly in “DumpDemoControl.*”.
You can find the solution in Core Lab PostgreSQLDirect for .NET2 -> Samples -> WinForms in "Start" menu

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Wed 09 Jan 2008 17:58

OK, well it's getting better in that I can now see my tables and data being backed up, however I now get an "Exception of type 'System.OutOfMemoryException' was thrown."

The stack trace is as follows:

System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
System.Text.StringBuilder.Append(String value)
System.IO.StringWriter.Write(String value)
CoreLab.PostgreSql.PgSqlDump.a(PgSqlDataReader A_0, String A_1, String A_2, TextWriter A_3)
CoreLab.PostgreSql.PgSqlDump.Backup(TextWriter writer)
CoreLab.PostgreSql.PgSqlDump.Backup()

This also occurs when using your demo to backup my database.

I am running on a machine that has 2GB with 1.5GB free which is lot more than most of our customers have therefore this is currently a real problem for us.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Jan 2008 12:04

Ok, so now you do have the backed up files, don't you?
Could specify the block of code, where exactly this exception occurred?
It doesn't seem to be connected with CoreLab.PostgreSql.PgSqlDump class.
Do you get this error at a constant place or the location changes?

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 14:30

Errr. no actually it doesn't create any backup file at all. Obviously it's building the backup internally within the pgsqldump class and then falling over because the string it's creating is too large.

It therefore does not dump anything to a file so there's no way of knowing at which point pgsqldump falls over.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Jan 2008 14:42

Please send me a small test project to reproduce the problem.
It is desirable to use 'test' schema objects, otherwise include the
definition of your own database objects.
I will fill the DB with 400 000 rows as you have mentioned.
Do not use third party components.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 15:08

There's no need to create a small test project. Just use the pgsqldemo that you suggested I look at earlier. It comes up with the same error.

The problem occurs on tblCoursesTreatments which is one of the bigger tables and only occurs after backing up around 20 other tables first. If I back up that one table on it's own then it works fine.

How do I submit a large database file to you?

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Jan 2008 15:30

We will test the PgSqlDump class. I will let you know about our results in the nearest time.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 15:37

Forget it, I got it working using the following code:

oDump = New CoreLab.PostgreSql.PgSqlDump
oDump.Connection = gConCS
oDump.IncludeDrop = True
oDump.Mode = CoreLab.PostgreSql.DumpMode.All
oDump.Tables = GetTableNames()
oDump.Backup(gsAppFolderPath & "\backup.sql")

This works fine, however if I use the method used in your demo which is:

oDump = New CoreLab.PostgreSql.PgSqlDump
oDump.Connection = gConCS
oDump.IncludeDrop = True
oDump.Mode = CoreLab.PostgreSql.DumpMode.All
oDump.Tables = GetTableNames()
oDump.Backup()
sw = New StreamWriter(gsAppFolderPath & "\backup.sql")
sw.WriteLine(oDump.DumpText)
sw.Close()
sw.Dispose()

Then it dies whilst doing the backup with an Out of Memory error.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 16:05

OK, next problem. I now have a successfully backed up file that contains both schema and data.

I then create a database, create a connection to that database and then try to restore the file using:

oRestore = New CoreLab.PostgreSql.PgSqlDump
oRestore.Connection = con
oRestore.Mode = CoreLab.PostgreSql.DumpMode.All
oRestore.Tables = GetTableNames()
oRestore.Restore(gsAppFolderPath & "\backup.sql")

Now, if I backup with IncludeDrop = True, then when I restore it using the code above it says "tblAccounts does not exist" obviously because it's trying to drop a table that doesn;t exist in the newly created database, but if set IncludeDrop = False then it comes up with "schema "public" already exists".

This one is probably me just being stupid and not understanding how this works, however your demo doesn;t make it clearer since all it calls is Me.dump.Restore()

Can you point me in the right direction.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Jan 2008 16:17

Now it's clear what's going on.
We wrote samples for small databases, not for commercial usage. Thus we used

Code: Select all

public void Backup();
The method generates a script and writes it to the “Restore” property, which is of course limited.
If you dump your database directly to a file, everything is ok.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 16:36

OK, next problem. I now have a successfully backed up file that contains both schema and data.

I then create a database, create a connection to that database and then try to restore the file using:

oRestore = New CoreLab.PostgreSql.PgSqlDump
oRestore.Connection = con
oRestore.Mode = CoreLab.PostgreSql.DumpMode.All
oRestore.Tables = GetTableNames()
oRestore.Restore(gsAppFolderPath & "\backup.sql")

Now, if I backup with IncludeDrop = True, then when I restore it using the code above it says "tblAccounts does not exist" obviously because it's trying to drop a table that doesn;t exist in the newly created database, but if set IncludeDrop = False then it comes up with "schema "public" already exists".

This one is probably me just being stupid and not understanding how this works, however your demo doesn;t make it clearer since all it calls is Me.dump.Restore()

Can you point me in the right direction.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 16:55

And yet another problem. Phew, never thought it would be this difficult!

It appears that your pgsqldump cannot handle field names with spaces in them. or example here's the backup file for tblAccounts:

CREATE TABLE public."tblAccounts"
(
Date date,
Number integer,
Course ID bigint,
Description varchar(50),
Debit double precision,
Credit double precision
);

This fails because there are no quotes around the fields names.

Any idea how to get around this as many of my fields have spaces in them and it is supported by pgSQL of course.

leeottaway
Posts: 30
Joined: Sat 10 Nov 2007 20:36

Post by leeottaway » Thu 10 Jan 2008 17:05

OK Found the QuoteIdentifier property of pgsqldump which works fine. I thought I'd just try restoring the backup to the same database that it came from without trying to be clever and recreate another database using the backup however when i do this it comes up with "cannot drop table "tblAppointmentHistory" because other objects depend on it"

How do I get around this?

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Jan 2008 17:19

OK, next problem. I now have a successfully backed up file that contains both schema and data.
It's kind of hard to reproduce the problem, because some code executes behind the scenes.
Where exactly do you get an exception?
I suggest you do the following changes:

Code: Select all

oRestore = New CoreLab.PostgreSql.PgSqlDump; 
oRestore.Connection = con; 
oRestore.Mode = CoreLab.PostgreSql.DumpMode.All; 	// it's excess, delete the row
oRestore.Tables = GetTableNames(); 			        // it's excess, delete the row
oRestore.IncludeDrop = true; 				           // or false (as you need) oRestore.Restore(gsAppFolderPath & "\backup.sql"); 
Perhaps you get an error in “GetTableNames()” method?[/quote]

Post Reply