Что такое postgresql? плюсы и минусы бесплатной базы данных

Содержание:

Установка RedHat Enterprise Linux 8 (RHEL 8.4). Подключение RHEL8 к домену Active Directory. Запуск терминального клиента.
Посмотреть и удалить активные запросы
Транзакции в PostgreSQL
Список существующих БД
Сервер 1С:Предприятие на Ubuntu 16.04 и PostgreSQL 9.6, для тех, кто хочет узнать его вкус. Рецепт от Капитана
Проверка репликации
- Посмотреть статус
- Создать тестовую базу
2017
Основные операции с БД
Установка и настройка
8.1.3. Floating-Point Types
Upgrading
Сетевые адреса
Notes
Короткий экскурс в историю PostgreSQL
Переменные окружения
Формат выводимой информации

Установка RedHat Enterprise Linux 8 (RHEL 8.4). Подключение RHEL8 к домену Active Directory. Запуск терминального клиента.

Операционная система – это один из краеугольных камней в фундаменте организации. От нее напрямую зависит надежность и безопасность корпоративной IT-инфраструктуры. Red Hat Enterprise Linux разработана с учетом всех требований и особенностей коммерческой эксплуатации Linux в производственной среде. Она проста в администрировании и управлении при развертывании приложений в физических, виртуальных и облачных средах. Обеспечивает высокую производительность и доступность приложений, а также обладает достаточной гибкостью, чтобы поддерживать рост организации и внедрение новых решений. Red Hat Enterprise Linux ценят за надежность, безопасность, стабильность, высокую производительность и масштабируемость, которые платформа предоставляет организациям. Клиентские решения Red Hat Enterprise Linux переносят эти инновации на рабочий стол.

Посмотреть и удалить активные запросы

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет — запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

select * from pg_stat_activity; # посмотреть все запросы
select * from pg_stat_activity WHERE current_query like 'SELECT%'; # посмотреть все SELECT запросы
select * from pg_stat_activity WHERE current_query like 'INSERT%';

# снять все активные select запросы
SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

# снять запрос VACUUM
SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

SELECT запросы можно снимать из ОС командой kill

# ps auxww | grep ^postgres
...
postgres 15724 97.7 11.3 2332996 1871476 ?     Rs   07:50   1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
...
# kill 15724

procpid содержит PID процесса, которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)

select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

Транзакции в PostgreSQL

В PostgreSQL Транзакция — это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

Список существующих БД

Список уже существующих на сервере баз данных можно получить командой

Обратите внимание на столбцы Encoding, Collate и Ctype. Знание кодировок может Вам пригодиться в будущем.

Сервер 1С:Предприятие на Ubuntu 16.04 и PostgreSQL 9.6, для тех, кто хочет узнать его вкус. Рецепт от Капитана

Если кратко описать мое отношение к Postgres: Использовал до того, как это стало мейнстримом.
Конкретнее: Собирал на нем сервера для компаний среднего размера (до 50 активных пользователей 1С).
На настоящий момент их набирается уже больше, чем пальцев рук пары человек (нормальных, а не фрезеровщиков).
Следуя этой статье вы сможете себе собрать такой же и начать спокойную легальную жизнь, максимально легко сделать первый шаг в мир Linux и Postgres.
А я побороться за 1. Лучший бизнес-кейс (лучший опыт автоматизации предприятия на базе PostgreSQL).
Если, конечно, статья придется вам по вкусу.

Проверка репликации

Посмотреть статус

Статус работы репликации можно посмотреть следующими командами.

На мастере:

=# select * from pg_stat_replication;

На слейве:

=# select * from pg_stat_wal_receiver;

Создать тестовую базу

На мастере заходим в командную оболочку Postgre:

su — postgres -c «psql»

Создаем новую базу данных:

=# CREATE DATABASE repltest ENCODING=’UTF8′;

Теперь на вторичном сервере смотрим список баз:

su — postgres -c «psql»

=# \l

Мы должны увидеть среди баз ту, которую создали на первичном сервере:

