PGCluster

PGCluster 

• PGCluster is a multi-master, synchronous replication system based on PostgreSQL Database Server.
• Synchronous Replication System
• No delay occurs for data duplication between the Cluster DBs.
• Multi-master Cluster DB System
• There is no Cluster DBs preference for queries. A user can use any node for any type of query

• PGCluster has two functions
• A load balancing function
• A high availability function

• It has 3 kinds of servers
• Replication Server[s]
• Cluster Servers
• Load Balancing Server[s]

PGCluster – Versions

• PGCluster 1.9 is for PostgreSQL 8.3.
• Latest minor version: 1.9.0rc4, for PostgreSQL 8.3.0
• PGCluster 1.7 is for PostgreSQL 8.2.
• Latest minor version: 1.7.0rc11, for PostgreSQL 8.2.6
• PGCluster 1.5 is for PostgreSQL 8.1.
• Latest minor version: 1.5.0rc20, for PostgreSQL 8.1.11
• PGCluster 1.3 is for PostgreSQL 8.0.
• Latest minor version: 1.3.0rc10, for PostgreSQL 8.0.15
• PGCluster 1.1 is for PostgreSQL 7.4.
• Latest minor version: 1.1.2rc9, for PostgreSQL 7.4.19

PGCluster – Architecture

Replication in PostgreSQL

PostgreSQL Backup and Recovery

Backup

• As with any database, PostgreSQL database should be backed up regularly.

• There are three fundamentally differentapproaches to backing up PostgreSQL data:
• SQL dump
• File system level backup
• On-line backup

Lets discuss them in detail.

Backup – SQL Dump

• Generate a text file with SQL commands

• PostgreSQL provides the utility program pg_dump for this purpose.

• pg_dump does not block reads or writers.

• pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.

• Dumps created by pg_dump are internally consistent, that is, the dump represents a snapshot of the database as of the time pg_dump begins running.

Syntax:
pg_dump [options] [dbname]

pg_dump Options

•  -a – Data only. Do not dump the data definitions (schema)

•  -s – Data definitions (schema) only. Do not dump the data

•  -n - Dump from the specified schema only

•  -t - Dump specified table only

•  -f - Send dump to specified file

•  -Fp – Dump in plain-text SQL script (default)

•  -Ft – Dump in tar format

•  -Fc – Dump in compressed, custom format

•  -v – Verbose option

•  -o use oids

Restore – SQL Dump

• The text files created by pg_dump are intended to be read in by the psql program. The general commandform to restore a dump is– psql dbname < infile where infile is what you used as outfile for the pg_dump command. The database dbname will not be created by this command, so you must create it yourself.

• pg_restore is used to restore a database backed up with pg_dump that was saved in an archive format – i.e., a non-text format Files are portable across architectures

Syntax:
pg_restore [options...] [filename.backup]

pg_restore Options

• -d - Connect to the specified database. Also restores to this database if –C option is omittedselect pg_start_backup('label_goes_here')

• -C – Create the database named in the dump file & restore directly into it

• -a – Restore the data only, not the data definitions (schema)

• -s – Restore the data definitions (schema) only, not the data

• -n - Restore only objects from specified schema

• -t - Restore only specified table

• -v – Verbose option

Backup & Restore entire database

Backup:
(Backup data to a file)

• pg_dumpall is used to dump an entire database cluster in plain-text SQL format

• Dumps global objects - user, groups, and associated permissions

• Use PSQL to restore

Syntax:
pg_dumpall [options...] > filename.backup

pg_dumpall Options

• -a – Data only. Do not dump the data definitions(schema)

• -s - Data definitions (schema) only. Do not dump the data

• -g - Dump global objects only – i.e., users and groups

• -v – Verbose option

Recovery:
(Recover data from a file)

Syntax:
psql –d template1 < filename.backup
or
psql –d template1 –f filename.backup

Any database in the cluster can be used for the initial connection – it doesn’t have to be template1

Backup - File system level backup

• An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database.

• You can use whatever method you prefer for doing usual file system backups, for example:
• tar -cf backup.tar /usr/local/pgsql/data

• The database server must be shut down in order to get a usable backup.

• File system backups only work for complete backup and restoration of an entire database cluster.

Backup - On-line backup

• Use when database must stay up while backup is occurring.

• postgres=# select pg_start_backup('label_goes_here')

• Copy the files/directory

• postgres=# select pg_stop_backup();

• Archive_command must be set in postgresql.conf which archives WAL logs and supports PITR

Point-in-Time Recovery - PITR

PITR

• Point-in-time recovery (PITR) is the ability to restore a database cluster up to the present or to a specified point of time in the past

