Schema location issue with pgDump & Restore

[ccampbell]

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]

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]

Okay I updated, (4.95.152) still an issue so it's probably a setting or something easy I'm hoping.

[StanislavK]

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.

[ccampbell]

"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.

[StanislavK]

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.