Обсуждение: Urgent !!! Please Help Me
Hello Every body:
I have implemented psql version 7.4.2 on Debian linux version 3.2 and it was running very well. At this moment it is being crashed when executing a single select statement. Details are mentionded as follows:
DB Dump size before this problem is arised: 95 MB
DB ENCODING is "EUC_JP"
After some investigation the problem is narrowed down :
-- A table namly t60 has 411120 rows .
--There is no Index for the t60 table .
-- t60 has column named c1, c2, c3 .
-- Statement " SELECT c1, c3 FROM t60 " Returns All rows of specified coumn.
But when execute Statement " SELECT c2 FROM t60 " causes Error like
&nbs p;
1." Invalid memory alloc request size 82127290 "
2. "ERROR: out of memory
DETAIL: Failed on request of size 875573295"
DETAIL: Failed on request of size 875573295"
I have then made an B-Tree Index of t60 and when execute "REINDEX t60 " then get error
"ERROR: index row requires 336336 bytes, maximum size is 8191"
I have also tried to Make a full dump of the DB but failed but success fully taken schema wise dump accept the the schema that holds t60 table.
Then I have copied full pgsql folder where pgsql is installed to another computer with same configaration and started the pgsql server on that mchaine successfully.And did all operation again on that DB as mentioned above .But gotten same errors again.
Now I have restored the DB from Previous bak cup db dump. But my client says that they have entered many data that stored on t60 table
and there is no way reenter data again.
I will be glad if any body let me know ASAP :
1.Why the problem is Happenning?
2. What is the solution to restore full datbase?
Regards
R.I. Russell
Yahoo! Mail
Bring photos to life! New PhotoMail makes sharing a breeze.
r irussel wrote:
> Hello Every body:
>
> I have implemented psql version 7.4.2 on Debian linux version 3.2
> and it was running very well. At this moment it is being crashed when
> executing a single select statement. Details are mentionded as
> follows:
You really should be running something later than 7.4.2 - there are a
lot of bug-fixes between that and 7.4.12
> DB Dump size before this problem is arised: 95 MB DB ENCODING is
> "EUC_JP"
>
> After some investigation the problem is narrowed down :
>
> -- A table namly t60 has 411120 rows .
> --There is no Index for the t60 table .
> -- t60 has column named c1, c2, c3 .
> -- Statement " SELECT c1, c3 FROM t60 " Returns All rows of specified
> coumn.
>
> But when execute Statement " SELECT c2 FROM t60 " causes Error like
>
> 1." Invalid memory alloc request size 82127290 " 2. "ERROR: out of
> memory DETAIL: Failed on request of size 875573295"
It looks like column c2 on t60 has been corrupted on at least one row.
Have you experienced any crashes on this database?
Anyway - the simplest thing to do is to copy the good rows into another
table and skip only the damaged data.
1. CREATE TABLE new_t60 AS SELECT * FROM t60 LIMIT 0;
2. INSERT INTO new_t60 SELECT * FROM t60 WHERE c1 BETWEEN ??? AND ???
Adjusting the ??? will let you work around the problem row(s).
Column c1 might not be the best choice - your primary key is what you
want to use.
3. Dump the new table's data.
4. Drop table t60 (remove any foreign-key references and turn off
triggers first)
5. Recreate table t60
6. Restore new_t60's data into t60 and restore foreign-key references etc.
Then, you can dump the new table and restore it with just the damaged
row(s) needing to be replaced/repaired.
Then, you need to upgrade to 7.4.12 and also figure out why this
happened. Have you had crashes? Is your RAM good? Are your disks syncing
when they say they are?
--
Richard Huxton
Archonet Ltd
Am 2006-03-13 23:58:40, schrieb r irussel:
> Hello Every body:
>
> I have implemented psql version 7.4.2 on Debian linux version 3.2
Where does this PostgreSQL version come from?
And there is NO version 3.2 of Debian GNU/Linux.
Only 3.0 (Woody), 3.1 (Sarge) and maybe 4.0 (Etch).
You should install at least to Sarge 3.1 and PostgreSQL 7.4.7-6sarge1.
Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant
--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)