FATAL: terminating connection because protocol synchronization was lost
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 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
Re: FATAL: terminating connection because protocol synchronization was lost
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 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.kla...@aklaver.com > -- Shrikant Bhende +91-9975543712
Re: FATAL: terminating connection because protocol synchronization was lost
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 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 > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > > > > -- > > Shrikant Bhende > > +91-9975543712 > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Shrikant Bhende +91-9975543712
Re: FATAL: terminating connection because protocol synchronization was lost
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 Thanks On Tue, Oct 16, 2018 at 2:55 AM Adrian Klaver 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 > <mailto:adrian.kla...@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 > > mailto:adrian.kla...@aklaver.com> > > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > <mailto:adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com > >> > > > > > > > > > > > > -- > > > Shrikant Bhende > > > +91-9975543712 > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > > > > -- > > Shrikant Bhende > > +91-9975543712 > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Shrikant Bhende +91-9975543712
Re: FATAL: terminating connection because protocol synchronization was lost
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, 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 > > Hmm. > > What OS is this? > > Does the error always happen in the same place in the restore? > > > > > Thanks > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Join for the parent table will not leverage the index scan
We have two tables, both have ~36 partitions. This is my first query, which attempts to join the parent tables on the columns that have been used to create multi-column indexes for all partitions (multi-column indexes defined on session_id, detail_id in that order): *PostgreSQL version : 10*-- *query* explain select * from dm_ci360.page_details pd inner join dm_ci360.page_details_ext pde on (pd.session_id = pde.session_id and pd.detail_id = pde.detail_id) where pd.session_dt >= now()::date-2; -- *plan* Hash Join (cost=36927955.75..100106605.07 rows=11 width=8355) Hash Cond: (((pd.session_id)::text = (pde.session_id)::text) AND ((pd.detail_id)::text = (pde.detail_id)::text)) -> Append (cost=1297.33..571870.20 rows=630535 width=8130) -> Bitmap Heap Scan on page_details_null pd (cost=1297.33..240207.85 rows=69259 width=8220) Recheck Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Index Scan on page_details_null_session_dt_idx (cost=0.00..1280.02 rows=69259 width=0) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m3_session_dt_idx on page_details_m3 pd_1 (cost=0.44..4.71 rows=1 width=8216) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m2_session_dt_idx on page_details_m2 pd_2 (cost=0.43..102.79 rows=621 width=8253) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m1_session_dt_idx on page_details_m1 pd_3 (cost=0.44..4.55 rows=1 width=8281) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_5_session_dt_idx on page_details_5 pd_4 (cost=0.57..51622.53 rows=196574 width=8202) Index Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Heap Scan on page_details_31 pd_5 (cost=982.61..194857.98 rows=52391 width=7721) Recheck Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Index Scan on page_details_31_session_dt_idx1 (cost=0.00..969.51 rows=52391 width=0) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_49_session_dt_idx on page_details_49 pd_6 (cost=0.45..10991.08 rows=50039 width=7721) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_52_session_dt_idx on page_details_52 pd_7 (cost=0.44..2337.94 rows=10222 width=8297) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_59_session_dt_idx on page_details_59 pd_8 (cost=0.57..24790.17 rows=50148 width=8276) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_60_session_dt_idx on page_details_60 pd_9 (cost=0.44..7292.41 rows=4259 width=8263) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_61_session_dt_idx on page_details_61 pd_10 (cost=0.43..309.10 rows=704 width=8182) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_63_session_dt_idx on page_details_63 pd_11 (cost=0.43..13.20 rows=25 width=8250) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_64_session_dt_idx on page_details_64 pd_12 (cost=0.44..5.41 rows=1 width=8314) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_66_session_dt_idx on page_details_66 pd_13 (cost=0.42..4.49 rows=1 width=8277) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_67_session_dt_idx on page_details_67 pd_14 (cost=0.42..58.62 rows=325 width=8227) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_68_session_dt_idx on page_details_68 pd_15 (cost=0.42..35.20 rows=111 width=8259) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_78_session_dt_idx on page_details_78 pd_16 (cost=0.44..1738.10 rows=4608 width=8263) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_98_session_dt_idx on page_details_98 pd_17 (cost=0.44..2157.31 rows=7136 width=8225) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_101_session_dt_idx on page_details_101 pd_18 (cost=0.44..2956.03 rows=9241 width=8205) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_130_session_dt_idx on page_details_130 pd_19 (cost=0.44..1683.53 rows=19656 width=8199) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m100_session_dt_idx on page_details_m100 pd_20 (cost=0.43..4.96 rows=1 width
