pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump why no indicator of completion
37+ messages / 12 participants
[nested] [flat]
* pg_dump why no indicator of completion
@ 2023-05-01 00:18 richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 5 replies; 37+ messages in thread
From: richard coleman @ 2023-05-01 00:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi all,
I've been working with pg_dump and one thing that's always struck me as
strange is that there never seems to be an indication in the log that the
pg_dump process completed successfully.
For example, I've been running a bunch of backups with pg_dump in directory
mode with multiple jobs. When I come back several hours later there are no
pg_dump processes running. The log is filled with:
dumping contents of foo
finished item 123456 of TABLE DATA bar
etc.
Other than trying to restore the multi terabyte database somewhere else is
there any way to know that it actually finished successfully?
Why doesn't pg_dump add a line in the output like:
pg_backup finished
when it's completed successfully?
This seems like a terrible oversight.
Am I missing something obvious?
Thanks,
rik.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 01:08 Gavan Schneider <list.pg.gavan@pendari.org>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
4 siblings, 0 replies; 37+ messages in thread
From: Gavan Schneider @ 2023-05-01 01:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>
On 1 May 2023, at 10:18, richard coleman wrote:
> Why doesn't pg_dump add a line in the output like:
>
> pg_backup finished
>
And, consider an option to include the MD5 and/or SHA256 checksum(s) for what should be on the file system.
> when it's completed successfully?
>
Or, more generally, the appropriate status
From a basic perspective we can expect the *nix shell to get a non-zero exit status for pg_dump in the event of failure, but this is not an explicit promise in the diagnostics section of my copy of the man entry (maybe too obvious :).
Regards
Gavan
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 03:28 Ron <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
4 siblings, 2 replies; 37+ messages in thread
From: Ron @ 2023-05-01 03:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 4/30/23 19:18, richard coleman wrote:
> Hi all,
>
> I've been working with pg_dump and one thing that's always struck me as
> strange is that there never seems to be an indication in the log that the
> pg_dump process completed successfully.
>
> For example, I've been running a bunch of backups with pg_dump in
> directory mode with multiple jobs. When I come back several hours later
> there are no pg_dump processes running. The log is filled with:
>
> dumping contents of foo
> finished item 123456 of TABLE DATA bar
> etc.
>
>
> Other than trying to restore the multi terabyte database somewhere else is
> there any way to know that it actually finished successfully?
>
> Why doesn't pg_dump add a line in the output like:
>
> pg_backup finished
>
> when it's completed successfully?
>
> This seems like a terrible oversight.
>
> Am I missing something obvious?
"They" expect you to do the Unix Thing and check $?. Thus, that's what I do;
any non-zero value generates an email with a scary subject line, the
specifics of which are based on the exact code value. I'm not near my work
computer; otherwise I'd share them with you.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 07:43 Holger Jakobs <holger@jakobs.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
4 siblings, 0 replies; 37+ messages in thread
From: Holger Jakobs @ 2023-05-01 07:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, richard coleman <rcoleman.ascentgl@gmail.com>
At least it you use plaintext dump, you can check with tail dumpFile for the message
Am 1. Mai 2023 02:18:06 MESZ schrieb richard coleman <rcoleman.ascentgl@gmail.com>:
>Hi all,
>
>I've been working with pg_dump and one thing that's always struck me as
>strange is that there never seems to be an indication in the log that the
>pg_dump process completed successfully.
>
>For example, I've been running a bunch of backups with pg_dump in directory
>mode with multiple jobs. When I come back several hours later there are no
>pg_dump processes running. The log is filled with:
>
>dumping contents of foo
>finished item 123456 of TABLE DATA bar
>etc.
>
>
>Other than trying to restore the multi terabyte database somewhere else is
>there any way to know that it actually finished successfully?
>
>Why doesn't pg_dump add a line in the output like:
>
>pg_backup finished
>
>when it's completed successfully?
>
>This seems like a terrible oversight.
>
>Am I missing something obvious?
>
>Thanks,
>rik.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 10:12 Frank Lanitz <frank@frank.uvena.de>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
4 siblings, 1 reply; 37+ messages in thread
From: Frank Lanitz @ 2023-05-01 10:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
On 01.05.23 02:18, richard coleman wrote:
> Hi all,
>
> I've been working with pg_dump and one thing that's always struck me as
> strange is that there never seems to be an indication in the log that
> the pg_dump process completed successfully.
I don't think it's needed.
How are you running pg_dump? There is a return code 0 if successful or
!= 0 in case of an error.
Cheer,
Frank
Attachments:
[application/pgp-signature] OpenPGP_signature (235B, ../../61e421bc-d47f-05ef-edb2-bd9cbce1be40@frank.uvena.de/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 10:14 Frank Lanitz <frank@frank.uvena.de>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 37+ messages in thread
From: Frank Lanitz @ 2023-05-01 10:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 01.05.23 05:28, Ron wrote:
> "They" expect you to do the Unix Thing and check $?. Thus, that's what I
> do; any non-zero value generates an email with a scary subject line, the
> specifics of which are based on the exact code value. I'm not near my
> work computer; otherwise I'd share them with you.
Or put it to an SystemD unit and check for failed ones inside your
monitoring ;)
.f
Attachments:
[application/pgp-signature] OpenPGP_signature (235B, ../../468cb22f-8824-f684-d395-75543a107643@frank.uvena.de/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 12:56 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 37+ messages in thread
From: richard coleman @ 2023-05-01 12:56 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Ron,
So what you are saying is that I have to write a shell script around
pg_dump to catch the exit code of pg_dump since the developers of pg_dump
didn't have pg_dump simply write an exit message?
Since multiple pg_dump commands are often run at the same command prompt,
and they can take hours, if not days to run, and there are a myriad of
other commands the will be run in the interim, any exit code generated by
pg_dump with be lost in the flotsam and jetsam of the multitude of exit
codes created by every other command run between the calling of pg_dump and
it's eventual termination. It could have completed successfully, crashed,
been killed, etc.
Or am I mistaken?
thanks,
rik.
On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 4/30/23 19:18, richard coleman wrote:
>
> Hi all,
>
> I've been working with pg_dump and one thing that's always struck me as
> strange is that there never seems to be an indication in the log that the
> pg_dump process completed successfully.
>
> For example, I've been running a bunch of backups with pg_dump in
> directory mode with multiple jobs. When I come back several hours later
> there are no pg_dump processes running. The log is filled with:
>
> dumping contents of foo
> finished item 123456 of TABLE DATA bar
> etc.
>
>
> Other than trying to restore the multi terabyte database somewhere else is
> there any way to know that it actually finished successfully?
>
> Why doesn't pg_dump add a line in the output like:
>
> pg_backup finished
>
> when it's completed successfully?
>
> This seems like a terrible oversight.
>
> Am I missing something obvious?
>
>
> "They" expect you to do the Unix Thing and check $?. Thus, that's what I
> do; any non-zero value generates an email with a scary subject line, the
> specifics of which are based on the exact code value. I'm not near my work
> computer; otherwise I'd share them with you.
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 12:59 Scott Ribe <scott_ribe@elevated-dev.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Scott Ribe @ 2023-05-01 12:59 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On May 1, 2023, at 6:56 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> Since multiple pg_dump commands are often run at the same command prompt, and they can take hours, if not days to run, and there are a myriad of other commands the will be run in the interim, any exit code generated by pg_dump with be lost in the flotsam and jetsam of the multitude of exit codes created by every other command run between the calling of pg_dump and it's eventual termination. It could have completed successfully, crashed, been killed, etc.
That sounds like a really bad way to do things...
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:01 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 37+ messages in thread
From: richard coleman @ 2023-05-01 13:01 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Scott,
Which, running more than one command from the shell prompt in a single
logon session, or not emitting an exit message?
rik.
On Mon, May 1, 2023 at 8:59 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On May 1, 2023, at 6:56 AM, richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
> >
> > Since multiple pg_dump commands are often run at the same command
> prompt, and they can take hours, if not days to run, and there are a myriad
> of other commands the will be run in the interim, any exit code generated
> by pg_dump with be lost in the flotsam and jetsam of the multitude of exit
> codes created by every other command run between the calling of pg_dump and
> it's eventual termination. It could have completed successfully, crashed,
> been killed, etc.
>
> That sounds like a really bad way to do things...
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:07 Scott Ribe <scott_ribe@elevated-dev.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 2 replies; 37+ messages in thread
From: Scott Ribe @ 2023-05-01 13:07 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On May 1, 2023, at 7:01 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> Which, running more than one command from the shell prompt in a single logon session, or not emitting an exit message?
Running multiple long-lived processes in a shell prompt, whose exit values you care about, running in the background, along with "a myriad of other commands", presumably short-lived, in the interim, then looking through "the flotsam and jetsam" for a success message. Sounds like a total mess. Put your important long-lived processesin their own sessions. Or, if you must interleave them, wrap the call to pg_dump in a simple conditional.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:26 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 37+ messages in thread
From: richard coleman @ 2023-05-01 13:26 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Scott,
Generally I run pg_dump nohup in the background with all outputs captured
to a file. Finding the exit message is simply a matter of navigating to
the bottom of the output file and checking.
Excepting for pg_dump, I don't generally have to worry about exit values as
most command line programs are either short-lived and obvious, like ls, or
emit their own text exit status.
In my experience checking exit status codes are what you do when writing
programs to determine the status of a subroutine or function that you are
calling. Stand alone programs that are expected to take any appreciable
amount of time to run generally provide an status output, precisely for
this reason.
I hope that the devs reconsider and add a status output message. If
checking the exit status is the only way to determine if the pg_dump
process completed successfully, then I am going to be forced to create a
wrapper to capture and emit the status around the pg_dump executable. If I
need to do that, so will every other user who needs to know the exit status
and runs pg_dump in a typical linux workflow. Seems like a terrible waste
having untold numbers of users having to constantly reinvent the wheel.
rik.
On Mon, May 1, 2023 at 9:07 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On May 1, 2023, at 7:01 AM, richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
> >
> > Which, running more than one command from the shell prompt in a single
> logon session, or not emitting an exit message?
>
> Running multiple long-lived processes in a shell prompt, whose exit values
> you care about, running in the background, along with "a myriad of other
> commands", presumably short-lived, in the interim, then looking through
> "the flotsam and jetsam" for a success message. Sounds like a total mess.
> Put your important long-lived processesin their own sessions. Or, if you
> must interleave them, wrap the call to pg_dump in a simple conditional.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:37 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 37+ messages in thread
From: Ron @ 2023-05-01 13:37 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 5/1/23 08:07, Scott Ribe wrote:
>> On May 1, 2023, at 7:01 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>>
>> Which, running more than one command from the shell prompt in a single logon session, or not emitting an exit message?
> Running multiple long-lived processes in a shell prompt, whose exit values you care about, running in the background,
Exactly. Cron is your friend!!!
> along with "a myriad of other commands", presumably short-lived, in the interim, then looking through "the flotsam and jetsam" for a success message. Sounds like a total mess. Put your important long-lived processesin their own sessions. Or, if you must interleave them, wrap the call to pg_dump in a simple conditional.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:43 Ron <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Ron @ 2023-05-01 13:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/1/23 07:56, richard coleman wrote:
> Ron,
> So what you are saying is that I have to write a shell script around
> pg_dump to catch the exit code of pg_dump since the developers of pg_dump
> didn't have pg_dump simply write an exit message?
>
> Since multiple pg_dump commands are often run at the same command prompt,
> and they can take hours, if not days to run,
Running a days-long pg_dump at any time except special occasions isn't very
wise. Especially from a command prompt.
Binary backup/restore programs are /much/ faster (if for no other reason
than they let you do incremental and differential backups).
> and there are a myriad of other commands the will be run in the interim,
> any exit code generated by pg_dump with be lost in the flotsam and jetsam
> of the multitude of exit codes created by every other command run between
> the calling of pg_dump and it's eventual termination. It could have
> completed successfully, crashed, been killed, etc.
>
> Or am I mistaken?
>
> thanks,
> rik.
>
> On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 4/30/23 19:18, richard coleman wrote:
>> Hi all,
>>
>> I've been working with pg_dump and one thing that's always struck me
>> as strange is that there never seems to be an indication in the log
>> that the pg_dump process completed successfully.
>>
>> For example, I've been running a bunch of backups with pg_dump in
>> directory mode with multiple jobs. When I come back several hours
>> later there are no pg_dump processes running. The log is filled with:
>>
>> dumping contents of foo
>> finished item 123456 of TABLE DATA bar
>> etc.
>>
>>
>> Other than trying to restore the multi terabyte database somewhere
>> else is there any way to know that it actually finished successfully?
>>
>> Why doesn't pg_dump add a line in the output like:
>>
>> pg_backup finished
>>
>> when it's completed successfully?
>>
>> This seems like a terrible oversight.
>>
>> Am I missing something obvious?
>
> "They" expect you to do the Unix Thing and check $?. Thus, that's what
> I do; any non-zero value generates an email with a scary subject line,
> the specifics of which are based on the exact code value. I'm not near
> my work computer; otherwise I'd share them with you.
>
> --
> Born in Arizona, moved to Babylonia.
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 13:55 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 37+ messages in thread
From: richard coleman @ 2023-05-01 13:55 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Ron,
Are you writing that pg_dump is unfit for purpose and that I should be
using a commercial backup solution instead?
rik.
On Mon, May 1, 2023 at 9:43 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>
> On 5/1/23 07:56, richard coleman wrote:
>
> Ron,
> So what you are saying is that I have to write a shell script around
> pg_dump to catch the exit code of pg_dump since the developers of pg_dump
> didn't have pg_dump simply write an exit message?
>
> Since multiple pg_dump commands are often run at the same command prompt,
> and they can take hours, if not days to run,
>
>
> Running a days-long pg_dump at any time except special occasions isn't
> very wise. Especially from a command prompt.
>
> Binary backup/restore programs are *much* faster (if for no other reason
> than they let you do incremental and differential backups).
>
> and there are a myriad of other commands the will be run in the interim,
> any exit code generated by pg_dump with be lost in the flotsam and jetsam
> of the multitude of exit codes created by every other command run between
> the calling of pg_dump and it's eventual termination. It could have
> completed successfully, crashed, been killed, etc.
>
> Or am I mistaken?
>
> thanks,
> rik.
>
> On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 4/30/23 19:18, richard coleman wrote:
>>
>> Hi all,
>>
>> I've been working with pg_dump and one thing that's always struck me as
>> strange is that there never seems to be an indication in the log that the
>> pg_dump process completed successfully.
>>
>> For example, I've been running a bunch of backups with pg_dump in
>> directory mode with multiple jobs. When I come back several hours later
>> there are no pg_dump processes running. The log is filled with:
>>
>> dumping contents of foo
>> finished item 123456 of TABLE DATA bar
>> etc.
>>
>>
>> Other than trying to restore the multi terabyte database somewhere else
>> is there any way to know that it actually finished successfully?
>>
>> Why doesn't pg_dump add a line in the output like:
>>
>> pg_backup finished
>>
>> when it's completed successfully?
>>
>> This seems like a terrible oversight.
>>
>> Am I missing something obvious?
>>
>>
>> "They" expect you to do the Unix Thing and check $?. Thus, that's what I
>> do; any non-zero value generates an email with a scary subject line, the
>> specifics of which are based on the exact code value. I'm not near my work
>> computer; otherwise I'd share them with you.
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:00 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Frank Lanitz <frank@frank.uvena.de>
0 siblings, 1 reply; 37+ messages in thread
From: richard coleman @ 2023-05-01 14:00 UTC (permalink / raw)
To: Frank Lanitz <frank@frank.uvena.de>; +Cc: pgsql-admin@lists.postgresql.org
Frank,
As I've mentioned in another response I typically run pg_dump as follows:
nohup pg_dump {various settings} > something.log 2>&1 &
rik.
On Mon, May 1, 2023 at 6:12 AM Frank Lanitz <frank@frank.uvena.de> wrote:
> Hello,
>
> On 01.05.23 02:18, richard coleman wrote:
> > Hi all,
> >
> > I've been working with pg_dump and one thing that's always struck me as
> > strange is that there never seems to be an indication in the log that
> > the pg_dump process completed successfully.
>
> I don't think it's needed.
>
> How are you running pg_dump? There is a return code 0 if successful or
> != 0 in case of an error.
>
> Cheer,
> Frank
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:07 Ron <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Ron @ 2023-05-01 14:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
That's like complaining that a Toyota Camry isn't fit for hauling 10 cubic
yards of sand.
On 5/1/23 08:55, richard coleman wrote:
> Ron,
>
> Are you writing that pg_dump is unfit for purpose and that I should be
> using a commercial backup solution instead?
>
> rik.
>
> On Mon, May 1, 2023 at 9:43 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>
>
> On 5/1/23 07:56, richard coleman wrote:
>> Ron,
>> So what you are saying is that I have to write a shell script around
>> pg_dump to catch the exit code of pg_dump since the developers of
>> pg_dump didn't have pg_dump simply write an exit message?
>>
>> Since multiple pg_dump commands are often run at the same command
>> prompt, and they can take hours, if not days to run,
>
> Running a days-long pg_dump at any time except special occasions isn't
> very wise. Especially from a command prompt.
>
> Binary backup/restore programs are /much/ faster (if for no other
> reason than they let you do incremental and differential backups).
>
>> and there are a myriad of other commands the will be run in the
>> interim, any exit code generated by pg_dump with be lost in the
>> flotsam and jetsam of the multitude of exit codes created by every
>> other command run between the calling of pg_dump and it's eventual
>> termination. It could have completed successfully, crashed, been
>> killed, etc.
>>
>> Or am I mistaken?
>>
>> thanks,
>> rik.
>>
>> On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> On 4/30/23 19:18, richard coleman wrote:
>>> Hi all,
>>>
>>> I've been working with pg_dump and one thing that's always
>>> struck me as strange is that there never seems to be an
>>> indication in the log that the pg_dump process completed
>>> successfully.
>>>
>>> For example, I've been running a bunch of backups with pg_dump
>>> in directory mode with multiple jobs. When I come back several
>>> hours later there are no pg_dump processes running. The log is
>>> filled with:
>>>
>>> dumping contents of foo
>>> finished item 123456 of TABLE DATA bar
>>> etc.
>>>
>>>
>>> Other than trying to restore the multi terabyte database
>>> somewhere else is there any way to know that it actually
>>> finished successfully?
>>>
>>> Why doesn't pg_dump add a line in the output like:
>>>
>>> pg_backup finished
>>>
>>> when it's completed successfully?
>>>
>>> This seems like a terrible oversight.
>>>
>>> Am I missing something obvious?
>>
>> "They" expect you to do the Unix Thing and check $?. Thus, that's
>> what I do; any non-zero value generates an email with a scary
>> subject line, the specifics of which are based on the exact code
>> value. I'm not near my work computer; otherwise I'd share them
>> with you.
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:13 Rui DeSousa <rui@crazybean.net>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Rui DeSousa @ 2023-05-01 14:13 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On May 1, 2023, at 9:55 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> Are you writing that pg_dump is unfit for purpose and that I should be using a commercial backup solution instead?
>
pg_dump is a logical backup. If you need a fast logical backup; then it’s the right tool. To get a fast logical backup use the directory format, multiple threads (jobs), and turn off compression. You shouldn’t have to wait days to get a logical backup; if so, maybe your system is too small and/or disks are too slow.
i.e. pg_dump --format=d --file=prod --compress=0 —jobs=16 prod
However, for database backups a binary solution is best as it is faster and allows for point in time recovery if archiving is enabled.
Relying on logical backups as a backup solution seems like a bad idea.
-rui=
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:31 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: richard coleman @ 2023-05-01 14:31 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Ron,
That seems to be a very odd stance. PostgreSQL and it's various utilities
are and have been used in very large commercial operations for decades.
pg_dump successfully backs up databases from 1MB to 10TB and beyond. The
only issue I am having is for some inexplicable reason the devs haven't
included a status message upon completion.
If you don't think that pg_dump is fit for purpose, then what do you
propose using in its stead?
thanks,
rik.
On Mon, May 1, 2023 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:
> That's like complaining that a Toyota Camry isn't fit for hauling 10 cubic
> yards of sand.
>
>
> On 5/1/23 08:55, richard coleman wrote:
>
> Ron,
>
> Are you writing that pg_dump is unfit for purpose and that I should be
> using a commercial backup solution instead?
>
> rik.
>
> On Mon, May 1, 2023 at 9:43 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>>
>>
>> On 5/1/23 07:56, richard coleman wrote:
>>
>> Ron,
>> So what you are saying is that I have to write a shell script around
>> pg_dump to catch the exit code of pg_dump since the developers of pg_dump
>> didn't have pg_dump simply write an exit message?
>>
>> Since multiple pg_dump commands are often run at the same command prompt,
>> and they can take hours, if not days to run,
>>
>>
>> Running a days-long pg_dump at any time except special occasions isn't
>> very wise. Especially from a command prompt.
>>
>> Binary backup/restore programs are *much* faster (if for no other reason
>> than they let you do incremental and differential backups).
>>
>> and there are a myriad of other commands the will be run in the interim,
>> any exit code generated by pg_dump with be lost in the flotsam and jetsam
>> of the multitude of exit codes created by every other command run between
>> the calling of pg_dump and it's eventual termination. It could have
>> completed successfully, crashed, been killed, etc.
>>
>> Or am I mistaken?
>>
>> thanks,
>> rik.
>>
>> On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com> wrote:
>>
>>> On 4/30/23 19:18, richard coleman wrote:
>>>
>>> Hi all,
>>>
>>> I've been working with pg_dump and one thing that's always struck me as
>>> strange is that there never seems to be an indication in the log that the
>>> pg_dump process completed successfully.
>>>
>>> For example, I've been running a bunch of backups with pg_dump in
>>> directory mode with multiple jobs. When I come back several hours later
>>> there are no pg_dump processes running. The log is filled with:
>>>
>>> dumping contents of foo
>>> finished item 123456 of TABLE DATA bar
>>> etc.
>>>
>>>
>>> Other than trying to restore the multi terabyte database somewhere else
>>> is there any way to know that it actually finished successfully?
>>>
>>> Why doesn't pg_dump add a line in the output like:
>>>
>>> pg_backup finished
>>>
>>> when it's completed successfully?
>>>
>>> This seems like a terrible oversight.
>>>
>>> Am I missing something obvious?
>>>
>>>
>>> "They" expect you to do the Unix Thing and check $?. Thus, that's what
>>> I do; any non-zero value generates an email with a scary subject line, the
>>> specifics of which are based on the exact code value. I'm not near my work
>>> computer; otherwise I'd share them with you.
>>>
>>> --
>>> Born in Arizona, moved to Babylonia.
>>>
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:31 Rui DeSousa <rui@crazybean.net>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Rui DeSousa @ 2023-05-01 14:31 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Frank Lanitz <frank@frank.uvena.de>; pgsql-admin@lists.postgresql.org
> On May 1, 2023, at 10:00 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> As I've mentioned in another response I typically run pg_dump as follows:
>
> nohup pg_dump {various settings} > something.log 2>&1 &
Why redirect standard error to standard out when using pg_dump? I assume that using standard out is not the backup stream.
If you want a success message in the log; write the success message to the log file if pg_dump exits successfully.
i.e.: nohup pg_dump --verbose prod > prod.sql 2> prod.log && echo "Backup Completed Successfully!" >> prod.log &
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:34 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 2 replies; 37+ messages in thread
From: richard coleman @ 2023-05-01 14:34 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Rui,
I'll be the first to admit that in this case some of the servers that were
procured for PostgreSQL are way too underspeced for the databases they are
now hosting. I am guessing that it's a result of the databases outgrowing
their servers.
As I've asked Ron, if pg_dump isn't fit for purpose, then what do you
believe is?
Thanks,
rik.
On Mon, May 1, 2023 at 10:13 AM Rui DeSousa <rui@crazybean.net> wrote:
>
> On May 1, 2023, at 9:55 AM, richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
>
> Are you writing that pg_dump is unfit for purpose and that I should be
> using a commercial backup solution instead?
>
>
> pg_dump is a logical backup. If you need a fast logical backup; then it’s
> the right tool. To get a fast logical backup use the directory format,
> multiple threads (jobs), and turn off compression. You shouldn’t have to
> wait days to get a logical backup; if so, maybe your system is too small
> and/or disks are too slow.
>
> i.e. pg_dump --format=d --file=prod --compress=0 —jobs=16 prod
>
> However, for database backups a binary solution is best as it is faster
> and allows for point in time recovery if archiving is enabled.
>
> Relying on logical backups as a backup solution seems like a bad idea.
>
> -rui
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:40 Ron <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Ron @ 2023-05-01 14:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I backup some databases every night using pg_dump. From a bash script
initiated by cron, the way God intended them to be run. And I check the
return code, like every good production shell script should.
I back up other databases using pgBackRest (also from a bash script
initiated by cron, the way God intended them to be run). And I check the
return code, like every good production shell script should (even though it
prints a message saying "Finished").
Because who in their right mind manually checks log files every morning,
when a shell script will gladly email you if something went wrong? *Make the
computer work for you!!!*
On 5/1/23 09:31, richard coleman wrote:
> Ron,
>
> That seems to be a very odd stance. PostgreSQL and it's various utilities
> are and have been used in very large commercial operations for decades.
> pg_dump successfully backs up databases from 1MB to 10TB and beyond. The
> only issue I am having is for some inexplicable reason the devs haven't
> included a status message upon completion.
>
> If you don't think that pg_dump is fit for purpose, then what do you
> propose using in its stead?
>
> thanks,
> rik.
>
> On Mon, May 1, 2023 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
> That's like complaining that a Toyota Camry isn't fit for hauling 10
> cubic yards of sand.
>
>
> On 5/1/23 08:55, richard coleman wrote:
>> Ron,
>>
>> Are you writing that pg_dump is unfit for purpose and that I should
>> be using a commercial backup solution instead?
>>
>> rik.
>>
>> On Mon, May 1, 2023 at 9:43 AM Ron <ronljohnsonjr@gmail.com> wrote:
>>
>>
>>
>> On 5/1/23 07:56, richard coleman wrote:
>>> Ron,
>>> So what you are saying is that I have to write a shell script
>>> around pg_dump to catch the exit code of pg_dump since the
>>> developers of pg_dump didn't have pg_dump simply write an exit
>>> message?
>>>
>>> Since multiple pg_dump commands are often run at the same
>>> command prompt, and they can take hours, if not days to run,
>>
>> Running a days-long pg_dump at any time except special occasions
>> isn't very wise. Especially from a command prompt.
>>
>> Binary backup/restore programs are /much/ faster (if for no other
>> reason than they let you do incremental and differential backups).
>>
>>> and there are a myriad of other commands the will be run in the
>>> interim, any exit code generated by pg_dump with be lost in the
>>> flotsam and jetsam of the multitude of exit codes created by
>>> every other command run between the calling of pg_dump and it's
>>> eventual termination. It could have completed successfully,
>>> crashed, been killed, etc.
>>>
>>> Or am I mistaken?
>>>
>>> thanks,
>>> rik.
>>>
>>> On Sun, Apr 30, 2023 at 11:28 PM Ron <ronljohnsonjr@gmail.com>
>>> wrote:
>>>
>>> On 4/30/23 19:18, richard coleman wrote:
>>>> Hi all,
>>>>
>>>> I've been working with pg_dump and one thing that's always
>>>> struck me as strange is that there never seems to be an
>>>> indication in the log that the pg_dump process completed
>>>> successfully.
>>>>
>>>> For example, I've been running a bunch of backups with
>>>> pg_dump in directory mode with multiple jobs. When I come
>>>> back several hours later there are no pg_dump processes
>>>> running. The log is filled with:
>>>>
>>>> dumping contents of foo
>>>> finished item 123456 of TABLE DATA bar
>>>> etc.
>>>>
>>>>
>>>> Other than trying to restore the multi terabyte database
>>>> somewhere else is there any way to know that it actually
>>>> finished successfully?
>>>>
>>>> Why doesn't pg_dump add a line in the output like:
>>>>
>>>> pg_backup finished
>>>>
>>>> when it's completed successfully?
>>>>
>>>> This seems like a terrible oversight.
>>>>
>>>> Am I missing something obvious?
>>>
>>> "They" expect you to do the Unix Thing and check $?. Thus,
>>> that's what I do; any non-zero value generates an email with
>>> a scary subject line, the specifics of which are based on
>>> the exact code value. I'm not near my work computer;
>>> otherwise I'd share them with you.
>>>
>>> --
>>> Born in Arizona, moved to Babylonia.
>>>
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 14:48 Scott Ribe <scott_ribe@elevated-dev.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Scott Ribe @ 2023-05-01 14:48 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On May 1, 2023, at 8:34 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> As I've asked Ron, if pg_dump isn't fit for purpose, then what do you believe is?
He didn't say pg_dump wasn't fit for purpose. He said use a binary format and multiple processes--which are pg_dump options.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:10 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 37+ messages in thread
From: richard coleman @ 2023-05-01 15:10 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Rui DeSousa <rui@crazybean.net>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Scott,
I do use multiple processes, when I use the directory output option which
is the only one that supports it.
I will admit that I haven't tried writing custom binary outputs yet,
typically using either the builtin custom format or directory format.
rik.
On Mon, May 1, 2023 at 10:48 AM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > On May 1, 2023, at 8:34 AM, richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
> >
> > As I've asked Ron, if pg_dump isn't fit for purpose, then what do you
> believe is?
>
> He didn't say pg_dump wasn't fit for purpose. He said use a binary format
> and multiple processes--which are pg_dump options.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:25 David G. Johnston <david.g.johnston@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
4 siblings, 1 reply; 37+ messages in thread
From: David G. Johnston @ 2023-05-01 15:25 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Sun, Apr 30, 2023 at 5:18 PM richard coleman <rcoleman.ascentgl@gmail.com>
wrote:
> For example, I've been running a bunch of backups with pg_dump in
> directory mode with multiple jobs. When I come back several hours later
> there are no pg_dump processes running. The log is filled with:
>
> dumping contents of foo
> finished item 123456 of TABLE DATA bar
>
>
I agree that any log file that contains this level of detail regarding the
operational detail can (IMO, should) also have an indication regarding the
final outcome of the process.
The arguments about "the Unix way" seem to have forgotten the point about
being silent except in cases of exception, and this level of detail already
violates that premise.
David J.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:29 Rui DeSousa <rui@crazybean.net>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Rui DeSousa @ 2023-05-01 15:29 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On May 1, 2023, at 10:34 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> As I've asked Ron, if pg_dump isn't fit for purpose, then what do you believe is?
If you need a logical backup then pg_dump is the right tool; as noted before but with better options to improve performance.
For backups; I would recommend physical backups which there are many solutions for. I have personally used snapshots and believe that is the best solution. It is the fastest and best solution to meet recovery time objective (RTO). For example, I’m able to make a backup of a multi-terabyte database in under a second and multiple backups are cheap. This allows me to do a backup of the system every 6 hours. The most expensive part of a database restore is going to be applying the WAL files when doing a point in time recovery (PITR). This means that at most, I would only have to apply around 6 hours of WAL files; which can still take a long time given a high volume system.
In my opinion snapshots are the best solution but it will depend on your infrastructure and requires knowledge of the infrastructure and setting it up correctly.
If I could not use snapshots; I would look into either pgBackRest or pgBarman. =
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:34 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 37+ messages in thread
From: richard coleman @ 2023-05-01 15:34 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Rui,
Thanks for those suggestions, and your earlier ones.
It would be helpful though if the dev included a completion status message
in pg_dump, even if it was only included when you specified the -v flag.
rik.
On Mon, May 1, 2023 at 11:29 AM Rui DeSousa <rui@crazybean.net> wrote:
>
>
> On May 1, 2023, at 10:34 AM, richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
>
> As I've asked Ron, if pg_dump isn't fit for purpose, then what do you
> believe is?
>
>
> If you need a logical backup then pg_dump is the right tool; as noted
> before but with better options to improve performance.
>
> For backups; I would recommend physical backups which there are many
> solutions for. I have personally used snapshots and believe that is the
> best solution. It is the fastest and best solution to meet recovery time
> objective (RTO). For example, I’m able to make a backup of a
> multi-terabyte database in under a second and multiple backups are cheap.
> This allows me to do a backup of the system every 6 hours. The most
> expensive part of a database restore is going to be applying the WAL files
> when doing a point in time recovery (PITR). This means that at most, I
> would only have to apply around 6 hours of WAL files; which can still take
> a long time given a high volume system.
>
> In my opinion snapshots are the best solution but it will depend on your
> infrastructure and requires knowledge of the infrastructure and setting it
> up correctly.
>
> If I could not use snapshots; I would look into either pgBackRest or
> pgBarman.
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:50 Scott Ribe <scott_ribe@elevated-dev.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Ribe @ 2023-05-01 15:50 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>; pgsql-admin@lists.postgresql.org
> On May 1, 2023, at 9:25 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> The arguments about "the Unix way" seem to have forgotten the point about being silent except in cases of exception, and this level of detail already violates that premise.
It would be a reasonable convenience. I think some of the pushback is from the framing that not having it is a defect.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 15:51 M Sarwar <sarwarmd02@outlook.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: M Sarwar @ 2023-05-01 15:51 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
If somebody is seriously looking into these suggestions, I like to bring another point for the development of pgdump log.
In pgdump while displaying backedup table names, the same row in the log can include the number of rows backedup for each table.
Thank you,
Sarwar
Working on the project where I do not know the application 🙂
________________________________
From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Monday, May 1, 2023 11:34 AM
To: Rui DeSousa <rui@crazybean.net>
Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_dump why no indicator of completion
Rui,
Thanks for those suggestions, and your earlier ones.
It would be helpful though if the dev included a completion status message in pg_dump, even if it was only included when you specified the -v flag.
rik.
On Mon, May 1, 2023 at 11:29 AM Rui DeSousa <rui@crazybean.net<mailto:rui@crazybean.net>> wrote:
On May 1, 2023, at 10:34 AM, richard coleman <rcoleman.ascentgl@gmail.com<mailto:rcoleman.ascentgl@gmail.com>> wrote:
As I've asked Ron, if pg_dump isn't fit for purpose, then what do you believe is?
If you need a logical backup then pg_dump is the right tool; as noted before but with better options to improve performance.
For backups; I would recommend physical backups which there are many solutions for. I have personally used snapshots and believe that is the best solution. It is the fastest and best solution to meet recovery time objective (RTO). For example, I’m able to make a backup of a multi-terabyte database in under a second and multiple backups are cheap. This allows me to do a backup of the system every 6 hours. The most expensive part of a database restore is going to be applying the WAL files when doing a point in time recovery (PITR). This means that at most, I would only have to apply around 6 hours of WAL files; which can still take a long time given a high volume system.
In my opinion snapshots are the best solution but it will depend on your infrastructure and requires knowledge of the infrastructure and setting it up correctly.
If I could not use snapshots; I would look into either pgBackRest or pgBarman.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-01 16:15 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 37+ messages in thread
From: Ron @ 2023-05-01 16:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/1/23 10:50, Scott Ribe wrote:
>> On May 1, 2023, at 9:25 AM, David G. Johnston<david.g.johnston@gmail.com> wrote:
>>
>> The arguments about "the Unix way" seem to have forgotten the point about being silent except in cases of exception, and this level of detail already violates that premise.
> It would be a reasonable convenience. I think some of the pushback is from the framing that not having it is a defect.
Exactly. It's a convenience, the lack of which is easily worked around.
What would /really/ be useful are timestamps (both start and completion of
dump) and an easy association of ".dat numbers" with table names.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 01:29 Tim <timfosho@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Tim @ 2023-05-02 01:29 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
This has been a very entertaining thread to read through. Thankyou.
> I will admit that I haven't tried writing custom binary outputs yet,
typically using either the builtin custom format or directory format.
If you really are working with Muli-TB databases (kinda hard to believe in
the first place, doing a backup that takes many hours with pg_dump will
cause your WAL folder to fill up), then its time to learn some more mature
tools.
pg-dump made sense maybe a decade ago. As recommended above, nowadays
people use *pgbackrest* and *pgbarman*.
> Since multiple pg_dump commands are often run at the same command prompt,
and they can take hours, if not days to run,
> and there are a myriad of other commands the will be run in the interim,
any exit code generated by pg_dump with be lost
> in the flotsam and jetsam of the multitude of exit codes created by
every other command run between the calling of pg_dump
> and it's eventual termination. It could have completed successfully,
crashed, been killed, etc.
Your workflow sounds completely mystifying.
> then I am going to be forced to create a wrapper to capture and emit the
status around the pg_dump executable.
> If I need to do that, so will every other user who needs to know the exit
status and runs pg_dump in a typical linux
> workflow.
I'm sorry, but If putting your multi terabyte backup process into a 3 line
script that checks the output and maybe emailing
you or just appending to the logfile seems like a crazy idea, you're going
to be completely floored once you learn a few extra basic scripting
patterns in the world of Linux.
You're basically saying you're entirely constrained to triggering processes
from the terminal by hand (please use a cronjob) and due to that
insurmountable limitation, you also wont learn how an && works.
> Seems like a terrible waste having untold numbers of users having to
constantly reinvent the wheel.
You might be overstating things a bit
On Mon, May 1, 2023 at 12:15 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 5/1/23 10:50, Scott Ribe wrote:
>
> On May 1, 2023, at 9:25 AM, David G. Johnston <david.g.johnston@gmail.com> <david.g.johnston@gmail.com> wrote:
>
> The arguments about "the Unix way" seem to have forgotten the point about being silent except in cases of exception, and this level of detail already violates that premise.
>
> It would be a reasonable convenience. I think some of the pushback is from the framing that not having it is a defect.
>
>
> Exactly. It's a convenience, the lack of which is easily worked around.
>
> What would *really* be useful are timestamps (both start and completion
> of dump) and an easy association of ".dat numbers" with table names.
>
> --
> Born in Arizona, moved to Babylonia.
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 02:42 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Tim <timfosho@gmail.com>
0 siblings, 2 replies; 37+ messages in thread
From: richard coleman @ 2023-05-02 02:42 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Tim,
I'm glad that I was able to bring you a modicum of joy.
Yes, I have databases that range from tens of megabytes to tens of
terabytes. Most are on servers that are far too under powered for the
databases they are running. As these databases are both in constant use,
with users that have, how do I put it kindly, interesting workflows, it
really isn't possible to cron a backup job. I do use cron for a number of
other things though. Backups have to be on a manual basis for the time
being when I can schedule enough time away from the users to complete it.
Manged a 2.2T database this afternoon, it only took about 10 hours with
pg_dump in the database format running 7 jobs. Amazingly, my WAL folder
has yet to fill up, I guess I must be doing it wrong. ¯\_(ツ)_/¯
Since pg_dumpall is limited to SQL I typically use a custom bash shell
script where I use pg_dumpall to backup the globals to a .tar.gz file, and
then loop through the cluster calling pg_dump on each database in
parallel. Depending on the situation, I either use one that writes each to
a separate compressed custom format (.backup), or a separate directory.
Each backup gets it's own log file that records the status with a double
verbose switch. After reading the responses in this thread I've adjusted
it to capture the exit status (using the same && that I use in my
collection of commands to apt-get upgrade, update, autoremove, and test
server updates) and append a "pg_dump complete." message to the end of the
log file if pg_dump completes successfully.
Looking over standard pg_restore log files is what prompted me to ask the
question in the first place. Whenever I run pg_restore it completes with
messages along the lines of:
pg_restore: finished main parallel loop
pg_restore: entering restore_toc_entries_postfork
pg_restore: warning: errors ignored on restore: 6
I found it rather odd that pg_dump, being the mirror of pg_restore, didn't
do something similar.
We would all like to be able to do things in a "better way" with the best
equipment, workflows, etc., but at the end of the day we are all limited by
the reality we find ourselves in.
Take care and keep smiling,
rik.
On Mon, May 1, 2023 at 9:30 PM Tim <timfosho@gmail.com> wrote:
> This has been a very entertaining thread to read through. Thankyou.
>
> > I will admit that I haven't tried writing custom binary outputs yet,
> typically using either the builtin custom format or directory format.
>
> If you really are working with Muli-TB databases (kinda hard to believe in
> the first place, doing a backup that takes many hours with pg_dump will
> cause your WAL folder to fill up), then its time to learn some more mature
> tools.
>
> pg-dump made sense maybe a decade ago. As recommended above, nowadays
> people use *pgbackrest* and *pgbarman*.
>
> > Since multiple pg_dump commands are often run at the same command
> prompt, and they can take hours, if not days to run,
> > and there are a myriad of other commands the will be run in the
> interim, any exit code generated by pg_dump with be lost
> > in the flotsam and jetsam of the multitude of exit codes created by
> every other command run between the calling of pg_dump
> > and it's eventual termination. It could have completed successfully,
> crashed, been killed, etc.
>
> Your workflow sounds completely mystifying.
>
> > then I am going to be forced to create a wrapper to capture and emit the
> status around the pg_dump executable.
> > If I need to do that, so will every other user who needs to know the
> exit status and runs pg_dump in a typical linux
> > workflow.
>
> I'm sorry, but If putting your multi terabyte backup process into a 3 line
> script that checks the output and maybe emailing
> you or just appending to the logfile seems like a crazy idea, you're going
> to be completely floored once you learn a few extra basic scripting
> patterns in the world of Linux.
>
> You're basically saying you're entirely constrained to triggering
> processes from the terminal by hand (please use a cronjob) and due to that
> insurmountable limitation, you also wont learn how an && works.
>
> > Seems like a terrible waste having untold numbers of users having to
> constantly reinvent the wheel.
>
> You might be overstating things a bit
>
>
>
> On Mon, May 1, 2023 at 12:15 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 5/1/23 10:50, Scott Ribe wrote:
>>
>> On May 1, 2023, at 9:25 AM, David G. Johnston <david.g.johnston@gmail.com> <david.g.johnston@gmail.com> wrote:
>>
>> The arguments about "the Unix way" seem to have forgotten the point about being silent except in cases of exception, and this level of detail already violates that premise.
>>
>> It would be a reasonable convenience. I think some of the pushback is from the framing that not having it is a defect.
>>
>>
>> Exactly. It's a convenience, the lack of which is easily worked around.
>>
>> What would *really* be useful are timestamps (both start and completion
>> of dump) and an easy association of ".dat numbers" with table names.
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 03:05 Erik Wienhold <ewie@ewie.name>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 37+ messages in thread
From: Erik Wienhold @ 2023-05-02 03:05 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On 02/05/2023 04:42 CEST richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> I'm glad that I was able to bring you a modicum of joy.
I find this entire thread entertaining.
> Backups have to be on a manual basis for the time being when I can schedule
> enough time away from the users to complete it. Manged a 2.2T database this
> afternoon, it only took about 10 hours with pg_dump in the database format
> running 7 jobs.
Differential/incremental backups with Barman or pgBackRest can cut down on this
time.
> Amazingly, my WAL folder has yet to fill up, I guess I must be doing it wrong.
Of course there's not much WAL activity if you schedule your backup for a time
window with low client activity.
--
Erik
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 03:14 Ian Lawrence Barwick <barwick@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Ian Lawrence Barwick @ 2023-05-02 03:14 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
2023年5月1日(月) 22:26 richard coleman <rcoleman.ascentgl@gmail.com>:
>
> Scott,
>
> Generally I run pg_dump nohup in the background with all outputs captured to a file. Finding the exit message is simply a matter of navigating to the bottom of the output file and checking.
> Excepting for pg_dump, I don't generally have to worry about exit values as most command line programs are either short-lived and obvious, like ls, or emit their own text exit status.
If you do need to run pg_dump manually, have you considered running each
invocation in a terminal multiplexer such as "screen" or "tmux"? The
thought of long-running processes hanging around in NOHUP purgatory
fills me with a sense of impending doom.
Regards
Ian Barwick
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 03:21 Tim <timfosho@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 37+ messages in thread
From: Tim @ 2023-05-02 03:21 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>; pgsql-admin@lists.postgresql.org
> After reading the responses in this thread I've adjusted it to capture
the exit status
Just so I'm not a complete troll, heres your example which also appends a
success/fail message :)
nohup $(pg_dump ${connection_details} > something.log 2>&1 && echo "pgdump
succeded" >> something.log || echo "pgdump failed" something.log) &
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 07:41 Ron <ronljohnsonjr@gmail.com>
parent: Tim <timfosho@gmail.com>
1 sibling, 0 replies; 37+ messages in thread
From: Ron @ 2023-05-02 07:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/1/23 22:21, Tim wrote:
> > After reading the responses in this thread I've adjusted it to capture
> the exit status
>
> Just so I'm not a complete troll, heres your example which also appends a
> success/fail message :)
>
> nohup $(pg_dump ${connection_details} > something.log 2>&1 && echo "pgdump
> succeded" >> something.log || echo "pgdump failed" something.log) &
Out of curiosity, why this:
> something.log 2>&1
instead of that?
&> something.log
Yes, "&>" is a bashism, but so what?
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 07:49 Ron <ronljohnsonjr@gmail.com>
parent: richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 0 replies; 37+ messages in thread
From: Ron @ 2023-05-02 07:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/1/23 21:42, richard coleman wrote:
> As these databases are both in constant use, with users that have, how do
> I put it kindly, interesting workflows, it really isn't possible to cron a
> backup job.
You can't edit crontab to run a script a few minutes from "now"? (My
"postgres" crontabs are replete with commented out /ad hoc/ scripts which
get uncommented and rescheduled as needed.)
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: pg_dump why no indicator of completion
@ 2023-05-02 12:28 richard coleman <rcoleman.ascentgl@gmail.com>
parent: Tim <timfosho@gmail.com>
1 sibling, 0 replies; 37+ messages in thread
From: richard coleman @ 2023-05-02 12:28 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org
Tim,
Thanks, but as I had previously mentioned, I had already taken care of that
little detail.
rik.
On Mon, May 1, 2023 at 11:22 PM Tim <timfosho@gmail.com> wrote:
> > After reading the responses in this thread I've adjusted it to capture
> the exit status
>
> Just so I'm not a complete troll, heres your example which also appends a
> success/fail message :)
>
> nohup $(pg_dump ${connection_details} > something.log 2>&1 && echo "pgdump
> succeded" >> something.log || echo "pgdump failed" something.log) &
>
>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
end of thread, other threads:[~2023-05-02 12:28 UTC | newest]
Thread overview: 37+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-01 00:18 pg_dump why no indicator of completion richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 01:08 ` Gavan Schneider <list.pg.gavan@pendari.org>
2023-05-01 03:28 ` Ron <ronljohnsonjr@gmail.com>
2023-05-01 10:14 ` Frank Lanitz <frank@frank.uvena.de>
2023-05-01 12:56 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 12:59 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-01 13:01 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 13:07 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-01 13:26 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-02 03:14 ` Ian Lawrence Barwick <barwick@gmail.com>
2023-05-01 13:37 ` Ron <ronljohnsonjr@gmail.com>
2023-05-01 13:43 ` Ron <ronljohnsonjr@gmail.com>
2023-05-01 13:55 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 14:07 ` Ron <ronljohnsonjr@gmail.com>
2023-05-01 14:31 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 14:40 ` Ron <ronljohnsonjr@gmail.com>
2023-05-01 14:13 ` Rui DeSousa <rui@crazybean.net>
2023-05-01 14:34 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 14:48 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-01 15:10 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 15:29 ` Rui DeSousa <rui@crazybean.net>
2023-05-01 15:34 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 15:51 ` M Sarwar <sarwarmd02@outlook.com>
2023-05-01 07:43 ` Holger Jakobs <holger@jakobs.com>
2023-05-01 10:12 ` Frank Lanitz <frank@frank.uvena.de>
2023-05-01 14:00 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-01 14:31 ` Rui DeSousa <rui@crazybean.net>
2023-05-01 15:25 ` David G. Johnston <david.g.johnston@gmail.com>
2023-05-01 15:50 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-05-01 16:15 ` Ron <ronljohnsonjr@gmail.com>
2023-05-02 01:29 ` Tim <timfosho@gmail.com>
2023-05-02 02:42 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-02 03:05 ` Erik Wienhold <ewie@ewie.name>
2023-05-02 03:21 ` Tim <timfosho@gmail.com>
2023-05-02 07:41 ` Ron <ronljohnsonjr@gmail.com>
2023-05-02 12:28 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-05-02 07:49 ` Ron <ronljohnsonjr@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