Pearl DB Service

PgBadger:                 The PostgreSQL log analyzer “pgBadger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file.                  The report it generates shows all information found in a nice and easy to read report format.  These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system. FEATURES:  pgBadger reports everything about your SQL queries:         Overall statistics.         The most frequent waiting queries.         Queries that waited the most.         Queries generating the most temporary files.         Queries generating the largest temporar...

Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportion...

Lock: PostgreSQL provides various lock modes to control concurrent access to data in tables.  These modes can be used for application-controlled locking in situations where MVCC does not give the desired behavior.                 Also, most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes. Types of Lock: Table-level Locks Row-level Locks Page-level Locks Deadlocks Advisory Locks 1.Table-level Locks: ACCESS SHARE: The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode. ROW SHARE: The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s)  (in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UP...

DB Link:                 ‘DB Link’ in PostgreSQL enables a database user to access a table present on a different postgres instance. It provides a functionality in PostgreSQL similar to that of ‘DB Link’ in Oracle, ‘Linked Server’ in SQL Server and ‘Federated tables’ in MySQL. Options: dblink_connect — opens a persistent connection to a remote database dblink_disconnect — closes a persistent connection to a remote database dblink — executes a query in a remote database dblink_exec — executes a command in a remote database Example: postgres=# create extension dblink ; CREATE EXTENSION postgres=# create server myserver foreign data wrapper dblink_fdw OPTIONS (host'192.168.1.201',dbname'pearl',port'5432'); CREATE SERVER postgres=# create user MAPPING FOR postgres SERVER myserver OPTIONS (user'postgres',password'post'); CREATE USER MAPPING postgres=# select * from dblink('myserver','select * from emp') as a(id int,name text,age int); ...

VACUUM: VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are  deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Vacuum Options: 1.VACUUM: VACUUM  simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained.          However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. Syntax: vacuum table_name; vacuum table_name (column_name); 2.FULL: Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete.        ...

View:                   A view is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.                  CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. Updatable Views Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:     The view must have...

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...

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,...

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+             ...

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...