#342 Migrating to PostgreSQL

Great episode!

A small side note: On Debian/Ubuntu/Mint Linux, you'll need libpq-dev (Postgres headers) to install the pg gem, not libpg.... That annoys me every time I setup a new server... ;-)

I recently switched from MySQL to PostgreSQL and couldn't be happier. The performance i get out of pg is great!

I found that migrating my data from MySQL to pg was a challenge though and i wanted to point anyone who's currently going through that @ this post: http://ruby.zigzo.com/2011/12/03/migrating-data-from-mysql-to-postgresql/

Basically it's a record of how I was able to migrate data from mysql -> pg using a gem by Max Lapshin.

BTW i should also add... i <3 the taps gem!!

Nice one Ryan! I've been thinking about switching ot PG.

If you do a follow-up, could you cover how to do a PG version of a mysqldump please? And then how to put such a dump back into a (new) DB?
(Or some other way to backup/restore a db, thanks!)

Edit: just found:
http://linux.die.net/man/1/pg_dump
http://linux.die.net/man/1/pg_restorev

AWESOME!

First time i'd heard of taps makes it a ton easier to make the jump!

Sorry but I think this episode is far below average level: nearly everything is obvious and can be googled easily.

This is AWESOME! My only problem is I installed via homebrew on Lion and when I do which psql I get:

/usr/bin/psql

How can I point it to the homebrew version?

Long been a PostgreSQL fan so great to see this being covered.

@anyone: taps is great, but don't rely on the record count, it stops working when you have more than 400 records

Love the screencast, thanks for mentioning my hstore article :)

Taps looks interesting, but complicated. The yaml_db gem looks like a simple way to backup and restore data. Does anyone have any experience using yaml_db to migrate from sqlite to PG?

Great to see this article, PostgreSQL is by far the best choice in many ways, if you want to create an application with accounts that might not have relation (like basecamp) you can create multitenant databases with the schema feature from PostgeSQL. I Have used it with bonsaierp.com

I followed the instructions for upgrading PostgreSQL on a new install of OS X Lion by typing "brew install postgresql."

But I think I am having a problem:

"psql --version" returns psql (PostgreSQL) 9.0.5
"which psql" returns /usr/bin/psql

"postgres --version" returns postgres (PostgreSQL) 9.1.3
/usr/local/bin/postgres

In the screencast, when Ryan types "psql --version", it's listed as postgres (PostgreSQL) 9.1.3. And "which psql" also returns "/usr/local/bin/psql" for Ryan.

I'm just confused and I'm not sure what to do, if anything.

I like the taps gem :) Thank you Ryan.

if you dont want to use the psql cli, you can try pgAdmin.
http://www.pgadmin.org/

Really glad to see postgres covered here!

A couple of tips:

• Because for local development we're using the unix username to connect to postgres, rbates in your case, you can simply ommit both username and password from database.yml. The underlying library will default to that user name. That way other developers working on the same project can use the same database.yml.
• When you specified the postgres URL for taps, you could've also taken advantage of the fact that it's using the default username and host to connect to postgres. The URL can therefore be merely: postgres:///store_development.

Finally, check out valkyrie, the likely successor of taps.

Thanks!

A simple note:

pg gem uses prepare statement in production env by default, which is different from MySQL.

This can be an issue if you are using unicorn. You may got PGError: ERROR: prepared statement "a3" already exists after several requests.

To solve this, make sure each unicorn process uses its own database connection by adding this to unicorn config file:

after_fork do |server, worker|
  ActiveRecord::Base.establish_connection
end

Hope this helps :)

And I want to point out a PGCon2009 video: PostgreSQL as a secret weapon for high-performance Ruby on Rails applications.

It suggests replace complex ActiveRecord logic with SQL to gain performance boost. The examples such as act_as_tree are impressing. The major disadvantage is that you can no more change db adapter easily.

