Data Types in PostgreSQL

There are number of data types in PostgreSQL and user can create their own data types as PostgreSQL is identified with object oriented database.

Data types in PostgreSQL has been differentiated on the basis of its use and its type, below is the list of data types available in PostgreSQL

• Numeric types
• Monetary types
• Character types
• Binary types
• Date/Time types
• Boolean types
• Enumerated types
• Geometric types
• Network Address types
• Bit string types
• Text search types
• UUID types
• XML types
• JSON types
• Arrays
• Composite types
• Range types
• Domain types
• Object oriented types
• pg_lsn types
• pseudo types

Now we will discussion each one in details

Numeric types

Below table represents all number data types available

Name	Storage Size	Description	Range
smallint	2 bytes	Small range integer	-32768 to +32767
integer	4 bytes	integer	-2147483648 to +2147483647
bigint	8 byes	Large range integer	-922337203685475808 to +922337203685475807
decimal	variable	User specified precision, exact	Upto 131072 digits before decimal, upto 16383 digits after decimal
numeric	Variable	User specified precision	Upto 131072 digits before decimal, upto 16383 digits after decimal
real	4 bytes	Variable precision inexact	6 decimal digit precision
double precision	8 bytes	Variable precision inexact	15 decimal digits precision
smallserial	2 bytes	Small auto increment integer	1 to 32767
serial	4 bytes	auto increment integer	1 to 2147483647
bigserial	8 bytes	large auto increment integer	1 to 922337203685475807

Monetary types

Below is the monetary data type available to use in PostgreSQL

Name	Storage Size	Description	Range
Money	8 bytes	Currency amount	-922337203685475808.08 to +922337203685475808.07

Character types

Below are described character types in PostgreSQL

Name	Description
Character varying(n), varchar(n)	Variable length with limit
Character(n) , char(n)	Fixed length, blank padded
Text	Variable unlimited length

Binary types

Name	Storage	Description
bytea	1 or 4 bytes plus actual binary string	Variable length binary string

bytea is binary data types allows to store binary strings

bytea Hex format:

Select '\xDEADBEEF';

bytea Escape format:

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;

     bytea

----------------

 abc klm *\251T

Date/Time types

Below are the available data types related to Date/time in PostgreSQL

Name	Storage Size	Description	Low value	High value	resolution
Timestamp[(p)][without timezone]	8 bytes	Both date and time no timezone	4713BC	294276 AD	1 microsec
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone	4713BC	294276 AD	1 microsec
date	4 bytes	date (no time of day)	4713BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	time of day (no date)	00:00:00	24:00:00	1 microsec
time [ (p) ] with time zone	12 bytes	time of day (no date), with time zone	00:00:00+1459	24:00:00-1459	1 microsec
interval [ fields ] [ (p) ]	16 bytes	time interval	178000000 years	178000000 years	1 microsec

Boolean type

Boolean data type is command and same is  every database system

Name	Storage Size	Description
Boolean	1 byte	State of true or false

Enumerated types

Enumerated data types are comprised static ordered set of  values. They are equivalent to enum types supported in number of programming languages.

example-days of the week, set of status value for piece of data

this kind of types are created as below

CREATE TYPE order AS enum ('Yes', 'No','NA');

Geometric type

Geometric data types represents two dimensional spatial objects, below are available data types in PostgreSQL

