Backing Up PostgreSQL

‹prev | My Chain | next›

For my chain tonight, I thought it worth verifying that I could generate JSON from my current application.

Sadly, this turns out to mean yak shaving. You see, I had made the unfortunate choice to override to_json (and to_hash) in the original code. For the life of me, I can not remember why I did this. The only JSON that I ever recall using was for a BMore on Rails presentation that I did back when the codebase was still on Rails 1.2.x (pre-dating core to_json?). This is what happens with inside-out development—weird artifacts of things I thought I might have needed in the future. Instead, they are causing me trouble now that the future it today.

Sooo....

I grab a copy of my production database, which is running on PostgreSQL:

pg_dump -d  -U  -h 127.0.0.1 > eeecooks_pg.sql
gzip eeecooks_pg.sql

Next up is installing PostgreSQL on my local Ubuntu. Following along with the Ubuntu community documentation, I install the package:

sudo apt-get install postgresql

And create a rails user and a database with which to work:

cstrom@jaynestown:~$ sudo -u postgres psql postgres
[sudo] password for cstrom: 
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# \q
cstrom@jaynestown:~$ sudo -u postgres createdb -O rails eeecooks_development

To restore the downloaded copy, unzip and then use the psql command:

cstrom@jaynestown:~$ gunzip -dc eeecooks_pg.sql.gzip
cstrom@jaynestown:~$ psql -U rails -h 127.0.0.1 -d eeecooks_development -f eeecooks_pg.sql

A quick check of the database shows that it restored OK:

cstrom@jaynestown:~$ psql -U rails -h 127.0.0.1 -d eeecooks_development
Password for user rails: 
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

eeecooks_development=> select count(*) from recipes;
 count 
-------
   578
(1 row)

Tomorrow night, I will connect the rails application code, remove the to_json hackery and decide where to go next.