• Uses a full database cluster backup and the write-ahead logs found in the /pg_xlog subdirectory 

• Must be configured before it is needed (write-ahead log archiving must be enabled)

Step 1 

• Edit the "postgresql.conf" file and set the “archive_command” parameter

• Unix:
  archive_command= ‘cp –i %p /mnt/server/archivedir/%f

• Windows:
  archive_command= 'copy "%p" c:\\mnt\\server\\archivedir\\"%f"
  
  %p is absolute path of WAL otherwise you can define the path 
  %f is a unique file name which will be created on above path.

Step 2

• Make a base backup

• Connect using edb-psql and issue the command: 

• SELECT pg_start_backup(‘any useful label’);

• Use a standard file system backup utility to back up the /data subdirectory 

• Connect using edb-psql and issue the command:

• SELECT pg_stop_backup();

• Continuously archive the WAL segment files 

Final Step: 

• Recovering the database

• Clean out all existing files in the /data directory and subdirectories (be sure to backup configuration files if you have not already done so)

• Restore the database files from the backup dump

• Copy any unarchived WAL files into the /pg_xlog directory

• Create a recovery.conf file in the /data directory

• Restart the database server

Settings in the recovery.conf file:

• restore_command(string)

       Unix:
       restore_command = 'cp /mnt/server/archivedir/%f "%p"‘

     Windows:
      restore_command = 'copy c:\\mnt\\server\\archivedir\\"%f" "%p"'

      recovery_target_time(timestamp)

      recovery_target_xid(string)

      recovery_target_inclusive(boolean)

Cursors in PL/pgSQL

About Cursors
Every SQL statement executed by the PostgreSQL Server has an individual cursor associated with it :

• Implicit cursors : Declared and managed by PL/pgSQL for all DML and PL/pgSQL SELECT statements
• Explicit cursors : Declared and managed by the programmer

Explicit Cursor Operations

Controlling Explicit Cursors

Declaring the Cursor

Syntax : 

             CURSOR cursor_name IS select_statement;

Example :

      DECLARE 

      CURSOR emp_cursor IS 

      SELECT empno, ename FROM emp 

      WHERE deptno =30;

Opening the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

       BEGIN

              OPEN emp_cursor;

Fetching Data from the Cursor :

       DECLARE

              CURSOR emp_cursor IS 

              SELECT empno, ename FROM emp

              WHERE deptno =30;

              empno emp.empno%TYPE;

              lname emp.ename%TYPE;

       BEGIN

              OPEN emp_cursor;

              FETCH emp_cursor INTO empno, lname;

              raise Notice ‘%’,empno; 

              raise Notice ‘%’,lname;

              EXIT WHEN NOT FOUND;

              END LOOP; 

       END;

Closing the Cursor

       ...  

       LOOP

       CLOSE emp_cursor;

END;

Iterative Control in PL/pgSQL

LOOP Statements

• Loops repeat a statement or sequence of statements multiple times.
• There are three loop types:
• Basic loop
• FOR loop
• WHILE loop

Basic Loops

• Syntax :

          LOOP

          statement1;

          . . .

          EXIT [WHEN condition];

          END LOOP;

• Example :

         create or replace function f2() returns void as

         $$

         DECLARE

         ctr numeric:=0;

         BEGIN

               LOOP

                     raise notice '%',ctr;

                     ctr:=ctr+1;

                     EXIT WHEN ctr > 3;

               END LOOP;

         END;

$$ language plpgsql;

WHILE Loops

• Syntax :

          WHILE condition LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Use the WHILE loop to repeat statements 
• while a condition is TRUE.

FOR Loops

• Use a FOR loop to shortcut the test for the numeric of iterations.
• Do not declare the counter; it is declared implicitly.
• 'lower_bound .. upper_bound' is required syntax.

          FOR counter IN [REVERSE]

          lower_bound..upper_bound LOOP

          statement1;

          statement2;

          . . .

          END LOOP;

• Example :

          create or replace function f3() returns void as

          $$

          BEGIN

          FOR ctr IN 1..3 LOOP

          raise notice '%',ctr;

          END LOOP;

          END;

          $$ language plpgsql;

Loop through Query Results

FOR record_or_row IN query LOOP

statements

END LOOP [ label ];

create or replace function f4() returns void as

$$

DECLARE

rec emp%ROWTYPE;

BEGIN

FOR rec IN select * from emp LOOP

raise notice '%',rec.ename;

END LOOP;

END;

$$ language plpgsql;

Controlling Flow of Execution in plsql

Controlling Flow of Execution

IF Statements


Syntax:

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;


Simple IF Statement

