Archive

Archive for the ‘PostgreSQL’ Category

Backing up your database

June 23rd, 2011 2 comments

Let me start by saying that there are MANY ways to backup databases and this might not be the absolute best way, but it definitely works. I’m backing up my PostgreSQL database that happens to be populated by Django, but you can muck with things as you see fit.

So, one of the decisions I had to make is whether to save a representation of the data or to just dump the database data itself. The first option would be something like this:

python manage.py dumpdata --format=yaml

Second option is to talk directly to the database. The benefit of this option is you get everything about the database in its current setup. Let’s say you made some changes to the schema directly within PostgreSQL. If you dumped the data into YAML or JSON you wouldn’t get those changes to the database itself. So it’s probably prefereable to go straight to the DB like so:

pg_dump <DATABASE NAME>

What I ended up with is a nightly backup on the database server. Then I pull that data from onto a second server using rsync.

 

Steps it took:
1. Setup e-mail sending, so that I get cron e-mails.

$ sudo apt-get install mailutils
$ sudo apt-get install exim4-config
$ sudo dpkg-reconfigure exim4-config

Test sending of mail from server

$ echo Test | mail -s Test <YOUR E-MAIL>

E-mail sending log lives here:

/var/log/exim4/mainlog

 

2. Setting up cron job on server to stash database schema and data. What I did is ran the cron job as the postgres user so that it would have access to the database.

$ su postgres

$ crontab -e
MAILTO="<YOUR E-MAIL>"
0 1 * * * pg_dump > "<PATH TO BACKUPS>/$(date +\%Y-%m-%d).psql"

 

3. Setting up pulling data onto another machine; setup e-mail just as you did on server machine

$ crontab -e
MAILTO="<YOUR E-MAIL>"
0 3 * * * rsync -vr --size-only<USERNAME>@<SERVER>:<PATH TO BACKUPS ON SERVER> <PATH TO BACKUPS ON SECOND MACHINE>
Categories: Django, PostgreSQL, Ubuntu Tags:

Quick PostgreSQL setup

January 26th, 2009 No comments

This is yet another one of those things I really don’t do all that often. So infrequently in fact that I forgot I had manipulated a config file when I originally setup my machine. I just updated my Ubuntu distro and with that came an updated verson of postgres (8.3) and of course my setup stopped working. The simple edit I have setup may not be safe, but it’s for a development machine so I really don’t think it matters. Here’s a quick way to get your Postgres DB setup…

Go to the bottom of your pg_hba.conf file which is found in /etc/postgresql/8.x/main/ and make changes to the settings so they match the following.

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               ident sameuser

Then restart the postgres server.

/etc/init.d/postgresql-8.x restart

Categories: PostgreSQL Tags: