PgSqlDump and pgAdmin III

JORGEMAL · Post by **JORGEMAL** » Sun 06 Feb 2011 21:00

I have been trying to restore a database dumped with pgSqlDump and I am getting an error message. I will describe this issue here and please let me know if this situation has to do with Devart.
The error message is as follows (I am translating it to English because it is in Spanish):
--------------------------------------------------------------------------
ERROR: duplicate key violates uniqueness restriction >.
DETAIL: The key already exists (applicationname)=(/).

********** Error **********
ERROR: duplicate key violates uniqueness restriction >.
SQL state: 23505
DETAIL: The key already exists (applicationname)=(/).
--------------------------------------------------------------------------

I have checked the "aspnet_roles" table and I do not see that the "applicationname" field has to do with a key of any kind. I do not even find a key with a name of "aspnet_roles_applicationname_key" in the dumped file. I have sent the file that contains the backup in case you need it. I am using pgAdmin III SQL Editor to restore the DB. My PostgreSQL version is 9.0.1. What I notice is that the error message refers to (applicationname)=(/) and such an application is running in a production web server; on the other hand, I want to perform the restore task in my development PC so, the applicationname is different. Nevertheless, if I get a compressed backup using pgAdmin III, I can successfully restore it to my PC using pgAdmin III too.

This is the VB code:

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.Open()

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

Regards,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Tue 08 Feb 2011 16:34

Thank you for the report. Apparently, the 'aspnet_roles_applicationname_key' key is generated at the server for the 'applicationname' column, which is marked as 'UNIQUE' in the backup script.

As far as I can understand, the 'aspnet_roles' table should have one unique key consisting of two columns. Am I correct? We have reproduced the problem with PgSqlDump generating multiple unique constraints in such case. We will investigate it and inform you about the results as soon as possible. A possible workaround for this situation is to remove the 'UNIQUE' keywords from the table definitions in the backup file.

StanislavK · Post by **StanislavK** » Thu 17 Feb 2011 17:36

We have fixed the issue with backing up composite unique keys as several one-field keys. The fix will be available in the nearest build which we plan to release in about a week. We will post here when this build is available.

StanislavK · Post by **StanislavK** » Thu 24 Feb 2011 17:35

We have released the new 5.10.111 build of dotConnect for PostgreSQL which includes the fix for dumping composite keys. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in dotConnect for PostgreSQL 5.10.111, please refer to
http://www.devart.com/forums/viewtopic.php?t=20339

JORGEMAL · Post by **JORGEMAL** » Sat 26 Feb 2011 22:15

I upgraded to dotConnect for PostgeSQL 5.10.111 and there is still a problem. I am getting the following error message:

ERROR: no existe la relación «restaurant.aspnet_personalization»
SQL state: 42P01

In English this means that the «restaurant.aspnet_personalization»
relation does not exist.

I review the backup file and I see that the "aspnet_personalization" table is dropped and never created. I wonder if there is another issue similar to this one. I sent the backup file for your review. I guess this is the problem unless I am missing something.

Regards,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Mon 28 Feb 2011 12:58

We couldn't reproduce this problem with the database restored from the PgAdmin backup file you've sent us: the 'create table' commands were successfully generated for all tables in the database. If possible, please send us the PgAdmin backup file of the database with which the issue occurs.

Also, please specify the following:
- whether the user account you are connecting with has any access limitations to the objects of the dumped schema; in particular, can the problem be reproduced when connecting as a superuser?
- whether any specific settings are enabled for the PgSqlDump object;
- whether any specific connection parameters are enabled (except the Unicode one).

JORGEMAL · Post by **JORGEMAL** » Mon 28 Feb 2011 15:00

The following is the code that performs the backup. The SUB receives 2 parameters:
1. strNombre is the name that the backup file will have.
2. strConnStr is the connection string to connect to the DB.
There is a control called txtUbicacion (a textbox) that indicates the path to the folder where the dumped file will be saved. Actually, I run this process for 3 DB's so I get 3 different backup files.
I connect to my postgres server as the postgres user who is a superuser.

I have sent you the following information:
1. 3 backup files gotten from pgAdmin III backup utility in compressed format. I specify WIN1252 as the encoding choice. Their names are LuciaReposteriaComp, RestaurantLosAjosComp and TeresAjosComp.
2. 3 backup files gotten from my small app. Their names are LuciaReposteria, RestaurantLosAjos and TeresAjos. When I load and run the script for RestaurantLosAjos I get the message saying that the aspnet_personalization relation does not exist. When I load and run the script for the other 2 files I get a syntax error. I am really lost with this situation and I very much appreciate your support and patience.

With respect,
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.Open()

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

StanislavK · Post by **StanislavK** » Tue 01 Mar 2011 15:47

We couldn't reproduce the problem: for the databases restored from the PgAdmin backup files you've sent us, the backup files created with PgSqlDump did not contain syntax errors and did include all tables defined in the original database.

As far as I can understand from the backup files you've sent us, a problem occurs during the dumping process: backup files end unexpectedly in the middle of an insert command. A possible cause for this is that the connection to the server breaks. Please perform the following:
- backup these databases within a separated console application (I will send you a sample in a letter);
- if an exception is raised during execution of the sample, please tell us its exact message, stack trace and the inner exception details (if any);
- if the application runs successfully, please send us the resulting backup file.

