dinnerattentionData Management

Nov 28, 2012 (4 years and 4 months ago)


On 20 September 2010, the
PostgreSQL Global Development
Group announced the release of
PostgreSQL 9.0. This major
release comprises numerous
improvements relating to
security, application support,
monitoring, performance and
the storage of special data,
which had been long awaited by
a growing number of commercial
users. Although its roots go back
to the 1980s, it is only in recent
years that PostgreSQL has
enjoyed any significant success
in the commercial sphere. Today
it must surely have the most
conspicuous level of inter-
national market growth of any
What are the reasons for this?
Firstly, a growing number of
companies are becoming per-
suaded that open source pro-
ducts offer a real alternative for
professional users in many fields
due to their commercial advan-
tages and widespread use.
Secondly, PostgreSQL now offers
everything that a commercial
user needs: it has reached a very
high degree of maturity and
many IT service providers increa-
singly support it. Cost-effective
packages including professional
support are now available on the
Although the product was for
many years overshadowed by
the major commercial databases
and by the open source database
MySQL, which grew rapidly in
popularity during the internet
boom, development work by the
community continued.
The increasing popularity of
PostgreSQL is also due to the
fact that following its acquisition
by Oracle, the future of the
other major open source data-
base, MySQL, is uncertain. In this
context it is unsurprising that an
increasing number of users are
switching from MySQL to

Comeback of a Database Veteran
September 2011 Page 1 of 4
Post-Ingres-Project to PostgreSQL
PostgreSQL originates from a database development project carried out at
the University of California. The aim of the Ingres project, which began in
1985, was to overcome problems with contemporary database management
systems. Postgres v1, released in 1987, was the first version suitable for
professional use. The rule system was added later, and is still present in
PostgreSQL today. Version 3 in 1991 saw further development of the query
In 1993, after the release of version 4.2, the University of California closed the
project due to the rapidly growing number of support requests. In 1995, the
original Berkeley Postgres source code, supplemented by an SQL query
interpreter, was released on the web under the name Postgres95. At this
time, the product was entirely written in ANSI C. Improvements to
maintainability and performance followed, and performance 50% better than
attained by the original Berkeley Postgres was eventually achieved.6.0.
Development of PostgreSQL began in 1996, at the same time as the switch to
the current name. The first version published under the new name was
version 6.0. Development of PostgreSQL has continued ever since.

September 2011 Page 2 of 4
Today PostgreSQL is presented
as a mature and stable product
capable of meeting (almost) any
requirement for a modern
relational database system.
Many are surprised by its
markedly strong performance
that withstands comparison with
some commercial products. For
professional users, however, it is
the new replication mechanisms
that are key. Since version 9,
PostgreSQL has supported hot
standby and streaming repli-
cation. Features such as online
backup and point-in-time reco-
very complete the range of
database administration features
that are important for professio-
nal users.
The increasing popularity of
PostgreSQL is also due in no
small part to its robust trans-
action processing and high relia-
bility. Version 9 stands out due
to its additional performance
features and column triggers
plus a host of other useful exten-
sions. Despite this, PostgreSQL is
a lean and very easy to admi-
nister database that keeps the
focus on its core task: managing
PostgreSQL has one decisive ad-
vantage over many other data-
bases: it has a very high degree
of compatibility with Oracle Da-
tabase. The benefits very quickly
become apparent in practice: the
effort involved in migration from
Oracle to PostgreSQL is often
quite reasonable. Oracle and
PostgreSQL databases can often
be operated together
Despite this high degree of
compatibility with Oracle, a
number of factors need to be
considered when migrating. As a
rule, extensive practical expe-
rience of database migration,
precise planning and detailed
schedules are vital if migration
effort is to be kept within rea-
sonable limits. A number of
possible options for replication
between PostgreSQL and Oracle,
varying in terms of cost and
maturity, are available. Possibili-
ties range from a database link
via individual solutions based on
log shipping through to Oracle
Streams or the new Oracle
XStream product.
Point in time Recovery
Point-in-time recovery permits
reconstruction of a database
from a defined starting point.
Using archived transaction logs,
a database can be restored
either fully or up to a desired
point in time.

The continuous archiving of
transaction logs (if point-in-time
recovery is enabled) facilitates
the creation and configuration
of high availability solutions for
PostgreSQL using one or more
standby servers. A primary
PostgreSQL server logs and
archives every transaction, while
a secondary server works in
recovery mode, reading the
primary server's transaction logs
and executing the transactions.
Then, if the main server fails,
work can continue on the
secondary server.
PostgreSQL in Practice
Migration and/or Mixed Operation?

September 2011 Page 3 of 4
pgAdmin is the most popular and
most comprehensive open
source administration tool for
PostgreSQL. It runs on Linux,
Solaris and Windows, and can be
used to manage databases in
version 7.3 and above.
Functionality ranges from simple
SQL queries to the development
of complex databases, and all
the main PostgreSQL functions
can be managed easily via the
GUI. The application comprises
an easy-to-use SQL editor, a
server-based code editor and
SQL, batch, shell and job
scheduling agents. Server
connections are over TCP/IP or
Unix domain sockets and can be
encrypted via SSL connection
with no need to install additional
drivers. pgAdmin is developed by
a community of PostgreSQL
experts and is available in
multiple languages. Software
licenses are free of charge.
PostgreSQL also keeps pace with
the “big” database systems in
regard to monitoring. Alongside
the option of sending SNMP
traps to an existing monitoring
tool, ready-made monitoring
solutions that can be
implemented directly are also
available. One such tool is
supplied by EnterpriseDB, while
another possibility is integration
into Nagios, a popular open
source monitoring tool for which
a range of plug-ins are available.
Scope of the plug-ins ranges
from a simple check on whether
a database server is accepting
connections to graphical re-
presentations for performance
data evaluation.

Administration and Monitoring Tools

September 2011 Page 4 of 4
PostgreSQL now runs on most
Unix and Linux platforms.
Windows has also been sup-
ported since version 8.0. The
database can be expanded using
functions and user-defined data
types and operators. Import and
export of both data and
database structures (schemas)
are possible. Theoretically, there
is no limit on database size in
PostgreSQL, with hardware
performance constituting the
only restriction. Maximum table
size is 32 TB. The number of
rows is unlimited. Maximum row
size is 1.6 TB. Only the number
of columns is limited, to
between 250 and 1600 depen-
ding on data type. Fields can
accommodate up to 1 GB of
data, and an unlimited number
of indexes may be defined for
each table.

PostgreSQL database files and
client program connections are
managed by the “postmaster”
server process. Various client
programs can be used for
communication with the
postmaster. The distribution
includes psql, a simple yet
speedy database monitor. Most
Linux distributions also include
the pgAccess graphical client.
PostgreSQL features a com-
prehensive transaction concept
that supports multiversion con-
currency control (MVCC).
Complex nested queries with
subselects are possible.
Referential integrity (including
constraints and foreign keys) is
guaranteed; views can be
defined and made updateable
using rules and triggers.
Triggers and stored procedures
in various languages (PL/pgSQL,
PL/Tcl, PL/Perl, PL/Python,
PL/Java, PL/PHP, PL/Ruby, PL/R,
PL/sh, PL/Scheme) are
supported; interfaces to
numerous programming lan-
guages (including C, Delphi, C++,
Java/JDBC, Tcl, PHP, Perl,
Python, Ruby and ODBC and
.NET) are available.
For a detailed overview of the
features of PostgreSQL-Features, see
Contact details
Date: September 2011
Author: Lutz Fröhlich

© 2011 avato consulting
Characteristics (Source PostgreSQL)