Does anybody know of a solution out of that on Linux? Or is there a dynamic way to put $PGDATA/base/pgsql_tmp into RAM without blocking it completely like a ram disk?
We put this in our startup script just before starting the actual database:
for x in $(find ${PGDATA}/base -mindepth 1 -maxdepth 1 -type d); do nDBNum=${x##*/} sDir=${DBSHM}/${nDBNum}
if [ ! -d "$sDir" ]; then su -c "mkdir $sDir" - $PGUSER fi done
Where PGDATA, DBSHM, and PGUSER are all set in /etc/sysconfig/postgresql. But DBSHM defaults to /dev/shm/pgsql_tmp on our Linux box.
Basically what this does is ensures a directory exists for each of your databases in shared memory. Then all we did was symlink the pgsql_tmp folder to point to those shared-memory directories. Many systems default so that up to half of total RAM can be used this way, so we're not at any risk with 64GB on our main nodes.
We already run a custom init.d script anyway because we needed something LSB compatible for Pacemaker. I highly recommend it. :)
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@peak6.com