Thread: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe

Hello,

I have been trying to import a Postgres 11 database into Postgres 14, 
but the pg_restore command exits with the following message:

    pg_restore: error: could not write to the communication channel: Broken 
pipe

The command I sent looks like this:

    /Library/PostgreSQL/14/bin/pg_restore -h localhost -p 48100 -U postgres 
-w -C -d template1 -j 24 -Fd /Volumes/Migration/dbname --verbose


It seems that the multiple jobs parameter is the cause. If I specify "-j 
1", the command works without problems. If I specify "-j 2" or higher, I 
get the above error after a few seconds.

Postgres is running on a Mac Pro 12-core machine, so it has plenty of 
resources at its disposal. The config file is a copy of the Postgres 11 
configuration, which has no problem with multiple jobs.


Furthermore, the pg_dump command seems to have the same issue as well. 
The following command:

    /Library/PostgreSQL/14/bin/pg_dump -h localhost -p 48100 -U postgres -w 
ServicePGR_UTF8 -j 24 -Fd -f /Volumes/Migration/dbname --verbose

will stop prematurely with the following error:

    pg_dump: error: could not write to the communication channel: Broken 
pipe


Does this sound familiar to anyone? Is it an issue with the new Postgres 
14 release, or is there something else that might be causing this?

Best regards,

Nick Renders

On 2021-Oct-15, Nick Renders wrote:

> Hello,
> 
> I have been trying to import a Postgres 11 database into Postgres 14, but
> the pg_restore command exits with the following message:
> 
>     pg_restore: error: could not write to the communication channel: Broken
> pipe
> 
> The command I sent looks like this:
> 
>     /Library/PostgreSQL/14/bin/pg_restore -h localhost -p 48100 -U postgres -w
> -C -d template1 -j 24 -Fd /Volumes/Migration/dbname --verbose
> 
> It seems that the multiple jobs parameter is the cause. If I specify "-j 1",
> the command works without problems. If I specify "-j 2" or higher, I get the
> above error after a few seconds.

Hi,

Yeah, pg_dump in parallel mode uses a pipe to communicate between leader
and workers; the error you see is what happens when a write to the pipe
fails.  It sounds to me like something in the operating system is
preventing the pipes from working properly.  I don't know anything about
macOS so I can't help you with that.  I can tell you however that this
error has not been reported previously.

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2021-Oct-15, Nick Renders wrote:
>> I have been trying to import a Postgres 11 database into Postgres 14, but
>> the pg_restore command exits with the following message:
>> pg_restore: error: could not write to the communication channel: Broken
>> pipe
>>
>> It seems that the multiple jobs parameter is the cause. If I specify "-j 1",
>> the command works without problems. If I specify "-j 2" or higher, I get the
>> above error after a few seconds.

> Yeah, pg_dump in parallel mode uses a pipe to communicate between leader
> and workers; the error you see is what happens when a write to the pipe
> fails.  It sounds to me like something in the operating system is
> preventing the pipes from working properly.  I don't know anything about
> macOS so I can't help you with that.  I can tell you however that this
> error has not been reported previously.

I tried to reproduce this on my own 2019 MacBook Pro running Big Sur
(11.6), without success.  I made a test database with

do $$
  begin
    for i in 1..5000 loop
      execute 'create table t'||i||' as select generate_series(1,100000) x';
  end loop;
end $$;

and then ran dumps and restores with the same parameters you used.
No sign of trouble.

I concur with the request to look in the postmaster log to see if
anything interesting shows up on that side.  Also, does the
behavior change if you don't use "-h localhost" but instead let it
default to a Unix socket?  (I don't have any real reason to think
that it would change, but we're grasping at straws here.)

            regards, tom lane

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Yeah, pg_dump in parallel mode uses a pipe to communicate between leader
> and workers; the error you see is what happens when a write to the pipe
> fails.  It sounds to me like something in the operating system is
> preventing the pipes from working properly.

BTW, I think a more likely explanation is "one of the pg_dump or
pg_restore worker processes crashed".  Why that should be is still
a mystery though.

            regards, tom lane

Thank you for all the feedback and suggestions.

It seems that the "-h localhost" parameter is triggering the issue. If I 
leave it out, pg_restore works without problems with multiple jobs. I 
have also tried specifying the IP number instead of "localhost", but 
that results in the same error.

I see now that our original pg_restore script does not include the -h 
parameter. Somehow, it has snuck in my commands when testing Postgres 
14. That might mean that the same issue exists in previous versions as 
well. I will investigate further.

Nick


On 15 Oct 2021, at 19:08, Tom Lane wrote:

> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> Yeah, pg_dump in parallel mode uses a pipe to communicate between 
>> leader
>> and workers; the error you see is what happens when a write to the 
>> pipe
>> fails.  It sounds to me like something in the operating system is
>> preventing the pipes from working properly.
>
> BTW, I think a more likely explanation is "one of the pg_dump or
> pg_restore worker processes crashed".  Why that should be is still
> a mystery though.
>
>             regards, tom lane

"Nick Renders" <postgres@arcict.com> writes:
> Thank you for all the feedback and suggestions.
> It seems that the "-h localhost" parameter is triggering the issue. If I 
> leave it out, pg_restore works without problems with multiple jobs. I 
> have also tried specifying the IP number instead of "localhost", but 
> that results in the same error.

Hmm ... do you have your firewall set up in any unusual way?
See System Preferences -> Security & Privacy -> Firewall ->
Firewall Options.  On mine, the only checked box is
"Automatically allow built-in software to receive incoming
connections".  I also have SSH enabled, though that doesn't
seem too relevant here.

            regards, tom lane