BASIC POSTGRESQL

6
- Lecture1.1
  
  PostgreSQL Brief History 30 min
- Lecture1.2
  
  PostgreSQL Features 20 min
- Lecture1.3
  
  PostgreSQL Monitoring Tools Overview 30 min
- Lecture1.4
  
  PostgreSQL – Oracle Vs PostgreSQL 30 min
- Lecture1.5
  
  PostgreSQL Data Types 30 min
- Lecture1.6
  
  PostgreSQL Maximum Objects Size 30 min
VMWARE & POSTGRESQL INSTALLATION

9
- Lecture2.1
  
  Vmware Installation 30 min
- Lecture2.2
  
  Creating Linux Virtual Machine 01 hour
- Lecture2.3
  
  PostgreSQL Installation Types 30 min
- Lecture2.4
  
  PostgreSQL GUI Installation 30 min
- Lecture2.5
  
  PostgreSQL Text Mode Installation 30 min
- Lecture2.6
  
  PostgreSQL Unattended Mode Installation 30 min
- Lecture2.7
  
  Configure the network & Disk Partition 30 min
- Lecture2.8
  
  How to install PostgreSQL10 On Linux Server ? 20 min
- Lecture2.9
  
  PostgreSQL -11 Installation (rpm & source code) 30 min
POSTGRESQL DATABASE

6
- Lecture3.1
  
  Connect Postgres Server 10 min
- Lecture3.2
  
  PostgreSQL startup / shutdown /restart the postgresql server 30 min
- Lecture3.3
  
  PostgreSQL .bash_profile Set Up 30 min
- Lecture3.4
  
  PostgreSQL Database Creation 30 min
- Lecture3.5
  
  PostgreSQL Connect Database 30 min
- Lecture3.6
  
  PostgreSQL Drop Database 30 min
POSTGRESQL TABLE

16
- Lecture4.1
  
  PostgreSQL Languages 30 min
- Lecture4.2
  
  PostgreSQL Create Table 30 min
- Lecture4.3
  
  PostgreSQL Select Table 30 min
- Lecture4.4
  
  PostgreSQL Alter Table 30 min
- Lecture4.5
  
  PostgreSQL Drop Table 30 min
- Lecture4.6
  
  PostgreSQL Truncate Table 30 min
- Lecture4.7
  
  PostgreSQL Rename 30 min
- Lecture4.8
  
  PostgreSQL Comment 30 min
- Lecture4.9
  
  PostgreSQL Insert 30 min
- Lecture4.10
  
  PostgreSQL Update 30 min
- Lecture4.11
  
  PostgreSQL Table Delete 30 min
- Lecture4.12
  
  PostgreSQL Merge Table 30 min
- Lecture4.13
  
  PostgreSQL UNION 30 min
- Lecture4.14
  
  PostgreSQL UNION ALL 30 min
- Lecture4.15
  
  PostgreSQL INTERSECT 30 min
- Lecture4.16
  
  PostgreSQL DISTINCT 30 min
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING

3
- Lecture5.1
  
  PostgreSQL Grant 30 min
- Lecture5.2
  
  PostgreSQL Revoke 30 min
- Lecture5.3
  
  Postgres user creation and restrict DDL statement & other database access 30 min
TRANSACTIONS - MVCC

3
- Lecture6.1
  
  PostgreSQL Commit 30 min
- Lecture6.2
  
  PostgreSQL Rollback 30 min
- Lecture6.3
  
  PostgreSQL Savepoint 30 min
POSTGRESQL USER/SCHEMA MANAGEMENT

9
- Lecture7.1
  
  PostgreSQL User Creation 30 min
- Lecture7.2
  
  PostgreSQL User Creation Using Utility 30 min
- Lecture7.3
  
  PostgreSQL Drop user 30 min
- Lecture7.4
  
  PostgreSQL Drop User Using Utility 30 min
- Lecture7.5
  
  PostgreSQL Password Changing and Views 30 min
- Lecture7.6
  
  PostgreSQL Group/role 30 min
- Lecture7.7
  
  Alter PostgreSQL User/Role/group 30 min
