Two databases with GORM. Both Postgres.

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Two databases with GORM. Both Postgres.

Moe-25
Hi, 

I am setting up a new environment, and need to split our project into having two databases. That would be preferable. 

One database should contain several domains that are related. The other should contain stuff like user accounts and such. 

Both use the Postgres database. 

How can I best split certain domains to be mapped to certain databases?

I think it can be done with separating one or both into plugins but please let me know what options I have, and preferably with some information on how that can be achieved.

Thank you, Moe
Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

lucastex
Check the DataSources plugin: http://www.grails.org/plugin/datasources

[]s,

Lucas Frare Teixeira .·.
- [hidden email]
- lucastex.com.br
- blog.lucastex.com
- twitter.com/lucastex


On Mon, Feb 7, 2011 at 10:55 AM, Moe <[hidden email]> wrote:
Hi, 

I am setting up a new environment, and need to split our project into having two databases. That would be preferable. 

One database should contain several domains that are related. The other should contain stuff like user accounts and such. 

Both use the Postgres database. 

How can I best split certain domains to be mapped to certain databases?

I think it can be done with separating one or both into plugins but please let me know what options I have, and preferably with some information on how that can be achieved.

Thank you, Moe

Reply | Threaded
Open this post in threaded view
|

НА: Two databases with GORM. Both Postgres.

ishe
In reply to this post by Moe-25
What do you think about groovy.sql.Sql aprouch for second instance and traditional grails aproach for first?



----- Исходное сообщение -----
От: Moe-25 [via Grails] <[hidden email]>
Отправлено: 7 февраля 2011 г. 14:56
Кому: ig78 <[hidden email]>
Тема: Two databases with GORM. Both Postgres.



Hi,

I am setting up a new environment, and need to split our project into having
two databases. That would be preferable.

One database should contain several domains that are related. The other
should contain stuff like user accounts and such.

Both use the Postgres database.

How can I best split certain domains to be mapped to certain databases?

I think it can be done with separating one or both into plugins but please
let me know what options I have, and preferably with some information on how
that can be achieved.

Thank you, Moe


_______________________________________________
If you reply to this email, your message will be added to the discussion below:
http://grails.1312388.n4.nabble.com/Two-databases-with-GORM-Both-Postgres-tp3264059p3264059.html
To start a new topic under Grails - user, email [hidden email]
To unsubscribe from Grails - user, visit не все содержимое сообщения]
Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
In reply to this post by Moe-25
Moe, can i suggest an easier approach in regards to postgresql usage?
Use 2 schemas in the same database and create your db objs in those
schemas instead of in the public one.

Best regards,
 Daniel.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
Can i = may i.
My bad.  :)

2011/2/7, Daniel Henrique Alves Lima <[hidden email]>:
> Moe, can i suggest an easier approach in regards to postgresql usage?
> Use 2 schemas in the same database and create your db objs in those
> schemas instead of in the public one.
>
> Best regards,
>  Daniel.
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Moe-25
Daniel, I am not sure I understood what you or how that can be accomplished easily without introducing unnecessary complexity. 
However,  I think the datasource plugin looks like what I am looking for. 

I will have a look it hopefully soon and possibly report back any info from my experience. 

Thanks all, Moe

On Mon, Feb 7, 2011 at 3:41 PM, Daniel Henrique Alves Lima <[hidden email]> wrote:
Can i = may i.
My bad.  :)

2011/2/7, Daniel Henrique Alves Lima <[hidden email]>:
> Moe, can i suggest an easier approach in regards to postgresql usage?
> Use 2 schemas in the same database and create your db objs in those
> schemas instead of in the public one.
>
> Best regards,
>  Daniel.
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
Moe, in non grails apps 2 schemas in a single db are easier to use
than 2 databases. You can avoid issues like 2pc and db links. But it
depends of your needs and it is up to you.

Best regards,
Daniel.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Maricel
In reply to this post by Moe-25
We had the same situation and used the Datasources plugin, it is very easy to use. I wrote a post here http://maricel-tech.blogspot.com/2011/01/using-multiple-datasources-in-grails.html with some tips and tricks that might be of help.
- Maricel
Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
In reply to this post by Moe-25
        Hi, Moe. I'm sorry for my poor explanation before. I was replying from
