PostgreSQL

PostgreSQL . Free object-oriented relational database management system , released under the BSD license .

Like many other open source projects , PostgreSQL development is not managed by a single company but is managed by a community of developers and commercial organizations that work on its development. This community is called the PGDG (PostgreSQL Global Development Group).

Summary

[ hide ]

  • 1 Product name
  • 2 History
  • 3 Features
    • 1 High attendance
    • 2 Other Features
    • 3 Functions
  • 4 Configuration
  • 5 Products around PostgreSQL
    • 1 Commercial Alternatives
    • 2 GIS
    • 3 Replication
    • 4 Administration tools
    • 5 Text search
    • 6 XML
  • 6 Awards
  • 7 External links

Product name

The use of uppercase characters in the PostgreSQL name may confuse some people at first glance. The different pronunciations of “SQL” can be confusing. PostgreSQL developers pronounce it / poːst ɡɹɛs kjuː ɛl / ;. It is also common to hear abbreviated as simply “Postgres”, which was its original name. Due to its support of the SQL standard among most relational databases, the community considered renaming the previous Postgres. However, the PostgreSQL Core Team announced in 2007 that the product would still be called PostgreSQL. The name refers to the origins of the project as the “post-Ingres” database, and the original authors also developed the Ingres database.

History

PostgreSQL has had a long evolution, which begins in 1982 with the Ingres project at the University of Berkeley. This project, led by Michael Stonebraker, was one of the first attempts to implement a relational database engine. After having worked for a long time at Ingres and having had a commercial experience with himself, Michael decided to return to the University in 1985 to work on a new project about the Ingres experience, this project was called post-income or simply POSTGRES .

The post-entry project aimed to solve the problems with the relational database model that had been clarified in the early 1980s. The main of these problems was the inability of the relational model to understand “types”, that is, combinations of simple data that make up a single unit. These are currently called objects. They endeavored to introduce as few functionalities as possible to complete the type support. These functionalities included the ability to define types, but also the ability to describe relationships – which until then were widely used but fully maintained by the user. In Postgres the database “understood” relationships and could obtain information from related tables using rules.

The following list shows the most important milestones in the life of the Postgres project.

  • 1986: Several papers were published that described the bases of the system.
  • 1988: A usable version was already available.
  • 1989: The group released version 1 for a small community of users.
  • 1990: Version 2 was published, which had the rules system practically rewritten.
  • 1991– Release of version 3, it added the capacity of multiple storage engines.
  • 1993: significant growth of the user community, which demanded more features.
  • 1994: after version 4 was released, the project ended and the group disbanded.

After the POSTGRES ‘project ended, two university graduates, Andrew Yu and Jolly Chen, started working on the POSTGRES code, this was possible since POSTGRES was licensed under the BSD , and the first thing they did was add support for the SQL language to POSTGRES, since it previously had an interpreter for the QUEL query language (based on Ingres), thus creating the system that they named Postgres95.

By 1996 outsiders like Marc Fournier from Hub.Org Networking Services, Bruce Momjian and Vadim B. Mikheev joined the project who provided the first non-university development server for the open source development effort and started to work to stabilize the Postgres95 code.

In 1996 they decided to change the name of Postgres95 to reflect the characteristic of the SQL language and ended up calling it PostgreSQL, the first open source version of which was released on August 1 , 1996 . The first formal version of PostgreSQL (6.0) was released in January 1997 . Since then, many enthusiastic developers of database engines joined the project, coordinated via the Internet, and together began to incorporate many features into the engine.

Although the license allowed PostgreSQL to be commercialized, the code was not originally developed for commercial purposes, which is surprising considering the benefits PostgreSQL offered. The main derivation originated when Paula Hawthtorn (a member of the original Ingres team who switched to Postgres) and Michael Stonebraker formed Illustra Information Technologies to market Postgres.

In 2000 , former Red Hat investors created the Great Bridge company to market PostgreSQL and compete against commercial database providers. Great Bridge sponsored several PostgreSQL developers and donated resources back to the community, but in late 2001 it closed due to stiff competition from companies like Red Hat and poor market conditions.

In 2001 , Command Prompt, Inc. launched Mammonth PostgreSQL, the oldest commercial PostgreSQL distribution. It continues to support the PostgreSQL community through the sponsorship of developers and projects, including PL / Perl, PL / php, and hosting community projects like PostgreSQL Build Farm.

