Getting bitcoin-ruby-blockchain and postgres working

These are just rough notes for myself. Perhaps you'll get something out of them too. At the moment I appear to be trying random things until I get it to work, so many of the steps below may not be necessary.

This post assumes you've set up a bitcoin ruby Ubuntu machine as per the previous posts. The following instructions should get you up and running with an (almost) complete postgres database using mhannes bitcoin-ruby-blockchain gem.

$ sudo git clone https://github.com/mhanne/bitcoin-ruby-blockchain.git

$ cd bitcoin-ruby-blockchain/

$ sudo gem build bitcoin-ruby-blockchain.gemspec

$ sudo gem install bitcoin-ruby-blockchain-0.0.1.gem

That gets the bitcoin ruby blockchain gem installed. Now you need to download the postgres database dump from webbtc.com. It's about 38Gb at the time of writing, so it will take about 10 hours. Go to http://dumps.webbtc.com/bitcoin/ and get the sql.gz file.

I couldn't unzip it with the default archive manager in Ubuntu, and had to resort to using 7zip on Windows. The resulting file is an 85Gb text file full of SQL commands. It's probably best to store it on an external drive as you won't be needing it more than once, hopefully. Again, unzipping took about 3 hours. Correction: use gunzip from a terminal, like so, and it takes less than two hours:

$ gunzip bitcoin_2015-03-02.sql.gz 

Now you need to get postgres running. Unlike Sqlite3, which stores the whole database in one file, postgres is a more professional (and hence more complicated) database system. It stores everything in a folder structure, and a server is run for database applications to connect to.

Run the following:

$ sudo apt-get update

$ sudo apt-get install postgresql postgresql-contrib

$ sudo apt-get install libpq-dev

$ sudo gem install pg

$ sudo apt-get update

$ sudo 

This ins talls all the backend postgres stuff. You'll need an administration tool. Get it from the Ubuntu software center; it's called pgAdmin III.

Open bitcoin-ruby/examples/balance.rb in a text editor, and change line 13 to read:

store = Bitcoin::Storage.sequel(:db => "sqlite://bitcoin.db")

At this point you can check you've got everything except an actual database by running 

$ sudo ruby balance.rb 1Q2TWHE3GMdB6BZKafqwxXtWAWgFt5Jvm3

If everything installed correctly you'll get an error that you can't connect to a database server.

Create a new postgres user

$ sudo createuser postgres

Select postgres as password, and leave all other info empty

$ sudo adduser postgres sudo

$ sudo su - postgres

But the server wasn't started. I found this page: http://www.rogerpence.com/2015/01/02/install-postgres-9-4-on-ubuntu/ and section 3 almost worked. Correcting the triple line to what's below worked, and a message claimed the server was started.

wget --quiet -O - https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

Start pgAdmin III. Click on the power plug symbol at the top left. Enter a name of bitcoin, a location of localhost and make sure you enter the postgres password.

If it doesn't work, try resetting the postgres password in psql using http://stackoverflow.com/questions/19774742/pgadmin3-server-connection-and-authentication-error

Expand the tree till you see Databases (1) and right click, then select add new database and create another - just give it the name bitcoin, everything else is okay.

Add a login role similarly and create a superuser called webbtc. Give it all the rights all the tabs allow you, and don't set an expiry.

Run pdump to execute the SQL commands in the downloaded unzipped file on the new database:

$ psql -d bitcoin -f bitcoin.sql

After about eight hours I had a working Postgres database with the blockchain up to 1 March 2015 running on my Ubuntu laptop. The bitcoin.rb balance program needed editing to use Postgres rather than mySQL, but after that it was possible to query the database for address balances.