DECLARE
myage numeric:=31;
BEGIN
IF myage < 11
THEN
raise notice ' I am a child ';
END IF;
END;


IF THEN ELSE Statement

DECLARE
myage numeric:=31;
BEGIN
IF myage < 11
THEN
raise notice ' I am a child ';
ELSE
raise notice ' I am not a child ';
END IF;
END;


CASE Expressions: Example

DECLARE
grade varchar := UPPER('a');
appraisal varchar(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
END;

The %TYPE Attribute in PL/pgSQL

The %TYPE attribute

• Is used to declare a variable according to : 
• A database column definition
• Another declared variable
• Is prefixed with :
• The database table and column
• The name of the declared variable

Declaring Variables with the %TYPE Attribute


Syntax :
identifier table.column_name%TYPE;


Examples :
emp_name emp.ename%TYPE;
balance NUMERIC;
min_balance balance%TYPE := 1000;

Use of Variables in PL/pgSQL

Variables can be used for :

• Temporary storage of data
• Manipulation of stored values
• Reusability

Handling Variables in PL/pgSQL

Variables are :

• Declared and initialized in the declarative section
• Used and assigned new values in the executable section
• Passed as parameters to PL/pgSQL subprograms
• Used to hold the output of a PL/pgSQL subprogram

         DECLARE
         emp_hiredate DATE;
         emp_deptno NUMERIC := 10;
         location VARCHAR(13) := 'Atlanta';

Guidelines for Declaring PL/pgSQL Variables

• Avoid using column names as identifiers.
• Use the NOT NULL constraint when the variable must hold a value.

PL/pgSQL

PL/pgSQL Block Structure

• DECLARE (Optional) 
• Variables, 
• cursors,
• BEGIN (Mandatory) 
• SQL statements 
• PLPGSQL statements
• EXCEPTION (Optional) 
• Actions to perform when errors occur
• END; (Mandatory)

• All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless doublequoted.
• There are two types of comments in PL/pgSQL.
• -- starts a comment that extends to the end of the line.
• /* multi-line comments */

Procedural Languages

Procedural Languages Overview

• PostgreSQL allows user-defined functions to be written in a variety of procedural languages. 
• The database server has no built-in knowledge about how to interpret the function's source text. 
• Instead, the task is passed to a handler that knows the details of that particular language.
• PostgreSQL currently supports several standard procedural languages :
• PL/pgSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java
• PL/Ruby
• Other languages can be defined by users

• PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
• PL/pgSQL has several distinct features :
• Can be used to create functions and trigger procedures,
• Adds control structures to the SQL language,
• Can perform complex computations,
• Inherits all user-defined types, functions, and operators,
• Can be defined to be trusted by the server,
• Is easy to use.
  

Indexes

An index:

• Is a schema object
• Can be used by the PostgreSQL server to speed up the retrieval of rows by using a pointer
• Can reduce disk I/O by using a rapid path access method to locate data quickly
• Is independent of the table that it indexes
• Is used and maintained automatically by the PostgreSQL server

How Are Indexes Created?

• Automatically : A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

• Manually : Users can create non unique indexes on columns to speed up access to the rows.

Creating an Index

• Create an index on one or more columns : 

         CREATE INDEX index ON table (column[, column]...);

• Improve the speed of query access to the 
• LAST_NAME column in the EMPLOYEES table :

         CREATE INDEX emp_last_name_idx ON employees(last_name);

Index Creation Guidelines :

Create an index when :

• A column contains a wide range of values
• A column contains a large number of null values
• One or more columns are frequently used together in a WHERE clause or a join condition
• The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

Do not create an index when :

• The columns are not often used as a condition in the query
• The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
• The table is updated frequently
• The indexed columns are referenced as part of an expression

Removing an Index

• Remove an index from the data dictionary by using the DROP INDEX command :

          DROP INDEX index;

• Remove the UPPER_LAST_NAME_IDX index 
• from the data dictionary :

          DROP INDEX emp_last_name_idx;

Sequences

A sequence:

• Can automatically generate unique numbers
• Is a sharable object
• Can be used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memory

CREATE SEQUENCE Statement: 

• Define a sequence to generate sequential numbers automatically
• Syntax:

        CREATE [ TEMPORARY | TEMP ] SEQUENCE name         
        [ INCREMENT [ BY ] increment ]         
        [ MINVALUE minvalue | NO MINVALUE ]         
        [ MAXVALUE maxvalue | NO MAXVALUE ]        
        [ START [ WITH ] start ]        
        [ CACHE cache ] [ [ NO ] CYCLE ]         
        [ OWNED BY { table.column | NONE } ]

Creating a Sequence

• Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. 
• Do not use the CYCLE option.

        CREATE SEQUENCE dept_deptid_seq 
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NO CYCLE;

NEXTVAL and CURRVAL Pseudocolumns

• NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.

Using a Sequence

• Insert a new department named “Support” in location Boston:

        INSERT INTO departments 
                       (department_id, department_name, location_id)
        VALUES (NEXTVAL(‘dept_deptid_seq’), 'Support', ‘Boston’);

• View the current value for the DEPT_DEPTID_SEQ sequence:

        SELECT CURRVAL(‘dept_deptid_seq’);

Caching Sequence Values

• Caching sequence values in memory gives faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table

Modifying a Sequence

• Change the increment value, maximum value, minimum value, cycle option, or cache option:

        ALTER SEQUENCE dept_deptid_seq
                    INCREMENT BY 20
                    MAXVALUE 999999
                   NO CYCLE;

Creating a View

Creating a View

•   You embed a subquery in the CREATE VIEW statement:

         CREATE [OR REPLACE] [TEMP|TEMPORARY  ] VIEW view
         [(column_name,..)] AS subquery

• The subquery can contain complex SELECT syntax.
•  Create the EMPVU80 view, which contains details of employees in department 80:

        CREATE VIEW empvu80  AS SELECT employee_id, last_name, salary
        FROM employees WHERE  department_id = 80;

• Describe the structure of the view by using the psql \d command:

       \d empvu80

Retrieving Data from a View

SELECT * FROM   empvu80;


Creating a Complex View

• Create a complex view that contains group functions to display values from two tables:

    CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
    AS SELECT   d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
    FROM     employees e JOIN departments d
    ON       (e.department_id = d.department_id)
    GROUP BY d.department_name;

Removing a View

• You can remove a view without losing data because a view is based on underlying tables in the database.

       Syntax :
       DROP VIEW view;

      Example :
      DROP VIEW empvu80;

Views

What Is a View?

• A view is nothing but a window of an existing table by which one can view and can also change the values in tables. 
• The main point aspect of view is views have no storage space. For this reason views are also called as virtual tables. 
• Thus in other words a view is a database object that gives a logical representation of one or more database tables.

Advantages of Views

• To restrict data access.
• To make complex queries easy.
• To provide data independence.
• To present different views of the same data.

Simple Views and Complex Views

Feature	Simple Views	Complex Views
Number of Tables	One	One or more
Contain Functions	No	Yes
Contain Group of Data	No	Yes

ALTER TABLE Statement

ALTER TABLE Statement :

• Use the ALTER TABLE statement to :
  – Add a new column
  – Modify an existing column
  – Define a default value for the new column
  – Drop a column

Dropping a Table :

• All data and structure in the table are deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• All constraints are dropped.
• You cannot roll back the DROP TABLE statement.
• DROP TABLE dept30;
  

Creating a Table by Using a Subquery

Creating a Table by Using a Subquery :

• Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
• Match the numeric of specified columns to the numeric of subquery columns.
• Define columns with column names and default values.
• CREATE TABLE table [(column, column...)] AS subquery;

Violating Constraints

Query :

• UPDATE emp SET department_id = 29 WHERE department_id = 30;

Error :

• insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"

DETAIL :

• Key (deptno)=(29) is not present in table "dept"
• Department 29 does not exist

You cannot delete a row that contains a primary key that is used as a foreign key in another table.

Query :

• DELETE FROM departments WHERE department_id = 60;

Error :

• update or delete on table "dept" violates foreign key constraint "emp_re f_dept_fk" on table "emp";

Detail :

• Key (deptno)=(10) is still referenced from table "emp".
  

CHECK Constraint

CHECK Constraint :

Defines a condition that each row must satisfy

• The following expressions are not allowed :
• References to CURR_VAL, NEXT_VAL and CTID pseudocolumns
• Queries that refer to other values in other rows
• ..., salary numeric(2)CONSTRAINT emp_salary_minCHECK (salary > 0),...

CREATE TABLE : Example

FOREIGN KEY Constraint

• 
• Defined at either the table level or the column level
• CREATE TABLE emp(employee_id numeric(6), last_name varchar(25) NOT NULL, email varchar(25), salary numeric(8,2), commission_pct numeric(2,2), hire_date DATE NOT NULL, ... department_id numeric(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES dept(deptno), CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY Constraint - Keywords :

• FOREIGN KEY : Defines the column in the child table at the table-constraint level
• REFERENCES : Identifies the table and column in the parent table
• ON DELETE CASCADE : Deletes the dependent rows in the child table when a row in the parent table is deleted
  
• ON DELETE SET NULL : Converts dependent foreign key values to null