PgSqlDump restore fails with Dump data's date format

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
kerrywales
Posts: 52
Joined: Tue 05 Jan 2010 12:26

PgSqlDump restore fails with Dump data's date format

Post by kerrywales » Mon 26 Nov 2012 11:59

Hi all,
I am using dotConnect to PostgreSQL 6.2.77.0

I have a pgsqldump routine to create a backup file and I am testing the file by restoring it.

When using

Code: Select all

DataRestore.Restore(RestoreString);
it fails with a date /time error.
date/time field value out of range: \"31/12/9999\"
I think this is from one of the earlies lines
CREATE USER **** WITH SYSID 16395 PASSWORD '****' CREATEDB CREATEUSER VALID UNTIL '31/12/9999';
If the dump function created this line why cannot the restore function restore with it?
I am presuming it is because the pg wants it in yyyy-mm-dd format.

A quick search in the 1Gb text file produced by the dump function shows that most data is in the american format.

dunmp uses the basic code:

Code: Select all

  var myConnection = new CSLConnect(UserName, Password, Port, HostName, DBName);
  if (myConnection.Login())
  {
    using (var dataBackup = new PgSqlDump()
    {
      Connection = myConnection.getConnection(),
      IncludeBlob = true,
      ObjectTypes = PgSqlDumpObjects.All,
      GenerateHeader = true,
      Mode = DumpMode.All
    })
    {
      dataBackup.Backup(Path.Combine(Destination, BackupName + ".backup"));
    }
    myConnection.Logout();
}
Regards

kerrywales
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Re: PgSqlDump restore fails with Dump data's date format

Post by kerrywales » Thu 29 Nov 2012 13:42

Removing the CREATE USER lines does allow the restore method to function except::::

either the restore method calculates the length before srtipping out special characters or the dump method is adding characters to the string.

I am finding that dumped strings close'ish to the limit of of the character varying field is erroring on restore with PG complaining that the string is too long.

e.g. a table containing multiline text which the db says has 441 chars in it (size is set to 470) reckons that the restore value has over 500. So it stops!!

I don't know if it is the dump or the restore.

kerrywales
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Re: PgSqlDump restore fails with Dump data's date format

Post by kerrywales » Thu 29 Nov 2012 22:21

Continuing my saga to try and work out the options to make backup and restore work (given that the test of a more complex db takes 2 hours) I have found a combination that creates the tables and constraints.

The backup object code is set to:
ObjectTypes = PgSqlDumpObjects.All ^ PgSqlDumpObjects.Users ^ PgSqlDumpObjects.Languages
Mode = DumpMode.All

This seems to work till the end. It fails starting with:

Code: Select all