Name	Storage Size	Description	Representation
Point	16 bytes	Point of plane	(x,y)
Line	32 bytes	Infinite line	{A,B,C}
Lseg	32 bytes	Finite line segment	((x1,y1),(x2,y2))
Box	32 bytes	Rectangular box	((x1,y1),(x2,y2))
Path	16+16n bytes	Closed path	[(x1,y1)…..]
path	16+16n bytes	Open path	((x1,y1)…..)
Polygon	40+16 bytes	Polygon	((x1,y1)…..)
circle	20 bytes	circle	<(x,y), r> (central point and radius

Network types

PostgreSQL offers data types to store IPv4, IPv6 and MAC addresses

Name	Storage Size	Description
cidr	7 or 19 bytes	IPv4 and IPv6 networks
inet	7 or 19 byes	IPv4 and IPv6 hosts and networks
macaddr	6 bytes	MAC addresses
macaddr8	8 bytes	MAC address (EUI -64 format)

Bit string types

Bit string types are strings of 1's and 0's. They can be used to store or visualize bit masks

bit(n)-- > exact match

bit varying(n) -- > variable length

Text Search types

there are two data types to support full text search 

tsvector

a tsvector value is stored list of distinct lexemes, which are words that have been normalized to merge different variables of the same word.

example

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

                      tsvector

----------------------------------------------------

 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat' 

tsquery

A tsquery value stores lexemes, that are to be searched for  and combine them using Boolean operator &(AND), | (OR) and !(NOT) as well as the phrase search operator <->

SELECT 'fat & rat'::tsquery;

    tsquery    

---------------

 'fat' & 'rat' 

UUID type

• This data stores universally unique identifier(UUID) as per standards defined.
• This identifier is 128 bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by any one else in the known universe  using same algorithm
• UUID is written as sequence of lower case hexadecimal digits , in several groups separated by hyphens specially group of 8 digits followed by three groups of 4 digits followed by group of 12 digits, total 32 digits representing 128 bits
• for example

           a0eebc99-9cob-4ef8-bb6d-6bb9bd380011

XML types

XML data types can be used to store XML data. To use data type, installation should be done using below option

configure --with-libxml

This data type can store well formed "documents", as defined by xml standards as well as "content" fragments which are defined by production XMLDecl? content in XML standards.

JSON types

JSON data type are for storing JSON (java script object notation) data

There are two types

1. json
2. jsonb

The major difference is one of efficiency 

• json- stores an exact copy of input text
• jsonb- 
• Data stored in decomposed binary format that makes it slightly slower to input due to added conversion overhead.
• It also support indexing

Json primitive type	PostgreSQL type	Notes
String	Text	\u0000 is allowed, as are non-ASCII unicode escape if db encoding is UTF-8
Number	Numeric	NaN and infinity values are disallowed
Boolean	Boolean	Lower case true and false
Null	(none)	SQL null is different concept

For example

select '5' ::json;

select '[1,2,"foo",null]'::json;

Composite types

A composite type represents the structure of row or record; it is essentially just a list of field names and their data types

Declaration

CREATE TYPE complex AS (

r double precision,

i double precision

);

CREATE TYPE inventory_item AS (

name text,

supplied_id integer,

price numeric

);

syntax is quite similar to create table command without any constraints

Use of composite types

CREATE TABLE on_hand (

item inventory_item,

count integer

);

Inserting data 

insert into on_hand values (ROW('Fuzzydice',45,1.99),1000)

Accessing data

select (item).name from on_hand where (item).price>9.99;

or

select (on_hand.item).name from on_hand where (on_hand.item).price >9.99;

Range types

There are following range types in PostgreSQL

Name	Description
int4range	Range of integer
Int8range	Range of big integer
numrange	Range of numeric
tsrange	Range of timestamp without timezone
tstzrange	Range of timestamp with timezone
daterange	Range of date

These are the data types available in PostgreSQL, this includes 11 version as well.

Read More

Oracle Data Guard Protection Modes

There are three types of data guard protection modes in oracle

1. Maximum Protection
2. Maximum Availability
3. Maximum Performance (default)

Before proceeding with next explaining these three modes, you can visit Standby database introduction to understand the purpose of standby database

All of these protection modes provides high data protection, but they differ in terms of availability and performance of primary database

Maximum Protection Mode

• Maximum protection mode guarantees that no data loss will occur if primary database fails by any means
• To provide this level of protection, redo data needs to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before transaction commits.
• To ensure that data loss can not occur, the primary database shutdown rather than continue processing transactions if it can not write to at lease one sync standby database
• Recommendation
• As this protection mode prioritizes data protection over primary database availability, oracle recommends to have minimum two standby database

Maximum availability Mode

• This protection mode provides the highest level of data protection that is possible without affecting availability of the primary database.
• Under normal operations, transactions to not commitment until all redo data needed to recover those transactions has been written to online redo log and based on user configuration one of the following is true
• Redo has been received at standby database and acknowledge sent to primary
• redo has been received and written to standby redo log and acknowledgement sent to primary
• In case primary does not receive acknowledgement from at least one synchronized standby database then it operates as if it were maximum performance mode to preserve primary database availability until it is again able to write it to redo stream o synchronized standby database
• To fully benefit from complete oracle data guard validation at the standby database,be sure to operate in real time apply mode so that redo changes are applied to standby database as fast as they are received.

Maximum Performance Mode

• This protection mode provides highest level of data protection that is possible without affecting the performance of primary database.
• This achieved by allowing transactions to commit as soon as all redo data generated by transactions has been written to the online log
• Redo data is also written to one or mode standby databases but this is due to asynchronously with respect to transactions commitment, so primary database performance is unaffected by time required to transmit redo data and receive acknowledgement from a standby database.
• This protection mode offers slightly less data protection than maximum availability  and has minimal impact on primary performance
• This is default protection mode in oracle.

Setting protection mode of primary database

Protection mode can be modified at any time as long as configuration meets requirements of mode.
Below are the steps for the same

• Decide the protection among the above mentioned three modes
• Verify the protection mode requirements with at lease one configuration standby database. Redo transport required for protection modes are

Availability	AFFIRM/NOAFFIRM	SYNC	DB_UNIQUE_NAME
Performance	NOAFFIRM	ASYNC	DB_UNIQUE_NAME
Protection	AFFIRM	SYNC	DB_UNIQUE_NAME

• Verify if DB_UNIQUE_NAME is set as this is mandatory parameter for data guard configuration
• Verify LOG_ARCHIVE_CONFIG parameter is set, its value includes a DG_CONFIG which includes DB_UNIQUE_NAME of primary and standby database for example 

       LOG_ARCHIVE_LOG='DG_CONFIG=('PRIM','STDBY')'

• Set data protection modes using below command

alter database set standby database to maximum {availability|performance|protection};

• Verify on primary using below command

select protection_mode from v$database;

Read More

Standby database Introduction

Standby database is an important aspect in oracle, its useful in disaster recovery scenarios and to carry out testing on production data without hurting production database performance. We will discuss more about standby database.

• Standby database is database replica created from backup of primary database
• By applying archived redo logs from primary database to standby database, once can keep two databases in synchronized
• Purpose of having standby database 
• Disaster protection
• Data corruption protection
• Supplemental reporting or testing

Configuration Options

We can setup a standby database in several different ways depending on method for

• Transferring archived redo logs
• Applying archived redo logs

Example: 

Managed standby environment allows primary database to automatically archives redo logs to standby database site so long as standby instance is started

Non-managed standby environment makes it compulsory to transfer archived redo logs manually

Managed standby mode - it automatically applies logs received from primary database

manual recovery mode - apply logs manually

Pros and Cons of standby database

Pros

• A standby database is powerful configuration for both disaster recovery and supplementary reporting and testing 
• We can maintain several standby database in geographically diverse locations
• Maintain primary and standby database on same machine on different drive/file system
• We can make standby database the new primary database with minimal loss of time and data
• Standby database provides protection against
• erroneous batch jobs
• user errors
• applying corruption on primary by not applying corrupt data on standby site

Cons

• It requires additional machine if you want to maximize disaster protection by keeping standby database on separate host
• Implementation and maintenance of Net8
• Additional system resource and cost
• Extra efforts for administration of standby site. 

Types of standby databases

In oracle standby database can be one of these types

1. Physical standby database
2. Logical standby database
3. Snapshot standby database

Physical standby database

• Physical standby database is an exact block for block copy of primary database
• This physical standby database uses process called redo apply i.e. redo received from primary database gets applied to standby database using recovery process
• This can be opened for read only access to execute number of reporting and testing queries
• With license of active data guard redo can be applied during database is open mode
• Benefits:
• Disaster recovery and high availability
• Data protection
• Reduction in primary database workload performance

Logical standby database

• Initially logical standby database is identical copy of primary database but later can be altered to have different structure
• This type of standby database is updated or brought up in sync by executing SQL statements.
• Oracle data guard automatically applies data from archived redo logs on standby database by transforming data into SQL statements and then executing SQL statements on logical standby database
• Database must remain open as it uses SQL statements to update logical standby database
• Although this database remain in read/write mode its target table will be only available for read only operations
• Benefits: 
• Ideal for High Availability
• Minimizes downtime on software update
• support for reporting and decision support requirements

Snapshot standby database

• Snapshot standby database is type of updatable standby database has full data protection for primary database
• A snapshot standby database receives and archives but does not apply redo data from its primary database
• Redo data received from primary database is applied when snapshot standby database is converted back into physical standby database.
• The data of primary database is fully protected as snapshot standby database can be converted to physical standby database any time and redo is then applied
• Benefits
• It provides exact replica of production database for development and testing purpose while data protection at all time
• It can be easily refreshed to contain current production data by converting to physical standby database and re synchronizing

In next article we are going to discuss about different protection modes in oracle for data guard configuration.

Read More

Configure and Use of Flash Recovery Area

Flash recovery area i.e. FRA available since oracle 10g edition and is identified as unified storage location for all recovery related files in oracle database. We will discuss more the about flash recovery area

• The flash recovery area cane reside in single file system or as an ASM diskgroup
• The following permanent items are stores in flash recovery area

Control file :

• Oracle stores one copy of the control file in flash recovery area during installation

Online redo logfile

• You can store one mirrored copy from each redo log file group in flash recovery area

 Following transient items are stored in flash recovery area

• Archived redo log files
• Flashback logs
• Controlfile automatic backup
• Datafile copies (during RMAN backup/cloning as ‘backup as copy’ command)
• RMAN backupsets
• RMAN files

There are three initialization parameters controls location of new control file, online redo logs and datafile are DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST

• The DB_CREATE_FILE_DEST specifies the default location for oracle managed datafiles (OMF) if we do not explicitly specify destination
• The DB_CREATE_ONLINE_LOG_DEST specifies upto five location for online redo logs files, if this parameter is not specified the oracle uses DB_CREATE_FILE_DEST as destination for online redo log files
• The DB_RECOVERY_FILE_DEST specify the default location for flash recovery are
• Recommended size of flash recovery are is sum of database size , size of incremental backup and size of all archived logs that have not been moved to tape or any other location.

Setting Flash Recovery Area

Setting flash recovery area for use requires below two parameters to be set

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

Make sure you enough space under server file system or ASM diskgroup

Setting parameters

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

----------------------------------- ----------- -----------

db_recovery_file_dest                string  

db_recovery_file_dest_size           big integer

set recovery location
alter system set DB_RECOVERY_FILE_DEST = '+RECO01' scope = both;

set the size of FRA
alter system set DB_RECOVERY_FILE_DEST_SIZE = 4G scope = both;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

---------------------------------- ----------- -----------

db_recovery_file_dest                string      +RECO01

db_recovery_file_dest_size           big integer  4G

Usage of Flash Recovery Area

The database alert log will indicate, if database FRA limit is full though you have enough space on file system or diskgroup.

Usage of FRA can be determined using below queries

Check location and size using v$recovery_file_dest

SQL>  select * from  v$recovery_file_dest;

NAME  SPACE_LIMIT  SPACE_U SPACE_REC NUMBER_OF_FILES       CON_ID
----------------- -------- --------- --------------- ---------------+FLASH 107374182400  2097152       0              2              0

Check usage for areas and purpose

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_S_USED PERCENT_S_RECLAIMABLE NUMBER  CON_ID
----------- -------------- ------------------- --------- ---------CONTROL FILE            0               0             0           0

REDO LOG                0               0             0           0
ARCHIVED LOG            0               0             2           0
BACKUP PIECE            0               0             0           0
IMAGE COPY              0               0             0           0
FLASHBACK LOG           0               0             0           0
FOREIGN ARCHIVED LOG    0               0             0           0
AUXILIARY DATAFILE COPY 0               0             0           0

Size of flash recovery area can be modified using alter system command. Database outage will not be required to carry out this change

alter system set db_recovery_dest_size = 6G scope=both;

Read More