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 |
Monday, 23 February 2026
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.
How to install telnet
[root@localhost ~]# rpm -qa|grep telnet [root@localhost ~]# yum install telnet telnet-server Last metadata expiration check: 0:08:21 ago on Mon 23 Feb 2026 07:49:23 AM UTC. Dependencies resolved. ===================================================================================================================================================================== Package Architecture Version Repository Size ===================================================================================================================================================================== Installing: telnet x86_64 1:0.17-76.el8 ol8_appstream 72 k telnet-server x86_64 1:0.17-76.el8 ol8_appstream 48 k Transaction Summary ===================================================================================================================================================================== Install 2 Packages Total download size: 120 k Installed size: 175 k Is this ok [y/N]: y Downloading Packages: (1/2): telnet-server-0.17-76.el8.x86_64.rpm 478 kB/s | 48 kB 00:00 (2/2): telnet-0.17-76.el8.x86_64.rpm 604 kB/s | 72 kB 00:00 --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 935 kB/s | 120 kB 00:00 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : telnet-server-1:0.17-76.el8.x86_64 1/2 Running scriptlet: telnet-server-1:0.17-76.el8.x86_64 1/2 Installing : telnet-1:0.17-76.el8.x86_64 2/2 Running scriptlet: telnet-1:0.17-76.el8.x86_64 2/2 Verifying : telnet-1:0.17-76.el8.x86_64 1/2 Verifying : telnet-server-1:0.17-76.el8.x86_64 2/2 Installed: telnet-1:0.17-76.el8.x86_64 telnet-server-1:0.17-76.el8.x86_64 Complete! [root@localhost ~]# rpm -qa|grep telnet telnet-0.17-76.el8.x86_64 telnet-server-0.17-76.el8.x86_64 [root@localhost ~]#
Subscribe to:
Comments (Atom)
