Ruby-on-rails 在新的 Rails 项目中从 SQLite 更改为 PostgreSQL

Change from SQLite to PostgreSQL in a fresh Rails project

提问by Vasseurth

I have a rails app that's databases are in SQLite (The dev and production). Since I am moving to heroku, I want to convert my database to PostgreSQL.

我有一个 Rails 应用程序，它的数据库位于 SQLite（开发和生产）中。由于我要转移到 heroku，我想将我的数据库转换为 PostgreSQL。

Anyways, I heard that the local, development, database does not need to be changed from SQLite, so I don't need to change that, however, how do I go about changing the production environment from SQLite to PostgreSQL?

无论如何，我听说本地、开发、数据库不需要从 SQLite 更改，所以我不需要更改，但是，我如何将生产环境从 SQLite 更改为 PostgreSQL？

Has anyone ever done this before and can help?

有没有人以前做过这个并且可以提供帮助？

P.S. I'm not sure what exactly this process is called, but I've heard about migrating the database from SQLite to PostgreSQL, is that what needs to be done?

PS 我不确定这个过程到底叫什么，但我听说过将数据库从 SQLite 迁移到 PostgreSQL，这是需要做的吗？

采纳答案by Chris Barretto

You can change your database.yml to this instead of using the out of the box sqlite one:

您可以将 database.yml 更改为此，而不是使用开箱即用的 sqlite 之一：

development:
  adapter: postgresql
  encoding: utf8
  database: project_development
  pool: 5
  username: 
  password:

test: &TEST
  adapter: postgresql
  encoding: utf8
  database: project_test
  pool: 5
  username: 
  password:

production:
  adapter: postgresql
  encoding: utf8
  database: project_production
  pool: 5
  username: 
  password:

cucumber:
  <<: *TEST

回答by ardochhigh

The steps below worked for me. It uses the tapsgem, created by Heroku and mentioned in Ryan Bates's Railscast #342. There are a few steps but it worked perfectly (even dates were correctly migrated), and it was far easier than the Oracle -> DB2 or SQL Server -> Oracle migrations I have done in the past.

以下步骤对我有用。它使用了由 Heroku 创建并在 Ryan Bates 的 Railscast #342 中提到的tapsgem。有几个步骤，但它工作得很好（即使日期被正确迁移），而且它比我过去做过的 Oracle -> DB2 或 SQL Server -> Oracle 迁移要容易得多。

Note that SQLite does not have a user id or password, but the taps gem requires something. I just used the literals "user" and "password".

请注意，SQLite 没有用户 ID 或密码，但 taps gem 需要一些东西。我只是使用了文字“用户”和“密码”。

Create the Postgres database user for the new databases

为新数据库创建 Postgres 数据库用户

$ createuser f3
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

EDIT - Updated command below - use this instead

编辑 - 下面更新了命令 - 改用它

$ createuser f3 -d -s

Create the required databases

创建所需的数据库

$ createdb -Of3 -Eutf8 f3_development
$ createdb -Of3 -Eutf8 f3_test

Update the Gemfile

更新 Gemfile

gem 'sqlite3'
gem 'pg'
gem 'taps'
$ bundle

Update database.yml

更新数据库.yml

#development:
#  adapter: sqlite3
#  database: db/development.sqlite3
#  pool: 5
#  timeout: 5000

development:
  adapter: postgresql
  encoding: unicode
  database: f3_development
  pool: 5
  username: f3
  password:

#test:
#  adapter: sqlite3
#  database: db/test.sqlite3
#  pool: 5
#  timeout: 5000

test:
  adapter: postgresql
  encoding: unicode
  database: f3_test
  pool: 5
  username: f3
  password:

Start the taps server on the sqlite database

在 sqlite 数据库上启动 taps 服务器

$ taps server sqlite://db/development.sqlite3 user password

Migrate the data

迁移数据

$ taps pull postgres://f3@localhost/f3_development http://user:password@localhost:5000

Restart the Rails webserver

重启 Rails 网络服务器

$ rails s

Cleanup the Gemfile

清理 Gemfile

