Pearl DB Service

Roles:                   PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.                   Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.                  Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role. Role Attributes: CREATEDB         NOCREATEDB CREATEROLE NOCREATEROLE LOGIN           NOLOGIN SUPERUSER          NOSUPERUSER INHERIT NOINHERIT BYPASSRLS         NOBYPASSRLS REPLICATION NOREPLICATION PASSWORD ENCRYPTED PASSWORD CONNECTION LIMIT VALID UNTIL Examples: postgres=# \du+                                           List of roles  Role name |                         Attributes           

INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma

POSTGRESQL SCHEMA: A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. There are several reasons why one might want to use schemas:     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. Default Schema: public Examples: Current schema list of table: pearl=# \dt+                     List of relations  Schema | Name  | Type  |  Owner   | Size  | Description  --------+-------+-------+----------+-------+-------------  public | muthu | table | postgres | 16 kB |  (1 row) Current database list of schemas: pearl=# \dn+                           List of schemas   Name  |  Owner   |  Access privileges   |      Description        --------+----------+----------------------+------------------

PostgreSQL Installation 1.Community PostgreSQL (Open Source) 1.Binary(Packages) 2.Source 3.RPM(Redhat Package Management) 2.Enterprisedb 1.Run File 1.GUI Mode 2.Text Mode 2.Repository(yum Installation) 1.Community PostgreSQL Installation: Binary Installation:(Online Installation - Internet Must) Step 1: goto https://www.postgresql.org/ and click Download Choose Packages on Top Left Cornor Step 2: Select Your Operating System. Choose Linux and select your Linux distribution(Redhat/centos). Step 3: Select PostgreSQL Version. Example Choose 12 Step 4: Select Linux Platform. Example select Red Hat enterprise, CentOS, Scientific or Oracle version 7 Step 5: Select OS architecture Type. Example Select x86_64 Step 6: Copy the Script and paste your terminal Example: # Install the repository RPM: yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg

Physical Structure: base Subdirectory containing per-database subdirectories global Subdirectory containing cluster-wide tables, such as pg_database pg_commit_ts   Subdirectory containing transaction commit timestamp data pg_dynshmem   Subdirectory containing files used by the dynamic shared memory subsystem pg_logical   Subdirectory containing status data for logical decoding pg_multixact   Subdirectory containing multitransaction status data (used for shared row locks) pg_notify   Subdirectory containing LISTEN/NOTIFY status data pg_replslot   Subdirectory containing replication slot data pg_serial   Subdirectory containing information about committed serializable transactions pg_snapshots   Subdirectory containing exported snapshots pg_stat   Subdirectory containing permanent files for the statistics subsystem pg_stat_tmp   Subdirectory containing temporary files for the statistics subsystem pg_subtrans   Subdirectory containing

Sequence: It is used to automatically generate the number(unique).I generate serial list of unique number for numerical column of database tables. Sequence Options: TEMPORARY or TEMP //If specified, the sequence object is created only for this session and is automatically dropped on session exit. IF NOT EXISTS //Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the sequence that would have been created - it might not even be a sequence. NAME //The name of the sequence to be created. INCREMENT  //The optional clause INCREMENT BY increment specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1. MINVALUE NO MINVALUE //The optional clause MINVALUE minvalue determines the minimum value a sequence c

Subquery:               A Subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc. Types of Subqueries Single Row Sub Query:  Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions. Example: pearl=# select name from siva where age = (select age from siva where age=21);  name -------  muthu (1 row) Multiple row sub query:  Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also. Usage of Multiple Row operators     [> ALL] More than the highest value returned by the subquery     [< ALL] Less than the lowest value

Joins: PostgreSQL JOINS are used to retrieve data from multiple tables. A PostgreSQL JOIN is performed whenever two or more tables are joined in a SQL statement. Types: 1.Basic Join 2.Inner Join 1.Equal Inner Join 2.Non-equal Inner Join 3.Outer Join 1.Left Outer Join 2.Right Outer Join 3.Full Outer Join 4.Cross Join Examples: Sample Tables: pearl=# select * from muthu;  id |    dob     |  place ----+------------+---------   1 | 1992-04-16 | theni   4 | 1991-07-22 | madurai   5 | 1991-07-22 | covai (3 rows) pearl=# select * from siva;  id |   name   | age | salary ----+----------+-----+--------   1 | muthu    |  21 |   2000   2 | siva     |  26 |   4000   3 | thanigai |  27 |   5000 (3 rows) Basic Join: pearl=# select a.id,a.name,b.dob from siva a,muthu b where a.id=b.id;  id | name  |    dob    ----+-------+------------   1 | muthu | 1992-04-16 (1 row) Inner Join: Equal Inner Join: pearl=# select * fro

