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;