pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbackup and recovery
21+ messages / 8 participants
[nested] [flat]
* backup and recovery
@ 2004-03-18 20:08 Mark M. Huber <MHuber@VMdirect.com>
2004-03-22 14:18 ` Re: backup and recovery Jeff <threshar@torgo.978.org>
0 siblings, 1 reply; 21+ messages in thread
From: Mark M. Huber @ 2004-03-18 20:08 UTC (permalink / raw)
To: pgsql-admin
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
<<Mark M. Huber.vcf>>
Attachments:
Mark M. Huber.vcf (text/x-vcard, 132B) download
BEGIN:VCARD
VERSION:2.1
N:Huber;Mark
FN:Mark M. Huber
EMAIL;PREF;INTERNET:MHuber@VMdirect.com
REV:20040220T180108Z
END:VCARD
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
2004-03-18 20:08 backup and recovery Mark M. Huber <MHuber@VMdirect.com>
@ 2004-03-22 14:18 ` Jeff <threshar@torgo.978.org>
0 siblings, 0 replies; 21+ messages in thread
From: Jeff @ 2004-03-22 14:18 UTC (permalink / raw)
To: Mark M. Huber <MHuber@VMdirect.com>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-19 16:20 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 0 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-19 16:20 UTC (permalink / raw)
To: Mark M. Huber <MHuber@VMdirect.com>; +Cc: pgsql-admin
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 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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-19 17:15 Mark M. Huber <MHuber@VMdirect.com>
0 siblings, 0 replies; 21+ messages in thread
From: Mark M. Huber @ 2004-03-19 17:15 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: pgsql-admin
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
>
> <<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 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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-22 16:19 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 0 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-22 16:19 UTC (permalink / raw)
To: Mark M. Huber <MHuber@VMdirect.com>; +Cc: Naomi Walker <nwalker@eldocomp.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-22 16:39 Mark M. Huber <MHuber@VMdirect.com>
0 siblings, 0 replies; 21+ messages in thread
From: Mark M. Huber @ 2004-03-22 16:39 UTC (permalink / raw)
To: Jeff <threshar@torgo.978.org>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-22 17:13 Mark M. Huber <MHuber@VMdirect.com>
2004-03-26 20:29 ` Re: backup and recovery Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 21+ messages in thread
From: Mark M. Huber @ 2004-03-22 17:13 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
2004-03-22 17:13 Re: backup and recovery Mark M. Huber <MHuber@VMdirect.com>
@ 2004-03-26 20:29 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 21+ messages in thread
From: Bruno Wolff III @ 2004-03-26 20:29 UTC (permalink / raw)
To: Mark M. Huber <MHuber@vmdirect.com>; +Cc: Naomi Walker <nwalker@eldocomp.com>; pgsql-admin
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).
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
@ 2004-03-24 00:02 Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:49 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
0 siblings, 2 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-24 00:02 UTC (permalink / raw)
To: Mark M. Huber <MHuber@VMdirect.com>; +Cc: Naomi Walker <nwalker@eldocomp.com>; pgsql-admin
I'm not sure of the correct protocol for getting things on the "todo"
list. Whom shall we beg?
At 10:13 AM 3/22/2004, Mark M. Huber wrote:
>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
>------------------------------------------------------------------------------------------------------------------------
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
@ 2004-03-24 00:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
1 sibling, 1 reply; 21+ messages in thread
From: Bruce Momjian @ 2004-03-24 00:50 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Naomi Walker wrote:
>
> I'm not sure of the correct protocol for getting things on the "todo"
> list. Whom shall we beg?
>
Uh, you just ask and we discuss it on the list.
Are you using INSERTs from pg_dump? I assume so because COPY uses a
single transaction per command. Right now with pg_dump -d I see:
--
-- Data for Name: has_oids; Type: TABLE DATA; Schema: public; Owner:
postgres
--
INSERT INTO has_oids VALUES (1);
INSERT INTO has_oids VALUES (1);
INSERT INTO has_oids VALUES (1);
INSERT INTO has_oids VALUES (1);
Seems that should be inside a BEGIN/COMMIT for performance reasons, and
to have the same behavior as COPY (fail if any row fails). Commands?
As far as skipping on errors, I am unsure on that one, and if we put the
INSERTs in a transaction, we will have no way of rolling back only the
few inserts that fail.
---------------------------------------------------------------------------
> >
> >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.
> > >
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
@ 2004-03-24 17:54 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:15 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Tsirkin Evgeny @ 2004-03-24 17:54 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Naomi Walker <nwalker@eldocomp.com>; +Cc: Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
On Tue, 23 Mar 2004 19:50:24 -0500 (EST), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
> Naomi Walker wrote:
>>
>> I'm not sure of the correct protocol for getting things on the "todo"
>> list. Whom shall we beg?
>>
>
> Uh, you just ask and we discuss it on the list.
>
> Are you using INSERTs from pg_dump? I assume so because COPY uses a
> single transaction per command. Right now with pg_dump -d I see:
>
> --
> -- Data for Name: has_oids; Type: TABLE DATA; Schema: public; Owner:
> postgres
> --
>
> INSERT INTO has_oids VALUES (1);
> INSERT INTO has_oids VALUES (1);
> INSERT INTO has_oids VALUES (1);
> INSERT INTO has_oids VALUES (1);
>
> Seems that should be inside a BEGIN/COMMIT for performance reasons, and
> to have the same behavior as COPY (fail if any row fails). Commands?
>
> As far as skipping on errors, I am unsure on that one, and if we put the
> INSERTs in a transaction, we will have no way of rolling back only the
> few inserts that fail.
>
That is right but there are sutuation when you prefer at least some
data to be inserted and not all changes to be ralled back because
of errors.
> ---------------------------------------------------------------------------
>
>> >
>> >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.
>> > >
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
@ 2004-03-24 18:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 22:54 ` Re: [Retrieved]RE: backup and recovery Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Bruce Momjian @ 2004-03-24 18:15 UTC (permalink / raw)
To: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; +Cc: Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Tsirkin Evgeny wrote:
> > Uh, you just ask and we discuss it on the list.
> >
> > Are you using INSERTs from pg_dump? I assume so because COPY uses a
> > single transaction per command. Right now with pg_dump -d I see:
> >
> > --
> > -- Data for Name: has_oids; Type: TABLE DATA; Schema: public; Owner:
> > postgres
> > --
> >
> > INSERT INTO has_oids VALUES (1);
> > INSERT INTO has_oids VALUES (1);
> > INSERT INTO has_oids VALUES (1);
> > INSERT INTO has_oids VALUES (1);
> >
> > Seems that should be inside a BEGIN/COMMIT for performance reasons, and
> > to have the same behavior as COPY (fail if any row fails). Commands?
> >
> > As far as skipping on errors, I am unsure on that one, and if we put the
> > INSERTs in a transaction, we will have no way of rolling back only the
> > few inserts that fail.
> >
> That is right but there are sutuation when you prefer at least some
> data to be inserted and not all changes to be ralled back because
> of errors.
Added to TODO:
* Have pg_dump use multi-statement transactions for INSERT dumps
For simple performance reasons, it would be good. I am not sure about
allowing errors to continue loading. Anyone else?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:15 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
@ 2004-03-24 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-24 23:07 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2004-03-24 22:54 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
> * Have pg_dump use multi-statement transactions for INSERT dumps
> For simple performance reasons, it would be good. I am not sure about
> allowing errors to continue loading. Anyone else?
Of course, anyone who actually cares about reload speed shouldn't be
using INSERT-style dumps anyway ... I'm not sure why we should expend
effort on that rather than just telling people to use the COPY mode.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:15 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 22:54 ` Re: [Retrieved]RE: backup and recovery Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-24 23:07 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 23:41 ` Re: [Retrieved]RE: backup and recovery Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 21+ messages in thread
From: Bruce Momjian @ 2004-03-24 23:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> > * Have pg_dump use multi-statement transactions for INSERT dumps
>
> > For simple performance reasons, it would be good. I am not sure about
> > allowing errors to continue loading. Anyone else?
>
> Of course, anyone who actually cares about reload speed shouldn't be
> using INSERT-style dumps anyway ... I'm not sure why we should expend
> effort on that rather than just telling people to use the COPY mode.
My bigger issue is that COPY will fail on a single row failure, and
nothing will be in the table, while INSERT will not.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Re: [Retrieved]RE: backup and recovery Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:15 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 22:54 ` Re: [Retrieved]RE: backup and recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-03-24 23:07 ` Re: [Retrieved]RE: backup and recovery Bruce Momjian <pgman@candle.pha.pa.us>
@ 2004-03-24 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2004-03-24 23:41 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My bigger issue is that COPY will fail on a single row failure, and
> nothing will be in the table, while INSERT will not.
In theory, there shouldn't be any failures, because the data was known
valid when it was dumped.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
@ 2004-03-24 17:49 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
1 sibling, 0 replies; 21+ messages in thread
From: Tsirkin Evgeny @ 2004-03-24 17:49 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; +Cc: pgsql-admin
Isn't it better to put this say in the pg_restore
or integrate such thing into psql ?
On Tue, 23 Mar 2004 17:02:36 -0700, Naomi Walker <nwalker@eldocomp.com>
wrote:
>
> I'm not sure of the correct protocol for getting things on the "todo"
> list. Whom shall we beg?
>
>
> At 10:13 AM 3/22/2004, Mark M. Huber wrote:
>> 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
>> ------------------------------------------------------------------------------------------------------------------------
>
> 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 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
@ 2004-03-24 18:46 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 0 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-24 18:46 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
>
>Added to TODO:
>
> * Have pg_dump use multi-statement transactions for INSERT dumps
>
>For simple performance reasons, it would be good. I am not sure about
>allowing errors to continue loading. Anyone else?
I'm thinking about a job where you were loading a few million rows, and
getting an error on the 2,999,990th row, because of something like a
duplicate index issue. It would be nice to not have to start from scratch.
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-------------------------------------------------------------------------------------------------------------------------
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
@ 2004-03-24 22:58 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 0 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-24 22:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
At 03:54 PM 3/24/2004, Tom Lane wrote:
>Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> > * Have pg_dump use multi-statement transactions for INSERT dumps
>
> > For simple performance reasons, it would be good. I am not sure about
> > allowing errors to continue loading. Anyone else?
>
>Of course, anyone who actually cares about reload speed shouldn't be
>using INSERT-style dumps anyway ... I'm not sure why we should expend
>effort on that rather than just telling people to use the COPY mode.
Understood. I would still love this feature for when in the COPY mode.
> regards, tom lane
-------------------------------------------------------------------------------------------------------------------------
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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: backup and recovery
@ 2004-03-25 16:29 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 0 replies; 21+ messages in thread
From: Naomi Walker @ 2004-03-25 16:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Naomi Walker <nwalker@eldocomp.com>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
>
>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.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
@ 2004-03-26 20:30 Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
0 siblings, 0 replies; 21+ messages in thread
From: Murthy Kambhampaty @ 2004-03-26 20:30 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
I think you can get both benefits of "multi-statement transactions for INSERT dumps" by doing "subset copies" ... without any changes in postgresql! The method I use is developed for handling single table "loads", but is still relatively painless even for database dumps; however, it is limited to text dumps.
Let's say you want to unload-load table "tbl_foo" from schema "sch_bar" from database "db_baz" and reload "sch_bar.tbl_foo" to database "db_quux". Try the following:
1.) Dump-restore the table schema so you create an empty table in the destination database. e.g.:
/usr/local/pgsql-7.4/bin/pg_dump -s -t tbl_foo --schema sch_bar db_baz | \
/usr/local/pgsql-7.4/bin/psql -d db_quux
This can be adjusted for different hosts, etc.
2.) COPY the records to a file:
/usr/local/pgsql-7.4/bin/psql -d db_bar \
-c "copy sch_bar.tbl_foo to stdout" > sch_bar.tbl_foo.dat
OR
/usr/local/pgsql-7.4/bin/psql -d db_bar -Aqt \
-c "select * from sch_bar.tbl_foo where <some condition>" > sch_bar.tbl_foo.dat
The latter is slower, but selective. You can also use the -p option to set col and row separators to whatever you like (as with copy options). If your source data came from a dump file, rather than a COPY, you can strip sql commands to leave data only, or modify the commands below.
3. Pipe the data from sch_bar.tbl_foo.dat to psql, with copy commands spliced in at chosen intervals (in numbers of lines) depending on your preferences for speed versus "recoverability". In the example below, the subset size is 2000 lines:
awk \
-v SubSize=2000 \
-v COPYSTMT="copy sch_bar.tbl_foo from stdin;" \
'BEGIN{ print COPYSTMT } \
{ print $0 } \
FNR % SubSize == 0 { \
print "\\.\n\n" ; \
print "\n"; \
print COPYSTMT }' "sch_bar.tbl_foo.dat" | \
/usr/local/pgsql-7.4/bin/psql -U gouser -d airfrance -f -
The awk command specifies the chosen subset size ("2000") and a copy statement for putting stdin in the selected table; at the "BEGIN"ning, a copy statmenet is issued and lines are streamed in from the text file containing table rows; after each SubSize number of lines the copy stream is ended (as in text dumps, with a "\."), and a new copy statment inserted.
For a 220,000 row table, times for the simple copy versus the subset copy were:
Simple copy:
real 0m21.704s
user 0m3.790s
sys 0m0.880s
Subset copy:
real 0m24.233s
user 0m5.710s
sys 0m1.090s
Over 10% more wall clock time, but the savings from not having to rerun the entire "load" if errors are found could be tremendous.
3a. Alternately, you can generate a log so you easily know which subset failed (if any):
# LogFile="/home/postgres/load.log"; \
awk \
-v SubSize=2000 \
-v COPYSTMT="copy S2.air from stdin;" \
-v LogF="$LogFile" \
'BEGIN{ print "Block Size: " SubSize > LogF; \
print "Copy Statment: " COPYSTMT > LogF; \
print "\n\n" > LogF; \
close(LogF) ; \
print COPYSTMT } \
{ print $0 } \
FNR % SubSize == 0 { \
print "\\.\n\n" ; \
printf("select \047Processed %d records from line no. %d to line no. %d\047;\n", SubSize, FNR -SubSize +1, FNR) ; \
print "\n"; \
print COPYSTMT }
END{ \
print "\\.\n\n" ; \
printf("select \047Processed a grand total of %d lines from %s\047;\n", NR, FILENAME ) }' \
"sch_bar.tbl_foo.dat" | \
/usr/local/pgsql-7.4/bin/psql -U gouser -d airfrance -Atnq -f - >> "$LogFile" 2>&1
Errors can be located with:
[postgres@mybox postgres]$ cat load.log | grep -B 3 -A 3 "ERROR:"
Processed 2000 records from line no. 192001 to line no. 194000
Processed 2000 records from line no. 194001 to line no. 196000
Processed 2000 records from line no. 196001 to line no. 198000
ERROR: invalid input syntax for integer: "My0"
CONTEXT: COPY tbl_foo, line 2000, column oct 02: "My0"
Processed 2000 records from line no. 198001 to line no. 200000
Processed 2000 records from line no. 200001 to line no. 202000
HTH
Murthy
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Naomi Walker
> Sent: Wednesday, March 24, 2004 5:58 PM
> To: Tom Lane
> Cc: Bruce Momjian; Tsirkin Evgeny; Naomi Walker; Mark M. Huber;
> pgsql-admin@postgresql.org
> Subject: Re: [Retrieved]RE: [ADMIN] backup and recovery
>
>
> At 03:54 PM 3/24/2004, Tom Lane wrote:
> >Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Added to TODO:
> > > * Have pg_dump use multi-statement transactions for
> INSERT dumps
> >
> > > For simple performance reasons, it would be good. I am
> not sure about
> > > allowing errors to continue loading. Anyone else?
> >
> >Of course, anyone who actually cares about reload speed shouldn't be
> >using INSERT-style dumps anyway ... I'm not sure why we should expend
> >effort on that rather than just telling people to use the COPY mode.
>
> Understood. I would still love this feature for when in the
> COPY mode.
>
>
> > regards, tom lane
>
> --------------------------------------------------------------
> -----------------------------------------------------------
> 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
>
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: [Retrieved]RE: backup and recovery
@ 2004-03-27 04:18 Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
0 siblings, 0 replies; 21+ messages in thread
From: Murthy Kambhampaty @ 2004-03-27 04:18 UTC (permalink / raw)
To: Murthy Kambhampaty <murthy.kambhampaty@goeci.com>; Naomi Walker <nwalker@eldocomp.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Tsirkin Evgeny <tsurkin@mail.jct.ac.il>; Mark M. Huber <MHuber@VMdirect.com>; pgsql-admin
Oops, sorry for the typo in the psql command invocation. The output of the awk command in Step 3 is piped to
/usr/local/pgsql-7.4/bin/psql -d db_quux -f - -Atnq
(in the logging alternative it goes to
/usr/local/pgsql-7.4/bin/psql -d db_quux -f - -Atnq >> "$LogFile" 2>&1)
Cheers,
Murthy
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Murthy Kambhampaty
Sent: Fri 3/26/2004 3:30 PM
To: Naomi Walker; Tom Lane
Cc: Bruce Momjian; Tsirkin Evgeny; Mark M. Huber; pgsql-admin@postgresql.org
Subject: Re: [Retrieved]RE: [ADMIN] backup and recovery
I think you can get both benefits of "multi-statement transactions for INSERT dumps" by doing "subset copies" ... without any changes in postgresql! The method I use is developed for handling single table "loads", but is still relatively painless even for database dumps; however, it is limited to text dumps.
Let's say you want to unload-load table "tbl_foo" from schema "sch_bar" from database "db_baz" and reload "sch_bar.tbl_foo" to database "db_quux". Try the following:
1.) Dump-restore the table schema so you create an empty table in the destination database. e.g.:
/usr/local/pgsql-7.4/bin/pg_dump -s -t tbl_foo --schema sch_bar db_baz | \
/usr/local/pgsql-7.4/bin/psql -d db_quux
This can be adjusted for different hosts, etc.
2.) COPY the records to a file:
/usr/local/pgsql-7.4/bin/psql -d db_bar \
-c "copy sch_bar.tbl_foo to stdout" > sch_bar.tbl_foo.dat
OR
/usr/local/pgsql-7.4/bin/psql -d db_bar -Aqt \
-c "select * from sch_bar.tbl_foo where <some condition>" > sch_bar.tbl_foo.dat
The latter is slower, but selective. You can also use the -p option to set col and row separators to whatever you like (as with copy options). If your source data came from a dump file, rather than a COPY, you can strip sql commands to leave data only, or modify the commands below.
3. Pipe the data from sch_bar.tbl_foo.dat to psql, with copy commands spliced in at chosen intervals (in numbers of lines) depending on your preferences for speed versus "recoverability". In the example below, the subset size is 2000 lines:
awk \
-v SubSize=2000 \
-v COPYSTMT="copy sch_bar.tbl_foo from stdin;" \
'BEGIN{ print COPYSTMT } \
{ print $0 } \
FNR % SubSize == 0 { \
print "\\.\n\n" ; \
print "\n"; \
print COPYSTMT }' "sch_bar.tbl_foo.dat" | \
/usr/local/pgsql-7.4/bin/psql -U gouser -d airfrance -f -
The awk command specifies the chosen subset size ("2000") and a copy statement for putting stdin in the selected table; at the "BEGIN"ning, a copy statmenet is issued and lines are streamed in from the text file containing table rows; after each SubSize number of lines the copy stream is ended (as in text dumps, with a "\."), and a new copy statment inserted.
For a 220,000 row table, times for the simple copy versus the subset copy were:
Simple copy:
real 0m21.704s
user 0m3.790s
sys 0m0.880s
Subset copy:
real 0m24.233s
user 0m5.710s
sys 0m1.090s
Over 10% more wall clock time, but the savings from not having to rerun the entire "load" if errors are found could be tremendous.
3a. Alternately, you can generate a log so you easily know which subset failed (if any):
# LogFile="/home/postgres/load.log"; \
awk \
-v SubSize=2000 \
-v COPYSTMT="copy S2.air from stdin;" \
-v LogF="$LogFile" \
'BEGIN{ print "Block Size: " SubSize > LogF; \
print "Copy Statment: " COPYSTMT > LogF; \
print "\n\n" > LogF; \
close(LogF) ; \
print COPYSTMT } \
{ print $0 } \
FNR % SubSize == 0 { \
print "\\.\n\n" ; \
printf("select \047Processed %d records from line no. %d to line no. %d\047;\n", SubSize, FNR -SubSize +1, FNR) ; \
print "\n"; \
print COPYSTMT }
END{ \
print "\\.\n\n" ; \
printf("select \047Processed a grand total of %d lines from %s\047;\n", NR, FILENAME ) }' \
"sch_bar.tbl_foo.dat" | \
/usr/local/pgsql-7.4/bin/psql -U gouser -d airfrance -Atnq -f - >> "$LogFile" 2>&1
Errors can be located with:
[postgres@mybox postgres]$ cat load.log | grep -B 3 -A 3 "ERROR:"
Processed 2000 records from line no. 192001 to line no. 194000
Processed 2000 records from line no. 194001 to line no. 196000
Processed 2000 records from line no. 196001 to line no. 198000
ERROR: invalid input syntax for integer: "My0"
CONTEXT: COPY tbl_foo, line 2000, column oct 02: "My0"
Processed 2000 records from line no. 198001 to line no. 200000
Processed 2000 records from line no. 200001 to line no. 202000
HTH
Murthy
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Naomi Walker
> Sent: Wednesday, March 24, 2004 5:58 PM
> To: Tom Lane
> Cc: Bruce Momjian; Tsirkin Evgeny; Naomi Walker; Mark M. Huber;
> pgsql-admin@postgresql.org
> Subject: Re: [Retrieved]RE: [ADMIN] backup and recovery
>
>
> At 03:54 PM 3/24/2004, Tom Lane wrote:
> >Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Added to TODO:
> > > * Have pg_dump use multi-statement transactions for
> INSERT dumps
> >
> > > For simple performance reasons, it would be good. I am
> not sure about
> > > allowing errors to continue loading. Anyone else?
> >
> >Of course, anyone who actually cares about reload speed shouldn't be
> >using INSERT-style dumps anyway ... I'm not sure why we should expend
> >effort on that rather than just telling people to use the COPY mode.
>
> Understood. I would still love this feature for when in the
> COPY mode.
>
>
> > regards, tom lane
>
> --------------------------------------------------------------
> -----------------------------------------------------------
> 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
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2004-03-27 04:18 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-18 20:08 backup and recovery Mark M. Huber <MHuber@VMdirect.com>
2004-03-22 14:18 ` Jeff <threshar@torgo.978.org>
2004-03-19 16:20 Re: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-19 17:15 Re: backup and recovery Mark M. Huber <MHuber@VMdirect.com>
2004-03-22 16:19 Re: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-22 16:39 Re: backup and recovery Mark M. Huber <MHuber@VMdirect.com>
2004-03-22 17:13 Re: backup and recovery Mark M. Huber <MHuber@VMdirect.com>
2004-03-26 20:29 ` Bruno Wolff III <bruno@wolff.to>
2004-03-24 00:02 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 00:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 17:54 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-24 23:07 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-03-24 23:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-24 17:49 ` Tsirkin Evgeny <tsurkin@mail.jct.ac.il>
2004-03-24 18:46 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-24 22:58 Re: [Retrieved]RE: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-25 16:29 Re: backup and recovery Naomi Walker <nwalker@eldocomp.com>
2004-03-26 20:30 Re: [Retrieved]RE: backup and recovery Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
2004-03-27 04:18 Re: [Retrieved]RE: backup and recovery Murthy Kambhampaty <murthy.kambhampaty@goeci.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox