在一个新的Rails项目中从SQLite改为PostgreSQL。

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?

我不确定这个过程到底是怎么回事，但是我听说过将数据库从SQLite迁移到PostgreSQL，这是需要做的吗?

11 个解决方案

#1

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

您可以更改数据库。yml to this而不是使用开箱即用的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

#2

The steps below worked for me. It uses the taps gem, 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的railscs# 342中提到。虽然有一些步骤，但它工作得很好(甚至可以正确地迁移日期)，而且它比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或密码，但是tap gem需要一些东西。我只是用了字母“user”和“password”。

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

更新的形式

#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数据库上启动tap服务器

$ 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

#3

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

#4

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

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

#5

Simply update the config/database.yml file:

只是更新配置/数据库。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'

#6

After replacing gem 'sqlite3 with gem pg in the gemfile, I kept getting the sqlite3 error when pushing to Heroku master because I forgot to commit the updated gemfile. Simply doing the following solved this:

在gemfile中使用gem pg替换了gem的sqlite3之后，我在推到Heroku master时不断地得到sqlite3错误，因为我忘记提交更新的gemfile了。简单地做以下事情就解决了这个问题:

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

#7

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、约定优于配置等。因此，在上面的代码中，我们不会一次又一次地重复相同的代码。

#8

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.

您还需要在gemfile中添加一行“gem 'pg”，“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.

^ ^ ^这是一个关键部分除了数据库。在选择的答案中描述的yml文件将您的Rails应用迁移到Postgres。

#9

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

#10

You can try following: sqlite3 development.db .dump | psql dbname username

您可以尝试如下:sqlite3开发。转储| psql dbname用户名

or try with sqlitetopgscript: http://trac-hacks.org/browser/sqlitetopgscript/0.10/sqlite2pg

或者试试sqlitetopgscript: http://trac-hacks.org/browser/sqlitetopgscript/0.10/sqlite2pg

#11

A possible solution (not for heroku) it's to use yaml.db from:

一个可能的解决方案(不是对heroku来说)是使用yaml。db:

http://www.railslodge.com/plugins/830-yaml-db

#1