In January 2005 , PostgreSQL received support from database provider Pervasive Software, known for its Btrieve product that was used on the Novell Netware platform, Pervasive announced commercial support and community engagement and achieved some success. However, in July of 2006 he left the PostgreSQL support market.

In mid- 2005, two other companies announced plans to market PostgreSQL with an emphasis on separate niche markets. EnterpriseDB added functionalities that made applications written to work with Oracle easier to run with PostgreSQL. Greenplum contributed improvements directly oriented to Data Warehouse and Business Intelligence applications, including the BizGres project.

In October of 2005 , John Loiacono, executive vice president of software at Sun Microsystems said: “We’re going after the OEM Microsoft but we are seeing PostgreSQL now”, although no specifications were given at that time. As of November 2005, Sun Solaris 10 (release 6/06) included PostgreSQL.

In August of 2007 EnterpriseDB announced the Postgres Resource Center and EnterpriseDB Postgres, designed to be a fully configured distribution of PostgreSQL including many contributed modules and aggregates. EnterpriseDB Postgres was renamed Postgres Plus in March of 2008 .

The PostgreSQL project continues to make major releases annually and minor bug fixes, all available under the BSD license, and based on contributions from commercial vendors, contributing companies, and mostly open source programmers.

characteristics

Some of its main characteristics are, among others:

High attendance

Using a system called MVCC (Multiversion Concurrent Access) PostgreSQL allows that while a process writes to a table, others access the same table without the need for locks. Each user gets a consistent view of what was committed last. This strategy is superior to the use of table or row locks common in other databases, eliminating the need for the use of explicit locks. Wide variety of native types

PostgreSQL natively provides support for:

  • Arbitrary precision numbers.
  • Unlimited length text.
  • Geometric figures (with a variety of associated functions)
  • Addresses IPIPv4 and IPv6 ).
  • CIDR style address blocks.
  • MACaddresses .

Additionally, users can create their own data types, which can be fully indexable thanks to the GiST infrastructure of PostgreSQL. Some examples are the GIS data types created by the PostGIS project.

Other features

  • Foreign Keys also called Foreign Keys or Foreign Keys.
  • Triggers: A trigger is defined in a specific action based on something that occurs in the database. In PostgreSQL this means executing a stored procedure based on a certain action on a specific table. Now all triggers are defined by six characteristics:
    • The name of the trigger
    • The moment when the trigger should start
    • The trigger event should fire on …
    • The table where the trigger will fire
    • The frequency of execution
    • The function that could be called

So combining these six features, PostgreSQL will allow you to create extensive functionality through its trigger activation system.

  • Transactional integrity.
  • Table inheritance.
  • Data types and geometric operations.
  • Support for distributed transactions. It allows PostgreSQL to be integrated into a distributed system made up of various resources (eg, a PostgreSQL database, another Oracle, an IBM MQ JMS message queue and an ERP SAP) managed by an application server where success (“commit “) of the goblal transaction is the result of the success of local transactions.

Features

Blocks of code running on the server. They can be written in various languages, with the power that each one gives, from basic programming operations, such as forks and loops, to the complexities of object-oriented programming or functional programming.

Triggers ( triggers in English) are functions linked to operations on data.

Some of the languages ​​that can be used are the following:

  • An own language called PL / PgSQL(similar to the PL / SQL of oracle).
  • C.
  • C ++.
  • Java PL / Java web.
  • PL / Perl.
  • plPHP.
  • PL / Python.
  • PL / Ruby.
  • PL / sh.
  • PL / Tcl.
  • PL / Scheme.
  • Language for applications statistics Rthrough PL / R .

PostgreSQL supports functions that return “rows”, where the output can be treated as a set of values ​​that can be treated equal to a row returned by a query.

The functions can be defined to be executed with the rights of the executing user or with the rights of a previously defined user. The concept of functions, in other DBMSs, are often referred to as “stored procedures”.

Setting

The first thing to do is create a user with super administrator permission, it is simple:

  1. First we authenticate as root sudo su.
  2. We become the postgres user, which is created when we install postgres your postgres
  3. We create the new user with super administrator permissions createuser -s -P username

The other drawback I found was that I could only authenticate locally from the PC where I installed the postgres and I needed that they could be authenticated from any PC in the same subnet of the lab. Solution: First we must tell it not to listen to requests only from the localhost, for that we edit the file located in /etc/postgresql/8.3/main/postgresql.conf

# sudo nano /etc/postgresql/8.3/main/postgresql.conf

Once we open the file we locate the following line:

#listen_addresses = ‘localhost’

We replace that line with the following:

listen_addresses = ‘*’

This way you will hear requests from the addresses allowed in the file pg_hba.conf

  1. Now we must tell it in the file pg_hba.conf which are the authorized IP addresses to connect to the server

sudo nano /etc/postgresql/8.3/main/pg_hba.conf

If we want it to listen from an entire subnet, we add the following line at the end

host all all 10.35.12.0/24 md5

There it is being said that for all databases (first all), all users who try to log in (second all) from Ip 10.35.12.1 to 10.35.12.254 can log in and use md5. If instead of the subnet we are only interested in having them log in from the IP 10.35.12.26:host all all 10.35.12.26 255.255.255.255 trust AND if you want them to be logged in from any PC in the UCI: h ost all all 0.0.0.0 0.0.0.0 md5 Then the postgres is restarted so that it takes the configuration that we have given it:

sudo /etc/init.d/postgresql-8.3 restart.

Well, up to here I solved what I needed, optionally if you want to manage the Postgres server from the web you can install the phppgadmin package

sudo apt-get install phppgadmin

Then to access from the web you do it with http: // server_ip / phppgadmin , you must have installed Apache to be able to access through the web. Note: If when trying to access from the web the server tells us that it cannot find the / phppgadmin address, we must tell Apache to load the phppgadmin configuration file, for that: We edit the Apache configuration file

sudo nano /etc/apache2/apache/apache2.conf

And we tell it to include the phppgadmin configuration file, for that we add the following line at the end: Include /etc/phppgadmin/apache.conf and restart Apache:

sudo /etc/init.d/apache2 restart

There is also a client for Postgres called PgAdmin, to install it you must install the pgadmin3 package on the PC from which you want to manage the server. If you have an application programmed with php on the same server and you need to access the postgres from it, you must install the following packages: php5, php5-pgsql (php support for postgres), apache2 (Apache web server), for this you run the following command:

sudo apt-get install php5 php5-pgsql apache2

Products around PostgreSQL

The PGDG only develops the Data Engine and a small number of utilities, to enhance the work with PostgreSQL it is usually necessary to add external utilities created especially for this engine, some of these tools are:

Commercial Alternatives

Thanks to its BSD license , the use of the code to be commercialized is allowed. One of the example cases is that of Enterprise DB (Postgresql Plus), which includes several aggregates and a Java- based development interface . Among other companies that use Postgresql to market is CyberTech (Germany), with its CyberCluster product.

CHALK

PostGIS

Extension that adds support for geographic objects to PostgreSQL and allows analysis using spatial SQL queries or through connection to GIS (Geographic Information System) applications.

Replication

PgCluster

Multi master replication.

Slony-I

Master slave replication.

PyReplica

Asynchronous multi-master and slave master replication.

Administration tools

PgAdmin3

Visual desktop environment.

PgAccess

Visual desktop environment.

PhpPgAdmin

Web environment.

psql

Console client.

Database Master

Visual desktop environment.

Text search

Full text search

Included in the kernel as of version 8.3.

Via Tsearch2 and OpenFTS for versions prior to 8.3.

XML

XML / XSLT support

Via XPath extensions in the contrib section .

Awards

PostgreSQL has received the following recognitions:

  • 1999LinuxWorld Editor’s Choice Award for Best Database
  • 2000Linux Journal Editors’ Choice Award for Best Database
  • 2002Linux New Media Editors Choice Award for Best Database
  • 2003Linux Journal Editors’ Choice Award for Best Database
  • 2004Linux New Media Award for Best Database
  • 2004 Linux Journal Editors’ Choice Award for Best Database
  • 2004 ArsTechnica Best Server Application Award
  • 2005Linux Journal Editors’ Choice Award for Best Database
  • 2006Linux Journal Editors’ Choice Award for Best Database
  • 2008com Product of the Year Database Tool

 

Leave a Comment