Postgres Create database with tablespace

alert log for postgres !!!! [root@localhost log]# pwd /var/lib/pgsql/17/data/log [root@localhost log]# locate postgresql*.log /var/lib/pgsql/17/data/log/postgresql-Fri.log /var/lib/pgsql/17/data/log/postgresql-Mon.log [root@localhost log]# tail -100f /var/lib/pgsql/17/data/log/postgresql-Mon.log Filename What it does pg_hba.conf Controls who can log in (the one we edited to fix your "Peer" error). postgresql.conf Controls how the server runs (memory, ports, logging, etc.). pg_ident.conf Maps Operating System users to Database users.

mkdir -p /var/lib/pgsql/tablespaces/salesspace
chmod 700 /var/lib/pgsql/tablespaces/salesspace

[root@localhost ~]# ls -ld /var/lib/pgsql/tablespaces/salesspace
drwx------. 2 postgres postgres 6 Feb 23 08:10 /var/lib/pgsql/tablespaces/salesspace


[root@localhost ~]#

show data_directory;

CREATE TABLESPACE salesspace
  OWNER salesapp
  LOCATION '/var/lib/pgsql/tablespaces/salesspace';

REVOKE ALL ON TABLESPACE salesspace FROM PUBLIC;
GRANT CREATE ON TABLESPACE salesspace TO salesapp;
 

select * from pg_tablespace;

select spcname
      ,pg_tablespace_location(oid) 
from   pg_tablespace;

select spcname ,pg_tablespace_location(oid) from pg_tablespace;

CREATE ROLE salesapp
  LOGIN
  PASSWORD 'salesapp';

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;



postgres=# \db
                      List of tablespaces
    Name    |  Owner   |               Location
------------+----------+---------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 salesspace | salesapp | /var/lib/pgsql/tablespaces/salesspace
(3 rows)



postgres=# SELECT spcname, spcowner::regrole, pg_tablespace_location(oid) FROM pg_tablespace;
  spcname   | spcowner |        pg_tablespace_location
------------+----------+---------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 salesspace | salesapp | /var/lib/pgsql/tablespaces/salesspace
(3 rows)


If only salesapp should use this tablespace:

ALTER ROLE salesapp SET default_tablespace = salesspace;





postgres=# \c sales
You are now connected to database "sales" as user "postgres".


sales=# \l sales
                                                 List of databases
 Name  |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules | Access privileges
-------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------
 sales | salesapp | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           |
(1 row)






sales=# GRANT CONNECT ON DATABASE sales TO salesapp;
GRANT
sales=# GRANT USAGE, CREATE ON SCHEMA public TO salesapp;
GRANT
sales=# GRANT CREATE ON TABLESPACE salesspace TO salesapp;
GRANT




