kirchaj
Posts: 215
|
| Posted: 04/25/2013, 7:03 PM |
|
I have two Postgres DBS with tables set up like with separate apps in CCS
Account
Public
Tables
Portfolio
Public
Tables
I want to move to a new setup like
Accountability
Account
Tables
Portfolio
Tables
I can physically get this done but I'm not sure of all the implications this will have with CCS. Anyone done this? Any gotchas that I need to watch for? Suggestions????
Thanks
TK
|
 |
 |
Lucius
Posts: 220
|
| Posted: 04/26/2013, 10:16 AM |
|
Hi,
All depends on the referencing you are using now, versus new setup. If the Tables and their connections (for example by foreign keys) remain the same, then your apps should still work.
If you have added some new fields to tables, again it should work. If you removed some, changed their names then most likely the apps will generate errors and will not work properly.
Only way to be sure - copy the DB, setup test environment with current apps and change the DB setup. Check all pages and functionality.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 04/26/2013, 12:11 PM |
|
All of the tables and structures will remain the same but I will be changing from 2 physical databases with a public schema to 1 physical database with an account and portfolio schemas.
I can setup the odbc client on my machine but don't know how to setup the server as it doesn't have the Same options to select the schema.
|
|
|
|
Lucius
Posts: 220
|
| Posted: 04/27/2013, 5:01 AM |
|
So in this case I would guess that it will not work - public schema is the default schema, so queries which do not have the schema explicitly used like:
SELECT field FROM table WHERE something
By default fall back to "public" schema, so above query is the same as:
SELECT field FROM public.table WHERE something
The first query falls back by default to public schema, but unless you configure server accordingly it will not fall back to other schemas you might have.
In CCS (in DB connection properties) you can select active schema, so that would be one solution, however last time I checked, a very long ago so it might have been fixed, it was not working properly.
Other solution would be to rewrite queries so they use schema in every table reference - a lot of work.
Last solution would be to use Schema Search Path, to point to another schema if fallback to "public" fails. This might be the easiest solution if you have such privileges on DB server: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html
However all solutions depend on what you need to achieve with new schema setup, for example it might not be a good idea to use Search Path if you set up schemas for better DB security.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 04/29/2013, 12:45 PM |
|
Thanks Lucius
Those are some of the issues I was afraid I would run into. I am not so worried about security as I am the ability to do searches and queries across the schemas. I cannot do that now with separate databases.
I may just have to live with it and keep them separate. 
TK
|
|
|
|
|
