Migrating from SQLite3 to Postgresql in the midst of development to host Sphinx thinking

Question

I have already done some work on the application, and I am approaching an implementation for the sphinx thinking and search functionality. TS requires running mySQL or Postgresql db, so I want to switch my development to Postgresql.

There are many great resources that will help you abandon SQLite3 when creating a new rails application, like here: http://mikewilliamson.wordpress.com/2010/04/19/postgres-and-rails-for-mysql-people/ and here : http://www.funonrails.com/2011/03/getting-started-with-rails-3-postgres.html

But they only cover Postgresql from an absolute start.

I initially gave him a chance by installing postgresql on my local computer and stone

gem 'pg', :require => 'pg'

and modifying my database.yml file to host postgresql using localhost connections, username, password, and adapters. However, this did not work, and I guess because, not starting with postgresql from the very beginning, he did not install several files that he needs.

Is there a good way to make the switch in the middle of development, or it will be too confusing and start to be preferred (which would not be so bad, but not perfect. And in another note, I heard it is better to develop with the same type of db with which you will be deployed. Although I could have guessed why, does anyone want to find out why this is preferable?)

Also, as the first user of postgresql (and rails newb), it seems that developing with postgresql is a bit more complicated than the built-in sqlite3 functionality. Is a switch suggested? Or should I find a way around it and stick with sqlite3?

Answer 1

Do you want you to develop PostgreSQL and then deploy to Sqlite3? This will not work.

These two are very different types of storage engines. Sqlite is just a handy library for storing data and accessing it using some subset of SQL. Postgres - a full-featured database server - it must be installed, the user needs it for himself, works as a service, preferably on a dedicated physical computer. Postgres is very powerful, fast and reliable, but also requires a lot of training to tame it.

These two storage systems use different SQL dialects. A query that works in one often fails in the other, or does something subtly different, or will be terribly slow.

It's not better to design the same storage engine that you deploy with - this is the only way .

Answer 2

I think you want something like this

 sqlite3 development.sqlite3 .dump | psql -h localhost -p 5432 -U postgres -W database-development 

Answer 3

The chances of being screwed by surprise become less when your development and deployment are on the same platform;) Therefore, it is always useful to work with the same db, rather than encounter problems later