PgSqlDump and pgAdmin III
PgSqlDump and pgAdmin III
If I backup a database using the PgSqlDump class, can I restore it with pgAdmin III?
Respcecfully,
Jorge Maldonado
Respcecfully,
Jorge Maldonado
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.
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
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
- 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.
* 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
* 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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.
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
"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
[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
"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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
In the code sample, did you mean
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.
Code: Select all
Dim pgConn As New PgSqlConnection(strConn)
pgConn.Unicode = True
pgConn.Open()
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.
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.
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.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
Jorge Maldonado
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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
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
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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.