Thread: Tablespace setup issue
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.
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.
On Thu, 2019-11-21 at 09:48 -0500, Sébastien Bihorel wrote: > 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. Don't create tablespaces. Stick with the default tablespace. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 11/21/19 8:52 AM, Laurenz Albe wrote: > On Thu, 2019-11-21 at 09:48 -0500, Sébastien Bihorel wrote: >> 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. > Don't create tablespaces. > Stick with the default tablespace. Why? -- Angular momentum makes the world go 'round.
On Thu, 2019-11-21 at 08:54 -0600, Ron wrote: > > Don't create tablespaces. > > Stick with the default tablespace. > > Why? Because you won't need them. Tablespaces have a limited number of use cases: - Distribute I/O across several devices (you can do the same on a lower level using striping). - Put some tables or indexes faster and some on slower storage. - Provide a size limit for a table or a database. - Have your temporary files on a different file system. In a virtualized environment, you never want tablespaces. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On 11/21/19 8:59 AM, Laurenz Albe wrote: > On Thu, 2019-11-21 at 08:54 -0600, Ron wrote: >>> Don't create tablespaces. >>> Stick with the default tablespace. >> Why? > Because you won't need them. > > Tablespaces have a limited number of use cases: > - Distribute I/O across several devices (you can do the same > on a lower level using striping). > - Put some tables or indexes faster and some on slower storage. > - Provide a size limit for a table or a database. > - Have your temporary files on a different file system. > > In a virtualized environment, you never want tablespaces. After adding another 350GB to the data/base filesystem, the storage team said that the virtual LUN on our ESX host is full. No more expansion of this database until and unless they create a new LUN (on storage replicated to a different DC using SRM). Thus, to enlarge the database on this VM, we either... 1. create a tablespace on a different (modest sized) LUN, or 2. create a completely new 10TB LUN and migrate the whole db (including backups) to that new LUN. Honestly, using a new tablespace seems simpler. -- Angular momentum makes the world go 'round.
On Thu, 2019-11-21 at 09:11 -0600, Ron wrote: > After adding another 350GB to the data/base filesystem, the storage team > said that the virtual LUN on our ESX host is full. No more expansion of > this database until and unless they create a new LUN (on storage replicated > to a different DC using SRM). > > Thus, to enlarge the database on this VM, we either... > > 1. create a tablespace on a different (modest sized) LUN, or > 2. create a completely new 10TB LUN and migrate the whole db (including > backups) to that new LUN. > > Honestly, using a new tablespace seems simpler. Ok, that is a valid use case :^) Change into the directory with the new file system. Create a subdirectory and "chown" it to "postgres". Become operating system user "postgres" and verify that you can write to that directory. If not, fix the permissions of the directories on the path. If you can write to the directory, you will be able to create a tablespace there. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
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
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@aklaver.com
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.
psql is the client, not the server. What user you run psql as doesn’t make a difference, it’s what user the server is running as that makes the difference, since it is the server that interacts with the file system. psql simply connects to and interacts with the PostgreSQL server.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell: 907-328-9145
cell: 907-328-9145
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).ThanksOn 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.