PostgreSQL - what is this program? Free Object-Relational Database Management System (DBMS) PostgreSQL

Unlike other transactional databases, Postgres prioritizes ANSI SQL, which contains a comprehensive SQL library and functionality that can be used to support advanced analytic workloads. Open source means that PostgreSQL can be downloaded and deployed on commercial equipment for free, or it can be run in the cloud through vendors. Although Postgres is feature rich and tailored for OLAP workloads, performance in Postgres tends to be limited because data volumes exceed several terabytes.

A brief history of creation

PostgreSQL, originally called Postgres, was created at UCB by computer science professor Michael Stonebreaker. He began work in 1986 at Postgres as an updated project of his predecessor, Ingres, and is now owned by Computer Associates.

The main periods:

• 1977-1985 - the period in which a project called Ingres was developed, and proof of concept for relational databases was also found. Ingres was founded in 1980, which was acquired by Computer Associates in 1994.
• 1986-1994 - the period in which the code base POSTGRES was adopted. Although the original option was that PostgreSQL did not use the codebase as its basis. In addition, a concept was developed at Ingres with a focus on object orientation and query language - Quel and commercialized as Illustra.
• 1994-1995 - SQL support was added during this period. Postgres 95 was released, which was later re-released in 1996 as PostgreSQL 6.0. A global PostgreSQL development team has been created with a free open source post-relational database system. It is used today in all operating systems.

PostgreSQL's 25-year history of excellence provides a huge set of opportunities for developers and database administrators, delivered on a reliable software server used around the world. PostgreSQL is ranked in the top 5 DBEngine databases.

Features of the latest development

This is PostgreSQL, which uses the latest techniques to develop new applications with millions of deployments in embedded systems, large providers, cloud computing and large on-premises installations. Advantages and differences from other systems:

1. Immunity to over deployment.
2. Redistribution is what some proprietary database providers consider to be their No. 1 problem due to license compliance. With PostgreSQL, no one can sue a user for violating a license. Since there are no associated software licensing costs.
3. Better support for creating a PostgreSQL database than patent holders.
4. A vibrant community of professional PostgreSQL enthusiasts.
5. Significant savings on staff costs.
6. Low maintenance and tuning requirements than leading patented databases, but at the same time retain all functions, stability and performance.
7. Product training programs are generally seen as much more costly, efficient, manageable, and practical in the real world than with leading proprietary database providers.
8. Legendary reliability and stability.
9. Source code is available for free.
10. If the user needs to configure or extend PostgreSQL in any way, then he will be able to do it with minimal effort and without any costs.

It is important that this PostgreSQL brings together a community of enthusiasts around the world who are actively expanding the software daily. Many users use a multi-row data storage strategy called MVCC to make PostgreSQL extremely responsive in high-volume environments.

Russian software registry

On March 18, 2016, the Software Expert Council in Russia approved 155 programs for submission to the Rosreestr. Among the Russian leaders, ABBYY and Kaspersky were PostgreSQL. Included in the list of "Linter" and Postgres. The issued PostgreSQL license entitles owners to develop and commercialize programs received from it. This is a fair assessment of the 20 years of work of the Postgres Professional team on the project. What now? Postgres Professional has released an updated release of the free version of Postgres Pro 9.6.2.1. DBMS functionality:

• multimaster cluster;
• adaptive queries;
• partitioning tables;
• block data compression;
• counter for transactions of 64-bit versions.

The principle of PostgreSQL DBMS software is based on machine learning, and therefore it can optimize query scheduling. Innovations dramatically (10 times) free up the memory occupied by data and create a jump in performance. To solve problems with overflowing PostgreSQL Professional systems, a 64-bit transaction counter with support of 100 million transactions / day was used. The pg_pathman extension allows you to partition tables and increase scalability through adaptability of planning algorithms.

Linux / Unix Installation

To use PostgreSQL Ubuntu, you must follow the installation instructions on your Linux computer. Before proceeding with the installation, make sure that they are logged in as root:

1. Select the desired version number of PostgreSQL and, as far as possible, the platform from EnterpriseDB. For example, postgresql-9.2.4-1-linux-x64.run is loaded for the 64-bit CentOS-6 processor.
2. After starting the PostgreSQL installer, Ubuntu answers a few basic questions, such as the installation location, the password of the user who will use the database, and the port number.
3. They are saved by default, except for a password that can be provided by choice.
4. PostgreSQL installation on a Linux computer is complete.

