You can see lab work for basic database management. Copied as is form.
Hierarchy:
database(instance) --> multiple schemas(users) --- objects will be there under schema (oracle)
instance--multiple databases are there---under database mutliple schemas are there (here schema means not user)---under schema multiple objects (postgresql)
if you want to run any query we can do schema_name.object_name.
select * from test.emp;
you can't perform cross join between multiple schemas which are under different databases.
you can perform cross join between two schemas if those are under same database. here database will be treated as separate instance oracle.
database connectivity:
======================
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql
psql (11.0)
Type "help" for help.
postgres=#
Find postgres version:
postgres=# show server_version;
server_version
----------------
11.0
(1 row)
to know the connection details:
--------------------------------
postgres=# \c
You are now connected to database "postgres" as user "postgres".
what are the database:
---------------------
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
what are the users:
-------------------
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
connect to other database:
-------------------------
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=#
template1=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
you can't able to conenct template0 database, we call this as dumb database, it will not allow any connections.
but while creating database u can use this as template if required.
create user:
-----------
template1=# create user pgscott with PASSWORD 'pgscott';
CREATE ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pgscott | | {}
alter user:
------------
template1=# alter user pgscott with CREATEDB CREATEROLE REPLICATION CONNECTION LIMIT 10 VALID UNTIL '11-18-2019';
ALTER ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pgscott | Create role, Create DB, Replication +| {}
| 10 connections +|
| Password valid until 2019-11-18 00:00:00-08 |
\h create user
template1=# alter user pgscott password 'sam';
ALTER ROLE
database creation:
-----------------
template1=# create database testdb;
CREATE DATABASE
template1=# alter DATABASE testdb OWNER to pgscott;
ALTER DATABASE
template1=# \l testdb
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+ ------- +----------+-------------+-------------+-------------------
testdb | pgscott | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
schemas under database:
-----------------------
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
by default if you create any database public schema will come.
create schema:
--------------
testdb=# create schema test;
CREATE SCHEMA
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
test | postgres
create objects under schema:
----------------------------
if you are not giving scheamname it will goes to public schema.
get the list of tables:
----------------------
testdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | emp | table | postgres
(1 row)
testdb=# alter table emp set SCHEMA test;
ALTER TABLE
testdb=# \dt
Did not find any relations.
testdb=# \dt test.*;
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
test | emp | table | postgres
(1 row)
list of views:
-------------
testdb=# \dv
Did not find any relations.
list of functions:
------------------
testdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
list of materilized views:
---------------------------
testdb=# \dm
Did not find any relations.
testdb=#
how to connect specific database with specific user:
---------------------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott
psql (11.0)
Type "help" for help.
testdb=> \c
You are now connected to database "testdb" as user "pgscott".
testdb=>
if you are connecting from another client:
-----------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott -h 127.0.0.1
psql (11.0)
Type "help" for help.
testdb=>
OR
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott
-d : database name
-U : Username
Create Group and add users to group:
------------------------------------------------------
hr1dev=#
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gouranga | Create role, Create DB, Replication +| {}
| 10 connections |
group1 | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | | {group1}
test2 | | {group1}
test3 | | {group1}
hr1dev=#
=============================================================
AUDIT LOG ENABLING
=============================================================
to track each everything happening db level, we will enable audit log.
to enable this we need to configure below parameter in postgresql.conf file.
log_destination = 'stderr' # audit captured in .txt file. we can write .csv also using 'stderr,crsvlog'
logging_collector = on # entries will be made, who connected and what actions performed.
log_directory = 'pg_log' # Log file will be created in pg_log directory inside the data directory
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # format of log files
log_rotation_age = 1d # log file will be rotated every day
log_rotation_size = 10MB # every log file will be 10MB max
log_min_duration_statement = 0 # this value in milli seconds, 60000 ms for one miniute--for tracking query execution more than mentioned time. -1, disable, 0 - capture all the queries
log_connections = on # What time connected
log_disconnections = on # what time dissconncted
log_line_prefix = '%a %u %d %h %p %t %i %e' # %a -application, %u -user, %d -database, %h -remote host, %p -process id ,%t -time ,%i %e - sql statement
log_statement = 'all' # it will capture all sql statements
restrat the postgres services to refrect the changes.
e.g.,
$ /usr/local/pgctl/bin/pg_ctl -D /usr/pgsql restart
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... 15632 2019-08-17 20:39:55 PDT 00000LOG: listening on IPv6 address "::1", port 5432
15632 2019-08-17 20:39:55 PDT 00000LOG: listening on IPv4 address "127.0.0.1", port 5432
15632 2019-08-17 20:39:55 PDT XX000LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
15632 2019-08-17 20:39:55 PDT XX000HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
15632 2019-08-17 20:39:55 PDT 00000LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
15632 2019-08-17 20:39:55 PDT 00000LOG: redirecting log output to logging collector process
15632 2019-08-17 20:39:55 PDT 00000HINT: Future log output will appear in directory "pg_log".
done
server started
===========================
vgenerate_series
===========================
[postgres@example01 pgsql]$ cd pg_log
[postgres@example01 pg_log]$ ls -ltr
total 4
-rw------- 1 postgres postgres 198 Aug 17 20:39 postgresql-2019-08-17_203955.log
[postgres@example01 pg_log]$ cat postgresql-2019-08-17_203955.log
15634 2019-08-17 20:39:55 PDT 00000LOG: database system was shut down at 2019-08-17 20:39:55 PDT
15632 2019-08-17 20:39:55 PDT 00000LOG: database system is ready to accept connections
[postgres@example01 pg_log]$
hr1dev=# create table emp(no integer,name varchar(20));
CREATE TABLE
hr1dev=# insert into emp values(generate_series(1,100),'data'||generate_series(1,100));
INSERT 0 100
hr1dev=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
public | emp | table | postgres | 8192 bytes |
(1 row)
hr1dev=# insert into emp values(generate_series(1,1000000),'data'||generate_series(1,1000000));
INSERT 0 1000000
hr1dev=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | emp | table | postgres | 42 MB |
(1 row)
hr1dev=#
=================================
set ECHO_HIDDEN on/off
=================================
hr1dev=# \set ECHO_HIDDEN on
hr1dev=# \dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
**************************
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
hr1dev=#
hr1dev=# \set ECHO_HIDDEN OFF
hr1dev=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
hr1dev=#
===================================================
create group
===================================================
hr1dev=#
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# alter user test1 CREATEDB;
ALTER ROLE
hr1dev=# alter user test2 CREATEROLE;
ALTER ROLE
hr1dev=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gouranga | Create role, Create DB, Replication +| {}
| 10 connections |
group1 | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | Create DB | {group1}
test2 | Create role | {group1}
test3 | | {group1}
hr1dev=#
================================================
To check the users having passowrd:
================================================
hr1dev=# alter user test1 password 'password';
ALTER ROLE
hr1dev=# select usename,passwd from pg_shadow where passwd IS NOT NULL;
usename | passwd
----------+-------------------------------------
gouranga | md510bf419924319e34e10ca0e87cbc7a8f
test1 | md565cb1da342495ea6bb0418a6e5718c38
(2 rows)
hr1dev=#
=========================================
To check hba file entries from db level.
=========================================
postgres=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
84 | local | {all} | {all} | | | trust | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
91 | local | {replication} | {all} | | | trust | |
92 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
(6 rows)
====================================================================
Usefull Views
====================================================================
pg_catalog | pg_indexes : list of indexes it will display.
pg_catalog | pg_stat_all_indexes : all indexes (user created + system created)
pg_catalog | pg_stat_sys_indexes : only system created indexes
pg_catalog | pg_stat_user_indexes :only user created indexes
pg_catalog | pg_stat_all_tables :system_user created
pg_catalog | pg_stat_sys_tables :system created
pg_catalog | pg_stat_user_tables :only user created table
pg_catalog | pg_tables:system+user created
====================================================================
-- List all tables/ relations under a database including all schemas
hr1dev=# select schemaname,relname from pg_stat_user_tables;
schemaname | relname
------------+---------
test | sample2
test | sample
public | emp
=============================
--- Describe a table
=============================
hr1dev=# create table test.emp(empno int primary key NOT NULL,ename varchar NOT NULL,job varchar,dob DATE,deptno int);
CREATE TABLE
vhr1dev=# \dt+ test.emp;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
test | emp | table | postgres | 8192 bytes |
(1 row)
hr1dev=# \d+ test.emp
Table "test.emp"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
empno | integer | | not null | | plain | |
ename | character varying | | not null | | extended | |
job | character varying | | | | extended | |
dob | date | | | | plain | |
deptno | integer | | | | plain | |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
hr1dev=#
==========================================
create & refresh materialized view
==========================================
hr1dev=# create materialized view test.emp_mv as select * from test.emp;
SELECT 0
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+-------+-----+-----+--------
(0 rows)
-- refresh scenarios
hr1dev=# insert into test.emp values(100,'Gouranga','Admin','10-Jan-1983',10);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+-------+-----+-----+--------
(0 rows)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
(1 row)
hr1dev=#
hr1dev=# insert into test.emp values(101,'John','User','10-Jan-1983',20);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
(1 row)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1983-01-10 | 20
(2 rows)
hr1dev=#
hr1dev=# update test.emp set dob='12-Nov-1987' where empno=101;
UPDATE 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1983-01-10 | 20
(2 rows)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
hr1dev=#
hr1dev=# select * from pg_matviews;
schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated | definition
------------+-------------+--------------+------------+------------+-------------+--------------------
public | emp_mv | postgres | | f | t | SELECT emp.no, +
| | | | | | emp.name +
| | | | | | FROM emp;
test | emp_mv | postgres | | f | t | SELECT emp.empno,+
| | | | | | emp.ename, +
| | | | | | emp.job, +
| | | | | | emp.dob, +
| | | | | | emp.deptno +
| | | | | | FROM test.emp;
(2 rows)
==============================================
sequences
==============================================
hr1dev=# create SEQUENCE test.seq1 AS int INCREMENT 1 MINVALUE 1 MAXVALUE 100;
CREATE SEQUENCE
hr1dev=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
test | seq1 | postgres | integer | 1 | 1 | 100 | 1 | f | 1 |
(1 row)
hr1dev=# \ds
Did not find any relations.
hr1dev=# \ds test.*
List of relations
Schema | Name | Type | Owner
--------+------+----------+----------
test | seq1 | sequence | postgres
(1 row)
hr1dev=# select nextval('test.seq1');
nextval
---------
1
(1 row)
hr1dev=# select nextval('test.seq1');
nextval
---------
2
(1 row)
hr1dev=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
test | seq1 | postgres | integer | 1 | 1 | 100 | 1 | f | 1 | 2
(1 row)
hr1dev=#
==============================================================
pg_stat_activity :currently running sessions from db level.
==============================================================
hr1dev=# select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
datname | pid | usename | client_addr | backend_start | state | query
---------+-------+----------+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------
| 4132 | | | 2019-12-28 22:41:37.543702+05:30 | |
| 4134 | postgres | | 2019-12-28 22:41:37.544265+05:30 | |
hr1dev | 10739 | postgres | | 2019-12-29 00:08:33.238153+05:30 | active | select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
| 4130 | | | 2019-12-28 22:41:37.53434+05:30 | |
| 4129 | | | 2019-12-28 22:41:37.533874+05:30 | |
| 4131 | | | 2019-12-28 22:41:37.53488+05:30 | |
(6 rows)
hr1dev=#
postgres=# create database demodb;
CREATE DATABASE
postgres=# select datname,numbackends,temp_files,deadlocks from pg_stat_database;
datname | numbackends | temp_files | deadlocks
-----------+-------------+------------+-----------
postgres | 1 | 0 | 0
template1 | 0 | 0 | 0
template0 | 0 | 0 | 0
hr1dev | 0 | 0 | 0
demodb | 0 | 0 | 0
(5 rows)
postgres=#
===============================================
Grants
===============================================
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | gouranga | |
(2 rows)
hr1dev=#
hr1dev=# grant usage on schema test to gouranga; # gouranga is a user
GRANT
hr1dev=# grant usage on schema test to group1; # group1 is group of many users
GRANT
hr1dev=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | gouranga | gouranga=UC/gouranga+|
| | group1=U/gouranga |
(2 rows)
hr1dev=#
if user gouranga is there, he has set of access rights, now at a time one request came to create 10 users with same access rights as 'gouranga' user.
hr1dev=# create user user4 password 'password';
CREATE ROLE
hr1dev=# grant gouranga to user4;
GRANT ROLE
hr1dev=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+------------+-------------
gouranga | Create role, Create DB, Replication +| {} |
| 10 connections | |
group1 | Cannot login | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
test1 | Create DB | {group1} |
test2 | Create role | {group1} |
test3 | | {group1} |
user4 | | {gouranga} |
hr1dev=#
Note: dba_sys_privs in oracle and special privilges in postgresql both are same.
administrable_role_authorizations : this one will displays users and its assigned roles, but if you ( > postgres 12)
hr1dev=# grant gouranga to user4 with admin option;
GRANT ROLE
===========================================
table_constraints : all constraints
============================================
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# select constraint_schema,constraint_name,table_name,constraint_type from information_schema.table_constraints where constraint_schema<>'pg_catalog';
constraint_schema | constraint_name | table_name | constraint_type
-------------------+------------------------+------------+-----------------
test | emp_pkey | emp | PRIMARY KEY
test | 16386_24592_1_not_null | emp | CHECK
test | 16386_24592_2_not_null | emp | CHECK
(3 rows)
hr1dev=#
---------------------------
information_schema
----------------------------
information_schema | table_privileges
information_schema | tables :list of tables
information_schema | triggered_update_columns: if you create any trigger which column will update as part of the trigger.
information_schema | triggers :list of triggers
information_schema | view_column_usage :columns part of view
information_schema | view_table_usage :tables part of views
===================================
Database size , Table size etc
===================================
hr1dev=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | pg_default |
hr1dev | gouranga | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 92 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7699 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7561 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7561 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
hr1dev=#
hr1dev-# \dt+ test.*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+----------+------------+-------------
test | emp | table | postgres | 16 kB |
test | sample | table | gouranga | 8192 bytes |
test | sample2 | table | postgres | 0 bytes |
(3 rows)
hr1dev-#
-- using query
hr1dev=# select pg_size_pretty(pg_relation_size('test.emp'));
pg_size_pretty
----------------
8192 bytes
(1 row)
hr1dev=# select pg_size_pretty(pg_relation_size('emp'));
pg_size_pretty
----------------
42 MB
(1 row)
hr1dev=# select pg_size_pretty(pg_database_size('hr1dev'));
pg_size_pretty
----------------
92 MB
(1 row)
------------------------------------------------
Using TABLESPACES
---------------------------------------------------
TABLESPACE : it is a logical representation for storage space(specific mount point ) or device to store the data.
in postgresql by default we have 2 Tablespaces.
postgres=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
postgres=# show data_directory;
data_directory
----------------
/usr/pgsql
(1 row)
postgres=# create tablespace hr_tbs location '/usr/pgsql/hr_tbs';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------+-------------------+---------+---------+-------------
hr_tbs | postgres | /usr/pgsql/hr_tbs | | | 0 bytes |
pg_default | postgres | | | | 122 MB |
pg_global | postgres | | | | 574 kB |
(3 rows)
postgres=#
by default if you create any tablesapce, size will be 0 bytes only, no objects will be created under the specific tablesapce,
so we have to assign tablesapce to specific object, then only objects will be create.
created directory after creating tablespace.
$ pwd
/usr/pgsql/hr_tbs/PG_11_201809051
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
----------------------
base/16384/24592
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | pg_default |
postgres=# alter database demodb set tablespace hr_tbs;
ALTER DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | hr_tbs |
limitations of tablesapces:
---------------------------
one tablesapce can have only one path.
one tablesapce can be assigned to multiple databases/objects.
we can't assign multiple tablesapces to one object.
eventhought your database assigned to different tablespace, you can assign another tablesapce to one of the table under same database.
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
----------------------
base/16384/24592
hr1dev=# alter table test.emp set TABLESPACE hr_tbs;
ALTER TABLE
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
---------------------------------------------
pg_tblspc/24634/PG_11_201809051/16384/24635
(1 row)
hr1dev=#
===================================================
Get DDL of a function
==================================================
-- find oid
hr1dev=# select proname,oid from pg_proc where proname like '%filepath%';
proname | oid
----------------------+------
pg_relation_filepath | 3034
(1 row)
-- get ddl from oid
hr1dev=# select pg_get_functiondef(3034);
pg_get_functiondef
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pg_catalog.pg_relation_filepath(regclass)+
RETURNS text +
LANGUAGE internal +
STABLE PARALLEL SAFE STRICT +
AS $function$pg_relation_filepath$function$ +
(1 row)
hr1dev=#
Hierarchy:
database(instance) --> multiple schemas(users) --- objects will be there under schema (oracle)
instance--multiple databases are there---under database mutliple schemas are there (here schema means not user)---under schema multiple objects (postgresql)
if you want to run any query we can do schema_name.object_name.
select * from test.emp;
you can't perform cross join between multiple schemas which are under different databases.
you can perform cross join between two schemas if those are under same database. here database will be treated as separate instance oracle.
database connectivity:
======================
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql
psql (11.0)
Type "help" for help.
postgres=#
Find postgres version:
postgres=# show server_version;
server_version
----------------
11.0
(1 row)
to know the connection details:
--------------------------------
postgres=# \c
You are now connected to database "postgres" as user "postgres".
what are the database:
---------------------
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
what are the users:
-------------------
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
connect to other database:
-------------------------
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=#
template1=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
you can't able to conenct template0 database, we call this as dumb database, it will not allow any connections.
but while creating database u can use this as template if required.
create user:
-----------
template1=# create user pgscott with PASSWORD 'pgscott';
CREATE ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pgscott | | {}
alter user:
------------
template1=# alter user pgscott with CREATEDB CREATEROLE REPLICATION CONNECTION LIMIT 10 VALID UNTIL '11-18-2019';
ALTER ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pgscott | Create role, Create DB, Replication +| {}
| 10 connections +|
| Password valid until 2019-11-18 00:00:00-08 |
\h create user
template1=# alter user pgscott password 'sam';
ALTER ROLE
database creation:
-----------------
template1=# create database testdb;
CREATE DATABASE
template1=# alter DATABASE testdb OWNER to pgscott;
ALTER DATABASE
template1=# \l testdb
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+ ------- +----------+-------------+-------------+-------------------
testdb | pgscott | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
schemas under database:
-----------------------
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
by default if you create any database public schema will come.
create schema:
--------------
testdb=# create schema test;
CREATE SCHEMA
testdb=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
test | postgres
create objects under schema:
----------------------------
if you are not giving scheamname it will goes to public schema.
get the list of tables:
----------------------
testdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | emp | table | postgres
(1 row)
testdb=# alter table emp set SCHEMA test;
ALTER TABLE
testdb=# \dt
Did not find any relations.
testdb=# \dt test.*;
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
test | emp | table | postgres
(1 row)
list of views:
-------------
testdb=# \dv
Did not find any relations.
list of functions:
------------------
testdb=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
list of materilized views:
---------------------------
testdb=# \dm
Did not find any relations.
testdb=#
how to connect specific database with specific user:
---------------------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott
psql (11.0)
Type "help" for help.
testdb=> \c
You are now connected to database "testdb" as user "pgscott".
testdb=>
if you are connecting from another client:
-----------------------------------------
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott -h 127.0.0.1
psql (11.0)
Type "help" for help.
testdb=>
OR
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/psql -d testdb -U pgscott
-d : database name
-U : Username
Create Group and add users to group:
------------------------------------------------------
hr1dev=#
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gouranga | Create role, Create DB, Replication +| {}
| 10 connections |
group1 | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | | {group1}
test2 | | {group1}
test3 | | {group1}
hr1dev=#
=============================================================
AUDIT LOG ENABLING
=============================================================
to track each everything happening db level, we will enable audit log.
to enable this we need to configure below parameter in postgresql.conf file.
log_destination = 'stderr' # audit captured in .txt file. we can write .csv also using 'stderr,crsvlog'
logging_collector = on # entries will be made, who connected and what actions performed.
log_directory = 'pg_log' # Log file will be created in pg_log directory inside the data directory
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # format of log files
log_rotation_age = 1d # log file will be rotated every day
log_rotation_size = 10MB # every log file will be 10MB max
log_min_duration_statement = 0 # this value in milli seconds, 60000 ms for one miniute--for tracking query execution more than mentioned time. -1, disable, 0 - capture all the queries
log_connections = on # What time connected
log_disconnections = on # what time dissconncted
log_line_prefix = '%a %u %d %h %p %t %i %e' # %a -application, %u -user, %d -database, %h -remote host, %p -process id ,%t -time ,%i %e - sql statement
log_statement = 'all' # it will capture all sql statements
restrat the postgres services to refrect the changes.
e.g.,
$ /usr/local/pgctl/bin/pg_ctl -D /usr/pgsql restart
[postgres@example01 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... 15632 2019-08-17 20:39:55 PDT 00000LOG: listening on IPv6 address "::1", port 5432
15632 2019-08-17 20:39:55 PDT 00000LOG: listening on IPv4 address "127.0.0.1", port 5432
15632 2019-08-17 20:39:55 PDT XX000LOG: could not bind IPv4 address "192.168.38.129": Cannot assign requested address
15632 2019-08-17 20:39:55 PDT XX000HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
15632 2019-08-17 20:39:55 PDT 00000LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
15632 2019-08-17 20:39:55 PDT 00000LOG: redirecting log output to logging collector process
15632 2019-08-17 20:39:55 PDT 00000HINT: Future log output will appear in directory "pg_log".
done
server started
===========================
vgenerate_series
===========================
[postgres@example01 pgsql]$ cd pg_log
[postgres@example01 pg_log]$ ls -ltr
total 4
-rw------- 1 postgres postgres 198 Aug 17 20:39 postgresql-2019-08-17_203955.log
[postgres@example01 pg_log]$ cat postgresql-2019-08-17_203955.log
15634 2019-08-17 20:39:55 PDT 00000LOG: database system was shut down at 2019-08-17 20:39:55 PDT
15632 2019-08-17 20:39:55 PDT 00000LOG: database system is ready to accept connections
[postgres@example01 pg_log]$
hr1dev=# create table emp(no integer,name varchar(20));
CREATE TABLE
hr1dev=# insert into emp values(generate_series(1,100),'data'||generate_series(1,100));
INSERT 0 100
hr1dev=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
public | emp | table | postgres | 8192 bytes |
(1 row)
hr1dev=# insert into emp values(generate_series(1,1000000),'data'||generate_series(1,1000000));
INSERT 0 1000000
hr1dev=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | emp | table | postgres | 42 MB |
(1 row)
hr1dev=#
=================================
set ECHO_HIDDEN on/off
=================================
hr1dev=# \set ECHO_HIDDEN on
hr1dev=# \dx
********* QUERY **********
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
ORDER BY 1;
**************************
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
hr1dev=#
hr1dev=# \set ECHO_HIDDEN OFF
hr1dev=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
hr1dev=#
===================================================
create group
===================================================
hr1dev=#
hr1dev=# create user test1;
CREATE ROLE
hr1dev=# create user test2;
CREATE ROLE
hr1dev=# create user test3;
CREATE ROLE
hr1dev=# create group group1;
CREATE ROLE
hr1dev=# alter group group1 add user test1,test2,test3;
ALTER ROLE
hr1dev=# alter user test1 CREATEDB;
ALTER ROLE
hr1dev=# alter user test2 CREATEROLE;
ALTER ROLE
hr1dev=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
gouranga | Create role, Create DB, Replication +| {}
| 10 connections |
group1 | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test1 | Create DB | {group1}
test2 | Create role | {group1}
test3 | | {group1}
hr1dev=#
================================================
To check the users having passowrd:
================================================
hr1dev=# alter user test1 password 'password';
ALTER ROLE
hr1dev=# select usename,passwd from pg_shadow where passwd IS NOT NULL;
usename | passwd
----------+-------------------------------------
gouranga | md510bf419924319e34e10ca0e87cbc7a8f
test1 | md565cb1da342495ea6bb0418a6e5718c38
(2 rows)
hr1dev=#
=========================================
To check hba file entries from db level.
=========================================
postgres=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
84 | local | {all} | {all} | | | trust | |
86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
91 | local | {replication} | {all} | | | trust | |
92 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
(6 rows)
====================================================================
Usefull Views
====================================================================
pg_catalog | pg_indexes : list of indexes it will display.
pg_catalog | pg_stat_all_indexes : all indexes (user created + system created)
pg_catalog | pg_stat_sys_indexes : only system created indexes
pg_catalog | pg_stat_user_indexes :only user created indexes
pg_catalog | pg_stat_all_tables :system_user created
pg_catalog | pg_stat_sys_tables :system created
pg_catalog | pg_stat_user_tables :only user created table
pg_catalog | pg_tables:system+user created
====================================================================
-- List all tables/ relations under a database including all schemas
hr1dev=# select schemaname,relname from pg_stat_user_tables;
schemaname | relname
------------+---------
test | sample2
test | sample
public | emp
=============================
--- Describe a table
=============================
hr1dev=# create table test.emp(empno int primary key NOT NULL,ename varchar NOT NULL,job varchar,dob DATE,deptno int);
CREATE TABLE
vhr1dev=# \dt+ test.emp;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
test | emp | table | postgres | 8192 bytes |
(1 row)
hr1dev=# \d+ test.emp
Table "test.emp"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
empno | integer | | not null | | plain | |
ename | character varying | | not null | | extended | |
job | character varying | | | | extended | |
dob | date | | | | plain | |
deptno | integer | | | | plain | |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
hr1dev=#
==========================================
create & refresh materialized view
==========================================
hr1dev=# create materialized view test.emp_mv as select * from test.emp;
SELECT 0
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+-------+-----+-----+--------
(0 rows)
-- refresh scenarios
hr1dev=# insert into test.emp values(100,'Gouranga','Admin','10-Jan-1983',10);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+-------+-----+-----+--------
(0 rows)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
(1 row)
hr1dev=#
hr1dev=# insert into test.emp values(101,'John','User','10-Jan-1983',20);
INSERT 0 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
(1 row)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1983-01-10 | 20
(2 rows)
hr1dev=#
hr1dev=# update test.emp set dob='12-Nov-1987' where empno=101;
UPDATE 1
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1983-01-10 | 20
(2 rows)
hr1dev=# refresh materialized view test.emp_mv;
REFRESH MATERIALIZED VIEW
hr1dev=# select * from test.emp_mv;
empno | ename | job | dob | deptno
-------+----------+-------+------------+--------
100 | Gouranga | Admin | 1983-01-10 | 10
101 | John | User | 1987-11-12 | 20
(2 rows)
hr1dev=#
hr1dev=# select * from pg_matviews;
schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated | definition
------------+-------------+--------------+------------+------------+-------------+--------------------
public | emp_mv | postgres | | f | t | SELECT emp.no, +
| | | | | | emp.name +
| | | | | | FROM emp;
test | emp_mv | postgres | | f | t | SELECT emp.empno,+
| | | | | | emp.ename, +
| | | | | | emp.job, +
| | | | | | emp.dob, +
| | | | | | emp.deptno +
| | | | | | FROM test.emp;
(2 rows)
==============================================
sequences
==============================================
hr1dev=# create SEQUENCE test.seq1 AS int INCREMENT 1 MINVALUE 1 MAXVALUE 100;
CREATE SEQUENCE
hr1dev=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
test | seq1 | postgres | integer | 1 | 1 | 100 | 1 | f | 1 |
(1 row)
hr1dev=# \ds
Did not find any relations.
hr1dev=# \ds test.*
List of relations
Schema | Name | Type | Owner
--------+------+----------+----------
test | seq1 | sequence | postgres
(1 row)
hr1dev=# select nextval('test.seq1');
nextval
---------
1
(1 row)
hr1dev=# select nextval('test.seq1');
nextval
---------
2
(1 row)
hr1dev=# select * from pg_sequences;
schemaname | sequencename | sequenceowner | data_type | start_value | min_value | max_value | increment_by | cycle | cache_size | last_value
------------+--------------+---------------+-----------+-------------+-----------+-----------+--------------+-------+------------+------------
test | seq1 | postgres | integer | 1 | 1 | 100 | 1 | f | 1 | 2
(1 row)
hr1dev=#
==============================================================
pg_stat_activity :currently running sessions from db level.
==============================================================
hr1dev=# select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
datname | pid | usename | client_addr | backend_start | state | query
---------+-------+----------+-------------+----------------------------------+--------+-----------------------------------------------------------------------------------------
| 4132 | | | 2019-12-28 22:41:37.543702+05:30 | |
| 4134 | postgres | | 2019-12-28 22:41:37.544265+05:30 | |
hr1dev | 10739 | postgres | | 2019-12-29 00:08:33.238153+05:30 | active | select datname,pid,usename,client_addr,backend_start,state,query from pg_stat_activity;
| 4130 | | | 2019-12-28 22:41:37.53434+05:30 | |
| 4129 | | | 2019-12-28 22:41:37.533874+05:30 | |
| 4131 | | | 2019-12-28 22:41:37.53488+05:30 | |
(6 rows)
hr1dev=#
postgres=# create database demodb;
CREATE DATABASE
postgres=# select datname,numbackends,temp_files,deadlocks from pg_stat_database;
datname | numbackends | temp_files | deadlocks
-----------+-------------+------------+-----------
postgres | 1 | 0 | 0
template1 | 0 | 0 | 0
template0 | 0 | 0 | 0
hr1dev | 0 | 0 | 0
demodb | 0 | 0 | 0
(5 rows)
postgres=#
===============================================
Grants
===============================================
postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | gouranga | |
(2 rows)
hr1dev=#
hr1dev=# grant usage on schema test to gouranga; # gouranga is a user
GRANT
hr1dev=# grant usage on schema test to group1; # group1 is group of many users
GRANT
hr1dev=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | gouranga | gouranga=UC/gouranga+|
| | group1=U/gouranga |
(2 rows)
hr1dev=#
if user gouranga is there, he has set of access rights, now at a time one request came to create 10 users with same access rights as 'gouranga' user.
hr1dev=# create user user4 password 'password';
CREATE ROLE
hr1dev=# grant gouranga to user4;
GRANT ROLE
hr1dev=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+------------+-------------
gouranga | Create role, Create DB, Replication +| {} |
| 10 connections | |
group1 | Cannot login | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
test1 | Create DB | {group1} |
test2 | Create role | {group1} |
test3 | | {group1} |
user4 | | {gouranga} |
hr1dev=#
Note: dba_sys_privs in oracle and special privilges in postgresql both are same.
administrable_role_authorizations : this one will displays users and its assigned roles, but if you ( > postgres 12)
hr1dev=# grant gouranga to user4 with admin option;
GRANT ROLE
===========================================
table_constraints : all constraints
============================================
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# select constraint_schema,constraint_name,table_name,constraint_type from information_schema.table_constraints where constraint_schema<>'pg_catalog';
constraint_schema | constraint_name | table_name | constraint_type
-------------------+------------------------+------------+-----------------
test | emp_pkey | emp | PRIMARY KEY
test | 16386_24592_1_not_null | emp | CHECK
test | 16386_24592_2_not_null | emp | CHECK
(3 rows)
hr1dev=#
---------------------------
information_schema
----------------------------
information_schema | table_privileges
information_schema | tables :list of tables
information_schema | triggered_update_columns: if you create any trigger which column will update as part of the trigger.
information_schema | triggers :list of triggers
information_schema | view_column_usage :columns part of view
information_schema | view_table_usage :tables part of views
===================================
Database size , Table size etc
===================================
hr1dev=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | pg_default |
hr1dev | gouranga | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 92 MB | pg_default |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7699 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7561 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7561 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
hr1dev=#
hr1dev-# \dt+ test.*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+----------+------------+-------------
test | emp | table | postgres | 16 kB |
test | sample | table | gouranga | 8192 bytes |
test | sample2 | table | postgres | 0 bytes |
(3 rows)
hr1dev-#
-- using query
hr1dev=# select pg_size_pretty(pg_relation_size('test.emp'));
pg_size_pretty
----------------
8192 bytes
(1 row)
hr1dev=# select pg_size_pretty(pg_relation_size('emp'));
pg_size_pretty
----------------
42 MB
(1 row)
hr1dev=# select pg_size_pretty(pg_database_size('hr1dev'));
pg_size_pretty
----------------
92 MB
(1 row)
------------------------------------------------
Using TABLESPACES
---------------------------------------------------
TABLESPACE : it is a logical representation for storage space(specific mount point ) or device to store the data.
in postgresql by default we have 2 Tablespaces.
postgres=# select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
postgres=# show data_directory;
data_directory
----------------
/usr/pgsql
(1 row)
postgres=# create tablespace hr_tbs location '/usr/pgsql/hr_tbs';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------------+-------------------+---------+---------+-------------
hr_tbs | postgres | /usr/pgsql/hr_tbs | | | 0 bytes |
pg_default | postgres | | | | 122 MB |
pg_global | postgres | | | | 574 kB |
(3 rows)
postgres=#
by default if you create any tablesapce, size will be 0 bytes only, no objects will be created under the specific tablesapce,
so we have to assign tablesapce to specific object, then only objects will be create.
created directory after creating tablespace.
$ pwd
/usr/pgsql/hr_tbs/PG_11_201809051
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
----------------------
base/16384/24592
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | pg_default |
postgres=# alter database demodb set tablespace hr_tbs;
ALTER DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
demodb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7561 kB | hr_tbs |
limitations of tablesapces:
---------------------------
one tablesapce can have only one path.
one tablesapce can be assigned to multiple databases/objects.
we can't assign multiple tablesapces to one object.
eventhought your database assigned to different tablespace, you can assign another tablesapce to one of the table under same database.
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
----------------------
base/16384/24592
hr1dev=# alter table test.emp set TABLESPACE hr_tbs;
ALTER TABLE
hr1dev=# select pg_relation_filepath('test.emp');
pg_relation_filepath
---------------------------------------------
pg_tblspc/24634/PG_11_201809051/16384/24635
(1 row)
hr1dev=#
===================================================
Get DDL of a function
==================================================
-- find oid
hr1dev=# select proname,oid from pg_proc where proname like '%filepath%';
proname | oid
----------------------+------
pg_relation_filepath | 3034
(1 row)
-- get ddl from oid
hr1dev=# select pg_get_functiondef(3034);
pg_get_functiondef
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION pg_catalog.pg_relation_filepath(regclass)+
RETURNS text +
LANGUAGE internal +
STABLE PARALLEL SAFE STRICT +
AS $function$pg_relation_filepath$function$ +
(1 row)
hr1dev=#
No comments:
Post a Comment