2017

Документация версии 10 локализована для России

11 октября 2017 года компания Postgres Professional сообщила о переводе документации по PostgreSQL 10.0 на русский язык. Материалы доступны в форматах Html, epub и pdf. Общий объем текста составляет почти 2 600 страниц.

Помимо документации на русском языке, российским пользователям PostgreSQL также доступна техническая поддержка в режиме 24/7, помощь в миграции с других СУБД на PostgreSQL, обучающие курсы и технические конференции.

В ближайшее время Postgres Professional планирует выпуск обновленной версии российской СУБД Postgres Pro Standard на основе PostgreSQL 10.0.

PostgreSQL 10

5 октября 2017 года состоялся релиз PostgreSQL версии 10. В целом с каждой версией, выходящей раз в год, PostrgeSQL получает возможности, расширяющие область эффективного применения СУБД.

Основные нововведения:

Логическая репликация: отдельные части этого механизма были добавлены в PostgreSQL уже довольно давно, а в этой версии логическая репликация стала полностью доступна для пользователей. С ее помощью можно выборочно реплицировать отдельные таблицы на другой сервер, который при этом может выполнять как читающие, так и пишущие запросы. Серверы, участвующие в репликации, могут работать под управлением разных версий PostgreSQL, что позволяет проводить обновление кластера с минимальным временем простоя.
Декларативное секционирование избавляет администратора от необходимости вручную определять иерархию таблиц, создавать триггеры и ограничения целостности.
Параллельное выполнение запросов стало возможным для сканирования битовых карт и индексов, для соединения слиянием и подзапросов в дополнение к тем возможностям, которые появились в предыдущей версии.
Синхронная репликация с учетом кворума позволяет фиксировать изменения, если их подтвердило необходимое число произвольных реплик.
SCRAM-аутентификация является более криптостойким вариантом используемой ранее MD5-аутентификации.

Всего, по словам разработчиков, в версию 10 вошло более 100 изменений и улучшений, часть из которых выполнена в компании Postgres Professional.

Интеграция с Ethereum

14 сентября 2017 года российская компания Postgres Professional объявила о создании прототипа расширения Posthereum для интеграции полнофункциональной СУБД PostgreSQL с блокчейн-платформой Ethereum, предназначенной для регистрации сделок с любыми видами активов на основе системы «умных контрактов». По замыслу компании, крупные российские банки, корпорации и госструктуры, работающие с СУБД PostgreSQL, с помощью данной разработки смогут объединить базы данных с блокчейн-приложениями на основе Ethereum. Подробнее здесь.

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать язык запросов к базе данных SQL.

Создание базы данных

Для создания базы данных используется команда:

create database

В приведенном ниже примере создается база данных с именем proglib_db.

Рисунок 3 — Создание базы данных с именем proglib_db

Если забыть точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-». Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

Рисунок 4 — вывод ошибки при создании базы данных

На рисунке 4 видно сообщение об ошибке из-за того, что в нашем случае база уже создана.

Создание нового юзера

Для создания пользователя существует команда:

create user

В приведенном ниже примере создается пользователь с именем author.

Рисунок 5 — Создание пользователя с именем author

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Можно установить пароль для существующего пользователя.

С этой задачей справится команда :

postgres=#\password author

Чтобы задать пароль при создании пользователя, можно использовать следующую команду:

postgres=#create user author with login password 'qwerty';

Удаление базы или пользователя

Для этой операции используется команда : она умеет удалять как пользователя, так и БД.

drop database <database_name>
drop user <user_name>

Данную команду нужно использовать очень осторожно, иначе удаленные данные будут потеряны, а восстановить их можно только из бэкапа (если он был).

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

psql

Войдем в базу proglib_db под пользователем author. Выполним команду , чтобы выйти из текущей БД, а затем выполните следующую команду:

Рисунок 6 — Вход в базу данных proglib_db

Установка и настройка

В данном разделе представлена инструкция по установки и настройке PostgreSQL для разных ОС

Установка

