PDA

View Full Version : Pg Admin III backup fails



Bibabu6
12-10-2009, 07:58 PM
Hi,

Today I tried to do a backup of my huge database to restore it on a new hard disk drive, unfortunately it's always a failure, I have the following message:

g_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not read block 154589 of relation 1663/16722/16953: Invalid argument
pg_dump: The command was: COPY public.postflopactions_month (compiledplayerresults_id, streetactiontype_id, count, betorraise, checks, checkcall, checkfold, checkraise, bet, betcall, betraise, betfold, raise, raisecall, raiseraise, raisefold, call, callcall, callraise, callfold, fold, unopenedinposition, betunopenedinposition, totalbets, totalcalls, sawshowdown, wonshowdown) TO stdout;
pg_dump: *** aborted because of error

Le process a retourné le code de sortie 1.

Is my DB corrupted ? Is there something I can do ?

TY for your reply.

Patvs
12-10-2009, 08:28 PM
Try a vacuum/analyse.
If you get an error doing that, your database is corrupt. (although you could try to FIX the corrupt index)

If you don't get an error doing a vacuum/analyse.... try the backup again.
How large is your database?
The .backup file usually it 1/10th the size of your database. So make sure you have enough diskspace.

Also try a "export database" in dbcontrolpanel/database management as an alternative backup option.

Bibabu6
12-10-2009, 08:35 PM
TY for your reply,

I tried another time, I have the same error message but it saves a file of 200 Mo, I purged almost all my hands and cause I had datamined hands my DB still weights around 5 gigas.

"although you could try to FIX the corrupt index" : how do you do that please, fix indexes in data control panel, what are the effects ? Is it safe ?

"Also try a "export database" in dbcontrolpanel/database management as an alternative backup option": I don't see this option in my DCP sorry.

Bibabu6
12-10-2009, 08:47 PM
Try a vacuum/analyse.
If you get an error doing that, your database is corrupt. (although you could try to FIX the corrupt index)

If you don't get an error doing a vacuum/analyse.... try the backup again.
How large is your database?
The .backup file usually it 1/10th the size of your database. So make sure you have enough diskspace.

Also try a "export database" in dbcontrolpanel/database management as an alternative backup option.

Vaccum / analyse fucks up dramatically

.................................................
.................................................
................................................

bla bla bla bla ...

0 pages are entirely empty.
CPU 0.00s/0.03u sec elapsed 0.71 sec.INFO: analyzing "public.playervsplayer_hero"INFO: "playervsplayer_hero": scanned 356 of 356 pages, containing 55836 live rows and 0 dead rows; 3000 rows in sample, 55836 estimated total rowsINFO: vacuuming "public.compiledplayerresults_month"INFO: scanned index "compiledplayerresults_month_idx1" to remove 2119 row versions
DETAIL: CPU 0.07s/0.25u sec elapsed 0.84 sec.INFO: "compiledplayerresults_month": removed 2119 row versions in 1450 pages
DETAIL: CPU 0.10s/0.03u sec elapsed 0.39 sec.INFO: index "compiledplayerresults_month_idx1" now contains 1297698 row versions in 3562 pages
DETAIL: 1612 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: "compiledplayerresults_month": found 376 removable, 1297698 nonremovable row versions in 92976 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 603094 unused item pointers.
3009 pages contain useful free space.
0 pages are entirely empty.
CPU 1.04s/0.78u sec elapsed 29.11 sec.INFO: analyzing "public.compiledplayerresults_month"INFO: "compiledplayerresults_month": scanned 3000 of 92976 pages, containing 41881 live rows and 0 dead rows; 3000 rows in sample, 1297976 estimated total rowsINFO: vacuuming "public.compiledplayerresultspositionaction_month"INFO: scanned index "compiledplayerresultspositionaction_month_idx1" to remove 1723 row versions
DETAIL: CPU 0.04s/0.18u sec elapsed 1.01 sec.INFO: "compiledplayerresultspositionaction_month": removed 1723 row versions in 1291 pages
DETAIL: CPU 0.09s/0.04u sec elapsed 0.92 sec.INFO: index "compiledplayerresultspositionaction_month_idx1" now contains 1297698 row versions in 3562 pages
DETAIL: 1430 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: "compiledplayerresultspositionaction_month": found 309 removable, 1297698 nonremovable row versions in 76453 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 602537 unused item pointers.
1333 pages contain useful free space.
0 pages are entirely empty.
CPU 0.98s/0.67u sec elapsed 35.15 sec.INFO: analyzing "public.compiledplayerresultspositionaction_month"INFO: "compiledplayerresultspositionaction_month": scanned 3000 of 76453 pages, containing 50920 live rows and 0 dead rows; 3000 rows in sample, 1297662 estimated total rowsINFO: vacuuming "public.postflopactions_month"
ERROR: could not read block 154589 of relation 1663/16722/16953: Invalid argument

ERROR: could not read block 154589 of relation 1663/16722/16953: Invalid argument

Patvs
12-10-2009, 08:49 PM
A 5 GB database is really small.

Yes,.. use the fix index in database management. It's safe, and can fix a index.
Also do a complete REINDEX of the whole database in pgAdminIII.






"Also try a "export database" in dbcontrolpanel/database management as an alternative backup option": I don't see this option in my DCP.



Sorry it's called "Export Hands" in Database Control Panel.


Edit: so it seems your database in corrupt... you can still try a REINDEX.... but it's best to just reimport all your handhistories into a new database. Purge again. And backup again.

Bibabu6
12-10-2009, 08:51 PM
A 5 GB database is really small.

Yes,.. use the fix index in database management. It's safe, and can fix a index.
Also do a complete REINDEX of the whole database in pgAdminIII.



Wich one first please ?

TY for your reply.

Patvs
12-10-2009, 08:57 PM
Do both..

Doesn't matter in which order.

..but it's best to just reimport all your handhistories into a new database. Purge again. And backup again.