"Birchall, Austen" <austen.birchall@metoffice.gov.uk> wrote:
> Up to now I have been creating the sub-directories that (hold)
> the OS level files that are associated with individual
> tablespaces directly under the
>
> ..../psql/data directory i.e.
>
> db=# create tablespace test owner test_admin location
> '/var/lib/pgsql/data/test';
> Is this standard/best practise - I can see that the data
> directory could get 'cluttered' in a db that has many
> tablespaces.
I've never spent a lot of time browing the subdirectories. Why
would "cluttered" matter?
> Is there a standard/recommended alternative for tablespace
> location for db which have many tablespaces?
It is not a good idea to put tablespaces under the $PGDATA
directory. For one thing, it creates challenges for backup
software, which generally tries to copy everything under the
$PGDATA directory and then add everything from each non-default
tablespace. Without a lot of care, you wind up copying everything
in every non-default tablespace twice.
Also, it doesn't really provide much benefit to use tablespaces
this way. Unless the tablespace is on a separate filesystem, you
don't get any performance benefit, unless the type of filesystem
being used performs poorly with a large number of files in one
subdirectory. If they are just being used for logical separation,
then separate schemas (namespaces) is a better way to do that.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company