Если установка происходит на macOS, то процесс установки можно запустить командой:

brew install postgresql

На Linux СУБД устанавливается так:

sudo apt-get install postgresql postgresql-contrib

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

postgres --version

Инструкция по установке в цифровом формате

Настройка

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL.

Необходимо ввести следующую команду:

psql postgres (для выхода из интерфейса используйте \q)

Этой командой запускается утилита psql. Хотя есть много сторонних инструментов для администрирования PostgreSQL, нет необходимости их устанавливать, т. к. psql удобен и отлично работает.

Если нужна помощь, введите (или ) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести , если вам нужна помощь по конкретной команде. Например, если ввести в консоли psql, отобразится синтаксис команды .

 1 Description update rows of a table
 2  WITH  RECURSIVE  with_query [,  
 3 UPDATE  ONLY  table_name  *    AS  alias 
 4     SET { column_name = { expression | DEFAULT } |
 5           ( column_name [,  ) = ( { expression | DEFAULT } [,  ) |
 6           ( column_name [,  ) = ( sub-SELECT )
 7         } [, 
 8      FROM from_list 
 9      WHERE condition | WHERE CURRENT OF cursor_name 
10      RETURNING * | output_expression   AS  output_name  [,

Для начала необходимо проверить наличие существующих пользователей и баз данных. Выполните следующую команду, чтобы вывести список всех баз данных:

\list или \l

Рисунок 1 — Результат выполнения операции \l

На рисунке выше вы видите три базы данных по умолчанию и суперпользователя postgres, которые создаются при установке PostgreSQL.

Чтобы вывести список всех пользователей, выполните команду . Атрибуты пользователя postgres говорят нам, что он суперпользователь.

Рисунок 2 — Результат выполнения операции \du

8.1.3. Floating-Point Types

The data types real and double precision are inexact, variable-precision
numeric types. In practice, these types are usually
implementations of IEEE
Standard 754 for Binary Floating-Point Arithmetic (single and
double precision, respectively), to the extent that the
underlying processor, operating system, and compiler support
it.

Inexact means that some values cannot be converted exactly
to the internal format and are stored as approximations, so
that storing and retrieving a value might show slight
discrepancies. Managing these errors and how they propagate
through calculations is the subject of an entire branch of
mathematics and computer science and will not be discussed
here, except for the following points:

If you require exact storage and calculations (such as
for monetary amounts), use the numeric type instead.
If you want to do complicated calculations with these
types for anything important, especially if you rely on
certain behavior in boundary cases (infinity, underflow),
you should evaluate the implementation carefully.
Comparing two floating-point values for equality might
not always work as expected.

On most platforms, the real type has a
range of at least 1E-37 to 1E+37 with a precision of at least 6
decimal digits. The double precision type
typically has a range of around 1E-307 to 1E+308 with a
precision of at least 15 digits. Values that are too large or
too small will cause an error. Rounding might take place if the
precision of an input number is too high. Numbers too close to
zero that are not representable as distinct from zero will
cause an underflow error.

In addition to ordinary numeric values, the floating-point
types have several special values:

Infinity-InfinityNaN

«infinity»»negative infinity»»not-a-number»UPDATE table SET x = 'Infinity'

PostgreSQL also supports
the SQL-standard notations float and
float(p)
for specifying inexact numeric types. Here, p specifies the minimum acceptable
precision in binary
digits. PostgreSQL accepts
float(1) to float(24) as selecting the real type, while float(25) to
float(53) select double precision. Values of p
outside the allowed range draw an error. float with no precision specified is taken to mean
double precision.

Upgrading

We always recommend that all users run the latest available minor
release for whatever major version is in use.

Major versions usually change the internal format of system tables and data
files. These changes are often complex, so we do not maintain backward
compatibility of all stored data. A dump/reload of the database or use of the
pg_upgrade module is required
for major upgrades. We also recommend reading the
upgrading section of the major
version you are planning to upgrade to. You can upgrade from one major version
to another without upgrading to intervening versions, but we recommend reading
the release notes of all intervening major
versions prior to doing so.

Upgrading to a minor release does not normally require a dump and restore; you
can stop the database server, install the updated binaries, and restart the
server. For some releases, manual changes may be required to complete the
upgrade, so always read the release notes before upgrading.

While upgrading will always contain some level of risk, PostgreSQL minor releases
fix only frequently-encountered bugs, security
issues, and data corruption problems to reduce the risk associated with
upgrading. For minor releases, the community considers not upgrading to be
riskier than upgrading.

Сетевые адреса

Имя	Размер	Описание	Пример
	7 или 19 байт	Узлы и сети IPv4 и IPv6	192.168.0.1/24192.168.0.0/24
cidr	7 или 19 байт	Сети IPv4 и IPv6	192.168.0.0/24
	6 байт	MAC-адреса	’08:00:2b:01:02:03′
	8 байт	MAC-адреса (в формате EUI-64)	’08:00:2b:01:02:03:04:05′

# CREATE TABLE network
(hostname varchar(10),
ip_address inet);

# INSERT INTO network (hostname, ip_address) VALUES
('gateway', '192.168.0.1/24'),
('pc-alex', '192.168.0.5/24'),
('pc-bob', '192.168.0.15/24');

# SELECT * FROM network;
 hostname |   ip_address
----------+-----------------
 gateway  | 192.168.0.1/24
 pc-alex  | 192.168.0.5/24
 pc-bob   | 192.168.0.15/24

Здесь стоит отметить что в cidr значение должно заканчиваться нулями (192.168.0.0/24), а в inet это не обязательно.

Notes

Using OIDs in new applications is not recommended: where
possible, using a SERIAL or other
sequence generator as the table’s primary key is preferred.
However, if your application does make use of OIDs to identify
specific rows of a table, it is recommended to create a unique
constraint on the oid column of that
table, to ensure that OIDs in the table will indeed uniquely
identify rows even after counter wraparound. Avoid assuming that
OIDs are unique across tables; if you need a database-wide unique
identifier, use the combination of tableoid and row OID for the purpose.

PostgreSQL automatically
creates an index for each unique constraint and primary key
constraint to enforce uniqueness. Thus, it is not necessary to
create an index explicitly for primary key columns. (See CREATE INDEX for more
information.)

Unique constraints and primary keys are not inherited in the
current implementation. This makes the combination of inheritance
and unique constraints rather dysfunctional.

Короткий экскурс в историю PostgreSQL

Генеалогическое древо PostgreSQL начинается в 1977 году в Калифорнийском университете Беркли. Реляционная база данных Ingres разрабатывалась в Беркли в 1977-85 годах. Ingres была очень популярна за пределами стен Калифорнийского университета, она появилась на многих компьютерах в университетских и исследовательских кругах. На свободный рынок Ingres была выведена Relational Technologies/Ingres Corporation, так она стала одной из первых коммерчески доступных реляционных систем управления базами данных. В наши дни Ingres превратилась в CA-INGRES II, продукт Computer Associates.

Тем временем в Беркли не прекращается работа над сервером реляционной базы данных (под названием Postgres), это продолжается с 1986 по 1994 год. Как и раньше, код приобретается коммерческой компанией и продукт на его основе выставляется на продажу. После поглощения компанией Informix он стал называться Illustra. Где-то в 1994 году в Postgres были добавлены возможности SQL, и возникло новое имя — Postgres95.

К 1996 году Postgres стала приобретать необыкновенную популярность, и было принято решение открыть ее код для некоторого количества программистов по списку рассылки, так началось весьма успешное сотрудничество добровольцев, направленное на продвижение Postgres. Тогда продукт в последний раз сменил имя, отбросив окончание «95» и заменив его на «SQL», которое лучше отражало поддержку стандартного языка запросов SQL в Postgres. Так родилась PostgreSQL.

GreatBridge осуществляет коммерческую поддержку проекта, а также предоставляет работу некоторым PostgreSQL-разработчикам.

Переменные окружения

Утилита psql может использовать переменные окружения. Для установки переменной используется команда “\set”, которой нужно передать имя и значение переменной. Если команде “\set” не передать никаких параметров, то она выведет все переменные окружения.

Вывести значение переменной можно с помощью команды “\echo”, которой нужно передать имя переменной, но перед именем нужно обязательно поставить двоеточие:

postgres=# \set TEST Hi!

postgres=# \echo :TEST
Hi!

postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'postgres'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
ROW_COUNT = '1'
SERVER_VERSION_NAME = '13.2'
SERVER_VERSION_NUM = '130002'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
SQLSTATE = '00000'
TEST = 'Hi!'
USER = 'postgres'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit'
VERSION_NAME = '13.2'
VERSION_NUM = '130002'

Чтобы удалить переменную окружения используйте команду “\unset”, например:

postgres=# \unset TEST

postgres=# \echo :TEST
:TEST

Как видно выше, если переменной нет, то “\echo” просто выводит переданный текст.

Но переменную можно создавать не только с помощью “\set”, также можно в переменную записать результат запроса. Чтобы это сделать, нужно использовать в конце запроса вместо точки с запятой (;) команду “\gset”:

postgres=# SELECT now() AS curr_time \gset

postgres=# \echo :curr_time
2021-04-09 10:31:49.521543+00

В примере выше мы результат функции now() записали в переменную curr_time!

Формат выводимой информации

Когда вы вводите и выполняете какой-нибудь запрос в терминале psql, то в выводе видите результат этого запроса. По умолчанию такой вывод показывается в форме таблички. Но вы можете настроить формат выводимой информации сами:

– с выравниванием / без выравнивания
– отображение строки заголовка и итоговой строки / без такого отображения
– можно задать разделитель (по умолчанию используется вертикальная черта ‘|’)
– расширенный режим, когда нужно вывести много столбцов одной таблицы, они будут выведены в один столбец

Например, получим информацию из представления pg_tables. Затем поменяем формат и снова получим информацию. А затем все вернем на место:

postgres@s-pg13:~$ psql
psql (13.3)
Type "help" for help.

postgres=# SELECT * FROM pg_tables LIMIT 5;
 schemaname |       tablename       | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog | pg_statistic          | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_type               | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_foreign_table      | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_authid             | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog | pg_statistic_ext_data | postgres   |            | t          | f        | f           | f
(5 rows)

postgres=# \t \a \pset fieldsep ' '
Tuples only is on.
Output format is unaligned.
Field separator is " ".

postgres=# SELECT * FROM pg_tables LIMIT 5;
pg_catalog pg_statistic postgres  t f f f
pg_catalog pg_type postgres  t f f f
pg_catalog pg_foreign_table postgres  t f f f
pg_catalog pg_authid postgres pg_global t f f f
pg_catalog pg_statistic_ext_data postgres  t f f f

postgres=# \t \a \pset fieldsep '|'
Tuples only is off.
Output format is aligned.
Field separator is "|".

postgres=# SELECT * FROM pg_tables LIMIT 5;
 schemaname |       tablename       | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog | pg_statistic          | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_type               | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_foreign_table      | postgres   |            | t          | f        | f           | f
 pg_catalog | pg_authid             | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog | pg_statistic_ext_data | postgres   |            | t          | f        | f           | f
(5 rows)

А вот пример использования расширенного режима (\x). Выглядит это так, как будто табличку перевернули:

postgres=# \x
Expanded display is on.

postgres=# SELECT * FROM pg_tables LIMIT 5;
-----------------------
schemaname  | pg_catalog
tablename   | pg_statistic
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f
-----------------------
schemaname  | pg_catalog
tablename   | pg_type
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f
-----------------------
schemaname  | pg_catalog
tablename   | pg_foreign_table
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f
-----------------------
schemaname  | pg_catalog
tablename   | pg_authid
tableowner  | postgres
tablespace  | pg_global
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f
-----------------------
schemaname  | pg_catalog
tablename   | pg_statistic_ext_data
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f