Обсуждение: 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
+91-9975543712
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
+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
+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
+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 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
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