我正在尝试将Postgresql Redshift数据库添加到我的Rails 4应用程序中,以便在本地和生产中使用。我先在开发中测试。
我已将database.yml文件更改为如下所示:

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

现在,当我点击localhost:3000时,我得到这个错误:
将参数“client_min_messages”设置为“warning”的权限被拒绝
:将客户端消息设置为“警告”
我好像找不出是什么原因造成的-似乎我的新数据库不允许SET命令?我不太确定,但我很感激你的帮助。

最佳答案

刚刚在别的地方回答了这个问题,但我今天也遇到了同样的问题,以下是我所做的,现在它正在工作:

#app/models/data_warehouse.rb
class DataWarehouse < ActiveRecord::Base
  establish_connection "redshift_staging"
  #or, if you want to have a db per environment
  #establish_connection "redshift_#{Rails.env}"
end

注意,我们连接的是5439,而不是默认的5432,所以我指定了端口
另外,我指定了一个模式beta,这是我们用于不稳定聚合的模式,您可以按照上面提到的每个环境使用不同的db,或者使用不同的模式并将它们包含在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 be an aws url from Redshift admin console

###选项2,直接PG连接
  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 to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'
(pry) output error: #<ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'>

更新:
我最终选择了选项1,但是现在使用这个适配器有多种原因:
https://github.com/fiksu/activerecord-redshift-adapter
原因1:ActiveRecord postgresql适配器设置客户端消息
原因2:适配器还尝试设置时区,而redshift不允许设置时区(http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html
原因3:即使您为前两个错误更改了ActiveRecord中的代码,您也会遇到其他错误,这些错误抱怨Redshift正在使用Postgresql 8.0,此时,我转到适配器,如果以后发现更好的代码,将重新访问并更新。
我将我的表重命名为base_aggregate_redshift_tests(注意复数),这样ActiveRecord很容易连接,如果不能在redshift中更改表名,请使用下面我注释过的set_t able方法
#Gemfile:
gem 'activerecord4-redshift-adapter', github: 'aamine/activerecord4-redshift-adapter'

选择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 to match my tables in Redshift, if you want you can set_table like I have commented out below

class BaseAggregatesRedshiftTest < ActiveRecord::Base
  establish_connection "redshift_staging"
  self.table_name = "beta.base_aggregates_v2"
end

在控制台中使用self.table_name——注意它查询了正确的表,因此您可以根据需要为模型命名
[DEV] main:0> redshift = BaseAggregatesRedshiftTest.first
D, [2014-07-17T15:31:58.678103 #43776] DEBUG -- :   BaseAggregatesRedshiftTest Load (45.6ms)  SELECT "beta"."base_aggregates_v2".* FROM "beta"."base_aggregates_v2" LIMIT 1

选择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, now it's using the new name for my redshift table, just assuming I've got the record at base_aggregates_redshift_tests because I didn't set the 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".* FROM "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
                                                }

祝你好运!

关于ruby-on-rails - 将外部数据库连接到Rails 4 App时出错,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23016500/

10-16 22:58