-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
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
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
PRACTICAL 6. CHANGING SEARCH_PATH
PRACTICAL 7. ASSIGN SEARCH_PATH PERMANANTLY AND PARTICULAR USER:
PRACTICAL 8. MULTIPLE SCHEMA ASSIGNING CURRENT USER:
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
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
–then create a t1 table and check the t1 table where to stored i’m telling that table
stored whether schema1 or schema2
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.
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
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.