backends die on pg_dump, and spurious files? - Mailing list pgsql-admin

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/<dbname> 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.<number>
     sessions.<number>

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