Thread

1. Adding pg_dump flag for parallel export to pipes

   Nitin Motiani <nitinmotiani@google.com> — 2025-04-07T17:16:58Z

   Hi Hackers,
   
   We are proposing the ability to specify a pipe command to pg_dump by a
   flag. And attaching the patch set.
   
   Why : Currently it is quite simple to pipe the output of pg_dump for
   text format to a pipe at command line and do any manipulations
   necessary. Following is an example :
   
          pg_dump <flags> <dbname> | lz4 | pv -L 10k | ssh remote.host
   "cat - > remote.dump.lz4"
   
   Here we first compress the stream using lz4 and then send it over ssh
   to a remote host to be saved as a file while rate-limiting the network
   usage to 10KB/s.
   
   Something like this is not possible for format=directory (-Fd) since
   all you can provide is the directory name to store the individual
   files. Note it is not possible to do this irrespective of the usage of
   the parallel dump option ('--jobs' flag).
   
   While the directory format supports compression using a flag, the rest
   of the operations in the above example are not possible. And a pipe
   command provides more flexibility in what compression algorithm one
   wants to use.
   
   This patch set provides pg_dump the ability to pipe the data in the
   directory mode by using a new flag '--pipe-command' (in both parallel
   and non-parallel mode).
   
   We also add a similar option to pg_restore.
   
   The following can be the major use cases of these changes :
     1. Stream pg_dump output to a cloud storage
     2. SSH the data to a remote host (with or without throttling)
     3. Custom compression options
   
   
   Usage Examples : Here is an example of how the pipe-command will look like.
   
        pg_dump -Fd mydb --pipe-command="cat > dumpdir/%f" (dumpdir
   should exist beforehand.)
   
   This is equivalent to
   
        pg_dump -Fd mydb --file=dumpdir
   
   (Please note that the flags '--file' or '--pipe-command' can't be used
   together.)
   
   For the more complex scenario as mentioned above, the command will be
   (with the parallelism of 5) :
   
         pg_dump -Fd mydb -j 5 --pipe-command="lz4 | pv -L 10k | ssh
   remote.host "cat > dumpdir/%f""
   
   Please note the use of %f in the above examples. As a user would
   almost always want to write the post-processing output to a file (or
   perhaps a cloud location), we provide a format specifier %f in the
   command. The implementation of pipe-command replaces these format
   specifiers with the corresponding file names. These file names are the
   same as they would be in the current usage of directory format with
   '--file' flag (<dump_id>.dat, toc.dat, blob_NNN.toc,
   blob_<blob_id>.dat).
   
   The usage of this flag with pg_restore will also be similar. Here is
   an example of restoring from a gzip compressed dump directory.
   
           pg_restore -C -Fd -d postgres --pipe-commnad="cat
   dumpdir/%f.gz | gunzip"
   
   The new flag in pg_restore also works with '-l' and '-L' options
   
           pg_restore -C -Fd -d postgres --pipe-commnad="cat dumpdir/%f" -L db.list
   
   
   Implementation Details : Here are the major changes :
      1. We reuse the same variables which store the file name to store
   the pipe command. And add a new bool fSpecIsPipe in _archiveHandle
   (similar bools in pg_dump.c and pg_restore.c) to specify if it's a
   pipe command.
       2. In the cases when the above bool is set to true, we use popen
   and pclose instead of fopen and fclose.
        3. To enable the format specifier %f in the pipe-command, we make
   changes to the file name creation logic in a few places. Currently the
   file name (corresponding to a table or large object) is appended to
   the directory name provided by '--file' command. In case of
   '--pipe-command', we use 'replace_percent_placeholders' to replace %f
   with the corresponding file name. This change is made for both table
   files and LO TOC files.
   
   With these core changes, the rest of the code continues working as-is.
   
   We are attaching 4 patches for this change :
   
     001-pg_dump_pipe has the pg_dump pipe support code.
     002-pg_restore_pipe has the pg_restore pipe support.
     003-pg_dump_basic_tests has a few basic validation tests for
   correctmflag combinations. We need to write more automated tests in
   002_pg_dump.pl but have been running into some issues with environment
   setup due to which certain pipe commands result in the shell process
   becoming defunct. These same commands are working fine in manual
   testing. We are still looking into this.
     004-pg_dump_documentation has the proposed documentation changes.
   
   We are working on the above test issues and cleanup of the patches.
   
   Open Questions : There are a couple of open questions in the implementation :
   
        1. Currently the LO TOC file (blob_NNN.toc) is opened in the
   append mode. This is not possible with popen for the pipe command.
   >From reading the code, it seems to us that this file doesn't need to
   be opened in the append mode. As '_StartLOs' is called once per
   archive entry in WriteDataChunksForToCEntry followed by the dumper
   function and then '_EndLOs', it should be okay to change this to 'w'
   mode. But this code has been there since the start so we haven't made
   that change yet. In the patch, we have changed it to 'w' pipe-command
   only and added the ideas for potential solutions in the comments.
        2. We are also not sure yet on how to handle the environment
   issues when trying to add new tests to 002_pg_dump.pl.
   
   Please let us know what you think.
   
   Thanks & Regards,
   Nitin Motiani
   Google
   

