pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump vs pg_basebackup
19+ messages / 9 participants
[nested] [flat]
* pg_dump vs pg_basebackup
@ 2014-03-25 04:45 gianfranco caca <limpcaca@yahoo.com>
0 siblings, 2 replies; 19+ messages in thread
From: gianfranco caca @ 2014-03-25 04:45 UTC (permalink / raw)
To: pgsql-performance
Hai,
Can anyone tell me the difference and performance between pgdump and pg_basebackup if I want to backup a large database.
Thanks
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 07:11 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: gianfranco caca <limpcaca@yahoo.com>
1 sibling, 1 reply; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 07:11 UTC (permalink / raw)
To: gianfranco caca <limpcaca@yahoo.com>; +Cc: pgsql-performance
Hi gianfranco,
How exactly large is your database and how heavy is a workload on it?
Usually if you have more than ~200Gb, better to use pg_basebackup
because pg_dump will take too long time. And please take in mind, that
pg_dump makes dump, which is actually not the same thing as a backup.
Best regards,
Ilya
On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca <limpcaca@yahoo.com> wrote:
> Hai,
>
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
>
> Thanks
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 07:19 gianfranco caca <limpcaca@yahoo.com>
parent: Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
0 siblings, 1 reply; 19+ messages in thread
From: gianfranco caca @ 2014-03-25 07:19 UTC (permalink / raw)
To: ik@postgresql-consulting.com <ik@postgresql-consulting.com>; +Cc: pgsql-performance
Hai ilya,
Thanks for the respond. The database is estimated over 100gb and the workload will be high. Can we use a pg_basebackup with pitr to restore based on transaction time?
Thanks
On Tuesday, 25 March 2014, 15:13, Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
Hi gianfranco,
How exactly large is your database and how heavy is a workload on it?
Usually if you have more than ~200Gb, better to use pg_basebackup
because pg_dump will take too long time. And please take in mind, that
pg_dump makes dump, which is actually not the same thing as a backup.
Best regards,
Ilya
On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca <limpcaca@yahoo.com> wrote:
> Hai,
>
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
>
> Thanks
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 07:33 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: gianfranco caca <limpcaca@yahoo.com>
0 siblings, 0 replies; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 07:33 UTC (permalink / raw)
To: gianfranco caca <limpcaca@yahoo.com>; +Cc: pgsql-performance
Yes, you need to set recovery_target_time in your recovery.conf while
performing recovery
(http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html).
That could be a tricky thing - depends on that exactly you need. All
those transactions, which were not committed at given timestamp, will
be rollbacked, so read url above carefully.
On Tue, Mar 25, 2014 at 8:19 AM, gianfranco caca <limpcaca@yahoo.com> wrote:
> Hai ilya,
>
> Thanks for the respond. The database is estimated over 100gb and the
> workload will be high. Can we use a pg_basebackup with pitr to restore based
> on transaction time?
>
> Thanks
>
>
> On Tuesday, 25 March 2014, 15:13, Ilya Kosmodemiansky
> <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
> Hi gianfranco,
>
>
> How exactly large is your database and how heavy is a workload on it?
> Usually if you have more than ~200Gb, better to use pg_basebackup
> because pg_dump will take too long time. And please take in mind, that
> pg_dump makes dump, which is actually not the same thing as a backup.
>
> Best regards,
> Ilya
>
> On Tue, Mar 25, 2014 at 5:45 AM, gianfranco caca <limpcaca@yahoo.com> wrote:
>> Hai,
>>
>> Can anyone tell me the difference and performance between pgdump and
>> pg_basebackup if I want to backup a large database.
>>
>> Thanks
>
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 07:39 David Johnston <polobo@yahoo.com>
parent: gianfranco caca <limpcaca@yahoo.com>
1 sibling, 1 reply; 19+ messages in thread
From: David Johnston @ 2014-03-25 07:39 UTC (permalink / raw)
To: pgsql-performance
gianfranco caca wrote
> Hai,
>
> Can anyone tell me the difference and performance between pgdump and
> pg_basebackup if I want to backup a large database.
>
> Thanks
Yes. And many of their words have been written down in the documentation in
a chapter named "Backup and Restore". Do you have a specific question about
what is written there?
I'll add that comparing the performance of both is relatively meaningless.
You need to understand how each works then choose the correct tool for your
situation.
Lastly, you should actually do both, on a development database, and measure
the time and effort while practicing both routines (backup and restoring)
yourself.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/pg-dump-vs-pg-basebackup-tp5797351p5797364.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 12:05 Claudio Freire <klaussfreire@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 19+ messages in thread
From: Claudio Freire @ 2014-03-25 12:05 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-performance
On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com> wrote:
>> Hai,
>>
>> Can anyone tell me the difference and performance between pgdump and
>> pg_basebackup if I want to backup a large database.
>>
>> Thanks
>
> Yes. And many of their words have been written down in the documentation in
> a chapter named "Backup and Restore". Do you have a specific question about
> what is written there?
>
> I'll add that comparing the performance of both is relatively meaningless.
> You need to understand how each works then choose the correct tool for your
> situation.
I don't know if meaningless is the right word here. I have a ~450G
database, and the difference is quite meaningful to me, as it is
measured in days.
The difference being, pg_basebackup is dumber and using it is harder,
but its performance is only limited by sequential I/O capacity (which
is usually quite high). It is also used in conjunction with PITR to
get not only that, but also incremental backups, which is something
you really want for big databass. pg_dump, on the other hand, will
only do full dumps and it will be limited both by I/O and CPU power,
because the reformatting involved in making a dump is considerable. In
my experience, a base backup takes hours, while a dump takes days.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 14:56 Joshua D. Drake <jd@commandprompt.com>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Joshua D. Drake @ 2014-03-25 14:56 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; +Cc: pgsql-performance
On 03/25/2014 05:05 AM, Claudio Freire wrote:
>
> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com> wrote:
>>> Hai,
>>>
>>> Can anyone tell me the difference and performance between pgdump and
>>> pg_basebackup if I want to backup a large database.
>>>
Honestly,
Neither is particularly good at backing up large databases. I would look
into PITR with rsync.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:10 desmodemone <desmodemone@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 19+ messages in thread
From: desmodemone @ 2014-03-25 15:10 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
2014-03-25 15:56 GMT+01:00 Joshua D. Drake <jd@commandprompt.com>:
>
> On 03/25/2014 05:05 AM, Claudio Freire wrote:
>
>>
>> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com> wrote:
>>
>>> Hai,
>>>>
>>>> Can anyone tell me the difference and performance between pgdump and
>>>> pg_basebackup if I want to backup a large database.
>>>>
>>>>
> Honestly,
>
> Neither is particularly good at backing up large databases. I would look
> into PITR with rsync.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
For large database it's possible also to consider , also, to change
database status in backup mode and after take a snapshoot and returning to
normal mode, saving also all archive after you finish the backup.
With that snapshoot you could easy mount it and restore on another machine
or open in readonly mode (hot standby and after do a logical dump ) , a lot
of storage have these capabilities and also filesystem or volume manager.
I think these is the fater option you have.
Mat Dba
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:18 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 2 replies; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 15:18 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
Joshua,
that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.
On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
> On 03/25/2014 05:05 AM, Claudio Freire wrote:
>>
>>
>> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com> wrote:
>>>>
>>>> Hai,
>>>>
>>>> Can anyone tell me the difference and performance between pgdump and
>>>> pg_basebackup if I want to backup a large database.
>>>>
>
> Honestly,
>
> Neither is particularly good at backing up large databases. I would look
> into PITR with rsync.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:21 Magnus Hagander <magnus@hagander.net>
parent: Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
1 sibling, 2 replies; 19+ messages in thread
From: Magnus Hagander @ 2014-03-25 15:21 UTC (permalink / raw)
To: ik@postgresql-consulting.com; +Cc: Joshua D. Drake <jd@commandprompt.com>; Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
I would say that's the one thing that rsync is *not*. pg_basebackup takes
care of a lot of things under the hood. rsync is a lot more complicated, in
particular in failure scenarios, since you have to manually deal with
pg_start/stop_backup().
There are definitely reasons you'd prefer rsync over pg_basebackup, but I
don't believe simplicity is one of them.
//Magnus
On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky <
ilya.kosmodemiansky@postgresql-consulting.com> wrote:
> Joshua,
>
> that is really good point: an alternative is to use pg_basebackup
> through ssh tunnel with compression, but rsync is much simpler.
>
> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com>
> wrote:
> >
> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
> >>
> >>
> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com>
> wrote:
> >>>>
> >>>> Hai,
> >>>>
> >>>> Can anyone tell me the difference and performance between pgdump and
> >>>> pg_basebackup if I want to backup a large database.
> >>>>
> >
> > Honestly,
> >
> > Neither is particularly good at backing up large databases. I would look
> > into PITR with rsync.
> >
> > JD
> >
> >
> > --
> > Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> > PostgreSQL Support, Training, Professional Services and Development
> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> > Political Correctness is for cowards.
> >
> >
> >
> > --
> > Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:22 Joshua D. Drake <jd@commandprompt.com>
parent: Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
1 sibling, 2 replies; 19+ messages in thread
From: Joshua D. Drake @ 2014-03-25 15:22 UTC (permalink / raw)
To: ik@postgresql-consulting.com; +Cc: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
>
> Joshua,
>
> that is really good point: an alternative is to use pg_basebackup
> through ssh tunnel with compression, but rsync is much simpler.
Or rsync over ssh. The advantage is that you can create backups that
don't have to be restored, just started. You can also use the
differential portions of rsync to do it multiple times a day without
much issue.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:29 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 1 reply; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 15:29 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
Joshua,
On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
The advantage is that you can create backups that don't
> have to be restored, just started. You can also use the differential
> portions of rsync to do it multiple times a day without much issue.
Are you sure, that it is a nice idea on a database with heavy write workload?
And also Im not sure, that differential backups using rsync will be
recoverable, if you have actually meant that.
>
>
> JD
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> Political Correctness is for cowards.
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:37 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 15:37 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
Magnus,
That is correct, but I'am afraid that such all-in-one functionality
also hides from one how backup really works. Probably such sort of
knowledge is so essential for a DBA, that it is better to learn both
methods, at least to be able to choose correctly? But maybe it is a
rhetorical question.
On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander <magnus@hagander.net> wrote:
> I would say that's the one thing that rsync is *not*. pg_basebackup takes
> care of a lot of things under the hood. rsync is a lot more complicated, in
> particular in failure scenarios, since you have to manually deal with
> pg_start/stop_backup().
>
> There are definitely reasons you'd prefer rsync over pg_basebackup, but I
> don't believe simplicity is one of them.
>
> //Magnus
>
>
> On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
> <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
>>
>> Joshua,
>>
>> that is really good point: an alternative is to use pg_basebackup
>> through ssh tunnel with compression, but rsync is much simpler.
>>
>> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com>
>> wrote:
>> >
>> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
>> >>
>> >>
>> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com>
>> >> wrote:
>> >>>>
>> >>>> Hai,
>> >>>>
>> >>>> Can anyone tell me the difference and performance between pgdump and
>> >>>> pg_basebackup if I want to backup a large database.
>> >>>>
>> >
>> > Honestly,
>> >
>> > Neither is particularly good at backing up large databases. I would look
>> > into PITR with rsync.
>> >
>> > JD
>> >
>> >
>> > --
>> > Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
>> > PostgreSQL Support, Training, Professional Services and Development
>> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> > Political Correctness is for cowards.
>> >
>> >
>> >
>> > --
>> > Sent via pgsql-performance mailing list
>> > (pgsql-performance@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>>
>> --
>> Ilya Kosmodemiansky,
>>
>> PostgreSQL-Consulting.com
>> tel. +14084142500
>> cell. +4915144336040
>> ik@postgresql-consulting.com
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:40 Magnus Hagander <magnus@hagander.net>
parent: Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
0 siblings, 1 reply; 19+ messages in thread
From: Magnus Hagander @ 2014-03-25 15:40 UTC (permalink / raw)
To: ik@postgresql-consulting.com; +Cc: Joshua D. Drake <jd@commandprompt.com>; Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
Oh, I agree it's good that you should know both methods. I only disagree
with that the choice of rsync be made with the argument of simplicity.
Simplicity is one of the main reasons to choose the *other* method
(pg_basebackup), and the rsync method is for more advanced usecases. But
it's definitely good to know both!
//Magnus
On Tue, Mar 25, 2014 at 4:37 PM, Ilya Kosmodemiansky <
ilya.kosmodemiansky@postgresql-consulting.com> wrote:
> Magnus,
>
> That is correct, but I'am afraid that such all-in-one functionality
> also hides from one how backup really works. Probably such sort of
> knowledge is so essential for a DBA, that it is better to learn both
> methods, at least to be able to choose correctly? But maybe it is a
> rhetorical question.
>
> On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander <magnus@hagander.net>
> wrote:
> > I would say that's the one thing that rsync is *not*. pg_basebackup takes
> > care of a lot of things under the hood. rsync is a lot more complicated,
> in
> > particular in failure scenarios, since you have to manually deal with
> > pg_start/stop_backup().
> >
> > There are definitely reasons you'd prefer rsync over pg_basebackup, but I
> > don't believe simplicity is one of them.
> >
> > //Magnus
> >
> >
> > On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
> > <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
> >>
> >> Joshua,
> >>
> >> that is really good point: an alternative is to use pg_basebackup
> >> through ssh tunnel with compression, but rsync is much simpler.
> >>
> >> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com>
> >> wrote:
> >> >
> >> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
> >> >>
> >> >>
> >> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com>
> >> >> wrote:
> >> >>>>
> >> >>>> Hai,
> >> >>>>
> >> >>>> Can anyone tell me the difference and performance between pgdump
> and
> >> >>>> pg_basebackup if I want to backup a large database.
> >> >>>>
> >> >
> >> > Honestly,
> >> >
> >> > Neither is particularly good at backing up large databases. I would
> look
> >> > into PITR with rsync.
> >> >
> >> > JD
> >> >
> >> >
> >> > --
> >> > Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
> >> > PostgreSQL Support, Training, Professional Services and Development
> >> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
> >> > Political Correctness is for cowards.
> >> >
> >> >
> >> >
> >> > --
> >> > Sent via pgsql-performance mailing list
> >> > (pgsql-performance@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >>
> >>
> >> --
> >> Ilya Kosmodemiansky,
> >>
> >> PostgreSQL-Consulting.com
> >> tel. +14084142500
> >> cell. +4915144336040
> >> ik@postgresql-consulting.com
> >>
> >>
> >> --
> >> Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >
> >
> >
> >
> > --
> > Magnus Hagander
> > Me: http://www.hagander.net/
> > Work: http://www.redpill-linpro.com/
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:41 Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 19+ messages in thread
From: Ilya Kosmodemiansky @ 2014-03-25 15:41 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
OK, agreed. Ive got your point;-)
On Tue, Mar 25, 2014 at 4:40 PM, Magnus Hagander <magnus@hagander.net> wrote:
> Oh, I agree it's good that you should know both methods. I only disagree
> with that the choice of rsync be made with the argument of simplicity.
> Simplicity is one of the main reasons to choose the *other* method
> (pg_basebackup), and the rsync method is for more advanced usecases. But
> it's definitely good to know both!
>
> //Magnus
>
>
>
> On Tue, Mar 25, 2014 at 4:37 PM, Ilya Kosmodemiansky
> <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
>>
>> Magnus,
>>
>> That is correct, but I'am afraid that such all-in-one functionality
>> also hides from one how backup really works. Probably such sort of
>> knowledge is so essential for a DBA, that it is better to learn both
>> methods, at least to be able to choose correctly? But maybe it is a
>> rhetorical question.
>>
>> On Tue, Mar 25, 2014 at 4:21 PM, Magnus Hagander <magnus@hagander.net>
>> wrote:
>> > I would say that's the one thing that rsync is *not*. pg_basebackup
>> > takes
>> > care of a lot of things under the hood. rsync is a lot more complicated,
>> > in
>> > particular in failure scenarios, since you have to manually deal with
>> > pg_start/stop_backup().
>> >
>> > There are definitely reasons you'd prefer rsync over pg_basebackup, but
>> > I
>> > don't believe simplicity is one of them.
>> >
>> > //Magnus
>> >
>> >
>> > On Tue, Mar 25, 2014 at 4:18 PM, Ilya Kosmodemiansky
>> > <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
>> >>
>> >> Joshua,
>> >>
>> >> that is really good point: an alternative is to use pg_basebackup
>> >> through ssh tunnel with compression, but rsync is much simpler.
>> >>
>> >> On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake <jd@commandprompt.com>
>> >> wrote:
>> >> >
>> >> > On 03/25/2014 05:05 AM, Claudio Freire wrote:
>> >> >>
>> >> >>
>> >> >> On Tue, Mar 25, 2014 at 4:39 AM, David Johnston <polobo@yahoo.com>
>> >> >> wrote:
>> >> >>>>
>> >> >>>> Hai,
>> >> >>>>
>> >> >>>> Can anyone tell me the difference and performance between pgdump
>> >> >>>> and
>> >> >>>> pg_basebackup if I want to backup a large database.
>> >> >>>>
>> >> >
>> >> > Honestly,
>> >> >
>> >> > Neither is particularly good at backing up large databases. I would
>> >> > look
>> >> > into PITR with rsync.
>> >> >
>> >> > JD
>> >> >
>> >> >
>> >> > --
>> >> > Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
>> >> > PostgreSQL Support, Training, Professional Services and Development
>> >> > High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> >> > Political Correctness is for cowards.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Sent via pgsql-performance mailing list
>> >> > (pgsql-performance@postgresql.org)
>> >> > To make changes to your subscription:
>> >> > http://www.postgresql.org/mailpref/pgsql-performance
>> >>
>> >>
>> >>
>> >> --
>> >> Ilya Kosmodemiansky,
>> >>
>> >> PostgreSQL-Consulting.com
>> >> tel. +14084142500
>> >> cell. +4915144336040
>> >> ik@postgresql-consulting.com
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-performance mailing list
>> >> (pgsql-performance@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-performance
>> >
>> >
>> >
>> >
>> > --
>> > Magnus Hagander
>> > Me: http://www.hagander.net/
>> > Work: http://www.redpill-linpro.com/
>>
>>
>>
>> --
>> Ilya Kosmodemiansky,
>>
>> PostgreSQL-Consulting.com
>> tel. +14084142500
>> cell. +4915144336040
>> ik@postgresql-consulting.com
>
>
>
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
--
Ilya Kosmodemiansky,
PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:48 Graeme B. Bell <grb@skogoglandskap.no>
parent: Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
0 siblings, 1 reply; 19+ messages in thread
From: Graeme B. Bell @ 2014-03-25 15:48 UTC (permalink / raw)
To: pgsql-performance
Postgresql rsync backups require the DB to be shutdown during the 'second' rsync.
1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99% consistent DB on the backup filesystem)
2. shut down the DB
3. rsync the shut down DB onto the backup filesystem (synchronises the last few files to make the DB consistent, and is usually very fast)
4. start the DB up again
Is there any way to notify postgres to pause transactions (and note that they should be restarted), and flush out write buffers etc, instead of doing a full shutdown?
e.g. so that the second rsync call would bring the backup filesystem's representation of the DB into a recoverable state without needing to shutdown the production DB completely.
G
On 25 Mar 2014, at 16:29, Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com> wrote:
> Joshua,
>
> On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> The advantage is that you can create backups that don't
>> have to be restored, just started. You can also use the differential
>> portions of rsync to do it multiple times a day without much issue.
>
> Are you sure, that it is a nice idea on a database with heavy write workload?
>
> And also Im not sure, that differential backups using rsync will be
> recoverable, if you have actually meant that.
>
>>
>>
>> JD
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
>> PostgreSQL Support, Training, Professional Services and Development
>> High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
>> Political Correctness is for cowards.
>
>
>
> --
> Ilya Kosmodemiansky,
>
> PostgreSQL-Consulting.com
> tel. +14084142500
> cell. +4915144336040
> ik@postgresql-consulting.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 15:55 Joshua D. Drake <jd@commandprompt.com>
parent: Magnus Hagander <magnus@hagander.net>
1 sibling, 0 replies; 19+ messages in thread
From: Joshua D. Drake @ 2014-03-25 15:55 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; ik@postgresql-consulting.com; +Cc: Claudio Freire <klaussfreire@gmail.com>; David Johnston <polobo@yahoo.com>; pgsql-performance
On 03/25/2014 08:21 AM, Magnus Hagander wrote:
> I would say that's the one thing that rsync is *not*. pg_basebackup
> takes care of a lot of things under the hood. rsync is a lot more
> complicated, in particular in failure scenarios, since you have to
> manually deal with pg_start/stop_backup().
>
> There are definitely reasons you'd prefer rsync over pg_basebackup, but
> I don't believe simplicity is one of them.
>
> //Magnus
Good God man... since when do you top post!
Well there are tools that use rsync to solve those issues :P. We even
have one that does multi-threaded rsync so you can pull many Terabytes
in very little time (relatively).
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 16:12 Alan Hodgson <ahodgson@simkin.ca>
parent: Graeme B. Bell <grb@skogoglandskap.no>
0 siblings, 0 replies; 19+ messages in thread
From: Alan Hodgson @ 2014-03-25 16:12 UTC (permalink / raw)
To: pgsql-performance
On Tuesday, March 25, 2014 03:48:07 PM Graeme B. Bell wrote:
> Postgresql rsync backups require the DB to be shutdown during the 'second'
> rsync.
>
> 1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99%
> consistent DB on the backup filesystem) 2. shut down the DB
> 3. rsync the shut down DB onto the backup filesystem (synchronises the
> last few files to make the DB consistent, and is usually very fast) 4.
> start the DB up again
>
> Is there any way to notify postgres to pause transactions (and note that
> they should be restarted), and flush out write buffers etc, instead of
> doing a full shutdown? e.g. so that the second rsync call would bring the
> backup filesystem's representation of the DB into a recoverable state
> without needing to shutdown the production DB completely.
>
You use pg_start_backup() before rsync, and pg_stop_backup() after. And keep
all your WAL log files. No need to pause transactions; whatever happens during
the rsync just gets replayed during recovery (as I understand it). You do need
to do a PITR restore to make use of this rsync copy.
That's basically what pg_basebackup does, I believe (I haven't used it, I only
do rsyncs).
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: pg_dump vs pg_basebackup
@ 2014-03-25 16:46 Claudio Freire <klaussfreire@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 19+ messages in thread
From: Claudio Freire @ 2014-03-25 16:46 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: ik@postgresql-consulting.com, David Johnston <polobo@yahoo.com>; pgsql-performance
On Tue, Mar 25, 2014 at 12:22 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
>>
>>
>> Joshua,
>>
>> that is really good point: an alternative is to use pg_basebackup
>> through ssh tunnel with compression, but rsync is much simpler.
>
>
> Or rsync over ssh. The advantage is that you can create backups that don't
> have to be restored, just started. You can also use the differential
> portions of rsync to do it multiple times a day without much issue.
rsync's delta transfer isn't relly very effective with postgres. You
don't save any I/O, just network traffic, and in general the
bottleneck is I/O (unless you have a monster I/O subsys or a snail of
a network one).
There were some musing about making delta transfer more efficient in
pg in hackers, but I don't think anything tangible came out of that,
so it's basically equivalent to a full transfer. The only reason to
leverage rsync's delta transfer would be to decrease the time between
pg_start_backup and pg_stop_backup, which could only matter if you're
low on WAL space, but the reduction, in my experience, isn't stellar.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2014-03-25 16:46 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-25 04:45 pg_dump vs pg_basebackup gianfranco caca <limpcaca@yahoo.com>
2014-03-25 07:11 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 07:19 ` gianfranco caca <limpcaca@yahoo.com>
2014-03-25 07:33 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 07:39 ` David Johnston <polobo@yahoo.com>
2014-03-25 12:05 ` Claudio Freire <klaussfreire@gmail.com>
2014-03-25 14:56 ` Joshua D. Drake <jd@commandprompt.com>
2014-03-25 15:10 ` desmodemone <desmodemone@gmail.com>
2014-03-25 15:18 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 15:21 ` Magnus Hagander <magnus@hagander.net>
2014-03-25 15:37 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 15:40 ` Magnus Hagander <magnus@hagander.net>
2014-03-25 15:41 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 15:55 ` Joshua D. Drake <jd@commandprompt.com>
2014-03-25 15:22 ` Joshua D. Drake <jd@commandprompt.com>
2014-03-25 15:29 ` Ilya Kosmodemiansky <ilya.kosmodemiansky@postgresql-consulting.com>
2014-03-25 15:48 ` Graeme B. Bell <grb@skogoglandskap.no>
2014-03-25 16:12 ` Alan Hodgson <ahodgson@simkin.ca>
2014-03-25 16:46 ` Claudio Freire <klaussfreire@gmail.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