my cell phone and it seems this usually is a bad idea :-)
        I'll trying again now:


Moe wrote:
> Daniel, I am not sure I understood what you or how that can be
> accomplished easily without introducing unnecessary complexity.

        I'm suspecting you're already introducing unnecessary complexity. I'll
explain better later.

> However,  I think the datasource plugin looks like what I am looking for.
>

        Yes, it probably is.


 > I am setting up a new environment, and need to split our project into
 > having two databases. That would be preferable.

 > One database should contain several domains that are related. The
 > other should contain stuff like user accounts and such.


        Ok. Based in my experience with non-Grails projects (feel free to
consider or to ignore any information from now on), this is a bad idea
in general. Unless you're already working with two different databases,
you probably will be better using two different schemas in *the same*
database (this can be false for Grails).
        I've worked in some projects that have started this way: The
application was divided in modules that could be used/sold without the
others (I don't know if this is your case). Then the next step was to
"split" the database model realization using a different database for
each module. After some months of development, this database design
decision begins to show its issues: You end up doing joins between
different db instances through a kind of database link (to reduce the
performance penalty of splitting your model) or, even worst, you end up
doing database joins at the application level instead of the database
level (to avoid some database link issue).

        So your application can become more complex or slower than the
necessary. That's why i've suggested to use 2 schemas in the same db
instance. In this way, you be able to isolate your app modules, if you
wish, and to provide the faster (and more reliable) way of one talking
with the other. But this is obviously dependent of what you need to do
and how easily Grails/GORM can work with different db schemas.


        From the PostgreSQL documentation:

        "(...) Users and groups of users are shared across the entire cluster,
but no other data is shared across databases. (...)
         There are several reasons why one might want to use schemas:

      * To allow many users to use one database without interfering with
each other.
      * To organize database objects into logical groups to make them
more manageable.
      * Third-party applications can be put into separate schemas so
they cannot collide with the names of other objects. (...)"

http://www.postgresql.org/docs/8.3/static/ddl-schemas.html


        From the PostgreSQL FAQ:

        "(...)
    How do I perform queries using multiple databases?

        There is no way to query a database other than the current one. Because
PostgreSQL loads database-specific system catalogs, it is uncertain how
a cross-database query should even behave.

        contrib/dblink allows cross-database queries using function calls. Of
course, a client can also make simultaneous connections to different
databases and merge the results on the client side.
        (...)"

http://wiki.postgresql.org/wiki/FAQ#How_do_I_perform_queries_using_multiple_databases.3F

        I think PostgreSQL 9 already has a dblink module.


        Just my 2 cents!

        Best regards,

                Daniel.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Moe-25
Thank you for your elaborate answer. Indeed valuable. It's as you said, it's modules that are somewhat isolated yet I do believe they would need to communicate with the other database and do some joins, so it might be a bad idea. 

I will try to see if the datasource plugin how and could be used together with two PostgreSQL schemas instead since that seems to provide better features. I would probably still need the datasource plugin to communicate with the two different schemas, right? Or does Grails offer good integration for this multiple schemas already?

Alternatively I might use just one schema for all. The problem is that on of the "separate" databases  ( incoming data ) grows with approximately 2-3 GB per month and is only read from our Grails app, that is why I wanted to isolate it, and yet be able to do simple backups and dumps. I am aware that I can do table specific dumps but those tend to less reliable to psql back. I have had issues with that on occasion. 

Thanks for your answer, Moe



On Tue, Feb 8, 2011 at 12:36 AM, Daniel Henrique Alves Lima <[hidden email]> wrote:
       Hi, Moe. I'm sorry for my poor explanation before. I was replying from my cell phone and it seems this usually is a bad idea :-)
       I'll trying again now:



Moe wrote:
Daniel, I am not sure I understood what you or how that can be accomplished easily without introducing unnecessary complexity.

       I'm suspecting you're already introducing unnecessary complexity. I'll explain better later.


However,  I think the datasource plugin looks like what I am looking for.

       Yes, it probably is.



> I am setting up a new environment, and need to split our project into
> having two databases. That would be preferable.

> One database should contain several domains that are related. The
> other should contain stuff like user accounts and such.


       Ok. Based in my experience with non-Grails projects (feel free to consider or to ignore any information from now on), this is a bad idea in general. Unless you're already working with two different databases, you probably will be better using two different schemas in *the same* database (this can be false for Grails).
       I've worked in some projects that have started this way: The application was divided in modules that could be used/sold without the others (I don't know if this is your case). Then the next step was to "split" the database model realization using a different database for each module. After some months of development, this database design decision begins to show its issues: You end up doing joins between different db instances through a kind of database link (to reduce the performance penalty of splitting your model) or, even worst, you end up doing database joins at the application level instead of the database level (to avoid some database link issue).

       So your application can become more complex or slower than the necessary. That's why i've suggested to use 2 schemas in the same db instance. In this way, you be able to isolate your app modules, if you wish, and to provide the faster (and more reliable) way of one talking with the other. But this is obviously dependent of what you need to do and how easily Grails/GORM can work with different db schemas.


       From the PostgreSQL documentation:

       "(...) Users and groups of users are shared across the entire cluster, but no other data is shared across databases. (...)
        There are several reasons why one might want to use schemas:

       * To allow many users to use one database without interfering with each other.
       * To organize database objects into logical groups to make them more manageable.
       * Third-party applications can be put into separate schemas so they cannot collide with the names of other objects. (...)"

