database - Error connecting external DB to Rails 4 App -

i attempting add postgresql redshift database rails 4 app, use locally , in production. testing in development first.

i have altered database.yml file this:

development:   adapter: postgresql   encoding: unicode   database: new_db   pool: 5   username: test   password: password   host: test_db.us-east-1.redshift.amazonaws.com   port: 5439 

now, when hit localhost:3000 error:

permission denied set parameter "client_min_messages" "warning" : set client_min_messages 'warning'

i can't seem find out causing - seems maybe new db isn't allow set command? i'm not sure, appreciated.

just answered somewhere else, had same issues today, here's did , it's working now:

#app/models/data_warehouse.rb class datawarehouse < activerecord::base                         establish_connection "redshift_staging"   #or, if want have db per environment   #establish_connection "redshift_#{rails.env}" end 

note connecting on 5439, not default 5432 specify port also, specify schema, beta, use our unstable aggregates, either have different db per environment mentioned above, or use various schemas , include them in search path activerecord

#config/database.yml redshift_staging:                                                             adapter: postgresql                                                         encoding: utf8                                                              database: db03                                                            port: 5439                                                                  pool: 5                                                                     schema_search_path: 'beta'                                                                                             username: admin                                                           password: supersecretpassword                                                  host: db03.myremotehost.us  #your remote host here, might aws url redshift admin console  

###option 2, direct pg connection

  class datawarehouse < activerecord::base                            attr_accessor :conn                                                             def initialize                                                                   @conn = pg.connect(                                                             database: 'db03',                                                           port: 5439,                                                                   pool: 5,                                                                      schema_search_path: 'beta',                                                   username: 'admin',                                                          password: 'supersecretpassword',                                                 host: 'db03.myremotehost.us'                                                      )                                                                            end       end   [dev] main:0> redshift = datawarehouse e, [2014-07-17t11:09:17.758957 #44535] error -- : pg::insufficientprivilege: error:  permission denied set parameter "client_min_messages" "notice" : set client_min_messages 'notice' (pry) output error: #<activerecord::statementinvalid: pg::insufficientprivilege: error:  permission denied set parameter "client_min_messages" "notice" : set client_min_messages 'notice'>    

update:

i ended going option 1, using adapter multiple reasons:

https://github.com/fiksu/activerecord-redshift-adapter

reason 1: activerecord postgresql adapter sets client_min_messages reason 2: adapter attempts set time zone, redshift doesn't allow (http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) reason 3: if change code in activerecord first 2 errors, run additional errors complain redshift using postgresql 8.0, @ point moved on adapter, revisit , update if find better later.

i renamed table base_aggregate_redshift_tests (notice plural) activerecord able connect, if can't change table names in redshift use set_table method have commented out below

#gemfile: gem 'activerecord4-redshift-adapter', github: 'aamine/activerecord4-redshift-adapter' 

option 1

#config/database.yml redshift_staging:                                                                                                                adapter: redshift                                                                                                              encoding: utf8                                                                                                                 database: db03                                                                                                              port: 5439                                                                                                                     pool: 5                                                                                                                        username: admin                                                                                                   password: supersecretpassword                                                                                                     host: db03.myremotehost.us                                                                                                          timeout: 5000     #app/models/base_aggregates_redshift_test.rb #model named match tables in redshift, if want can set_table have commented out below  class baseaggregatesredshifttest < activerecord::base   establish_connection "redshift_staging"   self.table_name = "beta.base_aggregates_v2" end 

in console using self.table_name -- notice queries right table, can name models whatever want

[dev] main:0> redshift = baseaggregatesredshifttest.first                                                                     d, [2014-07-17t15:31:58.678103 #43776] debug -- :   baseaggregatesredshifttest load (45.6ms)  select "beta"."base_aggregates_v2".* "beta"."base_aggregates_v2" limit 1             

option 2

#app/models/base_aggregates_redshift_test.rb class baseaggregatesredshifttest < activerecord::base   set_table "beta.base_aggregates_v2"    activerecord::base.establish_connection(     adapter: 'redshift',     encoding: 'utf8',     database: 'staging',     port: '5439',     pool: '5',     username: 'admin',     password: 'supersecretpassword',     search_schema: 'beta',     host: 'db03.myremotehost.us',     timeout: '5000'   )  end  #in console, abbreviated example of first record, it's using new name redshift table, assuming i've got record @ base_aggregates_redshift_tests because didn't set table_name  [dev] main:0> redshift = baseaggregatesredshifttest.first d, [2014-07-17t15:09:39.388918 #11537] debug -- :   baseaggregatesredshifttest load (45.3ms)  select "base_aggregates_redshift_tests".* "base_aggregates_redshift_tests" limit 1 #<baseaggregatesredshifttest:0x007fd8c4a12580> {                                                 :truncated_month => thu, 31 jan 2013 19:00:00 est -05:00,                                                 :dma => "cityville",                                                 :group_id => 9712338,                                                 :dma_id => 9999                                                  } 

good luck!