Thread: Locking Tables & Backup Inquiry

Locking Tables & Backup Inquiry

From
Carlos Mennens
Date:
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.

Re: Locking Tables & Backup Inquiry

From
Andy Colson
Date:
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

Re: Locking Tables & Backup Inquiry

From
Carlos Mennens
Date:
On Wed, Dec 14, 2011 at 1:15 PM, Andy Colson <andy@squeakycode.net> wrote:
> Yep, you simply cron a pg_dump.  (dumpall if you want users/roles and all
> databases).  No locking needed.

So how would one put this in cron if I wanted to run this everyday?

0 * * * * /usr/bin/pg_dumpall > pg_dumpall.$DATE.sql

Will that work above assuming I wanted to run this every day at that
specific time? I'm just guessing since I've never created a Crontab or
messed with PG backups.

Re: Locking Tables & Backup Inquiry

From
Andy Colson
Date:
On 12/14/2011 12:26 PM, Carlos Mennens wrote:
> On Wed, Dec 14, 2011 at 1:15 PM, Andy Colson<andy@squeakycode.net>  wrote:
>> Yep, you simply cron a pg_dump.  (dumpall if you want users/roles and all
>> databases).  No locking needed.
>
> So how would one put this in cron if I wanted to run this everyday?
>
> 0 * * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql
>
> Will that work above assuming I wanted to run this every day at that
> specific time? I'm just guessing since I've never created a Crontab or
> messed with PG backups.
>

google is your friend.

this'll run every hour.
 > 0 * * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql


try:
0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql

that'll run at 4am every day.

Watch the path's, who know's what directory is current:

0 4 * * * /usr/bin/pg_dumpall > /backup/pg_dumpall.$DATE.sql

-Andy

Re: Locking Tables & Backup Inquiry

From
Carlos Mennens
Date:
On Wed, Dec 14, 2011 at 1:38 PM, Andy Colson <andy@squeakycode.net> wrote:
> this'll run every hour.
>
>> 0 * * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql

Thank you!

> try:
>
> 0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql
>
> that'll run at 4am every day.

When I run the command in my shell (not in Cron), I'm prompted for my
login password. Should I change the permissions in pg_hba.conf and
enable INHERIT grants on my user? Should I place this in who's Cron
line? Postgres? Carlos? or Root?
>
> Watch the path's, who know's what directory is current:
>
> 0 4 * * * /usr/bin/pg_dumpall > /backup/pg_dumpall.$DATE.sql

Yes, I always check my paths and use full paths rather than symbolic links.

Re: Locking Tables & Backup Inquiry

From
Andy Colson
Date:
On 12/14/2011 12:54 PM, Carlos Mennens wrote:
> On Wed, Dec 14, 2011 at 1:38 PM, Andy Colson<andy@squeakycode.net>  wrote:
>> this'll run every hour.
>>
>>> 0 * * * * /usr/bin/pg_dumpall>    pg_dumpall.$DATE.sql
>
> Thank you!
>
>> try:
>>
>> 0 4 * * * /usr/bin/pg_dumpall>    pg_dumpall.$DATE.sql
>>
>> that'll run at 4am every day.
>
> When I run the command in my shell (not in Cron), I'm prompted for my
> login password. Should I change the permissions in pg_hba.conf and
> enable INHERIT grants on my user? Should I place this in who's Cron
> line? Postgres? Carlos? or Root?
>>
>> Watch the path's, who know's what directory is current:
>>
>> 0 4 * * * /usr/bin/pg_dumpall>  /backup/pg_dumpall.$DATE.sql
>
> Yes, I always check my paths and use full paths rather than symbolic links.
>

That's up to you I guess.  Assuming the crontab is running as root, you
could add a .pgpass to root's home, which should be secure enough.

-Andy

Re: Locking Tables & Backup Inquiry

From
"Albe Laurenz"
Date:
Carlos Mennens wrote:
>> 0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql
>>
>> that'll run at 4am every day.

> When I run the command in my shell (not in Cron), I'm prompted for my
> login password. Should I change the permissions in pg_hba.conf and
> enable INHERIT grants on my user? Should I place this in who's Cron
> line? Postgres? Carlos? or Root?

I'd use the postgres user and allow "trust" authentication for that
user on local connections in pg_hba.conf.  No grants are required.

If you have write activity during the backup and you need all data
of a database to be consistent, consider using pg_dump's
--serializable-deferrable flag.

Yours,
Laurenz Albe