PostgreSQL: location of DB files

adventuremalcolm · 10-07-2011, 09:51 PM

I want to keep my Postgresql database files on a separate volume from the OS files. I have a 2TB disc which I plan to mount to the location where PostgreSQL keeps it's files. I have assumed it is at /var/lib/postgresql/8.x/.

My problem is, when I mount a blank volume to that location, I can still see the default database files (postgres, template0 and template1) using psql. If the volume is blank, these shouldn't be there.

Further, I create a database, say "test1" in psql. When I then check the contents of my disc, there is nothing there.

This all means to me that postgresql is actually storing its files somewhere else. Would anybody know where that might be?

sag47 · 10-07-2011, 10:23 PM

Create a new database in psql and then find all files modified in the past 5 mins.

Code:

find / -mtime -0.003 -not -path '/proc*' -not -path '/sys*' 2> /dev/null

or past 2 min...

Code:

find / -mtime -0.001 -not -path '/proc*' -not -path '/sys*' 2> /dev/null

The followin excludes the paths /proc and /sys

Code:

-not -path '/proc*' -not -path '/sys*'

SAM

adventuremalcolm · 10-09-2011, 08:50 PM

You're a Wizard, SAM!

Thanks for the excellent idea. I do in fact find a bunch of files changed in

/var/lib/postgresql/8.4/main/base/

... which I must assume are those for the test db I just created.

From here, I guess I'll run through the whole mount operation, create another test DB and see if the files for that show up on the mounted volume.

Thanks again,
Malcolm