- Lecture7.8
  
  PostgreSQL Schema 30 min
- Lecture7.9
  
  PostgreSQL user creation and restrict DDL & database access 30 min
POSTGRESQL CONSTRAINTS

6
- Lecture8.1
  
  PostgreSQL Constraints 30 min
- Lecture8.2
  
  PostgreSQL Primary Key 30 min
- Lecture8.3
  
  PostgreSQL UNIQUE Constraint 30 min
- Lecture8.4
  
  PostgreSQL CHECK Constraint 30 min
- Lecture8.5
  
  PostgreSQL NOT NULL Constraint 30 min
- Lecture8.6
  
  PostgreSQL Foreign Key 30 min
POSTGRESQL ADVANCE DATA TYPE

5
- Lecture9.1
  
  PostgreSQL DOMAIN Data Type 30 min
- Lecture9.2
  
  PostgreSQL Alter Domain 30 min
- Lecture9.3
  
  PostgreSQL Drop DOMAIN 30 min
- Lecture9.4
  
  PostgreSQL Json Data Type 30 min
- Lecture9.5
  
  PostgreSQL Sequence 30 min
POSTGRESQL VIEWS

1
- Lecture10.1
  
  How to Create PostgreSQL View 30 min
POSTGRESQL MONITORING OBJECT USUAGE/SIZE

1
- Lecture11.1
  
  How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
POSTGRESQL DATABASE ARCHITECTURE

4
- Lecture12.1
  
  PostgreSQL Tablespace 30 min
- Lecture12.2
  
  PostgreSQL UnderStanding Of Objects 30 min
- Lecture12.3
  
  PostgreSQL Architecture 30 min
- Lecture12.4
  
  PostgreSQL Query Flow 30 min
POSTGRESQL BACKUP AND RECOVERY

13
- Lecture13.1
  
  PostgreSQL Backup & Recovery Overview 30 min
- Lecture13.2
  
  PostgreSQL Database Backup Tools – Pg_dump 30 min
- Lecture13.3
  
  PostgreSQL Database Backup Tools – Pg_dumpall 30 min
- Lecture13.4
  
  PostgreSQL Database Backup Tools – Pg_basebackup 30 min
- Lecture13.5
  
  PostgreSQL COPY data From Text File 30 min
- Lecture13.6
  
  PostgreSQL COPY data to Text File 30 min
- Lecture13.7
  
  PostgreSQL Clone Table 20 min
- Lecture13.8
  
  Postgres Database Schema Migration 30 min
- Lecture13.9
  
  PostgreSQL Database clone/migration 30 min
- Lecture13.10
  
  PostgreSQL Compress backup format 30 min
- Lecture13.11
  
  PostgreSQL Archivelog 30 min
- Lecture13.12
  
  PostgreSQL Point In Time Recovery 30 min
- Lecture13.13
  
  Taking Backup On Postgres Slave (Standby) Server 30 min
POSTGRESQL PERFORMANCE TUNING

5
- Lecture14.1
  
  PostgreSQL Index 30 min
- Lecture14.2
  
  PostgreSQL Reindex 30 min
- Lecture14.3
  
  PostgreSQL PerformanceTuning 30 min
- Lecture14.4
  
  Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
- Lecture14.5
  
  Postgres Autovacuum Configuration/Setup 30 min
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION

11
- Lecture15.1
  
  POSTGRESQL STREAMING REPLICATION 30 min
- Lecture15.2
  
  SSL Streaming replication with PostgreSQL 10 30 min
- Lecture15.3
  
  How to make Linux partition and configure the network on Redhat Linux7.3 30 min
- Lecture15.4
  
  How To Fix Firewall Issues while connecting the other server? 30 min
- Lecture15.5
  
  How to install the PostgreSQL10 On Linux ? 30 min
- Lecture15.6
  
  How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
- Lecture15.7
  
  How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
- Lecture15.8
  
  PostgreSQL Switchover 30 min
- Lecture15.9
  
  Postgres Failover 30 min
- Lecture15.10
  
  Postgres Upgrade 30 min
- Lecture15.11
  
  PostgreSQL Upgrade 9.5 to 11.3 . 30 min

