Railsアプリで、sqliteからPostgreSQlに乗り換えたいです

実現したいこと

Railsで使っているSQLite3から、PostgreSQLに乗り換えたいです。

前提

DB管理にSQLite3を使用しているのですが、
そこからPostgreSQLに乗り換えたいと
思っています。

発生している問題・エラーメッセージ

乗り換え方法について調べたのですが、自分の環境にあったものが
あらず、困っています。

試したこと

gemfile

gem
1gem "pg"

を追記

terminal
1bundle install

よくわからない文字の羅列が...

errors
1Puma caught this error: Error loading the 'sqlite3' Active Record adapter. Missing a gem it depends on? sqlite3 is not part of the bundle. Add it to your Gemfile. (LoadError)
2C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/bundler-2.3.26/lib/bundler/rubygems_integration.rb:280:in `block (2 levels) in replace_gem'
3C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/sqlite3_adapter.rb:13:in `<main>'
4C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/bootsnap-1.15.0/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:32:in `require'
5C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/bootsnap-1.15.0/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:32:in `require'
6C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/zeitwerk-2.6.6/lib/zeitwerk/kernel.rb:38:in `require'
7C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/abstract/connection_handler.rb:268:in `resolve_pool_config'
8C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/abstract/connection_handler.rb:129:in `establish_connection'
9C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_handling.rb:52:in `establish_connection'
10C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/railtie.rb:268:in `block (2 levels) in <class:Railtie>'
11C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:95:in `class_eval'
12C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:95:in `block in execute_hook'
13C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:85:in `with_execution_control'
14C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:90:in `execute_hook'
15C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:76:in `block in run_load_hooks'
16C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:75:in `each'
17C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/lazy_load_hooks.rb:75:in `run_load_hooks'
18C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/base.rb:333:in `<module:ActiveRecord>'
19C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/base.rb:15:in `<main>'
20C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/bootsnap-1.15.0/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:32:in `require'
21C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/bootsnap-1.15.0/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:32:in `require'
22C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/zeitwerk-2.6.6/lib/zeitwerk/kernel.rb:38:in `require'
23C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4/lib/active_record/query_cache.rb:36:in `run'
24C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/execution_wrapper.rb:29:in `before'
25C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:423:in `block in make_lambda'
26C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:199:in `block (2 levels) in halting'
27C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:687:in `block (2 levels) in default_terminator'
28C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:686:in `catch'
29C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:686:in `block in default_terminator'
30C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:200:in `block in halting'
31C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:595:in `block in invoke_before'
32C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:595:in `each'
33C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:595:in `invoke_before'
34C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/callbacks.rb:106:in `run_callbacks'
35C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/execution_wrapper.rb:129:in `run'
36C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/execution_wrapper.rb:125:in `run!'
37C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/execution_wrapper.rb:78:in `block in run!'
38<internal:kernel>:90:in `tap'
39C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/activesupport-7.0.4/lib/active_support/execution_wrapper.rb:75:in `run!'
40C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/actionpack-7.0.4/lib/action_dispatch/middleware/executor.rb:12:in `call'
41C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/actionpack-7.0.4/lib/action_dispatch/middleware/static.rb:23:in `call'
42C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/rack-2.2.4/lib/rack/sendfile.rb:110:in `call'
43C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/actionpack-7.0.4/lib/action_dispatch/middleware/host_authorization.rb:137:in `call'
44C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/railties-7.0.4/lib/rails/engine.rb:530:in `call'
45C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/configuration.rb:252:in `call'
46C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/request.rb:77:in `block in handle_request'
47C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/thread_pool.rb:340:in `with_force_shutdown'
48C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/request.rb:76:in `handle_request'
49C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/server.rb:443:in `process_client'
50C:/Ruby31-x64/lib/ruby/gems/3.1.0/gems/puma-5.6.5/lib/puma/thread_pool.rb:147:in `block in spawn_thread'

役立つかもしれないコード類

config/database.yml

yaml
1# SQLite. Versions 3.8.0 and up are supported.
2#   gem install sqlite3
3#
4#   Ensure the SQLite 3 gem is defined in your Gemfile
5#   gem "sqlite3"
6#
7default: &default
8  adapter: sqlite3
9  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
10  timeout: 5000
11
12development:
13  <<: *default
14  database: db/development.sqlite3
15
16# Warning: The database defined as "test" will be erased and
17# re-generated from your development database when you run "rake".
18# Do not set this db to the same as development or production.
19test:
20  <<: *default
21  database: db/test.sqlite3
22
23production:
24  <<: *default
25  database: db/production.sqlite3
26

gemfile

gem
1source "https://rubygems.org"
2git_source(:github) { |repo| "https://github.com/#{repo}.git" }
3
4ruby "3.1.3"
5
6# Bundle edge Rails instead: gem "rails", github: "rails/rails", branch: "main"
7gem "rails", "~> 7.0.4"
8
9# The original asset pipeline for Rails [https://github.com/rails/sprockets-rails]
10gem "sprockets-rails"
11
12# Use sqlite3 as the database for Active Record
13# gem "sqlite3", "~> 1.4"
14
15gem "pg"
16
17# Use the Puma web server [https://github.com/puma/puma]
18gem "puma", "~> 5.0"
19
20# Use JavaScript with ESM import maps [https://github.com/rails/importmap-rails]
21gem "importmap-rails"
22
23# Hotwire's SPA-like page accelerator [https://turbo.hotwired.dev]
24gem "turbo-rails"
25
26# Hotwire's modest JavaScript framework [https://stimulus.hotwired.dev]
27gem "stimulus-rails"
28
29# Build JSON APIs with ease [https://github.com/rails/jbuilder]
30gem "jbuilder"
31
32# Use Redis adapter to run Action Cable in production
33# gem "redis", "~> 4.0"
34
35# Use Kredis to get higher-level data types in Redis [https://github.com/rails/kredis]
36# gem "kredis"
37
38# Use Active Model has_secure_password [https://guides.rubyonrails.org/active_model_basics.html#securepassword]
39# gem "bcrypt", "~> 3.1.7"
40
41# Windows does not include zoneinfo files, so bundle the tzinfo-data gem
42gem "tzinfo-data", platforms: %i[ mingw mswin x64_mingw jruby ]
43
44# Reduces boot times through caching; required in config/boot.rb
45gem "bootsnap", require: false
46
47gem "bcrypt"
48
49gem 'rails-i18n'
50
51gem 'rack-attack'
52
53gem 'listen'
54
55gem 'brakeman'
56
57gem 'capistrano'
58
59gem 'stripe'
60
61# Use Sass to process CSS
62# gem "sassc-rails"
63
64# Use Active Storage variants [https://guides.rubyonrails.org/active_storage_overview.html#transforming-images]
65# gem "image_processing", "~> 1.2"
66
67group :development, :test do
68  # See https://guides.rubyonrails.org/debugging_rails_applications.html#debugging-with-the-debug-gem
69  gem "debug", platforms: %i[ mri mingw x64_mingw ]
70end
71
72group :development do
73  # Use console on exceptions pages [https://github.com/rails/web-console]
74  gem "web-console"
75
76  # Add speed badges [https://github.com/MiniProfiler/rack-mini-profiler]
77  # gem "rack-mini-profiler"
78
79  # Speed up commands on slow machines / big apps [https://github.com/rails/spring]
80  # gem "spring"
81end
82
83group :test do
84  # Use system testing [https://guides.rubyonrails.org/testing.html#system-testing]
85  gem "capybara"
86  gem "selenium-webdriver"
87  gem "webdrivers"
88end
89

補足情報（FW/ツールのバージョンなど）

環境

OS: Windows 10 Home 22H2
Ruby: 3.1.3
Rails: 7.0.4
SQL: SQLite3(いまこれをPostgreSQLに変えようとしている)
サーバー: Render.com
リポジトリ: github

このことについて検索しても、なかなか環境に合うものが見つからず、
困っています。ご回答宜しくお願い致します。

行動規範の内容に同意します

回答1件

ベストアンサー

database.yml を以下のように変更すると、エラーメッセージが変化しますか？

diff
1  default: &default
2-   adapter: sqlite3
3+   adapter: postgresql
4    pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
5    timeout: 5000
6
7  development:
8    <<: *default
9-   database: db/development.sqlite3
10+   database: アプリ名_development
11
12  test:
13    <<: *default
14-   database: db/test.sqlite3
15+   database: アプリ名_test
16
17  production:
18    <<: *default
19-   database: db/production.sqlite3
20+   database: アプリ名_production

投稿2023/04/07 02:00

shinoharat

総合スコア1685

michikusa-mc

2023/04/07 02:13

いつもご回答ありがとうございます。回答してくださった通りにコードを変更してみると、ActiveRecord::ConnectionNotEstablished connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (IPアドレス), port ポート failed: Connection refused (0x0000274D/10061) Is the server running on that host and accepting TCP/IP connections? となりました。エラーメッセージが変わりました！

shinoharat

2023/04/07 02:29

ローカルマシンに postgresql をインストールしましたか？もししてないのなら、 https://www.postgresql.org/download/ 　↓ Windows 　↓ Download the installer 　↓ Windows x86-64 からインストーラーを入手し、インストールしてください。

michikusa-mc

2023/04/07 03:14

インストールしてませんでした...ただただGemをインストールすればいいと思ってました。最低限インストールしました！

shinoharat

2023/04/07 06:14

チュートリアルの【 3) Verify the Installation 】を参考に、postgresql が正常にインストールできているか確認してください。 https://www.postgresqltutorial.com/postgresql-getting-started/install-postgresql/ 問題なさそうなら、いつも通りの手順でDBを作成し、 bundle exec rails db:create bundle exec rails db:migrate サーバーが起動できるか試してみてください。 bundle exec rails server

michikusa-mc

2023/04/08 10:06

ありがとうございます。しかし、rails sでサーバを起動してみると、connection to server at "localhost" (::1), port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. と出てきました... これは何なのでしょうか？

shinoharat

2023/04/09 05:29

「DBに接続出来ない」というエラーなので、postgresql に何らかの問題があるのだと思います。ただ、エラー文には「異常終了した」としか出てないので、このメッセージだけでは具体的な原因は特定できませんね・・・原因特定のため、いくつか確認させてください。 ❶ チュートリアルの【 3) Verify the Installation 】は試されましたか？ SELECT version(); コマンドでバージョンが表示されましたか？ ❷ db:create や db:migrate は正常終了しましたか？この時点で何かエラーメッセージは出ませんでしたか？（ちなみに db:drop でDBを消せば db:create db:migrate を再度試せます） ❸ postgresql のインストール時にユーザ名やパスワードを入力したと思いますが、それと同じ情報を database.yml に書きましたか？ default: &default 　username: ユーザ名　password: パスワード　host: localhost

michikusa-mc

2023/04/09 05:48

ありがとうございます。順に回答していきます。1. バージョン表示はされました。　2.db:createを実行したら「ActiveRecord::ConnectionNotEstablished」と「PG::ConnectionBad」が出ました。　3.書きました。　　　という感じです。

shinoharat

2023/04/09 06:16

早速のご返信ありがとうございます。 2番のエラーメッセージを、省略せずにすべて教えて頂けますか？

michikusa-mc

2023/04/09 07:14

はい、だいぶ長めですが全部載せます。 connection to server at "::1", port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Couldn't create 'myapp_development' database. Please check your configuration. rails aborted! ActiveRecord::ConnectionNotEstablished: connection to server at "::1", port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Caused by: PG::ConnectionBad: connection to server at "::1", port 5432 failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Tasks: TOP => db:create (See full trace by running task with --trace) です。

行動規範の内容に同意します