2. Re: Adding pg_dump flag for parallel export to pipes

   Hannu Krosing <hannuk@google.com> — 2025-04-07T19:48:20Z

   Just to bring this out separately : Does anybody have any idea why pipe
   commands close inside tests ?
   
   Re: 003-pg_dump_basic_tests has a few basic validation tests for
   correctmflag combinations. We need to write more automated tests in
   002_pg_dump.pl but have been running into some issues with environment
   setup due to which certain pipe commands result in the shell process
   becoming defunct. These same commands are working fine in manual
   testing. We are still looking into this.
   
   ----
   Hannu
   
   
   On Mon, Apr 7, 2025 at 7:17 PM Nitin Motiani <nitinmotiani@google.com>
   wrote:
   
   > Hi Hackers,
   >
   > We are proposing the ability to specify a pipe command to pg_dump by a
   > flag. And attaching the patch set.
   >
   > Why : Currently it is quite simple to pipe the output of pg_dump for
   > text format to a pipe at command line and do any manipulations
   > necessary. Following is an example :
   >
   >        pg_dump <flags> <dbname> | lz4 | pv -L 10k | ssh remote.host
   > "cat - > remote.dump.lz4"
   >
   > Here we first compress the stream using lz4 and then send it over ssh
   > to a remote host to be saved as a file while rate-limiting the network
   > usage to 10KB/s.
   >
   > Something like this is not possible for format=directory (-Fd) since
   > all you can provide is the directory name to store the individual
   > files. Note it is not possible to do this irrespective of the usage of
   > the parallel dump option ('--jobs' flag).
   >
   > While the directory format supports compression using a flag, the rest
   > of the operations in the above example are not possible. And a pipe
   > command provides more flexibility in what compression algorithm one
   > wants to use.
   >
   > This patch set provides pg_dump the ability to pipe the data in the
   > directory mode by using a new flag '--pipe-command' (in both parallel
   > and non-parallel mode).
   >
   > We also add a similar option to pg_restore.
   >
   > The following can be the major use cases of these changes :
   >   1. Stream pg_dump output to a cloud storage
   >   2. SSH the data to a remote host (with or without throttling)
   >   3. Custom compression options
   >
   >
   > Usage Examples : Here is an example of how the pipe-command will look like.
   >
   >      pg_dump -Fd mydb --pipe-command="cat > dumpdir/%f" (dumpdir
   > should exist beforehand.)
   >
   > This is equivalent to
   >
   >      pg_dump -Fd mydb --file=dumpdir
   >
   > (Please note that the flags '--file' or '--pipe-command' can't be used
   > together.)
   >
   > For the more complex scenario as mentioned above, the command will be
   > (with the parallelism of 5) :
   >
   >       pg_dump -Fd mydb -j 5 --pipe-command="lz4 | pv -L 10k | ssh
   > remote.host "cat > dumpdir/%f""
   >
   > Please note the use of %f in the above examples. As a user would
   > almost always want to write the post-processing output to a file (or
   > perhaps a cloud location), we provide a format specifier %f in the
   > command. The implementation of pipe-command replaces these format
   > specifiers with the corresponding file names. These file names are the
   > same as they would be in the current usage of directory format with
   > '--file' flag (<dump_id>.dat, toc.dat, blob_NNN.toc,
   > blob_<blob_id>.dat).
   >
   > The usage of this flag with pg_restore will also be similar. Here is
   > an example of restoring from a gzip compressed dump directory.
   >
   >         pg_restore -C -Fd -d postgres --pipe-commnad="cat
   > dumpdir/%f.gz | gunzip"
   >
   > The new flag in pg_restore also works with '-l' and '-L' options
   >
   >         pg_restore -C -Fd -d postgres --pipe-commnad="cat dumpdir/%f" -L
   > db.list
   >
   >
   > Implementation Details : Here are the major changes :
   >    1. We reuse the same variables which store the file name to store
   > the pipe command. And add a new bool fSpecIsPipe in _archiveHandle
   > (similar bools in pg_dump.c and pg_restore.c) to specify if it's a
   > pipe command.
   >     2. In the cases when the above bool is set to true, we use popen
   > and pclose instead of fopen and fclose.
   >      3. To enable the format specifier %f in the pipe-command, we make
   > changes to the file name creation logic in a few places. Currently the
   > file name (corresponding to a table or large object) is appended to
   > the directory name provided by '--file' command. In case of
   > '--pipe-command', we use 'replace_percent_placeholders' to replace %f
   > with the corresponding file name. This change is made for both table
   > files and LO TOC files.
   >
   > With these core changes, the rest of the code continues working as-is.
   >
   > We are attaching 4 patches for this change :
   >
   >   001-pg_dump_pipe has the pg_dump pipe support code.
   >   002-pg_restore_pipe has the pg_restore pipe support.
   >   003-pg_dump_basic_tests has a few basic validation tests for
   > correctmflag combinations. We need to write more automated tests in
   > 002_pg_dump.pl but have been running into some issues with environment
   > setup due to which certain pipe commands result in the shell process
   > becoming defunct. These same commands are working fine in manual
   > testing. We are still looking into this.
   >   004-pg_dump_documentation has the proposed documentation changes.
   >
   > We are working on the above test issues and cleanup of the patches.
   >
   > Open Questions : There are a couple of open questions in the
   > implementation :
   >
   >      1. Currently the LO TOC file (blob_NNN.toc) is opened in the
   > append mode. This is not possible with popen for the pipe command.
   > From reading the code, it seems to us that this file doesn't need to
   > be opened in the append mode. As '_StartLOs' is called once per
   > archive entry in WriteDataChunksForToCEntry followed by the dumper
   > function and then '_EndLOs', it should be okay to change this to 'w'
   > mode. But this code has been there since the start so we haven't made
   > that change yet. In the patch, we have changed it to 'w' pipe-command
   > only and added the ideas for potential solutions in the comments.
   >      2. We are also not sure yet on how to handle the environment
   > issues when trying to add new tests to 002_pg_dump.pl.
   >
   > Please let us know what you think.
   >
   > Thanks & Regards,
   > Nitin Motiani
   > Google
   >
   

3. Re: Adding pg_dump flag for parallel export to pipes

   Hannu Krosing <hannuk@google.com> — 2025-04-22T12:10:58Z

   If there are no objections we will add this to the commitfest
   
   On Mon, Apr 7, 2025 at 9:48 PM Hannu Krosing <hannuk@google.com> wrote:
   >
   >
   > Just to bring this out separately : Does anybody have any idea why pipe commands close inside tests ?
   >
   > Re: 003-pg_dump_basic_tests has a few basic validation tests for
   > correctmflag combinations. We need to write more automated tests in
   > 002_pg_dump.pl but have been running into some issues with environment
   > setup due to which certain pipe commands result in the shell process
   > becoming defunct. These same commands are working fine in manual
   > testing. We are still looking into this.
   >
   > ----
   > Hannu
   >
   >
   > On Mon, Apr 7, 2025 at 7:17 PM Nitin Motiani <nitinmotiani@google.com> wrote:
   >>
   >> Hi Hackers,
   >>
   >> We are proposing the ability to specify a pipe command to pg_dump by a
   >> flag. And attaching the patch set.
   >>
   >> Why : Currently it is quite simple to pipe the output of pg_dump for
   >> text format to a pipe at command line and do any manipulations
   >> necessary. Following is an example :
   >>
   >>        pg_dump <flags> <dbname> | lz4 | pv -L 10k | ssh remote.host
   >> "cat - > remote.dump.lz4"
   >>
   >> Here we first compress the stream using lz4 and then send it over ssh
   >> to a remote host to be saved as a file while rate-limiting the network
   >> usage to 10KB/s.
   >>
   >> Something like this is not possible for format=directory (-Fd) since
   >> all you can provide is the directory name to store the individual
   >> files. Note it is not possible to do this irrespective of the usage of
   >> the parallel dump option ('--jobs' flag).
   >>
   >> While the directory format supports compression using a flag, the rest
   >> of the operations in the above example are not possible. And a pipe
   >> command provides more flexibility in what compression algorithm one
   >> wants to use.
   >>
   >> This patch set provides pg_dump the ability to pipe the data in the
   >> directory mode by using a new flag '--pipe-command' (in both parallel
   >> and non-parallel mode).
   >>
   >> We also add a similar option to pg_restore.
   >>
   >> The following can be the major use cases of these changes :
   >>   1. Stream pg_dump output to a cloud storage
   >>   2. SSH the data to a remote host (with or without throttling)
   >>   3. Custom compression options
   >>
   >>
   >> Usage Examples : Here is an example of how the pipe-command will look like.
   >>
   >>      pg_dump -Fd mydb --pipe-command="cat > dumpdir/%f" (dumpdir
   >> should exist beforehand.)
   >>
   >> This is equivalent to
   >>
   >>      pg_dump -Fd mydb --file=dumpdir
   >>
   >> (Please note that the flags '--file' or '--pipe-command' can't be used
   >> together.)
   >>
   >> For the more complex scenario as mentioned above, the command will be
   >> (with the parallelism of 5) :
   >>
   >>       pg_dump -Fd mydb -j 5 --pipe-command="lz4 | pv -L 10k | ssh
   >> remote.host "cat > dumpdir/%f""
   >>
   >> Please note the use of %f in the above examples. As a user would
   >> almost always want to write the post-processing output to a file (or
   >> perhaps a cloud location), we provide a format specifier %f in the
   >> command. The implementation of pipe-command replaces these format
   >> specifiers with the corresponding file names. These file names are the
   >> same as they would be in the current usage of directory format with
   >> '--file' flag (<dump_id>.dat, toc.dat, blob_NNN.toc,
   >> blob_<blob_id>.dat).
   >>
   >> The usage of this flag with pg_restore will also be similar. Here is
   >> an example of restoring from a gzip compressed dump directory.
   >>
   >>         pg_restore -C -Fd -d postgres --pipe-commnad="cat
   >> dumpdir/%f.gz | gunzip"
   >>
   >> The new flag in pg_restore also works with '-l' and '-L' options
   >>
   >>         pg_restore -C -Fd -d postgres --pipe-commnad="cat dumpdir/%f" -L db.list
   >>
   >>
   >> Implementation Details : Here are the major changes :
   >>    1. We reuse the same variables which store the file name to store
   >> the pipe command. And add a new bool fSpecIsPipe in _archiveHandle
   >> (similar bools in pg_dump.c and pg_restore.c) to specify if it's a
   >> pipe command.
   >>     2. In the cases when the above bool is set to true, we use popen
   >> and pclose instead of fopen and fclose.
   >>      3. To enable the format specifier %f in the pipe-command, we make
   >> changes to the file name creation logic in a few places. Currently the
   >> file name (corresponding to a table or large object) is appended to
   >> the directory name provided by '--file' command. In case of
   >> '--pipe-command', we use 'replace_percent_placeholders' to replace %f
   >> with the corresponding file name. This change is made for both table
   >> files and LO TOC files.
   >>
   >> With these core changes, the rest of the code continues working as-is.
   >>
   >> We are attaching 4 patches for this change :
   >>
   >>   001-pg_dump_pipe has the pg_dump pipe support code.
   >>   002-pg_restore_pipe has the pg_restore pipe support.
   >>   003-pg_dump_basic_tests has a few basic validation tests for
   >> correctmflag combinations. We need to write more automated tests in
   >> 002_pg_dump.pl but have been running into some issues with environment
   >> setup due to which certain pipe commands result in the shell process
   >> becoming defunct. These same commands are working fine in manual
   >> testing. We are still looking into this.
   >>   004-pg_dump_documentation has the proposed documentation changes.
   >>
   >> We are working on the above test issues and cleanup of the patches.
   >>
   >> Open Questions : There are a couple of open questions in the implementation :
   >>
   >>      1. Currently the LO TOC file (blob_NNN.toc) is opened in the
   >> append mode. This is not possible with popen for the pipe command.
   >> From reading the code, it seems to us that this file doesn't need to
   >> be opened in the append mode. As '_StartLOs' is called once per
   >> archive entry in WriteDataChunksForToCEntry followed by the dumper
   >> function and then '_EndLOs', it should be okay to change this to 'w'
   >> mode. But this code has been there since the start so we haven't made
   >> that change yet. In the patch, we have changed it to 'w' pipe-command
   >> only and added the ideas for potential solutions in the comments.
   >>      2. We are also not sure yet on how to handle the environment
   >> issues when trying to add new tests to 002_pg_dump.pl.
   >>
   >> Please let us know what you think.
   >>
   >> Thanks & Regards,
   >> Nitin Motiani
   >> Google
   
   
   
   

4. Re: Adding pg_dump flag for parallel export to pipes

   Thomas Munro <thomas.munro@gmail.com> — 2025-04-26T01:07:09Z

   On Tue, Apr 8, 2025 at 7:48 AM Hannu Krosing <hannuk@google.com> wrote:
   > Just to bring this out separately : Does anybody have any idea why pipe commands close inside tests ?
   >
   > Re: 003-pg_dump_basic_tests has a few basic validation tests for
   > correctmflag combinations. We need to write more automated tests in
   > 002_pg_dump.pl but have been running into some issues with environment
   > setup due to which certain pipe commands result in the shell process
   > becoming defunct. These same commands are working fine in manual
   > testing. We are still looking into this.
   
   No comment on the wider project except that it looks generally useful,
   and I can see that it's not possible to use the conventional POSIX
   filename "-" to represent stdout, because you need to write to
   multiple files so you need to come up with *something* along the lines
   you're proposing here.  But I was interested in seeing if I could help
   with that technical problem you mentioned above, and I don't see that
   happening with the current patches.  Do I understand correctly that
   the problem you encountered is in some other tests that you haven't
   attached yet?  Could you post what you have so that others can see the
   problem and perhaps have a chance of helping?  I also recommend using
   git format-patch when you post patches so that you have a place to
   write a commit message including a note about which bits are WIP and
   known not to work correctly yet.
   
   
   
   