PostgreSQL Schema

PostgreSQL schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions.
Each user have their own schema and multiple schema
The schema search path can be altered so that each user find their tables first and access the other user tables also.
schema as a namespace for tables that also provides security.
By creating a schema for users and granting them rights on that schema,
The user will create tables on that schema instead of public.
schema search path is set which users objects set in postgreSQL database.
default for all users $user, public.
search path can be set on a users basis, to look thorough any random list of schemas.

Schema advantage:

To allow many users to use one database without interfering with each other.
To organize database objects into logical groups to make them more manageable.
Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Syntax:

create schema schema_name;
CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ];
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ];
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ];
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name;

PRACTICAL 1. CREATING SCHEMA

--To create "benz" schema
postgres=# create schema benz;
CREATE SCHEMA

--to  create a "dept" table in spcefic schema of "benz" as user default
postgres=# create table benz.emp(dept_id integer,department varchar);       
CREATE TABLE

what is search path:
search path which consist of just the table name. The system determines which
table is meant by following a search path, which is a list of schemas to look in.
The first matching table in the search path is taken to be the one wanted.
If there is no match in the search path, an error is reported, even if matching
table names exist in other schemas in the database.
The first schema named in the search path is called the current schema. Aside
from being the first schema searched, it is also the schema in which new tables

will be created if the CREATE TABLE command does not specify a schema name.

--Check the current search_path,By default such tables (and other objects) are 
automatically put into a schema named "public"

postgres=# show search_path;
  search_path   
----------------
 "$user",public
(1 row)

Below command is search the "emp" table in current search path that's why it shown 
error if you want to see "emp" table you need to specify ("benz.emp") schema name 
after that specify table name

postgres=# \d emp
Did not find any relation named "dept".
Note:you cannot view the (emp table)another schema table from current schema 
without specify schema name like following command

postgres=# \d benz.emp;
              Table "benz.emp"
   Column   |       Type        | Modifiers 
------------+-------------------+-----------
  dept_id   | integer           |

PRACTICAL 2. SCHEMA VIEWS

Here we gave some example for list down the roles,current Search path,Who is the owner of the table,user information and which schema is taken that table this is for just understanding purpose

--List down the role & roles privilege 
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        |                                                | {}

--show the current search path
postgres=# show search_path;
 "$user",public

--List down the table owner,schemaname,tablename,and tablespace name
postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='emp';                          
  schemaname | tablename | tableowner | tablespace 
------------+-----------+------------+------------
 benz       | emp       | postgres   | 
(1 row)

--List down user & schema
postgres=# select schemaname,relname from pg_stat_user_tables;           
 schemaname |    relname    
------------+---------------
 public     | school_bk2
 public     | school_bk
 public     | us_snail_addy
 benz       | dept
 public     | student
 benz       | emp
 public     | school
(7 rows)

--insert some data into the "emp" table

postgres=# insert into benz.emp values (1,'nijam');
INSERT 0 1
postgres=# insert into benz.emp values (1,'nijam');
INSERT 0 1
postgres=# insert into benz.emp values (2,'john'); 
INSERT 0 1

postgres=# select * from benz.emp;
 dept_id | department 
---------+------------
       1 | nijam
       1 | nijam
       2 | john
