On 11/21/19 6:48 AM, 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.
- 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...
As it stands now the directory allows you(sebastien) writes on that
directory. That is not the same as the Postgres system user, which I
assume is postgres. You need to verify this and also who is part of
postgres group and adjust the directory permissions to give the postgres
system user full writes. The thing to remember is that the Postgres
users(postgres, sebastien) are not the same as the system users even if
they have the same name.
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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
