Tablespace setup issue

From: Sébastien Bihorel <pomchip(at)free(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Tablespace setup issue
Date: 2019-11-21 14:48:31
Message-ID: CABR8ZvrdNH9HyzjKpvxaiM4bdYZhXo2tXhbD5ni78UsWNB7-EQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2019-11-21 14:48:53 Re: Remote Connection Help
Previous Message stan 2019-11-21 14:42:32 Re: Isolation of multiple databse instances provided by a single postgres server