(3 rows

PRACTICAL 3. CREATE SAME NAME TABLE INTO THE DIFFRENT SCHEMA:

you can create already exists table name but schema should be different inside the single schema you cannot create more than one table with same name

--create dept table to benz2 schema
postgres=# create table benz2.dept (dept_id integer, department varchar);
CREATE TABLE

--insert some data
postgres=# insert into benz2.dept values(1002,'HR');
INSERT 0 1
postgres=# insert into benz2.dept values(1013,'Manager');
INSERT 0 1

--view the dept table from benz2 schema
postgres=# select * from benz2.dept;
 dept_id | department 
---------+------------
    1002 | HR
    1013 | Manager
(2 rows)

--Create another dept table into the benz schema
postgres=# create table benz.dept (dept_id integer, department varchar);
CREATE TABLE

--Check the "dept" table for which schema that is own
postgres=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='dept';
 schemaname | tablename | tableowner | tablespace 
------------+-----------+------------+------------
 benz       | dept      | postgres   | 
 benz2      | dept      | postgres   | 
(2 rows)

--fetch the two table
postgres=# select * from benz.dept;
 dept_id | department 
---------+------------
(0 rows)

postgres=# select * from benz2.dept;
 dept_id | department 
---------+------------
    1002 | HR
    1013 | Manager
(2 rows
Note:- A user can create two table as similar name but different schemas

PRACTICAL 4. LISTING THE SCHEMA

--We can get a list of all tables in all schemas
postgres=# \dt *.*
                        List of relations
       Schema       |          Name           | Type  |  Owner   
--------------------+-------------------------+-------+----------
 benz2              | dept                    | table | postgres
 benz2              | t1                      | table | u7
 benz2              | t10                     | table | u3
 benz2              | t11                     | table | u5
 benz2              | t6                      | table | u2
 benz2              | t9                      | table | u2
 books              | databases               | table | u2
 information_schema | sql_features            | table | postgres
 information_schema | sql_implementation_info | table | postgres
 information_schema | sql_languages           | table | postgres
 information_schema | sql_packages            | table | postgres
 information_schema | sql_parts               | table | postgres
 information_schema | sql_sizing              | table | postgres
 information_schema | sql_sizing_profiles     | table | postgres
 pg_catalog         | pg_aggregate            | table | postgres
 pg_catalog         | pg_am                   | table | postgres
 pg_catalog         | pg_amop                 | table | postgres
 pg_catalog         | pg_amproc               | table | postgres
 pg_catalog         | pg_attrdef              | table | postgres
 pg_catalog         | pg_attribute            | table | postgres
 pg_catalog         | pg_auth_members         | table | postgres
 pg_catalog         | pg_authid               | table | postgres
 pg_catalog         | pg_cast                 | table | postgres
 pg_catalog         | pg_class                | table | postgres
 pg_catalog         | pg_collation            | table | postgres
 pg_catalog         | pg_constraint           | table | postgres
-

--WE can get a list of all tables In a particular schema:
postgres-# \dt benz2.* 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 benz2  | dept | table | postgres
 benz2  | t1   | table | u7
 benz2  | t10  | table | u3
 benz2  | t11  | table | u5
 benz2  | t6   | table | u2
 benz2  | t9   | table | u2
(6 rows)

--USING VIEWS
postgres=# select * from pg_tables where schemaname='benz2';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers 
------------+-----------+------------+------------+------------+----------+-------------
 benz2      | dept      | postgres   |            | f          | f        | f
 benz2      | t6        | u2         |            | f          | f        | f
 benz2      | t9        | u2         |            | f          | f        | f
 benz2      | t10       | u3         |            | f          | f        | f
 benz2      | t1        | u7         |            | f          | f        | f
 benz2      | t11       | u5         |            | f          | f        | f
(6 rows)

--We can get a list of particular tables In a particular schema with expression
postgres-# \dt (benz2|sara).(t*|t*)
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 benz2  | t1   | table | u7
 benz2  | t10  | table | u3
 benz2  | t11  | table | u5
 benz2  | t6   | table | u2
 benz2  | t9   | table | u2
 sara   | t1   | table | u2
(6 rows)

PRACTICAL 5. VIEW CREATION INTO THE SCHEMA

--Create a view from "benz2.dept" table
postgres=# create view dept_v as select * from benz2.dept;
CREATE VIEW

postgres=# select * from dept_v;
 dept_id | department 
---------+------------
    1011 | Research
    1012 | Sales
(2 rows)

postgres=# \dv
         List of relations
 Schema |  Name  | Type |  Owner   
--------+--------+------+----------
 public | dept_v | view | postgres
(1 row)

-- first grant the superuser privilege to "u2" user
postgres=# alter user u2 superuser;
ALTER ROLE

--connect as u2 user 
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2:

--Create a schema and create a table and view within it as a super user
postgres=# CREATE SCHEMA books
    CREATE TABLE databases (title text, release date, rating text)
    CREATE VIEW postgres_view AS SELECT title, release FROM databases WHERE rating IS NOT NULL;
CREATE SCHEMA

--set the book search path for objects read
postgres=# set search_path to books;
SET
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)

postgres=# \dv                      
          List of relations
 Schema |    Name     | Type | Owner 
--------+-------------+------+-------
 books  | postgres_view | view | u2
(1 row)

postgres=# \dt
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 books  | databases | table | u2
(1 row)

PRACTICAL 6. CHANGING SEARCH_PATH

--Change the search path from "public" to "benz" First check the search path 
postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | school        | table | postgres
 public | school_bk     | table | postgres
 public | school_bk2    | table | postgres
 public | student       | table | postgres
 public | us_snail_addy | table | postgres
(5 rows)

--set  search_path as benz
postgres=# set search_path to benz;
SET

--see what search path you are currently using
postgres=# show search_path;
 search_path 
-------------
 benz
(1 row)

--list down benz schema's table
postgres=# \dt 
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 benz   | dept | table | postgres
 benz   | emp  | table | postgres
(2 rows)

--Now you can select emp table without pointing dot(benz.emp) but if you want 
see (public or benz2) another  schema table you need to specify schema name 
like "benz2.dept" 

postgres=# select * from emp; 
 dept_id | department 
---------+------------
       1 | nijam
       1 | nijam
       2 | john
(3 rows)

PRACTICAL 7. ASSIGN SEARCH_PATH PERMANANTLY AND PARTICULAR USER:

--List down the users
postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        |

--check the user u6 serach path after connected u6 user
postgres=> show search_path ;
  search_path   
----------------
 "$user",public
(1 row)

--connect as super user(u2)then assign search path permanently for a user,here 
we are assigning two search path (schema) to particular user u6
postgres=# alter user u6 set search_path to sara,benz2;
ALTER ROLE
postgres=# GRANT create ON SCHEMA sara TO u6; 
GRANT

It's from u6 user
postgres=# \q
[root@r1 bin]# ./psql -U u6 -d db2

postgres=> show search_path;
 search_path 
-------------
 sara, benz2
(1 row)


postgres=# \conninfo
You are connected to database "postgres" as user "u6" via socket in "/tmp" at port "5432".
postgres=# \q

--ReConnect  as "U6" user and check the search path correctly assigned or not
[postgres@r1 ~]$ psql -U u6 -d postgres
Password for user u6: 

postgres=> show search_path ;
 search_path 
-------------
 sara, benz2
(1 row)

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=UC/postgres      +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 books   | u2       |                      | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       | u3=UC/u3            +| 
         |          | u5=C/u3             +| 
         |          | u6=C/u3              | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(8 rows)

PRACTICAL 8. MULTIPLE SCHEMA ASSIGNING CURRENT USER:

--connect as u2 user using command line
[postgres@r1 ~]$ psql -U u2 -d postgres
Password for user u2:

--check the search path of u2 user
postgres=# show search_path;
 search_path 
-------------
 books
(1 row)

--create  two schema names are schema,schema2
postgres=# create schema schema1;        
CREATE SCHEMA
postgres=# create schema schema2;
CREATE SCHEMA

--Now assigning multiple schema for current user
postgres=# set search_path to schema1,schema2;
SET

--Now u2 user got two schema's search path
postgres=# show search_path;
   search_path    
------------------
 schema1, schema2
(1 row)

--if u2 user search the table in the above schema the user did'nt see any 
table becouse schema1 and schema2 are new schema currently nobody did not 
create objects(tables,views...etc)

postgres=# \dt
No relations found.

Also, since schema1 is the first element in the path, new objects would by 
default be created in it

PRACTICAL 9. SCHEMA PRIVILEGES:

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object.
by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema. If you do not want to allow that, you can revoke that privilege

–Revoke create privilege from all user. Here first “public” is the schema,

the second “public” means “every user” after revoke privilege newly created user(U7)

cannot create any objects on public schema also

postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--Create u7 user
postgres=# create user u7 with password 'u7';
CREATE ROLE

--list down the users
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}

