• Sign In
  1. Messages

psqlexception on external schemas

LabKey Support Forum (Inactive)

View Message

view list print
psqlexception on external schemas bront  2013-08-12 07:02
Status: Closed
 
In an effort to migrate LabKey's data from PostgreSQL 8.1 to 9.1, I did a pg_dump and restore of the LabKey database, which seemed to work

I can log into LabKey, but I am now getting errors referencing our external schemas.

These external schemas were NOT part of the upgrade (they were already in PG 9.1).

There is no error when I try to reload schema, but I still get "Schema does not exist error".

When I delete and attempt to re-create a schema, I get this error:

org.postgresql.util.PSQLException: ERROR: column "dbschemaname" of relation "externalschema" does not exist

DialectSQL = INSERT INTO query.externalschema
     (entityid, created, createdby, modified, modifiedby, container, userschemaname, editable, metadata, datasource, indexable, tables, DbSchemaName)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?)
    RETURNING externalschemaid

request attributes
LABKEY.StartTime = 1376315250901
LABKEY.container = /xxx
LABKEY.action = insertExternalSchema
LABKEY.RequestURL = /labkey/query/DoD%20Lung%20Study%20%28LCRI%29/insertExternalSchema.view?
LABKEY.OriginalURLHelper = /labkey/query/DoD%20Lung%20Study%20%28LCRI%29/insertExternalSchema.view?
LABKEY.controller = query
X-LABKEY-CSRF = 4bd5bb8a53beb10b0ecaf1517b861aee
LABKEY.OriginalURL = https://mcclabkey.uky.edu/labkey/query/DoD%20Lung%20Study%20%28LCRI%29/insertExternalSchema.view?
LABKEY.Counter = 0

core schema database configuration
Server URL    jdbc:postgresql://xxx/labkey_prod?sslmode=require
Product Name    PostgreSQL
Product Version    9.1.9
Driver Name    PostgreSQL Native Driver
Driver Version    PostgreSQL 9.2 JDBC4 (build 1001)

Any ideas what might be going on?

Many thanks,

bront
 
 
bront responded:  2013-08-12 09:02
I see issues via Database Consistency checker... now seeking a way to fix.

bront
 
jeckels responded:  2013-08-12 09:16
Hi Bront,

This looks like it might be a LabKey Server version mismatch issue.

The upgrade from 12.3 to 13.1 renames the "DbSchemaName" column on the query.ExternalSchema table to "SourceSchemaName".

Is it possible that you're attempting to run version 12.3 against a database that's already been upgraded to 13.1?

Thanks,
Josh
 
bront responded:  2013-08-12 09:39
Josh,

That is exactly what has happened.

I moved the database to the upgraded version of PostgreSQL, changed the dataSource pointer in the labkey.xml file, and now I am getting the error.

I am going to restore the original database on the original server (and wait for our system admin to get back in the office before doing a full upgrade).

Question... Labkey must recognize the targeted version of PostgreSQL and update accordingly, correct?

As always, thanks for your prompt and clear help...

bront
 
adam responded:  2013-08-12 10:01
Newer versions of LabKey require more recent versions of PostgreSQL; for example, current LabKey Server releases don't support PostgreSQL 8.3 or earlier, since the PostgreSQL team no longer supports these old releasses. LabKey will refuse to start if it's pointed at an unsupported database version. Supported versions of external components are detailed here: https://www.labkey.org/wiki/home/Documentation/page.view?name=supported

LabKey Server automatically performs schema upgrades when you upgrade LabKey Server itself, but it performs no operations when you upgrade your database version. Instead, our database dialect layer automatically detects the current database version and runs the code most appropriate to that database version.

Our recommendation in general would be to upgrade your database version first, make sure that works correctly, and then upgrade LabKey version. Separating the steps will help isolate the cause of any problems, although it might not be possible in all scenarios.

I hope this helps...

Adam