I used PostgreSQL 8.1 on Gentoo/Linux 2.6.14r5. My db server's disk space looks like below:

db postgresql # df -l
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda3              9775248   2018528   7756720  21% /
udev                   1557872        88   1557784   1% /dev
shm                    1557872         0   1557872   0% /dev/shm
/dev/sda4            281096760 244270836  36825924  87% /var/lib/postgresql
/dev/sdb1            961402192 244780080 667785712  27% /mnt/sdb1

I can't restart ./etc/init.d/postgresql because the subdirectory data under /var/lib/postgresql is empty. The PostgreSQL8.1 was updated from 8.0, so there is a data.old in /var/lib/postgresql. When I execute 'du -b', the result is below:

     postgresql # du -b
471     ./.ssh
580     ./data
7697    ./paul/Fifthwindow-RogersBuck
19673   ./paul/Fifthwindow-Tattoo/Output
20633   ./paul/Fifthwindow-Tattoo
13762   ./paul/Fifthwindow-Beard/Output
14493   ./paul/Fifthwindow-Beard
3036    ./paul/Fifthwindow-Touch1/Output
10789   ./paul/Fifthwindow-Touch1
56931   ./paul
3624120 ./data.old/base/1
3624120 ./data.old/base/10792
3624120 ./data.old/base/10793
48      ./data.old/base/16394/pgsql_tmp
248802448893    ./data.old/base/16394
48      ./data.old/base/backup
248813321469    ./data.old/base
11370   ./data.old/paul/output_files
14332   ./data.old/paul
122952  ./data.old/pg_subtrans
48      ./data.old/pg_twophase
57416   ./data.old/pg_multixact/members
49224   ./data.old/pg_multixact/offsets
106736  ./data.old/pg_multixact
4880603 ./data.old/global
316494192       ./data.old/pg_clog
48      ./data.old/pg_xlog/archive_status
536872320       ./data.old/pg_xlog
48      ./data.old/pg_tblspc
249678076379    ./data.old
27023   ./scripts/cron/daily
917     ./scripts/cron/weekly
28036   ./scripts/cron
861     ./scripts/runOnce
599794  ./scripts/manual
628811  ./scripts
171463723       ./output
249850258001    .

When I execute 'pg_dump -h my.host.ip.0 -p 5432 -U postgres -F t -b -v -f "/some/directory/backup.file" mydb', the message is below:

pg_dump: dumping contents of table _selections_by_content_last30days
pg_dump: dumping contents of table _selections_by_content_last365days
pg_dump: dumping contents of table actionlog
pg_dump: ERROR:  could not count blocks of relation 1663/16394/17943: No such file or directory
pg_dump: SQL command to dump the contents of table "actionlog" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR:  could not count blocks of relation 1663/16394/17943: No such file or directory
pg_dump: The command was: COPY public.actionlog (eventdetail, eventdatetime, eventtypeid, consoleid, albumid, trackid, sequenceid, sessionid, contentid, actionlogid, fileid) TO stdout;
pg_dump: *** aborted because of error

please help me! Any idea will be appreciated!



Finally, I figured it out. That's because the disk partition folder 1663/16394/17943 was on another physical hard driver. So I had to mount it first and then did the next step.

That was a very old system. Fortunately, I didn't have to take care it any more.


Was your Postgres server accessible from the Internet? There was a Postgres vulnerability published lately, but version 8.1 is not supported since 2010 so it did not receive security updates. Using this version is unreasonable if your OS vendor does not provide backported updates — like for example RHEL5 or CentOS5 does. Your OS kernel is from about 2006 — does your OS still receive security updates?

It looks like your system was compromised and all your data simply deleted. If you don't have backups then I suppose it is unrecoverable (for mere mortals that is). If your database server is still running you can try to dump data from single tables — it looks like _selections_by_content_last30days and _selections_by_content_last30days was successfully dumped, and actionlog table wasn't. If your database is still running it may have some open files which were deleted but won't be actually freed until it stops — there may be some recoverable data there.

Data from old 8.0 release can still be recoverable, but it's a complicated operation, as you'll need to compile and install old version or postgres to access it. It's better to do this on another server — copy it somewhere safe as soon as possible!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.