5. Re: Adding pg_dump flag for parallel export to pipes

   Nitin Motiani <nitinmotiani@google.com> — 2025-04-28T08:22:53Z

   Thanks for the feedback, Thomas.
   
   > No comment on the wider project except that it looks generally useful,
   > and I can see that it's not possible to use the conventional POSIX
   > filename "-" to represent stdout, because you need to write to
   > multiple files so you need to come up with *something* along the lines
   > you're proposing here.  But I was interested in seeing if I could help
   > with that technical problem you mentioned above, and I don't see that
   > happening with the current patches.  Do I understand correctly that
   > the problem you encountered is in some other tests that you haven't
   > attached yet?  Could you post what you have so that others can see the
   > problem and perhaps have a chance of helping?
   
   Yes, we didn't add the failed tests to the patch. We'll add those and
   send new patches.
   
   > I also recommend using
   > git format-patch when you post patches so that you have a place to
   > write a commit message including a note about which bits are WIP and
   > known not to work correctly yet.
   
   Will follow these recommendations when sending the next set of patches.
   
   Regards,
   Nitin Motiani
   Google
   
   
   
   

6. Re: Adding pg_dump flag for parallel export to pipes

   Nitin Motiani <nitinmotiani@google.com> — 2025-06-05T12:39:26Z

   Hi,
   
   Apologies for the delay on this thread.
   
   On Mon, Apr 28, 2025 at 1:52 PM Nitin Motiani <nitinmotiani@google.com> wrote:
   >
   > Thanks for the feedback, Thomas.
   >
   > > Do I understand correctly that
   > > the problem you encountered is in some other tests that you haven't
   > > attached yet?  Could you post what you have so that others can see the
   > > problem and perhaps have a chance of helping?
   >
   > Yes, we didn't add the failed tests to the patch. We'll add those and
   > send new patches.
   >
   
   I'm attaching the patch files generated using git format-patch.
   
   0001 has the pg_dump pipe support code.
   0002 has the pg_restore pipe support.
   0003 has a few basic validation tests for correct flag combinations.
   0004 has the proposed documentation changes.
   
   The above 4 are the same as before.
   
   The 0005 patch is the new WIP patch file. This includes the tests
   which we have been trying to add but which are failing (although the
   same commands run fine manually).
   
   The tests in this patch are added to src/bin/pg_dump/t/002_pg_dump.pl.
   The original attempt was to have a test case with dump and restore
   commands using the new flag and run it in multiple scenarios. But
   since that was failing, for the ease of debugging I added a few
   standalone tests which just run a pg_dump with the pipe-command flag.
   In these tests, if the pipe-command is a simple command like 'cat' or
   'gzip', the test passes. But if the pipe-command itself uses a pipe
   (either to a file or another command), the test fails.
   
   In the following test
   
    ['pg_dump', '-Fd', '-B', 'postgres', "--pipe-command=\"cat > $tempdir/%f\"",],]
   
   I get the below error.
   
   # 'sh: line 1: cat >
   /usr/local/google/home/nitinmotiani/postgresql/src/bin/pg_dump/tmp_check/tmp_test_XpFO/toc.dat:
   No such file or directory
   
   I can see that the temp directory tmp_test_XpFO exists. Even when I
   changed the test to use an absolute path to an existing directory, I
   got the same error. When I do manual testing with the same
   pipe-command, it works fine. That is why we think there is some issue
   with our environment setup for the tap test where it is not able to
   parse the command.
   
   I also ran the following loop (started just before starting the test
   run) to print the output of ps commands around 'cat >' to see what
   happens.
   
    for i in $(seq 1 10000); do ps --forest -ef | grep "cat >" -A 5 >>
   ~/ps_output.txt; done
   
   The printed results showed that the child process with the pipe
   command became defunct.
   
    nitinmo+ 3180211 3180160  5 17:05 pts/1    00:00:00  |   |
      \_ /usr/local/google/home/nitinmotiani/postgresql/tmp_install/usr/local/pgsql/bin/pg_dump
   -Fd -B p     ostgres --pipe-command="cat >
   /usr/local/google/home/nitinmotiani/postgresql/src/bin/pg_dump/definite_dumpdir/%f"
    nitinmo+ 3180215 3180211  0 17:05 pts/1    00:00:00  |   |
          \_ [sh] <defunct>
   
   We are not sure how to handle this issue. Please let us know your thoughts.
   
   Thanks & Regards,
   Nitin Motiani
   Google
   

