Re: backup and recovery

What it was that I guess the pg_dump makes one large transaction and our shell script wizard wrote a perl program to add a commit transaction every 500 rows or what every you set. Also I should have said that we were doing the recovery with the insert statements created from pg_dump. So... my 500000 row table recovery took < 10 Min.

Thanks for your help.

Mark H

-----Original Message-----
From: Naomi Walker [mailto:nwalker(at)eldocomp(dot)com]
Sent: Friday, March 19, 2004 8:21 AM
To: Mark M. Huber
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] backup and recovery

At 01:08 PM 3/18/2004, Mark M. Huber wrote:
>Hi all I am reasonable new to postgres so any direction that you can give
>in the regards I am sure will be helpful so thanks very much in advance.
>
>I am not sure if I am doing something wrong or what exactly is going on. I
>have a db that I pg_dumped some tables out of and trying to recover them
>has taken more than 15 hours and these tables only one of them has over
>500,000 rows so what am I missing?
>
>It seams that any backup and recovery sucks. My main db backup takes over
>two hours to complete and 13 hours to recover what am I doing wrong? Any
>hints?ideas? Recommendations?

I'd expect this would take something more like 10 minutes. Things to
check, are you recovering in the same instance? Another machine? I've
noticed some funkyness when the table exists, but is emptied. I'd drop the
tables and recreate. Also, it often goes much faster is you load without
indexes, and create them later. You might also want to adjust your shared
memory parameters up some, but you have to restart the postmaster for it to
take effect.

There is definitely something wrong, because i've seen recovery to be
pretty darn fast.

>________________________________________
>Mark M. Huber
>DataBase Administrator
>(702)-938-9300
>markh(at)vmdirect(dot)com
>http://www.vmdirect.com
>3035 E. Patrick Lane
>Suite #1
>Las Vegas, NV 89120
>
> <<Mark M. Huber.vcf>>
>

-------------------------------------------------------------------------------------------------------------------------
Naomi Walker Chief Information Officer
Eldorado Computing, Inc.
nwalker(at)eldocomp(dot)com 602-604-3100
-------------------------------------------------------------------------------------------------------------------------
Forget past mistakes. Forget failures. Forget everything except what you're
going to do now and do it.
- William Durant, founder of General Motors
------------------------------------------------------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.