Re: Locking Tables & Backup Inquiry

On 12/14/2011 11:52 AM, Carlos Mennens wrote:
> I'm wanted to find out why is it recommended or even an option to lock
> tables during a backup of a database? I've never experimented with
> database backups so I'm only guessing it locks / freezes the data so
> no changes can be made while the backup is in process, correct? Just
> curious and wasn't able to find an answer online.
>
> My next question is more complex but more of a recommendations. I'm
> looking to see how do you guys backup your databases? Do you simply
> cron 'pg_dump' command line or do you have a script that gets called
> in cron using 'pg_dump' / 'pg_dumpall'? Just looking for ideas /
> recommendations for a simple / quick way to back up 5 small databases
> on my server.
>

I assume that is coming from a mysql world, where yes, locking is a good
option.

PG does not need it. Read up on MVCC. Your backup will "select * from
table" and read it just fine. Other transactions can update/delete from
the same table, but because of the magic of MVCC, the backup wont see
em, and neither will be blocked.

Meaning, your reads and writes dont block each other. Meaning, your app
wont freeze while the backup runs.

Yep, you simply cron a pg_dump. (dumpall if you want users/roles and
all databases). No locking needed.

-Andy