Re: Tablespace setup issue

Thank you everyone for your responses. It is great to see so much feedback.

Based upon all the responses, I was able to successfully set my tablespace
doing the following:

- Using /usr/data/pgdata96_sebastien as target tablespace directory
- Permissions were set to: chmod postgres:postgres
/usr/data/pgdata96_sebastien
- Starting psql using: sudo -u postgres psql
- Issuing:
- CREATE TABLESPACE sebdata LOCATION '/usr/data/pgdata96_sebastien';
- ALTER DATABASE sebastien set TABLESPACE sebdata;

One thing that is still a bit surprising to me is that even if I start psql
with the sebastien user, it seems that psql interacts with the file system
as postgres (at least, that's what I infer from the original error messages
I got when using the sebastien user and trying to set the tablespace to a
file owned by sebastien).

Thanks

On Thu, Nov 21, 2019 at 9:48 AM Sébastien Bihorel <pomchip(at)free(dot)fr> 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.
> - database: I have successfully created the new superuser sebastien
> List of roles
> Role name | Attributes |
> Member of
>
> -----------+------------------------------------------------------------+-----------
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
> {}
> sebastien | Superuser, Create DB +|
> {}
> | Password valid until infinity |
>
> 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 has the following
> properties:
>
> /home/sebastien/data $ ls -l
> drwxr-xr-x 2 sebastien postgres 4096 Nov 19 09:14 pgdata96_sebastien
>
> Starting psql as sebastien, I tried the following requests and got the
> following errors:
>
> sebastien=# CREATE TABLESPACE sebdata LOCATION
> '/home/sebastien/data/pgdata96_sebastien';
> ERROR: could not set permissions on directory
> "/home/sebastien/data/pgdata96_sebastien": Permission denied
> sebastien=# ALTER DATABASE sebastien set TABLESPACE
> '/home/sebastien/data/pgdata96_sebastien';
> ERROR: syntax error at or near "'/home/sebastien/data/pgdata96_sebastien'"
> LINE 1: ALTER DATABASE sebastien set TABLESPACE '/home/sebastien/dat...
>
> 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...
>
> I would greatly appreciated any pointers to reference material on these
> issues or advises on how to approach this.
>
> Thank you in advance for your time.
>