ROR change application database from SQLite to PostgreSQL

Question

I have a web application which uses SQLite. I deploy it on heroku which uses PostgreSLQ. This causes problems sometimes and I was advised to develop my app using PostgreSQL instead of SQLite.

I found out that I should modify database.yml like that (same for test and production):

development:
  adapter: postgresql
  database: my_database
  username: my_username
  password: my_passwod
  host: /var/run/postgresql or localhost

Well the only database I've ever used is SQLite, so I just tried to take my chances, but failed. I filled this file with some random data.

rake db:migrate resulted in:

When I used host: localhost

> could not connect to server: Connection refused   Is the server running
> on host "localhost" and accepting TCP/IP connections on port 5432?

When host: /var/run/postgresql

> could not connect to server: No such file or directory 
> Is the server running locally and accepting connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?

I suppose I should start PostgreSQL server first, but have no idea how to do this. Please give me a step by step answer how to move from a SQLite application to a working PostgreSQL application.

Answer 1

I would like to advise to you that you should download Postgresql including the PGADMIN itself which is easier to use than the psql terminal.

And I think when you download/install Postgresql from their official website... the package was complete already.

Upon installing, the postgresql will ask you a certain password that you will be using in accessing your postgresql server.

After the installation, open the PGADMIN and connect to the server. Enter your password (which you had declared during installation).

If you can't connect to the server, then edit the port. To do this, right click the server then go to properties... edit the port into something which is free. Example: 5433 and so on. It's up to you.

If everything's finally working... setup the correct config for your database.yml

This is important:

development:
  adapter: postgresql
  database: name_of_database_here
  host: localhost
  username: postgres
  password: your_db_server_password_here
  pool: 5
  timeout: 5000
  port: 5433

Okay from that config info above, specify the important parts. By default, your db server username is postgres and obviously your host is localhost because you are setting up under the development.

If your port is 5432 by default then just remove the port part.

Let's go to your gemfile.

In order for you to deploy your app in heroku. Use gem 'pg' instead of sqlite3.

If you have an existing sqlite3 database then put the gem inside the development group. In that case, Heroku will successfully bundle during git push heroku master process.

group :development do
       gem 'sqlite3'
end

Your gem 'pg' can either go outside the groups or put it in your production group.

Important:

Before any deployment procedure, make sure that you can run the app locally (localhost). Then if everything's working... that's the time that you should organize the necessary stuffs appropriately.

If you wish to switch to Postgresql instead of sqlite3 after pushing the app to Heroku... you can do so by pgbackups add-on and pg_restore the dump file into your local postgresql db server.

That's it. Hope it helps.