Re: Tablespace setup issue - Mailing list pgsql-general

On Nov 21, 2019, at 7:46 AM, Sébastien Bihorel <pomchip@free.fr> wrote:

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@free.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.