[GENERAL] : Postgresql Error after recovery
Hello Community, We have a critical situation where-in our production database server got effected by Root Kit. When tried to build a replication site by copying the data directory to a different server, so many files got missed while copying (this is due to root kit effect). So, we moved the missing files individually one-by-one and the situation was a bit better. We are struck with the following issue - We are unable to connect to the databases, when we try to do so, we are getting the below error - postgres=# \c oltp_db FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 HINT: Please REINDEX it. Previous connection kept Can anyone please help us. Regards, VB -- DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of *Ver sé Innovation Pvt Ltd*.
Re: [GENERAL] : Postgresql Error after recovery
\ postgres=# \c oltp_db FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 HINT: Please REINDEX it. Previous connection kept Can anyone please help us. You have HINT given to REINDEX it. Use reindexdb from OS. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] : Postgresql Error after recovery
On Wed, Jul 4, 2012 at 2:11 PM, Raghavendra raghavendra@enterprisedb.com wrote: \ postgres=# \c oltp_db FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 HINT: Please REINDEX it. Previous connection kept Can anyone please help us. You have HINT given to REINDEX it. Use reindexdb from OS. One more point, reindex the system catalog as reindexdb -s --Raghav
Re: [GENERAL] : Postgresql Error after recovery
On Wed, Jul 4, 2012 at 2:12 PM, Raghavendra raghavendra@enterprisedb.com wrote: On Wed, Jul 4, 2012 at 2:11 PM, Raghavendra raghavendra@enterprisedb.com wrote: \ postgres=# \c oltp_db FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 HINT: Please REINDEX it. Previous connection kept Can anyone please help us. You have HINT given to REINDEX it. Use reindexdb from OS. Re-index option did not work. Single user mode option did not work as well - [postgres@localhost bin]$ postgres --single oltp_db -E -D /usr/local/postgresql-9.0.1/data 2012-07-04 04:30:47 CDT [26072]: [1-1] user=,db= FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 2012-07-04 04:30:47 CDT [26072]: [2-1] user=,db= HINT: Please REINDEX it. Last option would be dump and restore. We need to avoid that reduce the downtime. Regards, Venkat -- DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of *Ver sé Innovation Pvt Ltd*.
Re: [GENERAL] : Postgresql Error after recovery
Re-index option did not work. Single user mode option did not work as well - [postgres@localhost bin]$ postgres --single oltp_db -E -D /usr/local/postgresql-9.0.1/data 2012-07-04 04:30:47 CDT [26072]: [1-1] user=,db= FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 2012-07-04 04:30:47 CDT [26072]: [2-1] user=,db= HINT: Please REINDEX it. Last option would be dump and restore. We need to avoid that reduce the downtime. How about starting the instance with ignore_system_indexes=true ? And do dump restore. Am not sure on this procedure. Should someone here would have better idea on this. http://www.postgresql.org/docs/9.0/interactive/runtime-config-developer.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] : Postgresql Error after recovery
I have restarted the cluster with ignore_system_indexes=true and was able to connect to databases. I have started re-indexing, seems to be working fine. Will get back if i find further issues. Regards, Venkat On Wed, Jul 4, 2012 at 3:35 PM, Raghavendra raghavendra@enterprisedb.com wrote: Re-index option did not work. Single user mode option did not work as well - [postgres@localhost bin]$ postgres --single oltp_db -E -D /usr/local/postgresql-9.0.1/data 2012-07-04 04:30:47 CDT [26072]: [1-1] user=,db= FATAL: index pg_attribute_relid_attnum_index contains unexpected zero page at block 0 2012-07-04 04:30:47 CDT [26072]: [2-1] user=,db= HINT: Please REINDEX it. Last option would be dump and restore. We need to avoid that reduce the downtime. How about starting the instance with ignore_system_indexes=true ? And do dump restore. Am not sure on this procedure. Should someone here would have better idea on this. http://www.postgresql.org/docs/9.0/interactive/runtime-config-developer.html -- DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of *Ver sé Innovation Pvt Ltd*.
Re: [GENERAL] View parsing
Hi, how about this one? SELECT n.nspname, c.relname, a.attname FROM pg_depend d INNER JOIN pg_class c ON (c.oid = refobjid) INNER JOIN pg_attribute a ON (c.oid = a.attrelid AND d.refobjsubid = a.attnum) INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN pg_rewrite rw ON (d.objid = rw.oid) WHERE rw.ev_class = 'MySchema.MyViewName'::regclass ORDER BY n.nspname, c.relname, a.attname This will answer for all involved tables and columns - not only produced by a view, but also involved in joins and sub-queries. Assumption: rules are deprecated and used only by PG internal engine (for views). If this assumption is not correct You should narrow pg_rewrite results to find proper rule. Reagards, Bartek
Re: [GENERAL] TG_COLUMNS_UPDATED
On Tue, Jul 3, 2012 at 2:47 PM, Bartosz Dmytrak bdmyt...@gmail.com wrote: [snip] FOR v_row IN SELECT attname FROM pg_attribute WHERE attrelid = (quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME))::text::regclass AND attnum 0 ORDER BY attnum LOOP EXECUTE 'SELECT NOT ($1.' || quote_ident(v_row.attname) || ' = $2.' || quote_ident(v_row.attname) || ')' INTO v_match USING NEW, OLD; v_match_array = array_append (v_match_array, v_match); END LOOP; A few problems with this function: 1.) The comparison should be using 'IS DISTINCT FROM' instead of != to handle NULLs 2.) The query against pg_attribute should respect 'attisdropped'. (There are also other ways to iterate over NEW/OLD fields, e.g. using hstore.) 3.) This solution doesn't solve the OP's stated goal: It would allow me to know whether various timestamp columns in the row were unlucky enough to have been set to the same exact value already existing in the table *versus* were simply not set by the UPDATE statement. I'm not sure how feasible it'd be add a new TG_ variable available to plpgsql for the problem above. Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question about source download site.
Hi, I have a small question about the site of PostgreSQL source. http://www.postgresql.org/ftp/source/ Now I can see some directories of PostgreSQL 9.2beta1/2. - v9.2.0beta1 - v9.2.0beta2 - v9.2beta1 - v9.2beta2 It seems to be pointed the same direcotory with each version. - v9.2beta1 = v9.2.0beta1 - v9.2beta2 = v9.2.0beta2 I don't know why it's duplicated. acording to my memory, it's not duplicated when 9.1 has released. anyone knows the reason ? Tomonari Katsumata -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