2012-11-29 21:47:28 GMT	ERROR	syntax error at or near "" PRIMARY KEY ("" at character 104237	
2012-11-29 21:47:28 GMT	STATEMENT	CREATE OR REPLACE FUNCTION "public"."dblink"( text, text)	
			  RETURNS SETOF record AS	
			$BODY$dblink_record$BODY$	
			  LANGUAGE 'c';	


Do I need to back up the dblink info. If not how do I say don't back it up

The text follows with setting defaults then views. I appears to try and run this section of script in one go.

Any advice if anyone has got the mechanism working correctly greatfully received.

kerrywales
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Re: PgSqlDump restore fails with Dump data's date format

Post by kerrywales » Fri 30 Nov 2012 08:30

Coming rapidly to the conclusion that backup/restore using the methods for anything other than simple DB's doesn't work. Been trying this on an off for many versions. Yet to have a satisfactory result.

doing it via pg_dump/restore is a bigger security hole though.

Mmmmmm

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDump restore fails with Dump data's date format

Post by Pinturiccio » Fri 30 Nov 2012 14:35

We have reproduced the issue with the exception "value too long for type character varying...". We are also investigating other issues you describe in this topic. We will notify you about the results as soon as possible.

kerrywales
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Re: PgSqlDump restore fails with Dump data's date format

Post by kerrywales » Fri 30 Nov 2012 14:59

Thank you for investigating.

For other poor souls trying this I though I would let you know where I have got to and what I have found.

1. Make sure that ALL tables are owned by the same user and that ALL constraints are owned by the same user. It is possible to have constraints set up between two tables owned by different users which pg_dump and pg_restore do not mind but pgsqldump does.

2. Do not dump the data out as data only the restore moans like mad.

3. Do dump the information in separate files.

4. Check that people who manage the database do NOT put in funny characters. In doing this exercise I identified that a user had a constraint name with a '"' (double quotes) contained within it. Again pg_dump & pg_restore was happy with this. The Devart mechanism was not.

I use Views, indexes and constraints to define relationships within the DB. The restoring works once the name of the constraint was modified (see previous comment).

Extracts from my backup/restore test code:

Code: Select all

var dataBackup = new PgSqlDump()
                    { 
                        Connection = myConnection.getConnection(),
                        QuoteIdentifier = true,
                        IncludeBlob = true,
                        IncludeDrop = false, 
                        IncludeUsers = false, 
                        GenerateHeader = false,
                        ObjectTypes = PgSqlDumpObjects.All ^ PgSqlDumpObjects.Users ^ PgSqlDumpObjects.Languages 
                            ^ PgSqlDumpObjects.Views ^ PgSqlDumpObjects.Indexes ^ PgSqlDumpObjects.Constraints,
                        Mode =  DumpMode.All
                    };

                    string name = Path.Combine(Destination, "dump_data.backup");
                    dataBackup.Backup(name);

Code: Select all

var viewsBackup = new PgSqlDump()
                    {
                        Connection = myConnection.getConnection(),
                        QuoteIdentifier = true,
                        GenerateHeader = false,
                        ObjectTypes = PgSqlDumpObjects.Views
                    };

                    name = Path.Combine(Destination, "dump_views.backup");
                    viewsBackup.Backup(name);
                    

Code: Select all

var indiciesBackup = new PgSqlDump()
                    {
                        Connection = myConnection.getConnection(),
                        QuoteIdentifier = true,
                        GenerateHeader = false,
                        ObjectTypes = PgSqlDumpObjects.Indexes
                    };

                    name = Path.Combine(Destination, "dump_indicies.backup");
                    indiciesBackup.Backup(name);
                    

Code: Select all

var constraintsBackup = new PgSqlDump()
                    {
                        Connection = myConnection.getConnection(),
                        QuoteIdentifier = true,
                        GenerateHeader = false,
                        ObjectTypes = PgSqlDumpObjects.Constraints
                    };

                    name = Path.Combine(Destination, "dump_constraints.backup");
                    constraintsBackup.Backup(name);
                    
Restoring means I
1. Create an empty database of correct ownership etc

2. Restore in this order (I also zip up the backups):

Code: Select all

DoDevartRestore("Data", openWhat.InitialDirectory, openWhat.FileName, "dump_data.backup");
                    DoDevartRestore("Indicies", openWhat.InitialDirectory, openWhat.FileName, "dump_indicies.backup");
                    DoDevartRestore("Constraints", openWhat.InitialDirectory, openWhat.FileName, "dump_constraints.backup");
                    DoDevartRestore("Views", openWhat.InitialDirectory, openWhat.FileName, "dump_views.backup");
                    

Code: Select all

private bool DoDevartRestore(string myMessage, string myDirectory, string zipFile, string myFile)
        {
            bool result = false;
            try
            {
                ...
                string myFilePath = Path.Combine(myDirectory, myFile);
                try
                {
                    if (File.Exists(myFilePath))
                        File.Delete(myFilePath);
                }
                catch { };
                
                ...
                    RunDevartRestore();
                    result = true;
                }
            }
            catch { };
            return result;
        }

Code: Select all

public void RunDevartRestore()
        {
            try
            {
                CSLConnect myConnection = new CSLConnect(teUser.Text,tePassword.Text, tePort.Text, teHost.Text, teDB.Text);

                if (myConnection.Login())
                {
                    using (PgSqlDump DataRestore = new PgSqlDump { Connection = myConnection.getConnection() })
                    {
                        if (File.Exists(RestoreString))
                        {
...                            
                            DataRestore.Restore(RestoreString);
                            myConnection.Logout();
                            File.Delete(RestoreString);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Message = ex.Message;
            }
        }
Timings

I have a pg_dump & pg_restore program with a wrapper as well. I tested the routine with the same database.
pg_dump & pg_restore to do both took less than 5 mins

Using the devart method it took 7 minutes to backup and 1 hr 10 mins to restore

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDump restore fails with Dump data's date format

Post by Pinturiccio » Mon 03 Dec 2012 17:15

Thank you for additional information. It will help us in our investigation.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDump restore fails with Dump data's date format

Post by Pinturiccio » Wed 19 Dec 2012 14:31

We have fixed the bug with formatting the string data with escape characters during PgSqlDump.Backup().
We have fixed the bug with formatting the timestamp data when generating the script for user creation during PgSqlDump.Backup().
We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
kerrywales wrote:I have a pg_dump & pg_restore program with a wrapper as well. I tested the routine with the same database.
pg_dump & pg_restore to do both took less than 5 mins

Using the devart method it took 7 minutes to backup and 1 hr 10 mins to restore
pg_dump and pg_restore are tools specially designed for backup and restore by the PostgreSQL company. We know that dotConnect for PostgreSQL creates backups and restores data slower than pg_dump and pg_restore.
kerrywales wrote:Do I need to back up the dblink info. If not how do I say don't back it up
We will investigate the issue and notify you about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDump restore fails with Dump data's date format

Post by Pinturiccio » Fri 21 Dec 2012 13:23

The new build of dotConnect for PostgreSQL 6.3.146 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 valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=25518

Post Reply