--connect as u7 user
[postgres@r1 ~]$ psql -U u7 -d postgres

--Create table on public & benz2 schema but we can't create table becouse we already 
revoke the privilege("REVOKE CREATE ON SCHEMA public FROM PUBLIC;")

postgres=> create table t1(id int);
ERROR:  permission denied for schema public
postgres=> create table benz2.t1(id int);
ERROR:  permission denied for schema benz2

--List down the "u7" privilege Here user u7 have not any privilege  so we need grant 
the privileges are "usage,create"

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres        | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)

--Now grant the privilege create,usage to u7 user

postgres=# GRANT USAGE ON SCHEMA benz2 TO u7;
GRANT
postgres=# GRANT create ON SCHEMA benz2 TO u7;
GRANT

Note:To access a schema at all,for any action, the user must be granted 
"usage" rights. Before a user can select, insert, update, or delete, a user 
must first be granted "usage" to a schema.

--List the schemas privileges just see the user "u7" privilege UC is here U 
means "usage"privilege C mean "create"privilege

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)

--Here we can create t1 table on benz2 schema only but we can't create t1 table on 
public schema becouse we didn't grant create privilege on public schema we already 

postgres=> revoked create privilege from public schema

postgres=> create table benz2.t1(id int);
CREATE TABLE

