FATAL: terminating connection because protocol synchronization was lost

On 10/15/18 5:36 AM, Shrikant Bhende wrote:

Hi Team,

I am trying to restore the backup into postgresql 9.6 and during restore
copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG:  could not send data to
client: Broken pipe

I would say the above is the issue.

Where is the client running relative to the server?

2018-10-12 06:21:40 UTC [40407]: [29-1]
application=psql.bin,user=postgres,db=cloudSTATEMENT:  COPY
d20160905_x_20da999ef9434e60942859c3690e642f (did, location_id, h00,
h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt, h02, h02_min,
h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04, h04_min, h04_max,
h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min, h06_max, h06_wgt,
h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max, h08_wgt, h09,
h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt, h11, h11_min,
h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13, h13_min, h13_max,
h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min, h15_max, h15_wgt,
h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max, h17_wgt, h18,
h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt, h20, h20_min,
h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22, h22_min, h22_max,
h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
2018-10-12 06:21:40 UTC [40407]: [30-1]
application=psql.bin,user=postgres,db=cloudFATAL: terminating connection
because protocol synchronization was lost

I am trying to migrate the DB from EDB PPAS to community PostgreSQL, we
don't have any oracle or PPAS dependent objects so I took the pg_dumpall
using the PostgreSQL binaries and trying to restore the same.

PPAS :: 9.3
PostgreSQL : 9.6
O/S :: Centos 6.6

Thanks and Regards.

-- 
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

Shrikant Bhende <shrikantbhende.net@gmail.com> writes:

I am trying to restore the backup into postgresql 9.6 and during restore
copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG: could not send data to
client: Broken pipe

This says the client disconnected first. What does the problem look
like from the client side?

regards, tom lane

Hi Adrian,

There is no explicit client its just simple restore using psql with all
default settings required.

On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/15/18 5:36 AM, Shrikant Bhende wrote:

Hi Team,

I am trying to restore the backup into postgresql 9.6 and during restore
copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG: could not send data to
client: Broken pipe

I would say the above is the issue.

Where is the client running relative to the server?

2018-10-12 06:21:40 UTC [40407]: [29-1]
application=psql.bin,user=postgres,db=cloudSTATEMENT: COPY
d20160905_x_20da999ef9434e60942859c3690e642f (did, location_id, h00,
h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt, h02, h02_min,
h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04, h04_min, h04_max,
h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min, h06_max, h06_wgt,
h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max, h08_wgt, h09,
h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt, h11, h11_min,
h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13, h13_min, h13_max,
h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min, h15_max, h15_wgt,
h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max, h17_wgt, h18,
h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt, h20, h20_min,
h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22, h22_min, h22_max,
h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
2018-10-12 06:21:40 UTC [40407]: [30-1]
application=psql.bin,user=postgres,db=cloudFATAL: terminating connection
because protocol synchronization was lost

I am trying to migrate the DB from EDB PPAS to community PostgreSQL, we
don't have any oracle or PPAS dependent objects so I took the pg_dumpall
using the PostgreSQL binaries and trying to restore the same.

PPAS :: 9.3
PostgreSQL : 9.6
O/S :: Centos 6.6

Thanks and Regards.

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

-- 
Shrikant Bhende
+91-9975543712

On 10/15/18 8:04 AM, Shrikant Bhende wrote:

Hi Adrian,

There is no explicit client its just simple restore using psql with all
default settings required.

psql is the client. Where is it running relative to the server locally
or remote?

On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/15/18 5:36 AM, Shrikant Bhende wrote:

Hi Team,

I am trying to restore the backup into postgresql 9.6 and during

restore

copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG:  could not send

data to

client: Broken pipe

I would say the above is the issue.

Where is the client running relative to the server?

2018-10-12 06:21:40 UTC [40407]: [29-1]
application=psql.bin,user=postgres,db=cloudSTATEMENT:  COPY
d20160905_x_20da999ef9434e60942859c3690e642f (did, location_id, h00,
h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt, h02,

h02_min,

h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04, h04_min,

h04_max,

h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min, h06_max,

h06_wgt,

h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max, h08_wgt, h09,
h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt, h11,

h11_min,

h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13, h13_min,

h13_max,

h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min, h15_max,

h15_wgt,

h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max, h17_wgt, h18,
h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt, h20,

h20_min,

h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22, h22_min,

h22_max,

h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
2018-10-12 06:21:40 UTC [40407]: [30-1]
application=psql.bin,user=postgres,db=cloudFATAL: terminating

connection

because protocol synchronization was lost

I am trying to migrate the DB from EDB PPAS to community

PostgreSQL, we

don't have any oracle or PPAS dependent objects so I took the

pg_dumpall

using the PostgreSQL binaries and trying to restore the same.

PPAS :: 9.3
PostgreSQL : 9.6
O/S :: Centos 6.6

Thanks and Regards.

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

-- 
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrain,
Its running on the local machine itself. I googled this around but most of
the blog says that stop supporting the older version, also I took
pg_dumpall from EDB PPAS cluster using PostgreSQL binaries, if that might
have caused anything.

Thanks.

On Mon, Oct 15, 2018 at 8:36 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/15/18 8:04 AM, Shrikant Bhende wrote:

Hi Adrian,

There is no explicit client its just simple restore using psql with all
default settings required.

psql is the client. Where is it running relative to the server locally
or remote?

On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/15/18 5:36 AM, Shrikant Bhende wrote:

Hi Team,

I am trying to restore the backup into postgresql 9.6 and during

restore

copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG: could not send

data to

client: Broken pipe

I would say the above is the issue.

Where is the client running relative to the server?

2018-10-12 06:21:40 UTC [40407]: [29-1]
application=psql.bin,user=postgres,db=cloudSTATEMENT: COPY
d20160905_x_20da999ef9434e60942859c3690e642f (did, location_id,

h00,

h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt, h02,

h02_min,

h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04, h04_min,

h04_max,

h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min, h06_max,

h06_wgt,

h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max, h08_wgt,

h09,

h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt, h11,

h11_min,

h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13, h13_min,

h13_max,

h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min, h15_max,

h15_wgt,

h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max, h17_wgt,

h18,

h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt, h20,

h20_min,

h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22, h22_min,

h22_max,

h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
2018-10-12 06:21:40 UTC [40407]: [30-1]
application=psql.bin,user=postgres,db=cloudFATAL: terminating

connection

because protocol synchronization was lost

I am trying to migrate the DB from EDB PPAS to community

PostgreSQL, we

don't have any oracle or PPAS dependent objects so I took the

pg_dumpall

using the PostgreSQL binaries and trying to restore the same.

PPAS :: 9.3
PostgreSQL : 9.6
O/S :: Centos 6.6

Thanks and Regards.

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

-- 
Shrikant Bhende
+91-9975543712

On 10/15/18 8:10 AM, Shrikant Bhende wrote:

Hi Adrain,
Its running on the local machine itself. I googled this around but most
of the blog says that stop supporting the older version, also I took
pg_dumpall from EDB PPAS cluster using PostgreSQL binaries, if that
might have caused anything.

The data looks alright, this is a client(psql) issue. So:

1) Which psql are you using the EDB or community one?

2) What is the exact psql command you are using?

Thanks.

On Mon, Oct 15, 2018 at 8:36 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/15/18 8:04 AM, Shrikant Bhende wrote:

Hi Adrian,

There is no explicit client its just simple restore using psql

with all

default settings required.

psql is the client. Where is it running relative to the server locally
or remote?

On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver

<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.com>>> wrote:

     On 10/15/18 5:36 AM, Shrikant Bhende wrote:
      > Hi Team,
      >
      > I am trying to restore the backup into postgresql 9.6 and

during

     restore
      > copy command fails with below error,
      >
      > 2018-10-12 06:21:40 UTC [40407]: [28-1]
      > application=psql.bin,user=postgres,db=cloudLOG:  could not

send

     data to
      > client: Broken pipe

     I would say the above is the issue.

     Where is the client running relative to the server?

      > 2018-10-12 06:21:40 UTC [40407]: [29-1]
      > application=psql.bin,user=postgres,db=cloudSTATEMENT:  COPY
      > d20160905_x_20da999ef9434e60942859c3690e642f (did,

location_id, h00,

      > h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt,

h02,

     h02_min,
      > h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04,

h04_min,

     h04_max,
      > h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min,

h06_max,

     h06_wgt,
      > h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max,

h08_wgt, h09,

      > h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt,

h11,

     h11_min,
      > h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13,

h13_min,

     h13_max,
      > h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min,

h15_max,

     h15_wgt,
      > h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max,

h17_wgt, h18,

      > h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt,

h20,

     h20_min,
      > h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22,

h22_min,

     h22_max,
      > h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
      > 2018-10-12 06:21:40 UTC [40407]: [30-1]
      > application=psql.bin,user=postgres,db=cloudFATAL: terminating
     connection
      > because protocol synchronization was lost
      >
      > I am trying to migrate the DB from EDB PPAS to community
     PostgreSQL, we
      > don't have any oracle or PPAS dependent objects so I took the
     pg_dumpall
      > using the PostgreSQL binaries and trying to restore the same.
      >
      > PPAS :: 9.3
      > PostgreSQL : 9.6
      > O/S :: Centos 6.6
      >
      > Thanks and Regards.
      >
      > --
      > Shrikant Bhende
      > +91-9975543712

     --
     Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

-- 
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Its a PostgreSQL binary and installer was downloaded from enterprisedb site.
Binary version : psql (PostgreSQL) 9.6.10

Command to restore the dump is :
./psql -p 5434 -d cloud -f <path of the file>

Thanks

On Tue, Oct 16, 2018 at 2:55 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/15/18 8:10 AM, Shrikant Bhende wrote:

Hi Adrain,
Its running on the local machine itself. I googled this around but most
of the blog says that stop supporting the older version, also I took
pg_dumpall from EDB PPAS cluster using PostgreSQL binaries, if that
might have caused anything.

The data looks alright, this is a client(psql) issue. So:

1) Which psql are you using the EDB or community one?

2) What is the exact psql command you are using?

Thanks.

On Mon, Oct 15, 2018 at 8:36 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/15/18 8:04 AM, Shrikant Bhende wrote:

Hi Adrian,

There is no explicit client its just simple restore using psql

with all

default settings required.

psql is the client. Where is it running relative to the server

locally

or remote?

On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver

<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.com>>> wrote:

On 10/15/18 5:36 AM, Shrikant Bhende wrote:

Hi Team,

I am trying to restore the backup into postgresql 9.6 and

during

restore

copy command fails with below error,

2018-10-12 06:21:40 UTC [40407]: [28-1]
application=psql.bin,user=postgres,db=cloudLOG: could not

send

data to

client: Broken pipe

I would say the above is the issue.

Where is the client running relative to the server?

2018-10-12 06:21:40 UTC [40407]: [29-1]
application=psql.bin,user=postgres,db=cloudSTATEMENT: COPY
d20160905_x_20da999ef9434e60942859c3690e642f (did,

location_id, h00,

h00_min, h00_max, h00_wgt, h01, h01_min, h01_max, h01_wgt,

h02,

h02_min,

h02_max, h02_wgt, h03, h03_min, h03_max, h03_wgt, h04,

h04_min,

h04_max,

h04_wgt, h05, h05_min, h05_max, h05_wgt, h06, h06_min,

h06_max,

h06_wgt,

h07, h07_min, h07_max, h07_wgt, h08, h08_min, h08_max,

h08_wgt, h09,

h09_min, h09_max, h09_wgt, h10, h10_min, h10_max, h10_wgt,

h11,

h11_min,

h11_max, h11_wgt, h12, h12_min, h12_max, h12_wgt, h13,

h13_min,

h13_max,

h13_wgt, h14, h14_min, h14_max, h14_wgt, h15, h15_min,

h15_max,

h15_wgt,

h16, h16_min, h16_max, h16_wgt, h17, h17_min, h17_max,

h17_wgt, h18,

h18_min, h18_max, h18_wgt, h19, h19_min, h19_max, h19_wgt,

h20,

h20_min,

h20_max, h20_wgt, h21, h21_min, h21_max, h21_wgt, h22,

h22_min,

h22_max,

h22_wgt, h23, h23_min, h23_max, h23_wgt) FROM stdin;
2018-10-12 06:21:40 UTC [40407]: [30-1]
application=psql.bin,user=postgres,db=cloudFATAL:

terminating

connection

because protocol synchronization was lost

I am trying to migrate the DB from EDB PPAS to community

PostgreSQL, we

don't have any oracle or PPAS dependent objects so I took

the

pg_dumpall

using the PostgreSQL binaries and trying to restore the

same.

PPAS :: 9.3
PostgreSQL : 9.6
O/S :: Centos 6.6

Thanks and Regards.

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

<mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Shrikant Bhende
+91-9975543712

--
Adrian Klaver
adrian.klaver@aklaver.com

-- 
Shrikant Bhende
+91-9975543712

On 10/16/18 7:29 AM, Shrikant Bhende wrote:

Hi Adrian,

Its a PostgreSQL binary and installer was downloaded from enterprisedb site.
Binary version : psql (PostgreSQL) 9.6.10

Command to restore the dump is :
./psql -p 5434 -d cloud -f <path of the file>

Hmm.

What OS is this?

Does the error always happen in the same place in the restore?

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Thanks for your reply.
O/S is centos 6.7 on AWS EC2 ,
this is happening when system starts copying data for the biggest table, so
just to reconfirm I have taken a pg_dump with Fp for that single table and
tried to restore the same into PG cluster which was successful, and then
again when I tried to restore the complete cluster dump taken using
pg_dumpall it failed again.
Table structure :
cloud=# \d+ t_3ecc35f89a0c485eb365744bde452408.jx_objectstore_journal
Table
"t_3ecc35f89a0c485eb365744bde452408.jx_objectstore_journal"
Column | Type | Modifiers | Storage |
Stats target | Description
----------------+-----------------------------+-----------+----------+--------------+-------------
did | integer | not null | plain |
|
start | timestamp without time zone | not null | plain |
|
ending | timestamp without time zone | not null | plain |
|
deltas | text | | extended |
|
deltacount | integer | | plain |
|
finalstate | text | | extended |
|
measure_start | timestamp without time zone | | plain |
|
measure_ending | timestamp without time zone | | plain |
|
Indexes:
"jx_objectstore_journal_pkey" PRIMARY KEY, btree (did, start, ending)
"idx_jx_objectstore_journal_did" btree (did)
"idx_jx_objectstore_journal_ending" btree (ending)
"idx_jx_objectstore_journal_start" btree (did, start)
Has OIDs: no

Actual table size is around 2GB and toast table size is 288 GB which might
have around 80 GB of dead rows.

Thanks

On Tue 16 Oct, 2018, 20:23 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:

On 10/16/18 8:36 AM, Shrikant Bhende wrote:

Hi Adrian,

Thanks for your reply.
O/S is centos 6.7 on AWS EC2 ,
this is happening when system starts copying data for the biggest table,
so just to reconfirm I have taken a pg_dump with Fp for that single
table and tried to restore the same into PG cluster which was
successful, and then again when I tried to restore the complete cluster
dump taken using pg_dumpall it failed again.

Got to believe it is AWS timing out on retrieving from EBS to the EC2
image. You might want to ask AWS tech support about this.

Actual table size is around 2GB and toast table size is 288 GB which
might have around 80 GB of dead rows.

The dead rows won't show up in the dump file. I would take a look at the
single table dump to get an idea of the amount of plain text data you
are working with.

Thanks

On Tue 16 Oct, 2018, 20:23 Adrian Klaver, <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/16/18 7:29 AM, Shrikant Bhende wrote:

Hi Adrian,

Its a PostgreSQL binary and installer was downloaded from

enterprisedb site.

Binary version : psql (PostgreSQL) 9.6.10

Command to restore the dump is :
./psql -p 5434 -d cloud -f <path of the file>

Hmm.

What OS is this?

Does the error always happen in the same place in the restore?

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com