Anatol Pomozov
2005-Oct-28 19:08 UTC
PostgreSQL. Multiply schemas in one database. How to?
Hi all. I am using PostgreSQL in my current project and I have couple of questions on this topic. Previously I have used 3 databases for project. i.e. megaproject_development, megaproject_test and megaproject_development. But I don''t like this variant because of ''rake dump_db_structure'' writes a lot of PostgreSQL specific data for structure file (operators, functions, views...) that are belongs to public schema. This info makes sql file huge (1500 lines) - I dont like such files with a lot of unnecessary information. And probably it would break if different devs working on different versions of db. So I decided to make following structure that seems much prettier for me megaproject_db - public schema (db info) - test schema - development schema - production schema Does anybody tried to make the same thing that I want to do? I found in wiki that I need to add schema_search_path to db config file. So first question: why so strange name for schema property?? I have looked through code and found that this property contains a list of schema name. And table names are looking for thought all this schemas. I am wonder, is anybody need it (i mean multiply schemas)? Well, after configuring I tried to run rake test but it is failed with log printed below. How to solve it and make it running?? ** Invoke clone_structure_to_test (first_time) ** Invoke db_structure_dump (first_time) ** Invoke environment (first_time) ** Execute environment ** Execute db_structure_dump ** Invoke purge_test_database (first_time) ** Invoke environment ** Execute purge_test_database dropdb: database removal failed: ERROR: database "darkthrone" is being accessed by other users createdb: database creation failed: ERROR: database "darkthrone" already exists ** Execute clone_structure_to_test psql:db/development_structure.sql:13: ERROR: schema "development" already exists psql:db/development_structure.sql:27: ERROR: schema "test" already exists psql:db/development_structure.sql:34: ERROR: language "plpgsql" already exists psql:db/development_structure.sql:45: ERROR: function "gtsvector_in" already exists with same argument types psql:db/development_structure.sql:54: ERROR: function "gtsvector_out" already exists with same argument types psql:db/development_structure.sql:67: ERROR: type "gtsvector" already exists psql:db/development_structure.sql:76: ERROR: function "tsquery_in" already exists with same argument types psql:db/development_structure.sql:85: ERROR: function "tsquery_out" already exists with same argument types psql:db/development_structure.sql:98: ERROR: type "tsquery" already exists psql:db/development_structure.sql:107: ERROR: function "tsvector_in" already exists with same argument types psql:db/development_structure.sql:116: ERROR: function "tsvector_out" already exists with same argument types psql:db/development_structure.sql:129: ERROR: type "tsvector" already exists psql:db/development_structure.sql:140: ERROR: relation "statinfo" already exists psql:db/development_structure.sql:150: ERROR: relation "tokenout" already exists psql:db/development_structure.sql:161: ERROR: relation "tokentype" already exists psql:db/development_structure.sql:175: ERROR: relation "tsdebug" already exists psql:db/development_structure.sql:184: ERROR: function "_get_parser_from_curcfg" already exists with same argument types psql:db/development_structure.sql:193: ERROR: function "concat" already exists with same argument types psql:db/development_structure.sql:202: ERROR: function "dex_init" already exists with same argument types psql:db/development_structure.sql:211: ERROR: function "dex_lexize" already exists with same argument types psql:db/development_structure.sql:220: ERROR: function "exectsq" already exists with same argument types ................ -- anatol _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Jeremy Kemper
2005-Oct-28 19:45 UTC
Re: PostgreSQL. Multiply schemas in one database. How to?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Oct 28, 2005, at 12:08 PM, Anatol Pomozov wrote:> Previously I have used 3 databases for project. i.e. > megaproject_development, megaproject_test and megaproject_development. > But I don''t like this variant because of ''rake dump_db_structure'' > writes a lot of PostgreSQL specific data for structure file > (operators, functions, views...) that are belongs to public schema. > This info makes sql file huge (1500 lines) - I dont like such files > with a lot of unnecessary information. And probably it would break > if different devs working on different versions of db.I would start by writing my own rake task here, to dump just what you want.> So I decided to make following structure that seems much prettier > for me > megaproject_db > - public schema (db info) > - test schema > - development schema > - production schema > > Does anybody tried to make the same thing that I want to do?I don''t think this is a good idea. I use multiple schemas in a group of cooperating apps, but not to emulate separate database deployments.> I found in wiki that I need to add schema_search_path to db config > file. > So first question: why so strange name for schema property?? I have > looked through code and found that this property contains a list of > schema name. And table names are looking for thought all this > schemas. I am wonder, is anybody need it (i mean multiply schemas)?The name for schema_search_path comes directly from PostgreSQL: SET search_path=''...'' The schema_ prefix is added in Rails for clarity. I use multiple schemas so that multiple apps can have their own tables and others that rely on the ''core'' schema.> Well, after configuring I tried to run rake test but it is failed > with log printed below. How to solve it and make it running?? > > ** Invoke clone_structure_to_test (first_time) > ** Invoke db_structure_dump (first_time) > ** Invoke environment (first_time) > ** Execute environment > ** Execute db_structure_dump > ** Invoke purge_test_database (first_time) > ** Invoke environment > ** Execute purge_test_database > dropdb: database removal failed: ERROR: database "darkthrone" is > being accessed by other users > createdb: database creation failed: ERROR: database "darkthrone" > already exists > ** Execute clone_structure_to_test > ....Someone is accessing the database, such as your running Rails app. Stop script/server (or Apache or lighttpd or ...) then try again. Best, jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDYn/rAQHALep9HFYRAoB/AJwMIWSyvzR859NK5N4rsDjEF0xePACeJxoS 1Ts/g34MoG14vFJx2POg16k=bfKg -----END PGP SIGNATURE-----
Anatol Pomozov
2005-Oct-29 10:52 UTC
Re: PostgreSQL. Multiply schemas in one database. How to?
Thanks, Jeremy for your response.
I made 3 databases (as it was before) but put all my tables to separate
schema.
It works except one thing - incorrect pg_dump invocation. pg_dump tries to
dump all database but I need to dump only my schema. So I rewrote standard
:db_structure_dump task. I just changed pg_dump command line and added -n
"#{abcs[RAILS_ENV]["schema_search_path"]}" parameter. It
says to dump only
my schema objects. I''m wonder if make sense to in standard task (in
rails
project)
On 10/28/05, Jeremy Kemper
<jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org>
wrote:>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Oct 28, 2005, at 12:08 PM, Anatol Pomozov wrote:
> > Previously I have used 3 databases for project. i.e.
> > megaproject_development, megaproject_test and megaproject_development.
> > But I don''t like this variant because of ''rake
dump_db_structure''
> > writes a lot of PostgreSQL specific data for structure file
> > (operators, functions, views...) that are belongs to public schema.
> > This info makes sql file huge (1500 lines) - I dont like such files
> > with a lot of unnecessary information. And probably it would break
> > if different devs working on different versions of db.
>
> I would start by writing my own rake task here, to dump just what you
> want.
>
>
> > So I decided to make following structure that seems much prettier
> > for me
> > megaproject_db
> > - public schema (db info)
> > - test schema
> > - development schema
> > - production schema
> >
> > Does anybody tried to make the same thing that I want to do?
>
> I don''t think this is a good idea. I use multiple schemas in a
group
> of cooperating apps, but not to emulate separate database deployments.
>
>
> > I found in wiki that I need to add schema_search_path to db config
> > file.
> > So first question: why so strange name for schema property?? I have
> > looked through code and found that this property contains a list of
> > schema name. And table names are looking for thought all this
> > schemas. I am wonder, is anybody need it (i mean multiply schemas)?
>
> The name for schema_search_path comes directly from PostgreSQL:
> SET search_path=''...''
> The schema_ prefix is added in Rails for clarity.
>
> I use multiple schemas so that multiple apps can have their own
> tables and others that rely on the ''core'' schema.
>
>
> > Well, after configuring I tried to run rake test but it is failed
> > with log printed below. How to solve it and make it running??
> >
> > ** Invoke clone_structure_to_test (first_time)
> > ** Invoke db_structure_dump (first_time)
> > ** Invoke environment (first_time)
> > ** Execute environment
> > ** Execute db_structure_dump
> > ** Invoke purge_test_database (first_time)
> > ** Invoke environment
> > ** Execute purge_test_database
> > dropdb: database removal failed: ERROR: database
"darkthrone" is
> > being accessed by other users
> > createdb: database creation failed: ERROR: database
"darkthrone"
> > already exists
> > ** Execute clone_structure_to_test
> > ....
>
> Someone is accessing the database, such as your running Rails app.
>
> Stop script/server (or Apache or lighttpd or ...) then try again.
>
> Best,
> jeremy
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2 (Darwin)
>
> iD8DBQFDYn/rAQHALep9HFYRAoB/AJwMIWSyvzR859NK5N4rsDjEF0xePACeJxoS
> 1Ts/g34MoG14vFJx2POg16k> =bfKg
> -----END PGP SIGNATURE-----
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
anatol
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Jeremy Kemper
2005-Oct-29 19:58 UTC
Re: PostgreSQL. Multiply schemas in one database. How to?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Oct 29, 2005, at 3:52 AM, Anatol Pomozov wrote:> Thanks, Jeremy for your response. > > I made 3 databases (as it was before) but put all my tables to > separate schema. > > It works except one thing - incorrect pg_dump invocation. pg_dump > tries to dump all database but I need to dump only my schema. So I > rewrote standard :db_structure_dump task. I just changed pg_dump > command line and added -n "#{abcs[RAILS_ENV] > ["schema_search_path"]}" parameter. It says to dump only my schema > objects. I''m wonder if make sense to in standard task (in rails > project)That''s great idea, Anatol! Look for it in Rails 1.0: http://dev.rubyonrails.org/changeset/2814 Best, jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDY9RlAQHALep9HFYRAlY4AJ4lSqhlsyd6t4T4ttX7efsLJBb9yQCgmLev 3LqvhYQ+UyxLa3H+xMooTuA=IMe8 -----END PGP SIGNATURE-----