Moving from Sqlite3 to PostgreSQL Rails

Question

I'm trying to move my database to a PostgreSQL because I'm putting it up on Heroku. Followed Railscast #342. Installed PostgreSLQ with its dependencies on my Ubuntu machine. When I installed it I think a user was created. I used this user in my database.yml. It looks like this:

production:
  adapter: postgresql
  encoding: unicode
  database: dlrvbtApp1_production
  pool: 5
  username: jdartland
  password: 

development:
  adapter: postgresql
  encoding: unicode
  database: dlrvbtApp1_development
  pool: 5
  username: jdartland
  password: 

test:
  adapter: postgresql
  encoding: unicode
  database: dlrvbtApp1_test
  pool: 5
  username: jdartland
  password:

Installed pg gem and the taps gem. Ran a Bundle install, created the databases with rake db:create:all

Started the taps senatra server with taps server sqlite://db/development.sqlite3 jdartland secure

The server started. And tried to pull the SQL to my new development db through this command.

taps pull postgres://jdartland@localhost/dlrvbtApp1_development http://jdartland:secret@localhost:5000

I then get this error:

Failed to connect to database:
  Sequel::DatabaseConnectionError -> PG::ConnectionBad: fe_sendauth: no password supplied

I have tried and tried, created new databases, canhing the .yml, pg_config and so on but I can't get it to work. This is my first time working with PostgreSQL and Heroku, please give me a hand! :)

Ok dumb question....... but you say the error is that there is no password set. But you've got no password in your database.yml file? — Richard Peck, Oct 15 '13 at 15:53
Well tried with that aswell, get the same error. First time working with databases, dont know how to pass the authentication to my localhost sinatra database hosted with the taps gem on my local mashine. — fynn, Oct 16 '13 at 08:10

NM Pennypacker · Answer 1 · 2013-10-15T14:06:10.960

1

Change the user on production to localhost and leave the password blank.

production:
  adapter: postgresql
  encoding: unicode
  database: dlrvbtApp1_production
  pool: 5
  username: localhost
  password:

edited Oct 15 '13 at 14:06

answered Oct 15 '13 at 13:56

NM Pennypacker

6,704
11
36
38

Should I do the same in development and test? – fynn Oct 16 '13 at 08:06
When I then do rake db:create:all I get FATAL: Peer authentication failed for user "localhost" – fynn Oct 16 '13 at 08:23
Good deal. Check out this post to configure from here on out. http://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres – NM Pennypacker Oct 16 '13 at 12:21

Richard Peck · Answer 2 · 2013-10-15T16:22:19.687

If you're moving your database to Heroku, the whole thing is just a case of connecting your DB to Heroku's PG one, and migrating the data.

Did you receive database details from Heroku? They basically use Amazon to serve their DB's, and you'll get some credentials to put into your yml file for it. Here is an example of one of our live Heroku apps:

production:
  adapter: postgresql
  database: ********
  pool: 5
  username: ****************
  password: ****************
  port: 5432
  host: ec2-54-228-234-250.eu-west-1.compute.amazonaws.com

Ways To Migrate To PostgreSQL (Heroku)

If you're looking to migrate your data from SQLite3 to PostgreSQL, I found a really good tutorial on how to do this here. Only problem is that it's not for SQLite lol

Haha thanks, yeah can be usefull in the future. Firstly I have to migrate from sqlite3 to postgresql. Thanks anyways! — fynn, Oct 16 '13 at 08:06

score 1 · Answer 3 · answered Oct 15 '13 at 21:53

If you're trying to pull down your database from Heroku into a local postgres database, use pg:pull or pgbackups:

You should also look into using foreman and a .env file to setup your DATABASE_URL similar to how it's ran on Heroku, for dev/prod parity:

https://devcenter.heroku.com/articles/procfile#developing-locally-with-foreman

Im trying to pull it from My local sinatra Sqlite3 database to a PostgreSQL — fynn, Oct 16 '13 at 08:08

score 0 · Accepted Answer · edited May 23 '17 at 12:05

Got It working by following another post here on stack. Simply went up one directory, installed taps Gem install Taps. Uninstalled Rack gem gem uninstall rack 4 then Reinstalled it gem install rack --version 1.0.1. Did not do this in my progect directory, just simply in RVM. Then Pulled the database from the same directory. (not from my project directory).

Here is the Whole tread: taps migration failing from sqlite to postgres rails4, ruby 1.9.3

Hope it helps someone with the same problem.

Now just one thing left, Push it to heroku..... We'll see how that goes...hehe

Thanks for all help!

Moving from Sqlite3 to PostgreSQL Rails

4 Answers4