Обсуждение: backup and recovery
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 thanksvery 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 ofand trying to recover them has taken more than 15 hours and these tables only one of them has over 500,000 rows so whatam 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 whatam 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 <<Mark M. Huber.vcf>>
Вложения
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 > > <<Mark M. Huber.vcf>> > ------------------------------------------------------------------------------------------------------------------------- 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 informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, 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 thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
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 <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
What it was that I guess the pg_dump makes one large transaction and our shell script wizard wrote a perl program to adda commit transaction every 500 rows or what every you set. Also I should have said that we were doing the recovery withthe 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 > > <<Mark M. Huber.vcf>> > ------------------------------------------------------------------------------------------------------------------------- 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 informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, 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 thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
>
>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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
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 <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
On Mon, Mar 22, 2004 at 09:13:06 -0800, "Mark M. Huber" <MHuber@vmdirect.com> 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).