http://www.postgresql.org/docs/8.3/static/ddl-schemas.html


       From the PostgreSQL FAQ:

       "(...)
       How do I perform queries using multiple databases?

       There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave.

       contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.
       (...)"

http://wiki.postgresql.org/wiki/FAQ#How_do_I_perform_queries_using_multiple_databases.3F

       I think PostgreSQL 9 already has a dblink module.


       Just my 2 cents!


       Best regards,

               Daniel.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

  http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
        Hi, Moe. I can run some tests later, but i believe that if you decide
to use two schemas, you will have 2 options:

1. To grant usage in one schema and to grant create in another to the
same user then to use a single database connection to see them both. In
this way you won't need datasources plugin after all. If i'm not wrong
you can also run the following command to avoid the necessity of full
schema qualification:

ALTER DATABASE my_db SET search_path = "$user", my_schema1, my_schema2,
public;
COMMIT;

        Then, you will need to map your domain classes properly. Be aware that
the following step is only necessary if your are using dbCreate option
in your Datasource.groovy. Otherwise, you won't need the table mapping
with schema information because of the search_path:

class MyX {
    static mapping = {
        table name: "some_table", schema: "my_schema1"
    }
}


class MyY {
    static mapping = {
        table name: "some_table", schema: "my_schema2"
    }
}


        If you have more db objects in one schema than another, you can use
this setting in your Config.groovy:


grails.gorm.default.mapping = {
     table schema:"my_schema1"
}


2. To grant usage in one schema for one user and to grant create in
another schema for another user then to use 2 db connections. In this
case you will need Datasources plugin, i think.


        Try to write a test application and see if everything works as expected.


        Best regards,

                        Daniel.


Moe wrote:
> Thank you for your elaborate answer. Indeed valuable. It's as you said,
> it's modules that are somewhat isolated yet I do believe they would need
> to communicate with the other database and do some joins, so it might be
> a bad idea.
> (...)

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Daniel Henrique Alves Lima
For dump purpouse you can use, pg_dump --schema --exclude-schema. It's
riskier, but you can test (for more info, consult postgresql docs).

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Two databases with GORM. Both Postgres.

Moe-25
Thanks Daniel, I am on a tight deadline on this project and unable to experiment with this now, but I will try it as soon as I get a chance. 

Hopefully we can continue this discussion then. To be able to develop against a large and changing content production table, it's going to become necessary I think to have a connection to that table and not having to keep up with a similar version on a local computer. Those dumps and restore on that table will become a bottleneck in the development process. Using dbCreate="...." with update or create will become a problem. We really need to support separate dbs/schemas because of the ability to keep our. 

Hopefully I will have an opportunity to look this topic in depth in the coming weeks. 

Thanks again, Moe





On Tue, Feb 8, 2011 at 11:21 AM, Daniel Henrique Alves Lima <[hidden email]> wrote:
For dump purpouse you can use, pg_dump --schema --exclude-schema. It's
riskier, but you can test (for more info, consult postgresql docs).

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email