Issue creating backup with PgSqlDump

ccampbell · Post by **ccampbell** » Fri 21 Oct 2011 02:32

Hi, (using vb.net 2010, Devart.data.PostgreSql 5.50.228.0, PostgreSql 9.0.4)

I’m unable to use the pgSqlDump routine to create a file that I can then use to import back into the database. The issue is not with the restore routine (using the PgSqlScript command) as I’m able to call pg_dump.exe directly and create a backup file that works great when I go to import it back in. The issue is in the file that gets created using PgSqlDump. My guess is that I’m not doing something right so I will try and be as detailed as possible.

When I call Pg_Dump.exe directly, I use the following command line:

Code: Select all

System.Diagnostics.Process.Start(strPGDumpCommand, strParams)

Where

Code: Select all

strPgDumpCommand = "C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_dump.exe "
strParameters = "--file="c:\users\public\documents\DD3\Backup\DD3_000_000_20111020_070742.pg" --username="postgres" --no-password --disable-dollar-quoting --format=plain --inserts --column-inserts "dd3_000""

This creates my backup file

I then use the following code to import the backup file. This replaces whatever currently exists in the database:

Code: Select all

Dim strStreamRestore As New StreamReader(gclsSession.App_BackupFolder & "\" & mstrBackupFile)

conn = gclsSession.GetDataConnection(True, True)

sqlText = "DROP DATABASE """ & gclsSession.pg_DatabaseName & """;"
pgCommand = conn.CreateCommand()
pgCommand.CommandText = sqlText
pgCommand.ExecuteScalar()
conn.Close()

sqlText = "CREATE DATABASE """ & gclsSession.pg_DatabaseName & """"
sqlText = sqlText & " With OWNER = postgres"
sqlText = sqlText & " ENCODING = 'UTF8'"
sqlText = sqlText & " TABLESPACE = pg_default"
sqlText = sqlText & " LC_COLLATE = 'English_United States.1252'"
sqlText = sqlText & " LC_CTYPE = 'English_United States.1252'"
sqlText = sqlText & " CONNECTION LIMIT = -1;"

conn.Open()
pgCommand = conn.CreateCommand()
pgCommand.CommandText = sqlText
pgCommand.ExecuteScalar()

conn.Close()
gclsSession.CloseDataConnection()
conn = gclsSession.GetDataConnection(True, False)

sqlText = strStreamRestore.ReadToEnd()
strStreamRestore.Close()
pgScript = New PgSqlScript(sqlText, conn)
pgScript.Execute()
conn.Close()

This works great.
So instead of calling Pg_Dump.exe directly I want to use PgSqlDump to create the same file.

So here is the code I’m using:

Code: Select all

Dim pgDump As PgSqlDump = New PgSqlDump
pgDump.Connection = gclsSession.GetDataConnection()
pgDump.ObjectTypes = PgSqlDumpObjects.All - PgSqlDumpObjects.Languages
pgDump.IncludeDrop = False
pgDump.IncludeBlob = True
pgDump.QuoteIdentifier = False

pgDump.Backup()

Dim stream As StreamWriter = New StreamWriter(gclsSession.App_BackupFolder & "\" & strFileName)
stream.WriteLine(pgDump.DumpText)
stream.Close()
stream.Dispose()
stream = Nothing

The first indication of a problem is that the file created with PgSqlDump is half the size of the file created with pg_Dump.exe. When I run this file through the same routine as shown above it errors out, stating that various relations (tables) don’t exist. The table it claims doesn’t exist varies from backup to backup, even though the database that got backed up is identical each time.

So I’m kind of at a loss as to how to resolve this. Any help would be appreciated. Let me know if I can provide any other information.

Regards,

ccampbell · Post by **ccampbell** » Fri 21 Oct 2011 16:29

Okay having played around with this for the better part of a day I think I'm getting close but I'm still getting hung up with an error message which reads:

Code: Select all

syntax error at or near ")"

Here is my revised code for creating the backup file:

Code: Select all

        pgDump.Connection = gclsSession.GetDataConnection(blnBailApp, True, True)
        pgDump.ObjectTypes = PgSqlDumpObjects.All - PgSqlDumpObjects.Languages
        pgDump.Mode = Devart.Common.DumpMode.All
        pgDump.IncludeDrop = False
        pgDump.Backup()

Here is my revised code for restoring the backup:

Code: Select all

            If mblnDatabaseExists = True Then
                sqlText = "DROP DATABASE """ & gclsSession.pg_DatabaseName & """;"
                pgCommand = conn.CreateCommand()
                pgCommand.CommandText = sqlText
                pgCommand.ExecuteScalar()
            End If

            ' --------------------------------  Create Database
            sqlText = "CREATE DATABASE """ & gclsSession.pg_DatabaseName & """"
            sqlText = sqlText & " With OWNER = postgres"
            sqlText = sqlText & " ENCODING = 'UTF8'"
            sqlText = sqlText & " TABLESPACE = pg_default"
            sqlText = sqlText & " LC_COLLATE = 'English_United States.1252'"
            sqlText = sqlText & " LC_CTYPE = 'English_United States.1252'"
            sqlText = sqlText & " CONNECTION LIMIT = -1;"

            pgCommand = conn.CreateCommand()
            pgCommand.CommandText = sqlText
            pgCommand.ExecuteScalar()

            gclsSession.CloseDataConnection()
            conn = gclsSession.GetDataConnection(blnBailApp, True, False)

            sqlText = strStreamRestore.ReadToEnd()
            strStreamRestore.Close()
            pgScript = New PgSqlScript(sqlText, conn)
            pgScript.Execute()
            conn.Close()

I used to get a similar error when importing the file I created using pg_Dump.exe. However, by using the

--disable-dollar-quoting

parameter it resolved the problem. I don't see an equivalent parameter for the pgSqlDump object

I'm including the backup file that was created using the above code: Um, or maybe I'm not as I don't see a way to attach a file to this post. Hmmm... Is there a way to email it to someone?

Shalex · Post by **Shalex** » Tue 25 Oct 2011 11:14

ccampbell wrote:syntax error at or near ")"

Are you getting this error when executing pgScript.Execute()? Why are you using PgSqlScript (not PgSqlDump) for restoring? Refer to samples at http://www.devart.com/dotconnect/postgr ... lDump.html.

If this doesn't help, try the following:
1) does the problem persist with the latest (5.50.237) version of dotConnect for PostgreSQL?
2) find out the exact query, which fails to execute, using the dbMonitor tool:
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/postgr ... nitor.html
If the incorrect query is constructed by our provider, please send us (http://www.devart.com/company/contact.html) a small test project with the corresponding DDL/DML script (or your dump) to reproduce the issue in our environment.

ccampbell · Post by **ccampbell** » Tue 25 Oct 2011 14:42

Thanks for your reply. I've downloaded the latest version (5.50.237) and still get the same error message. I get the same error regardless of whether I use the pgDump.restore or a pgScript.execute to restore the database

I've posted the information through your customer support form along with the dump file. It's interesting in that after purging a ton of data in order to get the dump file under your 2mb limit I actually got a different error which reads: 'function "array_agg" already exists with same argument types'

I would be very interested to know what's up

Thanks,

Chris

Shalex · Post by **Shalex** » Tue 01 Nov 2011 12:42

ccampell wrote:'function "array_agg" already exists with same argument types'

We have reproduced the problem with PgSqlDump.Backup() which creates duplicating CREATE statements for some AGGREGATE functions. We will investigate the issue and notify you about the results.

As a temporary workaround, please use pg_dump.exe instead of PgSqlDump.Backup() to generate dump of your database. If there are any problems with the script (generated by pg_dump.exe) when executing it via PgSqlDump.Restore()/PgSqlScript.Execute(), please use the dbMonitor tool to find the query that fails to execute. Then open the dump with any text editor and correct the wrong statement.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/postgr ... nitor.html

Shalex · Post by **Shalex** » Tue 31 Jan 2012 13:22

The bug with PgSqlDump.Backup(), when duplicating CREATE statements for some aggregate functions are generated, is fixed. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

Shalex · Post by **Shalex** » Fri 10 Feb 2012 12:18

New build of dotConnect for PostgreSQL 5.70.302 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23369 .