sales=# CREATE TABLE orders (
sales(#     order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sales(#     order_date date NOT NULL,
sales(#     amount     numeric(12,2) NOT NULL
sales(# )
sales-# TABLESPACE salesspace;
CREATE TABLE



sales=# ALTER TABLE orders OWNER TO salesapp;
ALTER TABLE


sales=# \d+ orders
                                                         Table "public.orders"
   Column   |     Type      | Collation | Nullable |           Default            | Storage | Compression | Stats target | Description
------------+---------------+-----------+----------+------------------------------+---------+-------------+--------------+-------------
 order_id   | bigint        |           | not null | generated always as identity | plain   |             |              |
 order_date | date          |           | not null |                              | plain   |             |              |
 amount     | numeric(12,2) |           | not null |                              | main    |             |              |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
Access method: heap





SELECT
    tablename,
    tableowner,
    tablespace
FROM pg_tables
WHERE tablename = 'orders';



sales=# SELECT
sales-#     tablename,
sales-#     tableowner,
sales-#     tablespace
sales-# FROM pg_tables
sales-# WHERE tablename = 'orders';
 tablename | tableowner | tablespace
-----------+------------+------------
 orders    | salesapp   |
(1 row)






postgres=# select * from pg_tables where schemaname ='public' limit 10;
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)




postgres=# select oid,datname,dattablespace from pg_database order by oid;
  oid  |  datname  | dattablespace
-------+-----------+---------------
     1 | template1 |          1663
     4 | template0 |          1663
     5 | postgres  |          1663
 16387 | test      |          1663
 16390 | sales     |         16389
(5 rows)

postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner |        spcacl         | spcoptions
-------+------------+----------+-----------------------+------------
  1663 | pg_default |       10 |                       |
  1664 | pg_global  |       10 |                       |
 16389 | salesspace |    16388 | {salesapp=C/salesapp} |
(3 rows)

postgres=#



SELECT 
    spcname AS tablespace_name, 
    pg_tablespace_location(oid) AS physical_path 
FROM pg_tablespace;

 tablespace_name |             physical_path
-----------------+---------------------------------------
 pg_default      |
 pg_global       |
 salesspace      | /var/lib/pgsql/tablespaces/salesspace
(3 rows)

postgres=#




SELECT 
    spcname AS tablespace,
    pg_size_pretty(pg_tablespace_size(spcname)) AS used_space,
    pg_tablespace_location(oid) AS physical_path
FROM pg_tablespace
WHERE spcname NOT LIKE 'pg_%';


tablespace | used_space |             physical_path
------------+------------+---------------------------------------
 salesspace | 7790 kB    | /var/lib/pgsql/tablespaces/salesspace
(1 row)





[postgres@localhost ~]$ psql -U salesapp -d sales
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "salesapp"



[root@localhost ~]# tail -100f /var/lib/pgsql/17/data/pg_hba.conf

# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all    all    192.168.1.188/32    md5
local   all     all             peer


tail -10 /var/lib/pgsql/17/data/pg_ident.conf
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
sales_admins    postgres           salesapp   <<<<<< add this 
[root@localhost ~]#




[root@localhost ~]# grep "^local" /var/lib/pgsql/17/data/pg_hba.conf
local   all             all                                     peer  <<<< commented this line
local   replication     all                                     peer
local   all             all                                     scram-sha-256

[root@localhost ~]# vi /var/lib/pgsql/17/data/pg_hba.conf

[root@localhost ~]# grep "^local" /var/lib/pgsql/17/data/pg_hba.conf
local   replication     all                                     peer
local   all             all                                     scram-sha-256




to load config !!!

 psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)



now able to connect !!!

[postgres@localhost ~]$ psql -U salesapp -d sales
Password for user salesapp:
psql (17.8)
Type "help" for help.




or 
[postgres@localhost ~]$ psql -U salesapp -d sales -h 127.0.0.1
Password for user salesapp:
psql (17.8)
Type "help" for help.




A Few Quick Navigation Tips:
\l: List all databases.

\dt: List all tables in the current database.

\du: List all users/roles and their permissions.

\q: Exit the psql prompt.




[postgres@localhost ~]$ psql -U postgres
Password for user postgres:
psql (17.8)
Type "help" for help.


SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;



    Name    |  Owner   |               Location
------------+----------+---------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 salesspace | salesapp | /var/lib/pgsql/tablespaces/salesspace
(3 rows)



select relname,reltablespace from pg_class where reltablespace in(select
oid from pg_tablespace where spcname not in ('pg_default','pg_global'));

            ^
postgres=# select relname,reltablespace from pg_class where reltablespace in(select
postgres(# oid from pg_tablespace where spcname not in ('pg_default','pg_global'));
 relname | reltablespace
---------+---------------
(0 rows)




postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner |        spcacl         | spcoptions
-------+------------+----------+-----------------------+------------
  1663 | pg_default |       10 |                       |
  1664 | pg_global  |       10 |                       |
 16389 | salesspace |    16388 | {salesapp=C/salesapp} |
(3 rows)


SELECT 
    spcname AS tablespace_name,
    pg_catalog.pg_get_userbyid(spcowner) AS owner,
    pg_catalog.pg_tablespace_location(oid) AS location,
    pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;




postgres=# SELECT schemaname, tablename, tableowner
postgres-# FROM pg_tables
postgres-# WHERE tablespace = 'salesspace';
 schemaname | tablename | tableowner
------------+-----------+------------
(0 rows)

postgres=#






psql -U salesapp -d sales





ASM Diskgroup (+DATA)
   ↓
ACFS filesystem
   ↓
/acfs/pgsql/salesspace
   ↓
PostgreSQL tablespace


| Storage type | Supported |
| ------------ | --------- |
| ext4 / xfs   | ✅ Yes     |
| LVM          | ✅ Yes     |
| SAN          | ✅ Yes     |
| NFS (tuned)  | ✅ Yes     |
| ASM direct   | ❌ No      |
| ASM via ACFS | ✅ Yes     |