Re: Tablespace setup issue - Mailing list pgsql-general

Sébastien Bihorel wrote:
> Hi,
> 
> I am new to this list and reaching out because I am having troubles 
> setting up a local PostgreSQL database on my laptop. Maybe, as a 
> preamble, I should say that I am pretty much a novice trying to teach 
> myself and get practical experience about database and SQL queries (I 
> have some general idea about relational DB structure and operation but 
> no actual experience creating a DB or writing SQL queries).
> 
> Some background about my setup:
> - OS: my laptop runs Linux Mint 19.1 (a flavor of ubuntu 18.04); psql 
> 12.1 was installed along with pgadmin 4. There is a single user (ie, 
> sebastien) on this machine and the group postgres exists.

> I have also successfully created a database (sebastien) for the new 
> superuser sebastien, and a schema (test) within this database.
> 
> Now, I would like to assign a particular disk location for the 
> tablespace used by this database but I am getting all kinds of errors 
> apparently linked to folder permissions.
> 
> The location is /home/sebastien/data/pgdata96_sebastien

This is probably not what going to do what you want.  At best it will be 
a spaghetti pile of mixed up permissions, working against several sets 
of common practice and security restrictions, to get it to work.  I 
wouldn't be surprised if you ran into SELinux or AppArmor restrictions 
too (or another similar system, I think there are a couple more).


> has the following properties:
> 
> /home/sebastien/data $ ls -l
> drwxr-xr-x 2 sebastien postgres 4096 Nov 19 09:14 pgdata96_sebastien

> The manual states "The location must be an existing, empty directory 
> that is owned by the PostgreSQL operating system user." So, I am not 
> sure what I am doing wrong. Web searches ran on these error messages did 
> not enlighten me more...

The files and directories that the Postgres server manages MUST be owned 
by the OS user "postgres".  This directory is still owned by your OS 
user sebastien.  You will probably also have to play games with the 
permissions on /home/sebastien and /home/sebastien/data to get this to 
work at all.

Your OS user will not be able to usefully read any files that Postgres 
creates directly, so there's not much value in putting them outside of 
the default directory tree under /var/lib/postgres.

Postgres is pretty good about managing its own data files;  unless you 
need to quickly glue on more storage without disturbing an active 
production server, or need to put a particular table or database on 
expensive-but-faster (or possibly slower-but-much-larger-and-cheaper) 
storage, you probably don't need to use tablespaces.

For experimenting with them it would be a lot simpler to create a 
directory (or two, or three) under /var/lib/postgres for them, owned by 
the postgres OS user.

-kgd