7. Re: Adding pg_dump flag for parallel export to pipes

   Hannu Krosing <hannuk@google.com> — 2025-07-04T07:12:20Z

   I have added this to the commitfest
   
   We would be grateful for any reviews and feedback on this.
   
   When adding to commitfest I tried to put Nitin as "first author" as he
   has done the bulk of the work (I did just a quick pg_dump-only PoC)
   but it looks like Commitfest just orders all provided authors
   alphabetically .
   
   
   
   

8. Re: Adding pg_dump flag for parallel export to pipes

   Andrew Jackson <andrewjackson947@gmail.com> — 2025-08-28T13:05:34Z

   Hi,
   
   Very interesting patch. One question: is it possible with this patch to pipe pg_dump directory output directly into pg_restore with this patch? Looking at the code I don't believe that is the case but figured I would ask.
   
   Thanks,
   Andrew Jackson

9. Re: Adding pg_dump flag for parallel export to pipes

   Andrew Jackson <andrewjackson947@gmail.com> — 2025-08-31T15:43:22Z

   Hi,
   
   Went ahead and experimented with your patch a bit. To answer my previous question this patch can be used to pipe pg_dump directly into pg_restore. This should absolutely be added as another use case to your list above as it is a well known limitation that you can use pg_dump/psql to do buffered copy but only with a single process, while using pg_dump/pg_restore is capable of multiprocessed copy but it must be saved to disk in its entirety before the restore can begin. This is extremely frustrating when dealing with large databases where you don't want multiple copies saved on disk and because it's not as fast as it can be. With this patch you can get the best of both worlds. 
   
    Example dump
   ```bash
   pg_dump --jobs=4 -Fd "${connection_str}" --pipe-command="mkfifo dumpdir/%f; cat >> dumpdir/%f"
   ```
   
   Example restore run in different process
   ```bash
   pg_restore --jobs=4 -Fd --dbname="${another_connection_str}" ./dumpdir
   ```
   Thanks,
   Andrew Jackson