postgres=> create table t1(id int);
ERROR:  permission denied for schema public

--We have a group role called "staff" and would like to grant all (or certain) privileges 
to this role on tables in a particular schema

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT SELECT ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

This will set privileges automatically for objects created in the future – and not affect already-existing objects.

Note that default privileges are only applied to objects created by the targeted user (FOR ROLE my_creating_role). If that clause is omitted, it defaults to the current user (that executes ALTER DEFAULT PRIVILEGES).

PRACTICAL 10. DROPPING SCHEMA:

–check the connectoin info,search_path and schema informtion

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema1 | u2       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)

postgres=# show search_path; 
   search_path    
------------------
 schema1, schema2
(1 row)

postgres=# \conninfo
You are connected to database "postgres" as user "u2" via socket in "/tmp" at port "5432".

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        | 

Note:i connected as u2 superuser he having two schema one is schema1 and another is schema2

–then create a t1 table and check the t1 table where to stored i’m telling that table

stored whether schema1 or schema2

postgres=# create table t1 as select * from pg_tables;
SELECT 59

postgres=# select schemaname,relname from pg_stat_all_tables where relname='t1';
 schemaname | relname 
------------+---------
 public     | t1
 schema1    | t1
(2 rows)

Note:t1 table is stored in primary schema of schema1

Here We can drop schema two method one they are

1. BY USING CASCADE OPTION

2. SIMPLE DROP METHOD

1. BY USING CASCADE OPTION:

if schema having any objects means when you dropping you need to specify Cascade Option Here i explained about cascade method following example

mind it when you dropping schema1 the table t1 also will be droped.

postgres=# drop schema schema1;
ERROR:  cannot drop schema schema1 because other objects depend on it
DETAIL:  table t1 depends on schema schema1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop schema schema1 cascade;
NOTICE:  drop cascades to table t1
DROP SCHEMA

--Check the table t1 And schema1 is properly droped or not
postgres=# \dt
No relations found.

postgres=> \dn+
                          List of schemas
  Name   |  Owner   |  Access privileges   |      Description       
---------+----------+----------------------+------------------------
 benz2   | postgres | postgres=UC/postgres+| 
         |          | u3=UC/postgres      +| 
         |          | u5=U/postgres       +| 
         |          | u6=U/postgres       +| 
         |          | u7=UC/postgres       | 
 public  | postgres | postgres=UC/postgres+| standard public schema
         |          | =U/postgres          | 
 sara    | u3       |                      | 
 schema2 | u2       |                      | 
 schema5 | postgres |                      | 
 schema6 | u2       |                      | 
(7 rows)

2. SIMPLE DROP METHOD

Here schema2 have not any objects also t1 table is stored in previous droped schema1 so you can use simple drop methode like following example

postgres=# drop schema schema2;
DROP SCHEMA

PRACTICAL 11. ASSIGN PARTICULAR SCHEMA FOR PARTICULAR USER:

if we set a schema then created tables as user never going to public it always belongs to the particular schema we can maintain safley.

postgres=# set search_path to schema5;
SET
postgres=# show search_path;
 search_path 
-------------
 schema5
(1 row)

postgres=# \dt
                     List of relations
 Schema |           Name            | Type  |    Owner     
--------+---------------------------+-------+--------------
 schema3| dept                      | table | jack
 sys    | dual                      | table | enterprisedb
 sys    | edb$session_wait_history  | table | enterprisedb
 sys    | edb$session_waits         | table | enterprisedb
 sys    | edb$snap                  | table | enterprisedb
 sys    | edb$stat_all_indexes      | table | enterprisedb
 sys    | edb$stat_all_tables       | table | enterprisedb
 sys    | edb$stat_database         | table | enterprisedb
 sys    | edb$statio_all_indexes    | table | enterprisedb
 sys    | edb$statio_all_tables     | table | enterprisedb
 sys    | edb$system_waits          | table | enterprisedb
 sys    | plsql_profiler_rawdata    | table | enterprisedb
 sys    | plsql_profiler_runs       | table | enterprisedb
 sys    | plsql_profiler_units      | table | enterprisedb
 sys    | product_component_version | table | enterprisedb
(15 rows)

postgres=# create table tony(id number);
CREATE TABLE

postgres=# \dt
                     List of relations
 Schema |           Name            | Type  |    Owner     
--------+---------------------------+-------+--------------
 schema3| dept                      | table | jack
 schema3| tony                      | table | jack
 sys    | dual                      | table | enterprisedb
 sys    | edb$session_wait_history  | table | enterprisedb
 sys    | edb$session_waits         | table | enterprisedb
 sys    | edb$snap                  | table | enterprisedb
 sys    | edb$stat_all_indexes      | table | enterprisedb
 sys    | edb$stat_all_tables       | table | enterprisedb
 sys    | edb$stat_database         | table | enterprisedb
 sys    | edb$statio_all_indexes    | table | enterprisedb
 sys    | edb$statio_all_tables     | table | enterprisedb
 sys    | edb$system_waits          | table | enterprisedb
 sys    | plsql_profiler_rawdata    | table | enterprisedb
 sys    | plsql_profiler_runs       | table | enterprisedb
 sys    | plsql_profiler_units      | table | enterprisedb
 sys    | product_component_version | table | enterprisedb
(16 rows)

Prev Alter PostgreSQL User/Role/group

Next PostgreSQL user creation and restrict DDL & database access

About Courses

Greenplum Database

About Courses

Greenplum Database

Databases

BASIC POSTGRESQL

VMWARE & POSTGRESQL INSTALLATION

POSTGRESQL DATABASE

POSTGRESQL TABLE

USER/OBJECTS PRIVILEGE AND ROLES ASIGNING

TRANSACTIONS - MVCC

POSTGRESQL USER/SCHEMA MANAGEMENT

POSTGRESQL CONSTRAINTS

POSTGRESQL ADVANCE DATA TYPE

POSTGRESQL VIEWS

POSTGRESQL MONITORING OBJECT USUAGE/SIZE

POSTGRESQL DATABASE ARCHITECTURE

POSTGRESQL BACKUP AND RECOVERY

POSTGRESQL PERFORMANCE TUNING

HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION

Become An Instructor?

About Courses

About Courses

Databases

BASIC POSTGRESQL

VMWARE & POSTGRESQL INSTALLATION

POSTGRESQL DATABASE

POSTGRESQL TABLE

USER/OBJECTS PRIVILEGE AND ROLES ASIGNING

TRANSACTIONS - MVCC

POSTGRESQL USER/SCHEMA MANAGEMENT

POSTGRESQL CONSTRAINTS

POSTGRESQL ADVANCE DATA TYPE

POSTGRESQL VIEWS

POSTGRESQL MONITORING OBJECT USUAGE/SIZE

POSTGRESQL DATABASE ARCHITECTURE

POSTGRESQL BACKUP AND RECOVERY

POSTGRESQL PERFORMANCE TUNING

HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION

PostgreSQL Schema

Login with your site account

Register a new account

Modal title