#gem 'sqlite3'
gem 'pg'
#gem 'taps'
$ bundle

回答by Jesse Wolgamott

Since you're moving to heroku, you can use taps to do this:

由于您要迁移到 heroku，因此可以使用点击来执行此操作：

heroku db:push

This will push your local development sqlite data to production, and heroku will automagically convert to postgres for you.

这会将您的本地开发 sqlite 数据推送到生产环境，heroku 会自动为您转换为 postgres。

This should also work to push a production sqlite db to heroku, but it's not tested.

这也应该可以将生产 sqlite db 推送到 heroku，但它没有经过测试。

RAILS_ENV=production heroku db:push

回答by Gus Shortz

you will also need to add the line "gem 'pg'" to your gemfile, 'pg' being the current postgres gem for Rails.

您还需要将“ gem 'pg'”行添加到您的 gemfile 中，'pg' 是当前用于 Rails 的 postgres gem。

回答by jungledre

Simply update the config/database.yml file:

只需更新 config/database.yml 文件：

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: projectname_development

test:
  <<: *default
  database: projectname_test

production:
  <<: *default
  database: projectname_production
  username: 
  password: 

The above is what's generated when you run:

以上是运行时生成的内容：

$ rails new projectname --database=postgresql --skip-test-unit

Also add this to your Gemfile:

还要将此添加到您的 Gemfile 中：

gem 'pg'

回答by K ABHIRAM

Now its become easy with the command

现在使用命令变得容易

bin/rails db:system:change --to=postgresql

reference

参考

https://github.com/rails/rails/pull/34832

回答by Zorak

After replacing gem 'sqlite3with gem pgin the gemfile, I kept getting the sqlite3 errorwhen pushing to Heroku master because I forgot to commit the updated gemfile. Simply doing the following solved this:

在gemfile'sqlite3中用 gem替换 gem 后pg，我一直sqlite3 error在推送到 Heroku master 时收到，因为我忘记提交更新的 gemfile。只需执行以下操作即可解决此问题：

git add .
git commit -m 'heroku push'
heroku create 
git push heroku master

回答by sunil

Just Update you datatbase.yml

只需更新您的 datatbase.yml

development: &development
  adapter: postgresql
  database: Your_database_name
  username: user_name
  password: password
  host:     localhost
  schema_search_path: public
  min_messages: warning

test:
  <<: *development
  database: test_database_name

production:
  <<: *development
  database: production_db_name

We are using rails and the basic standards should be follow like DRY, Convention over Configuration etc.. so in above code we are not repeating same code again and again.

我们正在使用 rails 并且应该遵循基本标准，如 DRY、约定优于配置等。所以在上面的代码中，我们不会一次又一次地重复相同的代码。

回答by Justin Houk

It's been mentioned above me, but I don't have enough reputation as a lurker to be able to upvote it. In the hopes of drawing a little more attention for Rails newbies reading this answer:

它已经在我上面提到过，但我作为潜伏者没有足够的声誉来支持它。希望引起阅读此答案的 Rails 新手的更多关注：

you will also need to add the line "gem 'pg'" to your gemfile, 'pg' being the current postgres gem for Rails.

您还需要将“gem 'pg'”行添加到您的 gemfile 中，'pg' 是当前 Rails 的 postgres gem。

^^^ This is a key piece in addition to the database.yml file described in the selected answer to migrate your Rails app to Postgres.

^^^ 除了所选答案中描述的 database.yml 文件之外，这是将 Rails 应用程序迁移到 Postgres 的关键部分。

回答by ianks

This is how I have mine setup. If you are only using MRI and not Jruby you can skip the logic in the adapter settings.

这就是我的设置方式。如果您只使用 MRI 而不是 Jruby，您可以跳过适配器设置中的逻辑。

defaults: &defaults
  adapter: <%= RUBY_ENGINE == 'ruby' ? 'postgresql' : 'jdbcpostgresql' %>
  encoding: unicode
  pool: 5
  timeout: 5000

development:
  database: project_development
  <<: *defaults

test:
  database: project_test
  <<: *defaults

production:
  database: project_production
  <<: *defaults