Thanks for the episode. I followed along and everything worked as expected (I did have to take aharte's advice to move /usr/local/bin above /usr/bin in /etc/paths to get 9.1.3 recognized).

However when I run "rails db" and try any commands against postgres I get a segmentation fault: 11. I installed postgres with the > brew install postgresql used in the railscast. I'm on Lion and > psql --version returns "psql (PostgreSQL) 9.1.3"

Any thoughts on the segmentation fault?
Thanks.

how about MongoDB? what's the Pro/Cons?

would love an episode on mongoDB

The PostgreSQL notices in the console really annoy me. You can turn them off by adding min_messages: warning in your database.yml file. See example

I'm running into a problem after I try to run the initdb /user/local/var/postgres command. It says:

creating directory /user/local/var/postgres ... initdb: could not create directory "/user": Permission denied

I've tried Googling it, but no luck. Any ideas?

Hey Ryan. Thank you for this great tutorial!

I stumbled into one problem where rake db:migrate:all fails, because of a missing port configuration for the test environment. I documented the problem and the solution on Stackoverflow.

One more question: Would you recommend to prepend bundle exec to the commands you used?

Love the PG Text Search links. Very useful~!

Is it possible only to push certain tables when using TAPS. I´m using a MYSQL DB in development, and need to transfer my users table and images table to production, which uses Postgresql.... Any suggestions?

Oh, my production database is on Heroku by the way

Can anyone help with this error I am getting on rake db:create:all?

http://stackoverflow.com/questions/10423304/rake-dbcreateall-error-cant-convert-nil-to-string

Getting error of Rails: rake db:create:all (could not connect to server)

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

http://stackoverflow.com/questions/10482853/rails-rake-dbcreateall-could-not-connect-to-server

I have a problem when running my tests.
Everytime I try to
rake db:test:prepare I get
PG::Error: ERROR: type "hstore" does not exist

I'm not able to run my tests now :(

Sure, everyone, who has

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=1, size=1646592, 03600).

problem, can easily fix this.
Run:

sudo sysctl -w kern.sysv.shmall=65536
sudo sysctl -w kern.sysv.shmmax=16777216

(The first one's the critical one, but second seems appropriate too.)
This changes the limits in the currently-running kernel. To make these values stick across reboots, add them to /etc/sysctl.conf, like this:

kern.sysv.shmall=65536
kern.sysv.shmmax=16777216

Found this here

I was getting the error:
>$ rake db:migrate
'rake aborted!
FATAL: role "blog" does not exist'

so I had to create a superuser 'blog':
>$createuser -s blog

I had issues installing Postgres with the instructions in this Railscast. However I was able to get postgres running on my project with http://postgresapp.com/ and http://inductionapp.com/ (Hat tip to this stackoverflow thread).

1. Download & install Postgres.app (documentation) Make sure to install in /Applications.
2. Update your PATH
   In my case I added the following to ~/.bash_profile
   export PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH"
   Check that Postgres.app is being loaded

$ which psql
/Applications/Postgres.app/Contents/MacOS/bin/psql

1. Update /config/database.yml

development:
     adapter: postgresql
     encoding: unicode
     database: myapp_development
     pool: 5
     host: localhost

1. bundle exec rake db:create:all
2. I'm hosting my app on Heroku so then ran a heroku db:pull
3. Download & install Induction.
4. Launch Induction and connect using these settings.
   NOTE Select the last postgres option in Adapter dropdown

Adapter     postgres
Hostname    localhost
Username    [I left this blank]
Password    [I left this blank]
Port        5432
Database    [I left this blank]

1. Once Induction launches you can select your app's database from list in top left dropdown.

Interesting talk at NDC 2012 on not so well known tid bits of Postgres

You are a GOD.

Great episode. Transition went smoothly

i love you.

thanks guys, you are doing greate job!

I had problems with the encoding of my mysql database, so here is the trick:

taps server mysql://root@localhost/example_database?encoding=UTF8 httpuser httppassword

From: https://github.com/ricardochimal/taps/issues/110#issuecomment-6364498

i was getting this error

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

and i solve it using host: localhost in the database.yml

Thanks! Was having trouble setting this up, great episode and much appreciated : )

For people having issues with transferring their data using taps, here is my fix:

https://github.com/ricardochimal/taps/issues/128#issuecomment-21049046

great episode, but i wish it had more info on how to set up and create a PostgreSQL database for new Postgres users. It would of definitely helped when migrating

To note if you're using JRuby and converting your database to postgresql for Heroku; the pg gem won't install. Instead use the activerecord-jdbcpostgresql-adapter gem in your gemfile per this Heroku dev center link

for those who are experiencing the error, "ERROR: Rack::Utils::OkJson::Error: cannot encode Symbol", i suggest using the taps-taps gem instead of just "taps." that's the only changed i had to make to get taps to work (it appears taps is incompatible with rack 1.5, but i can't say for sure). thanks to the following resource for solving the problem.

https://shellycloud.com/blog/2013/10/easy-database-migration-using-taps

I was having trouble to setup postgresql on my environment, the thing is that I'm working with https://c9.io, and just for the record, Cloud9 has his own setting up instructions https://docs.c9.io/docs/setting-up-postgresql.

after issuing taps server sqlite://db/development.sqlite3 mahesh secret command i got below error? how to rectify it?
Error:-`module:Templates': uninitialized constant Tilt::CompileSite (NameError)Did you mean? Complex