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;

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;

Limit the rows that are retrieved by a query

Limiting Rows Using a Selection :

• Restrict the rows that are returned by using the WHERE clause
• The WHERE clause follows the FROM clause
• SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

• eg : select*from emp where deptno=10;

Character Strings and Dates :

• Character strings and date values are enclosed by single quotation marks
• Character values are case-sensitive, and date values are format-sensitive

• eg : select*from emp where ename='CLARK';

Important Meta Commands in PostgreSQL

\c database name [username]

• to connect to a database

\l

• will return you the list of available databases

\du

• will return you the list of databases users

\dt

• will return you the list of tables in connected database

\d table name

• will returns you the structure of the given table

\e

• opens the sql query in editor or opens an editor to write query

show port

• will returns you the port no on which postgres is running

few notations which are supposed to use while parsing these inputs :

• -d : database name
• -U : User name
• -h : Host name
• -p : Port No.
• -f : File name (sql)
• -w : Password

Working with Duplicate Rows in SQL

Duplicate Rows :

• The default display of queries is all rows, including duplicate rows
• postgres=# select deptno from emp;
  deptno
  --------
  20
  30
  30
  20
  30
  30
  10
  20
  10
  30
  20
  30
  20
  10
  (14 rows)

Selecting Unique Rows (Deleting Duplicates) :

• postgres=# select distinct deptno from emp;

deptno
--------
10
20
30
(3 rows)

Using Literal Character Strings in SQL

A concatenation operator :

• Links columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression

Literal Character Strings :

• A literal is a character, a number, or a date that is included in the SELECT statement
• Date and character literal values must be enclosed by single quotation marks
• Each character string is output once for each row returned

Example :

• postgres=# select ename||' earns '||sal as Report from emp;

report
----------------------
SMITH earns 800.00
ALLEN earns 1600.00
WARD earns 1250.00
JONES earns 2975.00
MARTIN earns 1250.00
BLAKE earns 2850.00
CLARK earns 2450.00
SCOTT earns 3000.00
(8 rows)

postgres=#

here as you can see we have added a string "earns" to the two different fields of the table and the displayed result in one single column.

Concatination is done using ||

Defining a Column Alias

A column alias :

• Renames a column heading
• Is useful with calculations
• AS keyword is mandatory between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive

Example :

• postgres=# select ename, sal, comm as "commission" from emp;

ename | sal | commission
--------+---------+------------
SMITH | 800.00 |
ALLEN | 1600.00 | 300.00
WARD | 1250.00 | 500.00
JONES | 2975.00 |
MARTIN | 1250.00 | 1400.00
BLAKE | 2850.00 |
CLARK | 2450.00 |
SCOTT | 3000.00 |

In the previous post we have seen when we performed a mathematical operation the resultant column was labeled with ?Column? automatically... but here the same column has labeled as commission.

This is how we can name/rename the column name in SQL as per our requirement.

Defining a Null Value

Null Value :

• A null is a value that is unavailable, unassigned, unknown, or inapplicable
• A null is not the same as a zero or a blank space

Example :

• postgres=# select ename, sal, comm from emp;

ename | sal | comm
--------+---------+---------
SMITH | 800.00 |
ALLEN | 1600.00 | 300.00
WARD | 1250.00 | 500.00
JONES | 2975.00 |
MARTIN | 1250.00 | 1400.00
BLAKE | 2850.00 |
CLARK | 2450.00 |

Using Arithmetic Operators with SQL SELECT

Using Arithmetic Operators & Expressions :

Create expressions with number and date data by using arithmetic operators

• + : Add
• - : Subtract
• * : Multiply
• / : Divide

Example :

postgres=# select ename, sal, sal+100 from emp;

ename | sal | ?column?
--------+----------+---------
SMITH | 800.00 | 900.00
ALLEN | 160.00 | 1700.00
WARD | 1250.00 | 1350.00

• The example in the slide uses the addition operator to calculate a salary increase of $100 for all employees. The slide also displays a SAL+100 column in the output
• Note that the resultant calculated column SAL+100 is not a new column in the EMP table; it is for display only

Operator Precedence :

• postgres=# select ename, sal, 12*sal+100 from emp limit 3;

ename | sal | ?column?
-------+---------+----------
SMITH | 800.00 | 9700.00
ALLEN | 1600.00 | 19300.00
WARD | 1250.00 | 15100.00
(3 rows)

Writing SQL Statements

Writing SQL Statements :

• SQL statements are not case-sensitive
• SQL statements can be on one or more lines
• Keywords cannot be abbreviated or split across lines
• Clauses are usually placed on separate lines
• Indents are used to enhance readability
• In SQL Query Tool(PGADMIN3), SQL statements can optionally be terminated by a semicolon (;)
• Semicolons are required if you execute multiple SQL statements
• In PSQL Client, you are required to end each SQL statement with a semicolon (;)

SQL SELECT Statement

Capabilities of SQL SELECT Statements :



Basic SELECT Statement :

• SELECT * | {[DISTINCT] column | expression [alias],...} FROM table;
• SELECT identifies the columns to be displayed
• FROM identifies the table containing those columns
• * specifies Selecting All Columns
• column name specifies : selecting Specific Columns
• eg1 : postgres=# select*from dept; (will return the all the columns of table dept)
  
• eg2 : edb=# select deptno, dname from dept; (will return the specific columns of table dept)