backends die on pg_dump, and spurious files?

Поиск
Список
Период
Сортировка
Искать

backends die on pg_dump, and spurious files?

От:
Charles Martin <martin@chasm.org>
Дата:
Please help me with my database problem.  Our backends keep dying on
us, interrupting service for our web application.  This is an urgent
problem for us on our live web site.  We run 6.5.3 on FreeBSD 3.4.

There are at least three major problems:

   * VACUUM ANALYZE and pg_dump fail on one of our tables.

   * Another table regularly gets wedged; luckily, it is our session
     tracking table and can be dropped and re-created necessary.  This
     is obviously a non-optimal solution.

   * Thousands of spurious files have been created in the
     data/base/ directory.

Since pg_dump fails, I can't backup or recreate our database.  Perhaps
these problems all stem from using VACUUM on a live database, since
the mail archives seem to indicate that this is suspect?  Though I see
from other messages that some people run VACUUM hourly as a cron job!

Here is a deeper explanation of the problems we are encountering:


(1) The "users" table can't be VACUUM ANALYZED or pg_dump'ed:

The table is defined:

     CREATE TABLE users (user_id SERIAL PRIMARY KEY, ...);

Here's what I get from VACUUM and pg_dump; note that this takes place
with a "virgin" postmaster, ie, there are positively no other backends
operating.  I will show the results of VACUUM, VACUUM VERBOSE, VACUUM
VERBOSE ANALYZE, and pg_dump:

db000103=> vacuum users;
NOTICE:  Rel users: TID 4/28: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel users: TID 162/20: OID IS INVALID. TUPGONE 0.
ERROR:  No one parent tuple was found.

db000103=> vacuum verbose users;
NOTICE:  --Relation users--
NOTICE:  Rel users: TID 4/28: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel users: TID 162/20: OID IS INVALID. TUPGONE 0.
NOTICE:  Pages 306: Changed 100, Reapped 303, Empty 0, New 0; Tup
1961: Vac 4719, Keep/VTL 0/0, Crash 0, UnUsed 6964, MinLen 148, MaxLen
208; Re-Using: Free/Avail. Space 2156776/2149392;
EndEmpty/Avail. Pages 0/302. Elapsed 0/0 sec.
ERROR:  No one parent tuple was found.

db000103=> vacuum verbose analyze users;
NOTICE:  --Relation users--
NOTICE:  Rel users: TID 4/28: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel users: TID 162/20: OID IS INVALID. TUPGONE 0.
pqReadData() -- backend closed the channel unexpectedly.
	This probably means the backend terminated abnormally
	before or while processing the request.
We have lost the connection to the backend, so further processing is 
impossible.  Terminating.

$ pg_dump db000103 | gzip > db.0404.gz
pqWait() -- connection not open
PQendcopy: resetting connection
SQL query to dump the contents of Table 'users' did not execute
correctly.  After we read all the table contents from the backend,
PQendcopy() failed.  Explanation from backend: 'pqWait() -- connection
not open
'.
The query was: 'COPY "users" TO stdout;
'.

Why does the backend keep closing?!


(2) Thousands of spurious files in our data/base directory.

The extra files are of the form:

     content_pkey.
     sessions.

It seems at least plausible that these extra files keep us from
creating a primary key index for our content table, and that they
probably regularly mess up our sessions table.

The tables are defined:

     CREATE TABLE content (content_id SERIAL PRIMARY KEY, ...);
     CREATE TABLE sessions (cookie TEXT PRIMARY KEY, ...);

After a drop all indices, I try to rebuild them.  That works for most
tables, but not this one:

db000103=> create unique index content_pkey on content
db000103-> using btree (content_id int4_ops);
ERROR:  cannot create content_pkey

Here's the [excerpted] directory listing:

$ cd data/base/db000103; ls
PG_VERSION				content_pkey.7159
areas					content_pkey.716
areas_area_id_seq			content_pkey.7160
books					content_pkey.7161
books_book_id_seq			content_pkey.7162
content					content_pkey.7163
content_content_id_seq			content_pkey.7164
content_myindex				content_pkey.7165
content_pkey				content_pkey.7166
content_pkey.1				content_pkey.7167
content_pkey.10				content_pkey.7168
content_pkey.100			content_pkey.7169
content_pkey.1000			content_pkey.717
content_pkey.10000			content_pkey.7170
content_pkey.10001			content_pkey.7171
content_pkey.10002			content_pkey.7172
[...thousands of lines deleted from directory listing...]
content_pkey.12828			content_pkey.9998
content_pkey.12829			content_pkey.9999
content_pkey.1283			docs
content_pkey.12830			docs_doc_id_seq
content_pkey.12831			facts_fact_id_seq
content_pkey.12832			hospitals
content_pkey.12833			hospitals_hospital_id_seq
content_pkey.12834			links
content_pkey.12835			links_link_id_seq
content_pkey.12836			logins
content_pkey.12837			logins_id_seq
content_pkey.12838			nodes
content_pkey.12839			nodes_node_id_seq
content_pkey.1284			offices
content_pkey.12840			offices_office_id_seq
content_pkey.12841			pcal
content_pkey.12842			pg_aggregate
content_pkey.12843			pg_am
content_pkey.12844			pg_amop
content_pkey.12845			pg_amproc
content_pkey.12846			pg_attrdef
content_pkey.12847			pg_attrdef_adrelid_index
content_pkey.12848			pg_attribute
content_pkey.12849			pg_attribute_attrelid_index
content_pkey.1285			pg_attribute_relid_attnam_index
content_pkey.12850			pg_attribute_relid_attnum_index
content_pkey.12851			pg_class
content_pkey.12852			pg_class_oid_index
content_pkey.12853			pg_class_relname_index
content_pkey.12854			pg_description
content_pkey.12855			pg_description_objoid_index
content_pkey.12856			pg_index
content_pkey.12857			pg_indexes
content_pkey.12858			pg_inheritproc
content_pkey.12859			pg_inherits
content_pkey.1286			pg_internal.init
content_pkey.12860			pg_ipl
content_pkey.12861			pg_language
content_pkey.12862			pg_listener
content_pkey.12863			pg_opclass
content_pkey.12864			pg_operator
content_pkey.12865			pg_proc
content_pkey.12866			pg_proc_oid_index
content_pkey.12867			pg_proc_proname_narg_type_index
content_pkey.12868			pg_proc_prosrc_index
content_pkey.12869			pg_relcheck
content_pkey.1287			pg_relcheck_rcrelid_index
content_pkey.12870			pg_rewrite
content_pkey.12871			pg_rules
content_pkey.12872			pg_sorttemp91869.0
content_pkey.12873			pg_sorttemp91869.1
content_pkey.12874			pg_sorttemp91869.2
content_pkey.12875			pg_sorttemp91869.3
content_pkey.12876			pg_sorttemp91869.4
content_pkey.12877			pg_sorttemp91869.5
content_pkey.12878			pg_sorttemp91869.6
content_pkey.12879			pg_sorttemp92495.0
content_pkey.1288			pg_sorttemp92495.1
content_pkey.12880			pg_sorttemp92495.10
content_pkey.12881			pg_sorttemp92495.11
content_pkey.12882			pg_sorttemp92495.12
content_pkey.12883			pg_sorttemp92495.13
content_pkey.12884			pg_sorttemp92495.2
content_pkey.12885			pg_sorttemp92495.3
content_pkey.12886			pg_sorttemp92495.4
content_pkey.12887			pg_sorttemp92495.5
content_pkey.12888			pg_sorttemp92495.6
content_pkey.12889			pg_sorttemp92495.7
content_pkey.1289			pg_sorttemp92495.8
content_pkey.12890			pg_sorttemp92495.9
content_pkey.12891			pg_statistic
content_pkey.12892			pg_tables
content_pkey.12893			pg_trigger
content_pkey.12894			pg_trigger_tgrelid_index
content_pkey.12895			pg_type
content_pkey.12896			pg_type_oid_index
content_pkey.12897			pg_type_typname_index
content_pkey.12898			pg_user
content_pkey.12899			pg_views
content_pkey.129			pg_vlock
content_pkey.1290			postgres.core
content_pkey.12900			practices
content_pkey.12901			practices_practice_id_seq
content_pkey.12902			sections
content_pkey.12903			sections_section_id_seq
content_pkey.12904			sessions
content_pkey.12905			sessions.1
content_pkey.12906			sessions.10
content_pkey.12907			sessions.100
content_pkey.12908			sessions.1000
content_pkey.12909			sessions.1001
[...thousands of lines deleted from directory listing...]
content_pkey.7150			sessions.998
content_pkey.7151			sessions.999
content_pkey.7152			topics
content_pkey.7153			topics_topic_id_seq
content_pkey.7154			uploads
content_pkey.7155			uploads_upload_id_seq
content_pkey.7156			users
content_pkey.7157			users_user_id_seq
content_pkey.7158

So, why are all these files being created for the content_pkey index
and the sessions table?  Why not for other indices and tables?  Can I
safely delete them?  Might they be screwing up our database
operations?

Any help appreciated.  This is really a very serious problem for
us.  Thank you for your time.

Charles

Re: backends die on pg_dump, and spurious files?

От:
Charles Martin <martin@chasm.org>
Дата:
In accord with Murphy's Fifth Postulate, after being driven to send a 
message to pgsql-admin, I have since solved the problem.  Thus, I send this 
message to record the answers for posterity.

At 12:06 AM 4/5/00 -0700, Charles Martin wrote:
>There are at least three major problems:
>
>   * VACUUM ANALYZE and pg_dump fail on one of our tables.

There were two bogus rows in the "users" table.  One with OID 0 and one 
with OID 1634628924; how these rows got into the table I have no idea, but 
they were filled with bogus data.  Deleting these rows and vacuuming fixed 
the problems with this table (though I still see "no parent tuple" messages).

   * Thousands of spurious files have been created in the
>     data/base/ directory.

I just deleted all these spurious files.  (They were all of zero 
length.)  I have no idea why they were created in the first place.

Now that I can pg_dump, time for PostgreSQL 7.0 beta 2!

FAQ