SYS_DB_CHANGELOG not found in Studio when running app on Postgres

Support
#1

I am moving an app from Oracle to Postgres. I have made sure that sys_db_changelog exists and includes all the Postgresql init scripts as entries, but I still get the message “The database does not exist or does not contain the tables required for the application components, but the project already has initialization scripts”.

I turned on Debug in Studio. Here are the log entries:

io.backend.ed.ddl.DbManager - Opening connection to DB: url=jdbc:postgresql://postgres/pas, user=pasrade 
2020-06-30 12:33:29,546 [91693315]  DEBUG - tudio.backend.ed.ddl.DbManager - Opening connection to DB: url=jdbc:postgresql://postgres/pas, user=pasrade 
2020-06-30 12:33:30,009 [91693778]   INFO - tudio.backend.ed.ddl.DbManager - Table SYS_DB_CHANGELOG required for 'com.haulmont.cuba:cuba-global:$cubaVersion' application component is not found

Here are entries from the SQL debug log:

select current_database() as a, current_schemas(false) as b
-- ] --------------------------------------------------
-- 2020-06-29 13:54:48 [rade] [pas@postgres] [console: console [pas@postgres]] [session id: 943215277] [statement id: 1821245399] results received
-- 2020-06-29 13:54:48 [rade] [pas@postgres] [console: console [pas@postgres]] [session id: 943215277] [statement id: 1821245399] finished - execution time: 25 ms, fetching time: 30 ms, total result sets count: 1
-- [ --------------------------------------------------
-- 2020-06-29 13:54:48 [rade] [pas@postgres] [console: console [pas@postgres]] [session id: 943215277] [statement id: 1821245400] 
select * from sys_db_changelog
-- ] --------------------------------------------------
-- 2020-06-29 13:54:48 [rade] [pas@postgres] [console: console [pas@postgres]] [session id: 943215277] [statement id: 1821245400] results received
-- 2020-06-29 13:54:49 [rade] [pas@postgres] [console: console [pas@postgres]] [session id: 943215277] [statement id: 1821245400] finished - execution time: 64 ms, fetching time: 284 ms, total result sets count: 1

Here I show that the table does exist on the same pasrade schema using the same login:

image

There are even entries for the postgres init scripts:

image

How do I get past this message and run my app?

#2

Hi,
Your table is located in the “pasrade” schema. It is visible from the psql console.

CUBA needs that all tables are located in the same schema. By default “public” schema is used.
If you need to connect to non-default schema, you can append a connection parameter in the data store settings. See Connecting to a Non-Default Database Schema - CUBA Platform. Developer’s Manual

#3

Thank you! This is new for 7.2? I didn’t mention it, but I’m still on 7.1. Guess I will have to update.

#4

Well, it turns out it works with 7.1 as well. I did not see it in the documentation, though. Thanks for the help!

#5

And here I thought it was fixed. When I add the currentSchema setting, the original error goes away. BUT - all of my data no longer shows in the application. When I remove currentSchema from the connection, my data is there (but of course, I get the Initialization error message).

Is this because it is not completely implemented in 7.1? Is a 7.2 upgrade necessary?

#6

It is because all your tables must be in the same database schema, but they are not.

Run this query to make sure:

select tablename, schemaname 
from pg_catalog.pg_tables 
where schemaname not in ('pg_catalog', 'information_schema');

          tablename            | schemaname 
--------------------------------+------------
 sys_db_changelog               | public
 sys_server                     | public
 sys_config                     | public
 sys_lock_config                | public
 sys_entity_statistics          | public
 sys_file                       | public