PostgreSQL prevents HDD from spin-down

linuxpostgresql

I am using my router with OpenWRT as a small server. To experiment a little with more advanced database administration in my programming projects I set up psql on it. The.server uses my HDD, which is attached via USB to the router. This is no high-availability.system,. primarily as a DB connection point inside my private network.

Everything is going alright so far. I just experienced that my hd-idle setup is not working anymore. I highly suspect PostgreSQL to be the reason for that. Since the drive is not really that quiet and it's standing in my living room, I was Keen to spin the HDD down if I didn't use it for an hour. I now register quite some regular I/O on the device and I guess it's logging or similar processes.
Is it possible to reduce the frequency of these helper processes down to maybe once a day?

Best Answer

OpenWRT doesn't provide anything that I know of anyway to spindown hard drives. From the documentation,

Unfortunately there is no solution that works always for everybody. The first option to try is provided by the package hd-idle, which will spin down any disk accessible via the SCSI layer. Install it and test if it can spindown the hard disk (let us assume that your disk is /dev/sda):

The other solution is hdparm -y. So what you're doing is using something to tell your drives when to spin down. The idea is you now either

Script a daemon to monitor for when use stops (such as in the case of unmount),
Periodically tell the hard drive to spin itself down and hope it doesn't wake.

If PostgreSQL wakes the drive up, that's absolutely to be expected. Like any decent database, there is always maintenance to be done, and even when you're not accessing that database it will autovacuum. You may consider

Disabling autovacuum (which will help). But, then you'll have to manually VACUUM. Even disabling autovacuum, I have no idea if the PostgreSQL daemon will wake back up the drive.
Shutting down PostgreSQL before you set the hard drives to spindown.

Related Solutions

PostgreSQL – Starting Server After HDD Crash Results in FAILED STATE

The real answer will be in the PostgreSQL logs, in /var/lib/pgsql/data/pg_log.

However, before you take any action: It is vital that you take a file system level copy of your database before attempting repair if any of your data is valuable to you. See http://wiki.postgresql.org/wiki/Corruption . You must copy the whole data directory. On Fedora that's /var/lib/pgsql/data by default, but verify that's correct for your install.

Based on the logs you've posted you certainly have some degree of database corruption. The storage that the database is on (the hard drive or file system) is most likely damaged. Take a copy NOW, and put it on a different hard drive or system.

Only once you have made a full file-system level copy of your data directory, try using pg_resetxlog to clear the damaged transaction logs and start your database. Even if it starts it is highly likely to be corrupt; you should pg_dump it then re-initdb it and restore the dump to the fresh instance.

If you still can't start it after a pg_resetxlog then post an updated log of the startup attempt after resetxlog. It's possible you'll need to start Pg in stand-alone mode with:

sudo -u postgres postgres --single -D /var/lib/pgsql/data -P -f i postgres

If that works, giving you a backend> prompt, try again after replacing the last "postgres" with the name of the DB you want to connect to. You should be able to SELECT, COPY data from tables, etc.

If that doesn't work, ie you can't start a standalone backend, then it's probably time to restore from backups - since you're sensible enough to have them. If anyone else reading this is in the same position, contact an experienced PostgreSQL consultant to see if they can recover data from your database. Be prepared to pay for their time and expertise.

Your file system is probably damaged

The severity of the damage to the PostgreSQL install suggests that your whole file system is probably damaged. You may wish to consider restoring the whole system from a backup or reinstalling it.

I would not trust this file system, fsck or no fsck.

SMART-test your drive

I also recommend that you run a SMART check on your hard drive with smartctl from smartmontools; assuming it's /dev/hda that'd be smartctl -d ata -a /dev/sda | less . Look for a failed health test, uncorrectable_sectors, a high read error rate, a reallocated_sector_count of more than 2 or 3, or a non-zero current_pending_sector. Run smartctl -d ata -t long /dev/sda to execute a non-destructive self test on your HDD; it won't interrupt normal functioning of the system. When the estimated time has elapsed run smartctl -d ata /dev/sda again and look at the self test log to see if it passed.

If anything looks less than perfect, replace the drive.

In future, consider automating this testing via smartd for early warning of drive failures.

(Content in this post was obsoleted by updates to the question. If you're troubleshooting a similar problem, look at this answer's edit history).

Postgresql – How to Restore Postgresql Cluster from data directory

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.