SQL Commands and Useful Links

Using psql, you can generate a complete list of commands using the \ help command.

For the syntax of a specific command, use the following: postgres- # \ help.

The SQL registry operator of the Russian software consists of tokens in which everyone can represent a keyword, a quoted identifier, a constant, or a special character. The table below uses a simple SELECT statement to illustrate a basic but complete SQL statement and its components.

The following resources provide additional information about PostgreSQL. Useful links:

1. Homepage - the official PostgreSQL website, which presents the latest version of the installation, news and full information explaining what kind of PostgreSQL program it is.
2. PHP PostgreSQL - Provides comprehensive PHP support information for PostgreSQL databases.
3. The PostgreSQL JDBC driver is a library for accessing and creating Java PostgreSQL database files.
4. DBD-Pg-2.19.3, which will be used with the Perl DBI module.
5. DBI-1.625 is a module for providing a common interface for any database, including PostgreSQL.
6. PostgreSQL Python - The most popular PostgreSQL database adapter for the Python programming language. It provides an SQL interface that conforms to the DB-API 2.0 specification.

Permalink General Settings

Configuring a PostgreSQL database can be a complex process. The following are some basic configuration options that are recommended when using PostgreSQL in Linode. All of these options are described in more detail in the PostgreSQL configuration guide.

Data type when creating tables

When working with PostgreSQL when creating a table, for each column indicate the type of data that will be stored in the fields of the table. Numeric types are presented below.

This has several advantages:

1. Coherence. Operations against columns of the same data type give consistent results and are usually the fastest.
2. Validation The proper use of data types involves checking their format and discarding data outside the data volume.
3. Compactness. Because a column can contain one type of value, it is stored in a compact way.
4. Performance. The proper use of data types provides the most efficient data storage. Stored values ​​can be quickly processed, which improves performance.

Consider money types.

Windows Platform Hosting

To install PostgreSQL Windows on your PC, follow the installation instructions below.

Before installing, you need to make sure that third-party antivirus is disabled, and then:

1. Select the version number of PostgreSQL. For example, postgresql-9.2.4-1-windows.exe for a Windows PC running 32-bit as an administrator to install PostgreSQL.
2. Choose the place where you want to install it. By default, it is installed in the Program Files folder.
3. The next step in the installation process will be to select the directory in which the data will be stored. By default, it is located in the data directory.
4. PostgreSQL Windows installation then asks for a password, so any password can be used.
5. The next step is to specify the default port.
6. In the next step, when they ask Locale, they select the language.
7. Uncheck the box and click Finish.
8. Upon completion of the installation process, you can access the shell pgAdmin III, StackBuilder and PostgreSQL from the program menu.

Mac programming environment

Before proceeding with the installation on a Mac computer, the user must make sure that he is logged in as an administrator, and then run:

1. Select the version number of PostgreSQL for Mac OS available in EnterpriseDB, for example postgresql-9.2.4-1-osx.dmg for Mac OS running OS X version 10.8.3.
2. To open the dmg image in finder, simply double-click it, which will launch the PostgreSQL installer in the next window.
3. Then click on the postgres-9.2.4-1-osx icon, which displays a warning message.
4. Accept the warning and proceed with further installation. He will ask for the administrator password.
5. Enter your password, proceed with the installation, and then restart your Mac. If the initial screen of the program does not start, start the installation again.
6. After starting the installer, he asks several basic questions, such as the installation location, the password of the user who will use the database, and the port number.
7. Save all their default values, except for the password.

Now you can run any of the programs.

Getting the command line

Unlike SQL Server, PostgreSQL is usually not used through a graphical interface. Server and client tools usually access from the command line. To get the command shell created to run these tools on lab machines, download and run the PostgreSQL shell startup script.

It is better to run this script only from a Windows computer. To verify that the script has correctly configured the paths to the tools, you need to enter psql -? at the command prompt and press Enter.

After that, the user should see a usage message similar to the one below:

This is psql 8.3.7, the PostgreSQL interactive terminal.

Usage: psql [OPTIONS] ... [DBNAME [USERNAME]]

[[Continues ...]]

Then configure PostgreSQL.

Creating data folders

PostgreSQL stores databases in regular files on the file system. When this software is used, the user creates a data folder only for himself. To create data files, do the following:

1. Create a folder in which to place the data files. It is recommended to place this folder on the local computer - C or D: drive for quick access or somewhere so that it is accessible to all computers. Say, the Windows or Unix home folders, in order to avoid the need to back up the data folder every time you log out. For example, Z: \ pgsql_data is a folder located in the Windows home folder (Z: \).
2. Open a PostgreSQL shell using the above environment script.
3. Run the initdb command to create the data files:> initdb -DZ: \ pgsql_data.

When the database files are prepared, log messages similar to the following will appear.

What it will mean: files belonging to this database system belong to michaelr, and it must have a server process.

Program start and stop

To start PostgreSQL, run the pg_ctl start command by selecting "Start-> Run", specifying the name of the previously created data folder:

> "C: \ Program Files \ PostgreSQL \ 8.3 \ bin \ pg_ctl.exe" start -DZ: \ pgsql_data.

A shell window will appear that will be displayed as server starting among other messages. When this is done, you can stop PostgreSQL by pressing Ctrl-C in the shell window or using the pg_ctl stop command:

> "C: \ Program Files \ PostgreSQL \ 8.3 \ bin \ pg_ctl.exe" stop -DZ: \ pgsql_data.

Users are advised to pay attention to the following:

1. Vista does not show the Start-> Run prompt, you just need to enter the command directly into the search panel that appears when you click the Start button.
2. If the user does not want PostgreSQL log messages cluttering up the shell window, you can redirect the messages to a file using the -l (lowercase L) option pg_ctl start:> "C: \ Program Files \ PostgreSQL \ 8.3 \ bin \ pg_ctl.exe" start -DZ: \ pgsql_data -l Z: \ pgsqllog.txt.

Creating a PostgreSQL Database

User tables must be placed in the database that they must first create. To create a database called my_database, do the following:

1. Be sure that PostgreSQL is running, and open the PostgreSQL shell function.
2. Use the createdb command to create the database:> createdb my_database.

The user should pay attention to the fact that PostgreSQL is case-sensitive when searching for database and table names. However, it automatically reduces all the names specified in the SQL code, so case sensitivity only affects code other than SQL, which must know the database name, including the Createdb command. In such code, the database name is always indicated in the same way as during creation, being case-sensitive.

Querying with psql

To run SQL queries, use SQL Server Management Studio, also called SSMS or SqlWb. If the user prefers a GUI for executing queries, you need to try pgAdmin III, located in the "Start-> Programs" menu in the PostgreSQL 8.3 section on Windows machines. After starting PostgreSQL, you can start psql by opening the PostgreSQL shell and typing> psql my_database, where my_database is the name of the database.

If you omit the database name, psql by default, then access the database with the same name as the CSE username. When psql opens, a message similar to the following appears.

The string michaelr = # is a prompt for SQL statements that are sent to the database server, or non-SQL commands interpreted by psql. Here michaelr is the name of the database, it may differ on the system. As the message shows, the user exits psql by typing \ q and pressing Enter, while paying attention to the absence of a semicolon, this is necessary because \ q is not an SQL statement.

Error message

Users sometimes get a Postgresql error message:

ERROR: must be owner of relation [your_table_here].

But do not worry, this message means that the user does not own the table that he is trying to change. For example, he had a problem running some ALTER TABLE statements. And it turned out that the user did not register his name, so he did not have the right to correct the tables.

To fix this failure, you just need to send an email to the database administrator, tell him what the problem is, and he will fix the resolution. After that, all ALTER TABLE commands will work perfectly. You can also see the tables in the database by running this command from the command line: \ d. This Postgresql command will show something like this (see photo).

Joining system users

It is implemented to database users using pg_ident.conf. Sometimes, especially when connecting from remote hosts, the Linux user name may not match the PostgreSQL database username. In these cases, you can specify the mapping in /etc/postgresql/9.5/main/pg_ident.conf.

The entries in this file take the form:

• MAPNAME - may be arbitrary.
• SYSTEM-USERNAME is the Linux username.
• PG-USERNAME - is the user of the corresponding database.

In the following exampleuser example, you can log in to postgres as the db_user database user:

• examplemap;
• exampleuser;
• db_user.

If the user specifies a mapping in this file, he must add map = map-name after the authentication method to the corresponding pg_hba.conf entry. To allow the user from the pg_hba.conf example to log into the db_user system, the full entry will look like this:

/etc/postgresql/9.5/main/pg_hba.conf

As you can see, Postgres is prioritizing SQL compliance and expansion. Organizations that want a high level of data customization usually choose Postgres. This is due to support for ACID transactions and user procedures.