Re: Tablespace setup issue

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