Switching from SQLite3 to Postgresql in the midst of development to accommodate Thinking Sphinx

I've already done a certain amount of work on an application and I am approaching the implementation for thinking sphinx and search functionality. TS needs either mySQL or Postgresql db to run, so I am looking to switch my development to Postgresql.

There are many great resources that help you switch away from SQLite3 at the creation of a new rails app 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 these only cover using Postgresql from the absolute beginning.

I initially gave 开发者_JS百科it a shot by installing postgresql on my local machine and the gem

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

and by changing my database.yml file to accommodate postgresql with the hookups to local host, username, password and adapters. However, this didnt work and I assume its because by not starting with postgresql from the beginning, it didnt install a number of files it needs to.

Is there a good way to make a switch in the middle of development or would it be too messy and would starting over be preferred (which wouldnt be too bad, but not ideal. And on a different note, I've heard its better to develop with the same type of db that you will deploy with. Though I could take guesses as to why, anybody care to elaborate why this is preferred?)

Also, being a first time postgresql user (and rails newb), it looks like developing with postgresql is a bit more complicated than sqlite3's built in functionality. Is the switch suggested? Or should I find a way around it and stick with sqlite3?

Do you mean that you want to develop on PostgreSQL and then deploy on Sqlite3? This won't work.

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

These two storage engines use different dialects of SQL. A query which works in one would often fail in another, or do something subtly different, or be terribly slow.

It is not better to develop with the same storage engine that you deploy with — it is the only way.

I think you want something like this

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

Chances of being screwed by surprise becomes less when your development & deployment is on same platform ;) So its always good to work with same db rather than facing probs later