gpt4 book ai didi

ruby-on-rails - 将外部数据库连接到 Rails 4 App 时出错

转载 作者:行者123 更新时间:2023-11-29 13:31:07 24 4
gpt4 key购买 nike

我正在尝试将 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 时,我得到了这个错误:

permission denied to set parameter "client_min_messages" to "warning" : SET client_min_messages TO '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,这是我们用于不稳定聚合的模式,您可以如上所述为每个环境使用不同的数据库,或者使用各种模式并将它们包含在 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

###OPTION 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

原因一:ActiveRecord postgresql适配器设置client_min_messages原因 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_table 方法

#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/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com