Re: pg_dump why no indicator of completion

[richard coleman <rcoleman.ascentgl@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> wrote:

On 5/1/23 10:50, Scott Ribe wrote:

On May 1, 2023, at 9:25 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> 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.