JORGEMAL · Post by **JORGEMAL** » Tue 01 Mar 2011 17:45

I guess I did not explain myself correctly. The problem I am having is NOT with pgSqlDump class, at least not directly. The last piece of VB code I sent last time (Protected Sub Respaldo) ends without any exception and does not terminate unexpectedly. The files that contain the backup data are supposed to be correctly generated. The problem comes when performing the restore operation. I open the files generated by the dump process (using SQL Editor of pgAdmin III) and click the button to execute the query. It is at this moment that the operation fails. I sent 3 files named losajos.backup, luciareposteria.backup and teresajos.backup. These files were generated by my small dump application and these are the ones I open and execute in pgAdmin III without success.

However, I will go over the project you sent and perform the necessary tests. I will inform you about the results.

Regards,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Wed 02 Mar 2011 13:17

The problem is that the backup files were not generated properly: for example, the teresajos.backup file ends up with the command like

Code: Select all

INSERT INTO [table name] VALUES ([integer], [string], [string], 'E

and includes lines like

Code: Select all

eger NOT NULL,
[property name 1] char(5) NOT NULL,
[property name 2] varchar(24) NOT NULL,
...

which apparently should be a part of a table definition. We couldn't reproduce such issues when backing up databases in our environment, thus we've sent you a test project which should help to localize the issue when backing up the database you are working with.

JORGEMAL · Post by **JORGEMAL** » Wed 02 Mar 2011 17:04

I review the project you sent and I see the following differences.
My connectionString includes a "schema = restaurant" (or Initial Schema) entry and yours does not. I added it to the connectionString in your project, ran the backup and then tried to run the result in the pgAdmin III's SQL Editor. The result is an error message saying that the «areas_servicio_clave_seq» relation does not exist. Nevertheless, as soon as I remove the "schema" entry from your project's connectionString, everything works correctly. I also commented out the dump.Schema = "restaurant" in your project and left the schema entry in the connectionString with the same results. So, the only way your project works is when no schema is defined in the connectionString.

I tried to replicate this setting in my project without success. I removed the "schema" entry from my connectionString leaving only the pgDump.Schema = "restaurant" in my VB code and what I got was a file that does not include any INSERT statements. I sent a sample of such a file.

So, in short, it seems that including the "schema" or "Initial Schema" in the connectionString is leading to some kind of problem. Please note that all of my connectionString entries in my project are located in the web.config file.

This is the connectionString I use in my project:

This connectionString does not have a schema entry and was used for testing purposes only:

The user "administrador" has all the rights in the database.

***** Please disregard the file I sent, the backup/restore works fine if I remove the "schema" entry from my connectionString. The only problem is when such an entry is if such an entry is included. *****

This is the modified connectionString from your project in order to work in my environment:
Dim connectionString As String = _
"Server = 127.0.0.1; user id = postgres; password = password; port = 5432; Database = restaurant; Unicode = true;". The only difference is the "Server" and "password" values.

Respectfully,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Thu 03 Mar 2011 14:36

Thank you for your assistance, we've reproduced the problem with the sequence backed up incorrectly when schema name is specified.

As far as I can understand, you are able to successfully backup the database with the test project and restore it with PgAdmin (provided that schema name is not set in the connection string).

Please specify whether you are able to use the backup files generated by your real application (in case that schema is not specified in the connection string). If not, there should be a difference in the way PgSqlDump is used in the test project and your application. We would be grateful if you could describe this difference, so that we are able to reproduce and analyze the issue. In particular, please check the following in this case:
- both applications are connecting to the same database and with the same connection string;
- both applications are run on the same machine;
- the Devart assemblies used in the real application and in the test project are from the same build of dotConnect for PostgreSQL.

JORGEMAL · Post by **JORGEMAL** » Thu 03 Mar 2011 18:07

Yes, I could run the backup and restore operations with your test project provided the schema name is not set in the connection string.

I am going to perform additional tests in order to give you more information but I need to ask a question: when I get a backup using pgAdmin III, privileges and tablespaces are included; in the "Dump Options #1" tab, section "Don't save", I do not check the "Privilege" and "Tablespace" checkboxes. Are there any equivalent settings in pgSqlDmp class?

Regards,
Jorge Maldonado

StanislavK · Post by **StanislavK** » Fri 04 Mar 2011 14:35

You can use the IncludeUsers property to backup information about user accounts and their privileges.

As for tablespaces, PgSqlDump does not take them into account at the moment: all tables are created in the default space of the database. We will consider including tablespaces into the backup script, and post here when our investigation is completed.

JORGEMAL · Post by **JORGEMAL** » Mon 07 Mar 2011 19:38

* Yes, both applications connect to the same DB and the connection string is the same.

* I do not understand what you mean with "both applications are run on the same machine".

* The Devart assemblies are the same for both, the development PC and the production server (5.10.111.0).

All of my tests indicate that the problem comes when the schema is set in the connection string. I performed such tests as the postgres user to make sure there are no privileges issues.

I have noticed that, when running the script in the SQL Editor to restore the dump that I got using pgSqlDump, my tables' privileges are missing. Is this feature not implemented?

Regards,
Jorge Maldonado