Set Operations: operator to combine result sets of multiple queries into a single result sets. union union all intersect except Sample Tables: pearl=# select * from siva;  id |   name   | age | salary ----+----------+-----+--------   1 | muthu    |  21 |   2000   2 | siva     |  26 |   4000   3 | thanigai |  27 |   5000 (3 rows) pearl=# select * from muthu ;  id |    dob     |  place  ----+------------+---------   1 | 1992-04-16 | theni   4 | 1991-07-22 | madurai   5 | 1991-07-22 | covai (3 rows) Union Examples: pearl=# select id from siva union select id from muthu;  id ----   2   5   4   3   1 (5 rows) Union all Examples: pearl=# select id from siva union all select id from muthu;  id ----   1   2   3   1   4   5 (6 rows) Intersect Examples: pearl=# select id from siva intersect select id from muthu;  id ----   1 (1 row) Except Examples: pearl=# select id from siva except select id from muthu;  id ----   3   2 (2 rows)

Build-in Functions: Aggregate functions Scalar Functions Aggregate Functions: Examples: pearl=# select * from siva ;  id |   name   | age | salary ----+----------+-----+--------   1 | muthu    |  21 |   2000   2 | siva     |  26 |   4000   3 | thanigai |  27 |   5000   4 | chandru  |  28 |   2000   5 | kathir   |  27 |   3000 (5 rows) pearl=# select max(age) from siva;  max -----   28 (1 row) pearl=# select min(age) from siva;  min -----   21 (1 row) pearl=# select avg(salary) from siva;           avg          -----------------------  3200.0000000000000000 (1 row) pearl=# select count(id) from siva;  count -------      5 (1 row) pearl=# select sum(salary) from siva;   sum  -------  16000 (1 row) pearl=# select * from siva order by salary;  id |   name   | age | salary ----+----------+-----+--------   1 | muthu    |  21 |   2000   4 | chandru  |  28 |   2000   5 | kathir   |  27 |   3000   2 | siva     |  26 |   4000   3 |

Import and Export on CSV Create CSV File: [root@server1 /]# mkdir import_dir [root@server1 /]# chown -R postgres. import_dir/ [root@server1 /]# chmod 700 import_dir/ [root@server1 /]# cd import_dir/ [root@server1 import_dir]# cat siva.csv id,name,age 1,muthu,21 2,siva,26 3,thanigai,27 4,chandru,28 5,kathir,27 [root@server1 import_dir]# chown -R postgres. siva.csv Syntax: \copy table_name from <location + filename> delimiter ',' HEADER; \copy //perform SQL COPY with data stream to the client host table_name //Table Name from //import from a file to //in order to export it to a file <location + filename> //location and file name delimiter ',' //Specifies the delimiter, which in our case is a comma: ‘,’ CSV //Specifies the file type from which we are going to import HEADER //Signifies that we have a header row in our .csv file and while importing we should ignore the first row (similarly, while exporting we can use this to

Enterprisedb Migration Toolkit: EDB Migration Toolkit is a powerful command-line tool that offers granular control of the migration process. Migration Toolkit facilitates migration of database objects and data to an EDB Postgres Advanced Server (Advanced Server) or PostgreSQL database from: Oracle MySQL Microsoft SQL Server Sybase which objects migrate to postgres Oracle Objects: Schemas   Tables    List-Partitioned Tables         Range-Partitioned Table         Constraints   Indexes   Triggers         Table Data   Views     Materialized Views         Packages         Procedures   Functions       Sequences       Users/Roles   Profiles   Object Types   Object Type Methods   Database Links     Queues       MySQL Objects: Schemas Tables Constraints Indexes Table Data Microsoft SQL Server Objects: Schemas Tables Constraints Indexes Table Data Sybase Obj

Oracle to PostgreSQL Migration – ORA2PG       Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects to the Oracle database, scans it automatically, extracts its structure or data and then generates SQL scripts that you can load into your PostgreSQL database.      The cost estimation in an Oracle to PostgreSQL migration is not easy.Ora2Pg inspects all database objects, all functions and stored procedures to detect if there’s still some objects and PL/SQL code that cannot be automatically converted by Ora2Pg. This tool is very helpful for the following conversions: Schema conversion PLSQL to PLPGSQL conversion TOOLS: Software Tools DBA-ORACLE //Oracle machine DBD-PG                //PostgreSQL machine DBI-1                //PostgreSQL machine ORA2PG        //PostgreSQL machine INSTALLATION STEPS: STEPS 1: (oracle machine) go to https://metacpan.org/pod/DBD::Oracle and download DBA-ORACLE tar -xvzf DBD-Oracl