PgSqlDump and pgAdmin III

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

PgSqlDump and pgAdmin III

Post by JORGEMAL » Thu 13 Jan 2011 22:10

If I backup a database using the PgSqlDump class, can I restore it with pgAdmin III?

Respcecfully,
Jorge Maldonado

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 14 Jan 2011 15:30

The PgSqlDump component backups data in the form of SQL statements. Thus, it is possible to restore data executing these statements in pgAdmin or other standard tool.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Tue 18 Jan 2011 18:33

I wrote a small app to backup a test database and, when I run pgAdmin III for a restore, I only get the Cancel button enabled; buttons Display Objects and OK are disabled. The code is very much like the example in your documentation. I have a SUB which accepts 2 parameters: the name of the backup file and the name of the connection string which resides in the web.config file. My web form has a textbox which reads the path where the file will be saved. I sent the backup file using the support form for your review. Below is my code for your review.

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 19 Jan 2011 14:06

PgSqlDump backs up data as SQL commands into non-compressed text files. This is analogous to backing up with PgAdmin in the 'PLAIN' format.

To restore a database from a non-compressed file in PgAdmin, you can, for example, execute SQL from this file in SQL Editor instead of using the 'Restore' dialog.

Feel free to contact us if anything is unclear.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Wed 19 Jan 2011 15:30

I tried to open the backup file generated by my simple app within SQL Editor and I got the following error message:

The file 'c:\TeresTemp\LOSAJOS.sql' could not be opened because it contains characters that could not be interpreted.

The original backup file name was LOSAJOS.BACKUP but I changed it to LOSAJOS.SQL because SQL Editor looks for .sql extension.
I also tried to open it as a pgScript file changing the extension to .pgs but the result was the same.

P.D.: I made a backup with pgAdmin III to a plain format and then tried to open the file with SQL Editor and I also got the error message. If I select the proper encoding format at backup time the error dissapears.

Best regards,
Jorge Maldonado

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 21 Jan 2011 18:17

Please specify the following:
- the versions of PostgreSQL servers where original and target databases are located;
- the charsets of both databases (you can check them with the 'show server_encoding' command);
- whether any non-standard symbols (e.g., national characters) may be present in the backup.

If possible, please send us this backup file so that we are able to analyze the issue in details.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Fri 21 Jan 2011 20:29

* I am only using one version of PostgreSQL which is 9.0.1. I never mix different versions of it.
* I use WIN1252 encoding so Spanish, Mexico characters are supported.
* Yes, characters like Ñ are present in the database.
I sent a sample using the Contat form in the Devart site. Such a sample is a real one that is actually working in our site. I tried to perform a restore from pgAdmin III and I got the following message:

1) D:/Archivos de programa/PostgreSQL/9.0/bin\pg_restore.exe --host localhost --port 5432 --username postgres --dbname restaurant --list "C:\TeresTemp\LOSAJOS.backup"
2) pg_restore: [archivador] el archivo de entrada no parece ser un archivador válido.
Process returned exit code 1.

The second message, which is in spanish, means: "the input file seems to be an invalid file").

Best regards,
Jorge Maldonado

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 24 Jan 2011 18:01

As for restoring from a text file, this is the expected behaviour, as the Restore PgAdmin command is supposed to work with compressed backups only. For example, if you will create a backup via PgAdmin in the PLAIN mode, trying to restore from such file will cause the same error.

As for using SQL editor, please ensure that you are setting Unicode to true when creating the backup. We couldn't reproduce the problem with non-recognized characters on Unicode connections.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Mon 24 Jan 2011 19:51

This is the modified code, which includes setting the Unicode property to True, but I am still getting the same message when I try to open the backup file with SQL Editor:

"The file 'LOSAJOS.backup' could not be opened because it contains characters that could not be interpreted.".

I even tried to use the Charset property of the connection to "WIN1252" (which is the one I use in my DB) without success.

Am I missing anything?
Regards,
Jorge Maldonado

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        pgConn.Unicode = True

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Mon 24 Jan 2011 20:12

