# Connecting to postgres **URL:** \ **Category:** Rails\ **Created:** [February 28, 2007, 2:24am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087 "2007-02-28T02:24:45Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 2:24am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/1 "2007-02-28T02:24:45Z")
Hello everyone! Please forgive my ignorance, am pretty new to rails. Am creating an application on rails framework that will use a legacy postgres database with about 119 tables and 121 sequences that define the primary key fields. The database is ready now and i need to hook it to active:record:Base i have installed these two gems: postgres-pr ruby-postgres My YML file reads like this: development: adapter: postgresql database: DB\_NAME username: george password: tanzanite host: localhost schema\_order: public # Warning: The database defined as ‘test’ will be erased and # re-generated from your development database when you run ‘rake’. # Do not set this db to the same as development or production. test: adapter: postgresql database: DB\_NAME\_test username: tanzanite password: password host: localhost schema\_order: public production: adapter: postgresql database: DB\_NAME\_production username: tanzanite password: password host: localhost schema\_order: public i then wrote a configuration file that stops pluralization of the tables. i generated a model scaffold to “test the waters” and lol and behold! it could not connect to through active:record. Please can someone point me to a useful resource to guide me on how to accomplish this. All resources seem to use mysql and am only finding no information on connecting to postgres legacy databases. How do i map the recquired id field to my user-defined primary key generically named Tablename\_id? What configuration details am i missing? Thank you. rgds tanzanite ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 3:24am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/2 "2007-02-28T03:24:08Z")
Hello, database.yml seems to be ok (maybe schema\_order… i don’t know i don’t use this parameter). are you sure about your postgresql database config? can you connect from application server to your postgresql database? if not check : - pg\_hba.conf should be configure to allow application server to connect. - and listen\_addresses in postgresql.conf should be correct to. bye On 28 fév, 02:24, Tanzanite T. \ **Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 3:42am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/3 "2007-02-28T03:42:09Z") 1. What exceptions are in logs? 2. For legacy databases you might find useful class methods set\_primary\_key, set\_table\_name, set\_sequence\_name, see [ActiveRecord::Base](http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001013) and primary\_key\_prefix\_type [http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active\_record/base.rb?rev=6139#L279](http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active_record/base.rb?rev=6139#L279) 2007/2/28, Tanzanite T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 3:53am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/4 "2007-02-28T03:53:31Z")
Lugovoi N. wrote: > 1. What exceptions are in logs? > > 2. For legacy databases you might find useful class methods > set\_primary\_key, set\_table\_name, set\_sequence\_name, see > [ActiveRecord::Base](http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001013) > and primary\_key\_prefix\_type > [http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active\_record/base.rb?rev=6139#L279](http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active_record/base.rb?rev=6139#L279) > > 2007/2/28, Tanzanite T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): Comments appreciated This is what is in the logs ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission denied for relation feature Faclchk.c L1395 Raclcheck\_error: SELECT count(\*) AS count\_all FROM feature ): C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active\_record/connection\_adapters/abstract\_adapter.rb:128:in `log' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in `execute’ C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active\_record/connection\_adapters/postgresql\_adapter.rb:433:in `select' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `select\_one’ C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active\_record/connection\_adapters/abstract/database\_statements.rb:19:in `select_value' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:212:in `execute\_simple\_calculation’ C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active\_record/calculations.rb:121:in `calculate' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:117:in `catch’ C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active\_record/calculations.rb:117:in `calculate' C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/calculations.rb:45:in `count’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/pagination.rb:173:in `count_collection_for_pagination' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/pagination.rb:197:in `paginator\_and\_collection\_for’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/pagination.rb:130:in `paginate' /app/controllers/admin_controller.rb:12:in `list’ /app/controllers/admin\_controller.rb:3:in `index' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:1095:in `send’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/base.rb:1095:in `perform_action_without_filters' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:632:in `call\_filter’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/filters.rb:634:in `call_filter' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/filters.rb:619:in `perform\_action\_without\_benchmark’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/benchmarking.rb:66:in `perform_action_without_rescue' C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/benchmarking.rb:66:in `perform_action_without_rescue' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/rescue.rb:83:in `perform\_action’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/base.rb:430:in `send' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/base.rb:430:in `process\_without\_filters’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/filters.rb:624:in `process_without_session_management_support' C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action_controller/session_management.rb:114:in `process’ C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.13.1/lib/action\_controller/base.rb:330:in `process' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/dispatcher.rb:41:in `dispatch’ C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick\_server.rb:113:in `handle_dispatch' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick_server.rb:79:in `service’ C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:104:in `service' C:/ruby/lib/ruby/1.8/webrick/httpserver.rb:65:in `run’ C:/ruby/lib/ruby/1.8/webrick/server.rb:173:in `start_thread' C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start’ C:/ruby/lib/ruby/1.8/webrick/server.rb:162:in `start_thread' C:/ruby/lib/ruby/1.8/webrick/server.rb:95:in `start’ C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `each' C:/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start’ C:/ruby/lib/ruby/1.8/webrick/server.rb:23:in `start' C:/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start’ C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/webrick\_server.rb:63:in `dispatch' C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/servers/webrick.rb:59 C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem\_original\_require’ C:/ruby/lib/ruby/site\_ruby/1.8/rubygems/custom\_require.rb:27:in `require' C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in `require’ C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active\_support/dependencies.rb:343:in `new_constants_in' C:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.4.0/lib/active_support/dependencies.rb:496:in `require’ C:/ruby/lib/ruby/gems/1.8/gems/rails-1.2.1/lib/commands/server.rb:39 C:/ruby/lib/ruby/site\_ruby/1.8/rubygems/custom\_require.rb:27:in `gem_original_require' C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require’ ./script/server:3 -e:4:in `load’ -e:4 ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 4:01am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/5 "2007-02-28T04:01:12Z")
2007/2/28, Tanzanite T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > This is what is in the logs > > ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission > denied for relation feature Faclchk.c L1395 Raclcheck\_error: SELECT > count(\*) AS count\_all FROM feature ): So, the problem shows out clear: you connect to database with postgres role that does not have enough privilieges. Make sure you did GRANT’ed necessary access to database objects. > **[GRANT](https://www.postgresql.org/docs/current/sql-grant.html)** > > GRANT GRANT — define access privileges Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | … ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 4:15am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/6 "2007-02-28T04:15:37Z")
Lugovoi N. wrote: > 2007/2/28, Tanzanite T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > > > This is what is in the logs > > > > ActiveRecord::StatementInvalid (RuntimeError: ERROR C42501 Mpermission > > denied for relation feature Faclchk.c L1395 Raclcheck\_error: SELECT > > count(\*) AS count\_all FROM feature ): > > So, the problem shows out clear: you connect to database with postgres > role that does not have enough privilieges. > Make sure you did GRANT’ed necessary access to database objects. > [PostgreSQL: Documentation: 15: GRANT](http://www.postgresql.org/docs/current/static/sql-grant.html) Hey! Thank you! Thank you! it worked These are the additional configurations i have added: i have manually set the primary key to the actual name of the primary key in my table. This is not a DRY compliant method and i have to change that so that i dont have to repeat that for each of the 119 tables! but otherwise it means it can work and for that am greatful for your comments! rgds tanzanite ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 3:56am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/7 "2007-02-28T03:56:27Z")
Jean-sébastien Jney wrote: > Hello, > > database.yml seems to be ok (maybe schema\_order… i don’t know i > don’t use this parameter). > are you sure about your postgresql database config? > can you connect from application server to your postgresql database? > if not check : > > - pg\_hba.conf should be configure to allow application server to > connect. > - and listen\_addresses in postgresql.conf should be correct to. > > bye > > On 28 f�v, 02:24, Tanzanite T. \ **Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 4:55am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/8 "2007-02-28T04:55:22Z") 2007/2/28, Tanzanite T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > These are the additional configurations i have added: > i have manually set the primary key to the actual name of the primary > key in my table. This is not a DRY compliant method and i have to change > that so that i dont have to repeat that for each of the 119 tables! Well, using legacy database schema that does not conform to Rails conventions and cannot be changed, is indeed not very DRY. e.g. when defining associations one may need to declare foreign key names and class names explicitly, but from my own experience it’s not that bad even with dozens of tables 🙂 But, you can dig postgres system tables (I mean with some ruby/sql script) and build first scratch version of ActiveRecord models automatically. ---
**Author:** ![georgeuoa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/7ab992/32.png) [@georgeuoa](https://www.ruby-forum.com/u/georgeuoa)\ **Post date:** [February 28, 2007, 11:15am UTC](https://www.ruby-forum.com/t/connecting-to-postgres/87087/9 "2007-02-28T11:15:50Z")
Hi, for all those who may be wishing to integrate postgresql into their rails framework, I received this from the postgresql list: ============================================================= Given the recent discussions of applications stacks, PHP & Ruby etc. it seems an ideal time for me to introduce a project I’ve been working on. StackBuilder is an extension of the Windows installer for PostgreSQL that will allow the user to quickly and easily download and install additional software to build the application stack they desire around PostgreSQL. The project includes the StackBuilder wizard as well as a toolkit for building application installers. Whilst the StackBuilder itself is still in development, the first application installer, pgRails, is available for manual download and testing from [http://pgfoundry.org/projects/stackbuilder/](http://pgfoundry.org/projects/stackbuilder/) pgRails is a distribution of Ruby, Rails, and the Ruby PostgreSQL connector all preconfigured for use with PostgreSQL on Windows 2000 and above. I’d like to invite anyone who is interested to download and try it out, and report any issues or problems using the project’s trackers on pgFoundry. # Regards, Dave. Hope that helps someone… 😉 – Regards Andrew