Re: got some errors after upgrade poestgresql from 9.5 to 9.6
От | Pavel Stehule |
---|---|
Тема | Re: got some errors after upgrade poestgresql from 9.5 to 9.6 |
Дата | |
Msg-id | CAFj8pRBc-=KsG1B+kqr1JcmMETFrbEioJJNJoB=+6Y3_+SNaOg@mail.gmail.com обсуждение исходный текст |
Ответ на | got some errors after upgrade poestgresql from 9.5 to 9.6 (张嘉志 <zhangjiazhi@p1.com>) |
Ответы |
Re: got some errors after upgrade poestgresql from 9.5 to 9.6
(Michael Paquier <michael.paquier@gmail.com>)
Re: got some errors after upgrade poestgresql from 9.5 to 9.6 (张嘉志 <zhangjiazhi@p1.com>) |
Список | pgsql-bugs |
Hi 2016-11-23 12:49 GMT+01:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.com>= : > Hi > i got an error when upgrade postgresql9.5 to 9.6.1 ,server terminate > the requests , but don't give the reasons , i execute this query manuall= y > , it works .could you explain this ? > > pg_dump: [archiver (db)] query failed: server closed the connection > unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname A= S > indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, > t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident, > t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred, > c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintd= ef(c.oid, > false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE > s.oid =3D t.reltablespace) AS tablespace, t.reloptions AS indreloptions F= ROM > pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid =3D i.indexrel= id) > LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid =3D c.conrelid AND > i.indexrelid =3D c.conindid AND c.contype IN ('p','u','x')) WHERE i.indre= lid > =3D '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY > indexname > > > > 219733,1 Bot > > =3D=3D=3DLOGS > > 2016-11-23 19:26:05.027 CST,,,7888,,58357a04.1ed0,3,,2016-11-23 19:14:12 > CST,,0,LOG,00000,"server process (PID 7993) was terminated by signal 9: > Killed","Failed process was running: SELECT t.tableoid, t.oid, t.relname = AS > indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, > t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident, > t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred, > c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintd= ef(c.oid, > false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE > s.oid =3D t.reltablespace) AS tablespace, t.reloptions AS indreloptions F= ROM > pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid =3D i.indexrel= id) > LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid =3D c.conrelid AND > i.indexrelid =3D c.conindid AND c.contype IN ('p','u','x')) WHERE i.indre= lid > =3D '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY > It looks like somebody killed Postgres - Postgres doesn't use signal 9 what I know - probably someone did kill -9 on some PostgreSQL process. Regards Pavel > indexname",,,,,,,,"" > > 2016-11-23 19:26:05.031 CST,,,7888,,58357a04.1ed0,4,,2016-11-23 19:14:12 > CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,= "" > > 2016-11-23 19:26:05.059 CST,"postgres","putong-shard- > 2",7992,"[local]",58357a30.1f38,6,"SELECT",2016-11-23 19:14:56 > CST,5/11,0,WARNING,57P02,"terminating connection because of crash of > another server process","The postmaster has commanded this server process > to roll back the current transaction and exit, because another server > process exited abnormally and possibly corrupted shared memory.","In a > moment you should be able to reconnect to the database and repeat your > command.",,,,,,,"pg_dump" > > 2016-11-23 19:26:05.165 CST,,,7888,,58357a04.1ed0,5,,2016-11-23 19:14:12 > CST,,0,LOG,00000,"all server processes terminated; > reinitializing",,,,,,,,,"" > > 2016-11-23 19:26:05.473 CST,,,7888,,58357a04.1ed0,6,,2016-11-23 19:14:12 > CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,"" > > 2016-11-23 19:26:05.481 CST,,,8016,,58357ccd.1f50,1,,2016-11-23 19:26:05 > CST,,0,LOG,00000,"database system was interrupted; last known up at > 2016-11-23 19:24:19 CST",,,,,,,,,"" > > 2016-11-23 19:26:22.417 CST,,,8016,,58357ccd.1f50,2,,2016-11-23 19:26:05 > CST,,0,LOG,00000,"database system was not properly shut down; automatic > recovery in progress",,,,,,,,,"" > > 2016-11-23 19:26:22.752 CST,,,8016,,58357ccd.1f50,3,,2016-11-23 19:26:05 > CST,,0,LOG,00000,"redo starts at 44A/2C0CEE30",,,,,,,,,"" > > 2016-11-23 19:26:22.760 CST,,,7888,,58357a04.1ed0,7,,2016-11-23 19:14:12 > CST,,0,LOG,00000,"abnormal database system shutdown",,,,,,,,,"" > > 2016-11-23 19:43:12.371 CST,,,8051,,583580cf.1f73,1,,2016-11-23 19:43:11 > CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will g= o > to log destination ""csvlog"".",,,,,,,"" > > > ------------------------------ > *=E5=8F=91=E4=BB=B6=E4=BA=BA: *"=E5=BC=A0=E5=98=89=E5=BF=97" <zhangjiazhi= @p1.com> > *=E6=94=B6=E4=BB=B6=E4=BA=BA: *"Pavel Stehule" <pavel.stehule@gmail.com> > *=E6=8A=84=E9=80=81: *pgsql-bugs@postgresql.org, "backend" <backend@p1.co= m>, "dba" < > dba@p1.com> > *=E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: *=E6=98=9F=E6=9C=9F=E4=B8=89, 2016= =E5=B9=B4 10 =E6=9C=88 12=E6=97=A5 =E4=B8=8B=E5=8D=88 1:51:47 > > *=E4=B8=BB=E9=A2=98: *Re: [BUGS] got some errors after upgrade poestgresq= l from 9.5 to 9.6 > > thanks for your reply , i try to vacuum the table , can't work , i also > recreate the table , worked , but can't do this in production , because w= e > have lots of big table had change the columns , > and thanks for your reminder , before delete the data from system catalog > table , i do a backup , and restore it after test. .accturlly , vacuum fu= ll > is like recreate a new table , but i will test > can someone can explain this errors. > > thanks a lot . > > ------------------------------ > *=E5=8F=91=E4=BB=B6=E4=BA=BA: *"Pavel Stehule" <pavel.stehule@gmail.com> > *=E6=94=B6=E4=BB=B6=E4=BA=BA: *"=E5=BC=A0=E5=98=89=E5=BF=97" <zhangjiazhi= @p1.com> > *=E6=8A=84=E9=80=81: *pgsql-bugs@postgresql.org, "backend" <backend@p1.co= m>, "dba" < > dba@p1.com> > *=E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: *=E6=98=9F=E6=9C=9F=E4=B8=89, 2016= =E5=B9=B4 10 =E6=9C=88 12=E6=97=A5 =E4=B8=8B=E5=8D=88 1:40:15 > *=E4=B8=BB=E9=A2=98: *Re: [BUGS] got some errors after upgrade poestgresq= l from 9.5 to 9.6 > > > > 2016-10-12 7:27 GMT+02:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.com= >: > >> Hi >> >> I am got a problem after upgrade poestgresql from 9.5 to 9.6 .it's >> occurred when calling an exits function . >> >> >> > It is little bit strange - I don't remember any related change in this > area. > > >> >> putong-contacts=3D# select insert_user_mobile_contact_hashes(4009, '', >> ARRAY['7bd94067bb0597bf995c4eb46364a4ce'], ARRAY[' >> c55288a186d0e6f0e3fb3b66deb65366'], ARRAY[''], false); >> ERROR: table row type and query-specified row type do not match >> DETAIL: Query provides a value for a dropped column at ordinal position >> 6. >> CONTEXT: SQL statement "UPDATE c >> SET >> phone_number =3D phone_number_arr_[i], >> name =3D name_ >> WHERE >> user_id =3D user_id_ >> AND >> md5_hash11 =3D md5_hash11_arr_[i] >> AND >> coalesce(phone_number,'') =3D '' >> AND >> char_length(phone_number_arr_[i]) > 0" >> PL/pgSQL function insert_user_mobile_contact_hashes(integer,character >> varying,character varying[],character varying[],character >> varying[],boolean) line 36 at SQL statement >> >> >> and here is the column in this table be dropped >> >> putong-contacts=3D# select * from pg_attribute where attrelid =3D >> 'user_mobile_contact_hashes'::regclass and attisdropped; >> attrelid | attname | atttypid | attstattarget | >> attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | >> attstorage | attalign | attnotnull | atthasdef | attisdropped | attisloc= al >> | attinhcount | attcollation | attacl | attoptions | attfdwoptions >> ----------+-------------------------------+----------+------ >> ---------+--------+--------+----------+-------------+------- >> ----+----------+------------+----------+------------+------- >> ----+--------------+------------+-------------+------------- >> -+--------+------------+--------------- >> 6097850 | ........pg.dropped.6........ | 0 | 0 | >> 8 | 6 | 0 | -1 | -1 | f | p = | >> d | f | f | t | t | = 0 >> | 0 | | | >> 6097850 | ........pg.dropped.10........ | 0 | 0 | >> 1 | 10 | 0 | -1 | -1 | f | p = | >> c | f | f | t | t | = 0 >> | 0 | | | >> >> >> >> >> when i create this table user_mobile_contact_hashes , the function works >> well. i try to delete those 2 dropped column info from system catalog ta= ble >> , but it can't work and got other problems. >> > > It is most bad idea! Newer delete anything from system tables. Now, the > system catalogue is broken. > > The correct fix for first issue is a VACUUM FULL. Second issue - you can > try drop table and recreate it, > > Regards > > Pavel > > >> >> putong-contacts=3D# select insert_user_mobile_contact_hashes(4009, '', >> ARRAY['7bd94067bb0597bf995c4eb46364a4ce'], ARRAY[' >> c55288a186d0e6f0e3fb3b66deb65366'], ARRAY[''], false); >> ERROR: catalog is missing 2 attribute(s) for relid 6097850 >> >> Can you explain this issue and give me some advise how to handle this . >> thanks a lot . >> >> >> -- >> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> > > > >
В списке pgsql-bugs по дате отправления:
Следующее
От: Michael PaquierДата:
Сообщение: Re: got some errors after upgrade poestgresql from 9.5 to 9.6