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

Anything interesting in the Postgres server log when this happens?

On Fri, Oct 15, 2021, 05:21 Nick Renders <postgres@arcict.com> wrote:

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:

"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