Here’s a nice snippet that can be used in a PostgreSQL database to output the current database’s size.

SELECT pg_size_pretty(pg_database_size(current_database())) AS human_size, 
       pg_database_size(current_database())                 AS raw_size;

That statement with output something along the lines of:

human_size  | raw_size
------------+--------------
181 GB      | 193841573064

I use Postgres.app to run a local Postgres server while doing development work and it’s great. Postgres.app basically provides a completely contained pg server that’s ready to go out of the box — just launch the app and you’re good to go.

Unfortunately because it’s a self contained app things are installed in non-standard places.

More specifically, while installing the `pg` gem on a system I recently had it fail with this error:Continue reading

Today at AppAcademy we have gone through almost all of Michael Hartl’s rails tutorial.  I just wanted to quote Hartl explanation of what a database index is and how it works.  I love the analogy because it’s probably the most simplistic examples I’ve probably ever seen:

Putting an index on the email column fixes the problem. To understand a database index, it’s helpful to consider the analogy of a book index. In a book, to find all the occurrences of a given string, say “foobar”, you would have to scan each page for “foobar”. With a book index, on the other hand, you can just look up “foobar” in the index to see all the pages containing “foobar”. A database index works essentially the same way.

Bam!

I recently setup my development system from scratch and one of the tasks is installing PostgreSQL as my apps are almost always deployed to Heroku and they <3 PG.

As you may or may not know, Lion ships with a castrated Postgres installation which (if you don’t already know this (!!!!)) can make it challenging to use something like Homebrew for your installation medium.

To avoid this whole situation I suggest you checkout Postgres.app.  It’s written by the guys @ Heroku and is essentially a .app that you drop in your Applications folder.  Run whenever you want a PG server — it’s THAT easy.

Well… Sorta…

After installing it I had some issues but was able to figure it out so that’s really the “gotcha” part of this post.

On first launch i realized i couldn’t connect to the PG server and immediately went to my system’s console for clues.  This was sitting there looking pretty:

 

Ye olde  “Failed system call was shmget” error….

I believe the root of this issue is that OSX’s shared memory configuration is just really small. And after Googling around I found this post on Railscasts.com by Denis Savitsky (aka sadfuzzy).

In a nut shell there are two ways to fix this:

1- Temporary Solution: Gone after reboot.

2- Permanent Solution: Forever, forever, ever, forever, ever? Yep. After reboot the settings will stick.

 

You can also see this post for further details.

 

Some Preface

An app I’m working on has a rake that that basically gathers data from the web.  Doing things serially is really REALLY slow and to get around this I started using the (fantastic) parallel gem by Michael Grosser.

This gem is great — it’s simple to use and in something like 3 minutes after installing it I had my task running in 10 processes and absolutely murdering the work that needed to be done.

The Issue

I swapped databases not too long ago and moved from MySQL to PostgreSQL for full text search (which i don’t use anymore) and to stay in line with my host, Heroku.

Today i attempted to run the task against my PostgreSQL db and came up with an error I had never seen before:

message type 0x5a arrived from server while idle

WHUT?

After some investigating on Google I found the root cause — PostgreSQL does not allow using the same connection for more than one thread.

That’s pretty straight forward.

I believe the issue was that I had something like 10 ruby processes that were spawned from 1 process that was holding the db connection.  Not allowed!

The Solution

The solution is actually very straight forward as well.  To get around this you simply need to reconnect to the database each time you spawn a process or thread.

What does this look like in code?

Before (bad) :

After (GOOD):

 

And that’s pretty much it.

A Little Back Story…

Migrating from MySQL to PostgreSQL is not as straight forward as it sounds.  As you may or may not know, MySQL’s ‘mysqldump’ utility comes with a “compatible” option where you can specific “postgresql” but the catch is this:  the resulting SQL is not properly escaped and will need it’s data types augmented.

In other words… or in my words, the SQL generated from mysqldump that’s meant to be compatible with postgresql is basically worthless in it’s exported state.

 

How I Migrated

Thanks to people smarter than me, (specifically Max Lapshin) the process of dealing with the above is pretty straight forward with the help of mysql2postgres!

Here is what I did to migrate my MySQL data —  a few million rows clocking in just under 1 gig.

 

Step 1:
Install the mysql2postgres gem.

Before you just go and clone the gem to your local system, take a look @ the forks as they might have something interesting you want.  At the time of this writing, maxlapshin’s repo had like 5 or 6 outstanding pull requests.  My example code will just use his repo as the clone source.

 

Step 2:
Configuration

After installation, the ‘mysql2psql’ command should be available.  If it’s not, just close your terminal, open a new one and navigate back to where you cloned the gem.

By default you won’t have a configuration file.  Run the ‘mysql2psql’ command and it will generate a default one for you.  Once you do that, just open it up and configure it to your heart’s desires.

 

Once you open the configuration file, just read it top to bottom.  Everything is commented and it’s very straight forward stuff.  Fill in your MySQL details, then fill in your postgres details (or use the file: option).

Last part is to run the migration.

Step 3:
Run the Migration

This part will *probably* be as simple as doing this:

 

If that worked for you then enjoy the sweet sweet glory of being migrated.  Go check your data, utf8 encodings and all that fun stuff.

If that did not work out and you got an error like the following… then you might be using a mac, along with homebrew and can easily fix it!

Le Error

The Fix (aka How to Install mysql-connector-c on a mac)

  1. FIRST get rid of homebrew’s mysql-connector-c with: ‘brew remove mysql-connector-c’
  2. Go to MySQL’s download page and grab the Connector/C for your flavor of OSX
  3. Extract it and do the following:
    1. Copy the lib/ files to /usr/local/lib
    2. Copy the include/ files to /usr/local/include
    3. Copy the bin/ files to /usr/local/bin
  4. Profit.

Note: I’m still not fully sure why this fixed it for me.  The version i install from MySQL is the same version Homebrew uses. :/