[quote="JORGEMAL"]This is the modified code in 2 versions, which includes setting the Unicode property to True, but I am still getting the same message when I try to open the backup file with SQL Editor:

"The file 'LOSAJOS.backup' could not be opened because it contains characters that could not be interpreted.".

I even tried to use the Charset property of the connection to "WIN1252" (which is the one I use in my DB) without success. I tested with and without it.

Am I missing anything?
Regards,
Jorge Maldonado

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        pgConn.Unicode = True
        pgConn.Charset = "WIN1252"

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Open()
        'pgConn.Unicode = True
        'pgConn.Charset = "WIN1252"

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Connection.Unicode = True
        pgDump.Connection.Charset = "WIN1252"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 26 Jan 2011 15:21

In the code sample, did you mean

Code: Select all

Dim pgConn As New PgSqlConnection(strConn) 
pgConn.Unicode = True
pgConn.Open() 
instead of opening connection and setting the Unicode property after that? The point is that if you change the Unicode property for an opened connection, connection will be closed, thus the further code should generate an exception.

Could you please send us the dump file you are getting on a Unicode connection?

JIC: provided that the Unicode mode is enabled, the Charset property should be irrelevant.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Wed 26 Jan 2011 15:57

Below is the modified code where I define the Unicode property before openning the connection. I also commented such a line and used the Charset property. Only one property was used at a time. The sample I sent uses the Unicode property, not the Charset.

Code: Select all

Protected Sub Respaldo(ByVal strNombre As String, ByVal strConnString As String)
        Dim strConn As String = ConfigurationManager.ConnectionStrings(strConnString).ConnectionString
        Dim pgConn As New PgSqlConnection(strConn)
        pgConn.Unicode = True
        'pgConn.Charset = "WIN1252"
        pgConn.Open()

        Dim pgDump As PgSqlDump = New PgSqlDump
        pgDump.Connection = pgConn
        pgDump.Schema = "restaurant"
        pgDump.Backup(txtUbicacion.Text + strNombre + ".backup")
        pgConn.Close()
    End Sub
Regards,
Jorge Maldonado

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 27 Jan 2011 12:01

Please try the latest 5.10.96 version of dotConnect for PostgreSQL. I couldn't reproduce the problem with it, i.e., the backup file created by PgSqlDump contained proper Unicode symbols, and was successfully opened in SQL editor.

JORGEMAL
Posts: 171
Joined: Thu 03 Jul 2008 23:55

Post by JORGEMAL » Thu 27 Jan 2011 23:12

I upgraded to dotConnect for PostgreSQL 5.10.96 and the dumped file was read by pgAmin III SQL Editor. Nevertheless, I found the following issues:

1. When I open the file with SQL Editor I get a message that says "The file contains mixed line endings. They will be converted to the current setting". What does this mean?

2. I ran an Explain Query and an error appears as follows (I included translation from Spanish to English):

Query result with 0 rows will be returned.
ERROR: error de sintaxis en o cerca de «SET»
(This means "Syntax error in or near SET).
LINE 2: SET client_encoding = 'UTF8';
^

********** Error **********
ERROR: error de sintaxis en o cerca de «SET»
(This means "Syntax error in or near SET).
SQL state: 42601
Character: 60

This error is always thrown even if I remove 1 line, 2 lines, 5 lines from the file.

3. You suggested to set the conecction's Unicode property to true. I suppose this will not cause any problems with characters like "Ñ". I reviewed the backup file and everything looks fine but I wonder if, when performing a restore, things will work fine too.

Best regards,
Jorge Maldonado

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 28 Jan 2011 18:14

1) This message specifies that line endings of both CR and CRLF types are present in the dump file. PgSqlDump adds line endings of the CR type only; as I can understand, CRLFs originate from text data being inserted.

2) Please address this issue to the PostgreSQL support. Apparently, the EXPLAIN command cannot be used for SET statements.

3) Restoring from the dump file should be successful, as long as you use a Unicode connection for this. Feel free to tell us if you encounter any problems with this.

Post Reply