Re: Location of databases

2009/7/27 john shipley <johnwshipley(at)hotmail(dot)co(dot)uk>:
> I have just installed PostgreSQL, with a view to using it in conjunction
> with PSPP and also with OpenOffice Base.  I have created a test database
> called exampledb with a table in it.  Where do I locate the database,
> please?  A file search for the name reveals nothing in the filesystem,
> although the database and table are obviously there.

MySQL stores the database in a directory with the same name and the
tables in files with the names based on the table names. PostgreSQL
works differently. It does not store the databases/tables in files or
directories that are named after the databases/tables. For small
databases or ones where you don't need to worry too much about
performance you don't really need to know where the files are
(depending on how you're doing backups.)

On Debian and Ubuntu the files are stored under
/var/lib/postgresql/VERSION/main, e.g. /var/lib/postgresql/8.3/main.
Also, a "postgres" user gets created with /var/lib/postgresql as its
home directory, so you can "cd ~postgres" to get there.

On FreeBSD I think the username is pgsql as far as I remember, but I
think you can do the same "cd ~pgsql" trick there to find where the
files are being kept.

I believe you can tell PostgreSQL to store the data somewhere else by
using tablespaces (which I've never looked at, so I don't know the
details.)

--
Michael Wood <esiotrot(at)gmail(dot)com>