Issues using pgSQLDump
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
Issues using pgSQLDump
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
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
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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.
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.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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.
It therefore does not dump anything to a file so there's no way of knowing at which point pgsqldump falls over.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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?
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?
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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.
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.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20: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.
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.
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Now it's clear what's going on.
We wrote samples for small databases, not for commercial usage. Thus we used
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.
We wrote samples for small databases, not for commercial usage. Thus we used
Code: Select all
public void Backup();
If you dump your database directly to a file, everything is ok.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20: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.
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.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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.
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.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
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?
How do I get around this?
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
It's kind of hard to reproduce the problem, because some code executes behind the scenes.OK, next problem. I now have a successfully backed up file that contains both schema and data.
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");