Schema location issue with pgDump & Restore

Schema location issue with pgDump & Restore

Postby ccampbell » Tue 14 Sep 2010 19:11

Hi,

Using: devart.data 5.0.99.0
devart.data.postgresql 4.95.146.0
Postgres v8.4.4

I’m having an issue with the pgDump in that the schema under my database is getting restored to the public database rather than the database it belongs under, for example:
Here is my original cluster structure
Databases(2)
--DDir000
----schemas(2)
--------data1
--------public

--postgres
----Schemas(1)
--------Public

However, after I do a restore, the data1 schema is loaded under the postgres database rather than DDir000 where is belongs:
Databases(2)
--DDir000
----Schemas(1)
--------public
--postgres
----Schemas(2)
--------Data1
--------Public

On the Backup, my data connection is attached to DDir000
On the Restore, my data connection is attached to postgres (because DDir000 doesn't exist yet)

The restore is occuring in a clean install of Postgres

Here is my code:
// === Create the Dump file
pgDump.Connection = conn
pgDump.ObjectTypes = PgSqlDumpObjects.All - PgSqlDumpObjects.Languages
pgDump.CreateConstraints = False
pgDump.Schema = ""
pgDump.Tables = ""
pgDump.IncludeDrop = False
pgDump.Mode = DumpMode.All
pgDump.Backup()

Dim stream As StreamWriter = New StreamWriter(gstrTempPath & "\sqldump.dmp")
stream.WriteLine(pgDump.DumpText)
stream.Close()

// === Restore the dump file into new install of Postgres
Dim pgDump As PgSqlDump = New PgSqlDump
pgDump.Connection = conn
pgDump.Restore(gstrTempPath & "\sqldump.dmp")

Am I missing a setting somewhere?

Thanks,
Chris
ccampbell
 
Posts: 29
Joined: Tue 01 Jun 2010 17:31
Location: Oregon

Post subject: Schema location issue with pgDump & Restore

Postby ccampbell » Tue 14 Sep 2010 19:19

Sorry, I just noticed I wasn't using the latest version. I will try again and let you kow whether this is still an issue. Thanks.
ccampbell
 
Posts: 29
Joined: Tue 01 Jun 2010 17:31
Location: Oregon

Schema location issue with pgDump & Restore

Postby ccampbell » Tue 14 Sep 2010 20:32

Okay I updated, (4.95.152) still an issue so it's probably a setting or something easy I'm hoping.
ccampbell
 
Posts: 29
Joined: Tue 01 Jun 2010 17:31
Location: Oregon

Postby StanislavK » Wed 15 Sep 2010 16:32

Please check the databases to which you are connecting on both backup and restore. The PgSqlDump component uses the database specified in the connection being used. A probable cause of the issue is that you are connecting to the DDir000 database on backup and to postgres during restore.

Please tell us if this helps.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby ccampbell » Wed 15 Sep 2010 18:30

"A probable cause of the issue is that you are connecting to the DDir000 database on backup and to postgres during restore"

Well yes, as I said above, this is in fact the case. However what other option do I have? The database I want to backup is DDir000, not postgres. If I don't attach to DDir000 and attach to postgres instead all I get is postgres exported, which isn't what I want.

On the restore, I can't attach to DDir000 because it doesn't exist yet. Remember, as I said above, I'm restoring into a fresh install of PostgreSQL. The idea is to take a snap shot of one database and recreate it exactly on another pc/server.

Upon restoring, the database (DDir000) does get created. Unfortunately, the schema (data1) gets placed under the wrong database (postgres instead of DDir000) That is the issue...

Perhaps the problem is the fact that DDir000 doesn't already exist in the fresh install? Seems to me this should work, it almost does. It's just that during the restore, the schema, which started out under DDir000 and associated objects take a wrong turn and get placed under the default database (postgres).

Hopefully I've done a better job of explaining what I'm doing and what's not quite working. thanks.
ccampbell
 
Posts: 29
Joined: Tue 01 Jun 2010 17:31
Location: Oregon

Postby StanislavK » Thu 16 Sep 2010 14:03

Thank you for detailed explanation, we've analyzed the situation. The problem is that PostgreSQL does not support cross-database commands, hence it is impossible to manipulate the DDir000 database when being connected to the postgres database.

We recommend to remove PgSqlDumpObjects.Database from the ObjectTypes property of your PgSqlDump, and to create databases via PgSqlCommand. We will also consider removing the Database element from the PgSqlDumpObjects enumerator.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL