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
