Saturday, March 14, 2009

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.

No comments:

Post a Comment