10. Re: Adding pg_dump flag for parallel export to pipes

    Dilip Kumar <dilipbalaut@gmail.com> — 2025-09-09T06:37:19Z

    On Thu, Jun 5, 2025 at 6:09 PM Nitin Motiani <nitinmotiani@google.com> wrote:
    >
    > Hi,
    >
    > Apologies for the delay on this thread.
    >
    > On Mon, Apr 28, 2025 at 1:52 PM Nitin Motiani <nitinmotiani@google.com> wrote:
    > >
    > > Thanks for the feedback, Thomas.
    > >
    > > > Do I understand correctly that
    > > > the problem you encountered is in some other tests that you haven't
    > > > attached yet?  Could you post what you have so that others can see the
    > > > problem and perhaps have a chance of helping?
    > >
    > > Yes, we didn't add the failed tests to the patch. We'll add those and
    > > send new patches.
    > >
    >
    > I'm attaching the patch files generated using git format-patch.
    >
    > 0001 has the pg_dump pipe support code.
    > 0002 has the pg_restore pipe support.
    > 0003 has a few basic validation tests for correct flag combinations.
    > 0004 has the proposed documentation changes.
    >
    > The above 4 are the same as before.
    >
    > The 0005 patch is the new WIP patch file. This includes the tests
    > which we have been trying to add but which are failing (although the
    > same commands run fine manually).
    >
    > The tests in this patch are added to src/bin/pg_dump/t/002_pg_dump.pl.
    > The original attempt was to have a test case with dump and restore
    > commands using the new flag and run it in multiple scenarios. But
    > since that was failing, for the ease of debugging I added a few
    > standalone tests which just run a pg_dump with the pipe-command flag.
    > In these tests, if the pipe-command is a simple command like 'cat' or
    > 'gzip', the test passes. But if the pipe-command itself uses a pipe
    > (either to a file or another command), the test fails.
    >
    > In the following test
    >
    >  ['pg_dump', '-Fd', '-B', 'postgres', "--pipe-command=\"cat > $tempdir/%f\"",],]
    >
    > I get the below error.
    >
    > # 'sh: line 1: cat >
    > /usr/local/google/home/nitinmotiani/postgresql/src/bin/pg_dump/tmp_check/tmp_test_XpFO/toc.dat:
    > No such file or directory
    >
    > I can see that the temp directory tmp_test_XpFO exists. Even when I
    > changed the test to use an absolute path to an existing directory, I
    > got the same error. When I do manual testing with the same
    > pipe-command, it works fine. That is why we think there is some issue
    > with our environment setup for the tap test where it is not able to
    > parse the command.
    >
    > I also ran the following loop (started just before starting the test
    > run) to print the output of ps commands around 'cat >' to see what
    > happens.
    >
    >  for i in $(seq 1 10000); do ps --forest -ef | grep "cat >" -A 5 >>
    > ~/ps_output.txt; done
    >
    > The printed results showed that the child process with the pipe
    > command became defunct.
    >
    >  nitinmo+ 3180211 3180160  5 17:05 pts/1    00:00:00  |   |
    >    \_ /usr/local/google/home/nitinmotiani/postgresql/tmp_install/usr/local/pgsql/bin/pg_dump
    > -Fd -B p     ostgres --pipe-command="cat >
    > /usr/local/google/home/nitinmotiani/postgresql/src/bin/pg_dump/definite_dumpdir/%f"
    >  nitinmo+ 3180215 3180211  0 17:05 pts/1    00:00:00  |   |
    >        \_ [sh] <defunct>
    >
    > We are not sure how to handle this issue. Please let us know your thoughts.
    
    The latest patch set is not applying on HEAD can you rebase the patch
    set.  And also there are many TODOs in the patch, if those TODOs are
    just good to do and you are planning for future development better to
    get rid of those.  OTOH if some of those TODOs are mandatory to do
    before we can commit the patch then are you planning to work on those
    soon?  I am planning to review this patch so are you planning to send
    the rebased version with implementing the TODO which are required for
    the first version.
    
    -- 
    Regards,
    Dilip Kumar
    Google
    
    
    
    

11. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2025-09-09T15:11:24Z

    On Tue, Sep 9, 2025 at 12:07 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
    >
    >
    > The latest patch set is not applying on HEAD can you rebase the patch
    > set.  And also there are many TODOs in the patch, if those TODOs are
    > just good to do and you are planning for future development better to
    > get rid of those.  OTOH if some of those TODOs are mandatory to do
    > before we can commit the patch then are you planning to work on those
    > soon?  I am planning to review this patch so are you planning to send
    > the rebased version with implementing the TODO which are required for
    > the first version.
    >
    
    Thanks for the feedback, Dilip. We will rebase the patch soon and send
    it. Regarding the TODOs, some of those are plans for future
    development (i.e. refactor). There are also TODOs in the first patch
    file 0001 which are actually removed in file 0002. We can clean those
    up or combine the two files. Other than that, some are about the open
    questions. We will remove those from the code and will discuss those
    issues on the thread.
    
    Thanks,
    Nitin Motiani
    Google
    
    
    
    

12. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-01T08:01:42Z

    Thanks a lot for your feedback Mahendra. I have rebased the changes. I'll
    follow up on your other comments regarding cleanup and the shortened flag
    name. I'm currently uploading the latest version post rebase as I've made
    some changes to fix the test failures. Will let cfbot run those once.
    
    Regards
    Nitin Motiani,
    Google
    

13. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-04T04:39:13Z

    Hi Mahendra,
    
    I'm attaching the latest version of the patch. This incorporates
    several suggestions Mahendra made. I've also fixed the failing test
    and added support for Windows in the test by using perlbin instead of
    cat. Here is the list of changes in this version:
    
    1. Changed pipe-command to pip.
    2. Added better error handling for invalid command.
    3. Added shell escaping in the command before setting it as the file path.
    4. I decided to change the mode to PG_BINARY_W for the large object
    toc files even in the standard case. If there is a concern, we can
    revert to the older version.
    5. Added a bunch of new tests for various scenarios and parallelism.
    
    Currently these changes are in a separate commit. After the review, I
    can squash the first 3 commits of pg_dump, pg_restore, and the fixes
    in one commit.
    
    On Sat, Mar 14, 2026 at 10:37 PM Mahendra Singh Thalor
    <mahi6run@gmail.com> wrote:
    >
    > Comment5: I think we can support this new pipe option with pg_dumpall also as we support directory mode in pg_dumpall from v19.
    
    Regarding this, I haven't looked into the details of how that would
    work. If the rest look good, we can consider implementing this in
    another patch. For the time being, I've skipped this option in
    pg_dumpall and the global restore. Let me know what you think.
    
    Thanks
    
    Nitin Motiani,
    Google
    

14. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-07T08:35:36Z

    I did another round of rebase and added a couple of extra tests.
    Changed error messages in the tests for windows.
    
    
    > 3. Added shell escaping in the command before setting it as the file path.
    
    Removed this because it might have been causing Windows test failures.
    Since the command runs from the client, this step isn't necessary.
    
    Thanks
    
    Nitin Motiani
    Google
    

15. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-08T17:07:06Z

    Hi,
    
    Made another small test change. And rebased the code. I'm attaching
    the latest patches.
    
    Thanks
    
    Nitin Motiani
    Google
    

16. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-11T05:34:08Z

    Attaching another version with minor changes to the test code for
    windows backslash paths.
    
    Thanks
    
    Nitin Motiani
    Google
    

17. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-14T10:09:09Z

    I rebased again and cleaned up the test files to remove the split
    command in the Windows test. Attaching the latest version.
    

18. Re: Adding pg_dump flag for parallel export to pipes

    Nitin Motiani <nitinmotiani@google.com> — 2026-05-21T09:56:43Z

    Changed how pipe commands are quoted in the Windows test. The latest
    versions are attached.
    
    Thanks
    
    Nitin Motiani
    Google
    

19. Re: Adding pg_dump flag for parallel export to pipes

    solai v <solai.cdac@gmail.com> — 2026-05-22T10:34:23Z

    Hi all,
    
    Thank you for the updated patch.
    
    On Fri, May 22, 2026 at 1:03 PM Nitin Motiani <nitinmotiani@google.com> wrote:
    >
    > Changed how pipe commands are quoted in the Windows test. The latest
    > versions are attached.
    
    I worked on reproducing the current limitation around parallel dumps
    and then tested the latest v16 patch adding --pipe support for
    pg_dump. To begin with, I verified the existing behavior.
    For example:
    pg_dump postgres | gzip > dump.sql.gz works, but does not support parallelism,
    whereas:
    pg_dump -Fd -j 4 -f dumpdir postgres
    du -sh dumpdir
    21M dumpdir
    requires intermediate disk storage. This demonstrates the current
    limitation where users must choose between parallelism and streaming
    pipelines.
    I then tested the patch introducing --pipe support. The feature is
    quite useful for modern workflows where users want to stream dump
    output directly to compression or upload pipelines without relying on
    intermediate storage. Basic functionality worked as expected.
    For example:
    pg_dump -p 55432 -Fd -j 4 --pipe="cat > dump.out" postgres, produced a
    ~38MB output file,
    and:
    pg_dump -p 55432 -Fd -j 4 --pipe="gzip > dump.gz" postgres produced, a
    compressed file (~11MB).
    The initial contents appeared valid:
    gunzip -c dump.gz | head
    1
    2
    3
    ...
    Also, no intermediate directory was created, confirming that the patch
    enables streaming without filesystem-backed staging. Error handling
    also behaved correctly.
    For example:
    --pipe="invalid_cmd"
    resulted in:
    pg_dump: error: pipe command failed: command not found
    and:
    --pipe="gzip | false"
    resulted in:
    pg_dump: error: pipe command failed: child process exited with exit code 1
    However, I observed an important issue when using the feature with
    multiple parallel workers. Since the pipe command is executed per
    output file, using: --pipe="gzip > dump.gz", it results in multiple
    workers invoking independent gzip processes that all write to the same
    output file. This leads to corrupted or truncated output.
    In my testing:
    gunzip -c dump.gz > dump.sql
    failed with:
    gzip: dump.gz: unexpected end of file
    This suggests that concurrent writes to a shared output target are not
    coordinated and can result in invalid dumps. It would be helpful to
    clarify expected usage patterns here. For example: whether users are
    expected to generate distinct outputs per worker, or whether
    safeguards should be implemented to prevent multiple workers from
    writing to the same destination. Additionally, during failure
    scenarios I observed backend logs such as:
    FATAL: connection to client lost
    Broken pipe
    While this is expected when the pipe terminates prematurely, it may be
    worth considering whether error messaging or cleanup behavior can be
    made clearer from the user perspective.
    Overall, the feature is valuable and aligns well with modern backup
    workflows. However, behavior in multi-worker scenarios with shared
    pipe targets may need further clarification or safeguards to avoid
    data corruption. Looking forward to more feedback.
    
    
    Regards.
    Solai