backup and recovery

Поиск
Список
Период
Сортировка
Искать

backup and recovery

От:
"Mark M. Huber" <MHuber@VMdirect.com>
Дата:
Вложения:
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?

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

 <> 

Re: backup and recovery

От:
Naomi Walker <nwalker@eldocomp.com>
Дата:
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@vmdirect.com
>http://www.vmdirect.com
>3035 E. Patrick Lane
>Suite #1
>Las Vegas, NV 89120
>
>  <>
>

-------------------------------------------------------------------------------------------------------------------------
Naomi Walker                         Chief Information Officer
                                               Eldorado Computing, Inc.
nwalker@eldocomp.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.

Re: backup and recovery

От:
Jeff <threshar@torgo.978.org>
Дата:

On Mar 18, 2004, at 3:08 PM, Mark M. Huber wrote:

> 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?
>
>

Increasing sort_mem dramatically (say, 128M) will greatly speed things 
up - especially with foriegn key creation and index creation (Those two 
things are what takes the longest in a PG restore).  Be sure to turn it 
back down to something normal after the store (If you didn't change it 
on a single session)

However a 500k row table is not terribly big.. How big is the dump in 
terms of MB?

--
Jeff Trout 
http://www.jefftrout.com/
http://www.stuarthamm.net/

Re: backup and recovery

От:
"Mark M. Huber" <MHuber@VMdirect.com>
Дата:
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@eldocomp.com]
Sent: Friday, March 19, 2004 8:21 AM
To: Mark M. Huber
Cc: pgsql-admin@postgresql.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@vmdirect.com
>http://www.vmdirect.com
>3035 E. Patrick Lane
>Suite #1
>Las Vegas, NV 89120
>
>  <>
>

-------------------------------------------------------------------------------------------------------------------------
Naomi Walker                         Chief Information Officer
                                               Eldorado Computing, Inc.
nwalker@eldocomp.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.

Re: backup and recovery

От:
Naomi Walker <nwalker@eldocomp.com>
Дата:
That brings up a good point.  It would be extremely helpful to add two 
parameters to pg_dump.  One, to add how many rows to insert before a 
commit, and two, to live through X number of errors before dying (and 
putting the "bad" rows in a file).


At 10:15 AM 3/19/2004, Mark M. Huber wrote:
>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
>
>
>-------------------------------------------------------------------------------------------------------------------------
>Naomi Walker                         Chief Information Officer
>                                                Eldorado Computing, Inc.
>nwalker@eldocomp.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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

-------------------------------------------------------------------------------------------------------------------------
Naomi Walker                         Chief Information Officer
                                               Eldorado Computing, Inc.
nwalker@eldocomp.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.

Re: backup and recovery

От:
Naomi Walker <nwalker@eldocomp.com>
Дата:

>
>Of course, practice often differs from theory, but I wonder whether we
>aren't talking about palliating a symptom instead of fixing the real
>problem.

Certainly a good point, since I might be using the tools to do things that 
were unintended.  We have development, test, and production areas for our 
applications using postgres.  I regularly move rows and tables from one 
place to another, and often one platform to another.

For example, i'll dump a table with a group of customers from one machine, 
and add all those rows to the test and development areas, and sometimes 
issues occur.  It is not the case that I always move whole databases of 
instances from place to place.

We've also written several tools that "COPY table from stdin", followed by 
specific "selects" to move rows around.

Instead of suggesting the solution, I should probably state the problem.

1) Before performing any maintenance on a table, we unload it first, just 
in case.  We need quick reload methods.  psql typically works well, but is 
sometimes slow.

2) We could really use a generic tool to unload "selected" rows from a 
table, and move the unload somewhere else to reload.  [NOTE: we had to 
stick in all sorts of perl things to deal with \n-like stuff).]

Perhaps i'm using the wrong tools, but I am trainable.

We are running postgres 7.3.4 on Solaris and Redhat Linux servers.

Naomi



-------------------------------------------------------------------------------------------------------------------------
Naomi Walker                         Chief Information Officer
                                               Eldorado Computing, Inc.
nwalker@eldocomp.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.

Re: backup and recovery

От:
"Mark M. Huber" <MHuber@VMdirect.com>
Дата:
The file is just over 4 gig.

-----Original Message-----
From: Jeff [mailto:threshar@torgo.978.org]
Sent: Monday, March 22, 2004 6:19 AM
To: Mark M. Huber
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] backup and recovery



On Mar 18, 2004, at 3:08 PM, Mark M. Huber wrote:

> 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?
>
>

Increasing sort_mem dramatically (say, 128M) will greatly speed things 
up - especially with foriegn key creation and index creation (Those two 
things are what takes the longest in a PG restore).  Be sure to turn it 
back down to something normal after the store (If you didn't change it 
on a single session)

However a 500k row table is not terribly big.. How big is the dump in 
terms of MB?

--
Jeff Trout 
http://www.jefftrout.com/
http://www.stuarthamm.net/

Re: backup and recovery

От:
"Mark M. Huber" <MHuber@VMdirect.com>
Дата:
That sounds like a brilliant idea, who do we say it to make it so?

Mark H

-----Original Message-----
From: Naomi Walker [mailto:nwalker@eldocomp.com]
Sent: Monday, March 22, 2004 8:19 AM
To: Mark M. Huber
Cc: Naomi Walker; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] backup and recovery


That brings up a good point.  It would be extremely helpful to add two 
parameters to pg_dump.  One, to add how many rows to insert before a 
commit, and two, to live through X number of errors before dying (and 
putting the "bad" rows in a file).


At 10:15 AM 3/19/2004, Mark M. Huber wrote:
>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
>
>
>-------------------------------------------------------------------------------------------------------------------------
>Naomi Walker                         Chief Information Officer
>                                                Eldorado Computing, Inc.
>nwalker@eldocomp.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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

-------------------------------------------------------------------------------------------------------------------------
Naomi Walker                         Chief Information Officer
                                               Eldorado Computing, Inc.
nwalker@eldocomp.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.

Re: backup and recovery

От:
Bruno Wolff III <bruno@wolff.to>
Дата:
On Mon, Mar 22, 2004 at 09:13:06 -0800,
  "Mark M. Huber"  wrote:
> That sounds like a brilliant idea, who do we say it to make it so?

It might be better to make this part of pg_restore, rather than pg_dump.

> 
> Mark H
> 
> -----Original Message-----
> From: Naomi Walker [mailto:nwalker@eldocomp.com]
> Sent: Monday, March 22, 2004 8:19 AM
> To: Mark M. Huber
> Cc: Naomi Walker; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] backup and recovery
> 
> 
> That brings up a good point.  It would be extremely helpful to add two 
> parameters to pg_dump.  One, to add how many rows to insert before a 
> commit, and two, to live through X number of errors before dying (and 
> putting the "bad" rows in a file).
FAQ