Software catalogue — postgresql extensions

Содержание

Настройка с доступом по паролю

Настройка пользователя postgres

Задайте пароль для пользователя :


psql -U postgres

psql (11.2)
Введите "help", чтобы получить справку.
postgres=# \password
Введите новый пароль:
Повторите его:
postgres=# \q

Настройка доступа к базам

Выполните настройки доступа к SQL-серверу, разрешив доступ только локальным запросам и из локальной сети 192.168.0.0/24:

/etc/postgresql-11/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
host    all             all             192.168.0.0/24          password
# IPv6 local connections:
#host   all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local  replication     all                                     trust
#host   replication     all             127.0.0.1/32            trust
#host   replication     all             ::1/128                 trust

Через запятую укажите IP-адреса, которые должен слушать сервер PostgreSQL:

/etc/postgresql-11/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '192.168.0.1,127.0.0.1' # what IP address(es) to listen on;
                                           # comma-separated list of addresses;
                                           # defaults to 'localhost'; use '*' for all
                                           # (change requires restart)

Заметка

Если нужно, чтобы сервер был доступен на всех сетевых интерфейсах, вместо IP-адресов укажите .

Перезагрузите сервис баз данных:

/etc/init.d/postgresql-11 restart

postgresql-11 | * Stopping PostgreSQL 11 (this can take up to 92 seconds) ...  ok 
postgresql-11 | * /run/postgresql: correcting mode
postgresql-11 | * Starting PostgreSQL 11 ...                                   ok 

Проверьте, какие порты работают:

netstat -an

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp        0      0 192.168.0.1:5432        0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
udp        0      0 127.0.0.1:41006         127.0.0.1:41006         ESTABLISHED
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2           STREAM     LISTENING     281718   /run/postgresql/.s.PGSQL.5432

Пример создания базы данных и пользователя

Создайте базу данных dbtest и пользователя test для работы с ней:

psql -U postgres

Пароль пользователя postgres: 
psql (11.2)
Введите "help", чтобы получить справку.

postgres=# create database dbtest;
CREATE DATABASE
postgres=# create role test with login;
CREATE ROLE
postgres=# \password test
Введите новый пароль: 
Повторите его: 
postgres=# grant connect, create on database dbtest to test;
GRANT
postgres=# \l dbtest
                                 Список баз данных
    Имя    | Владелец | Кодировка | LC_COLLATE |  LC_CTYPE  |     Права доступа     
-----------+----------+-----------+------------+------------+-----------------------
 dbtest    | postgres | UTF8      | ru_RU.utf8 | ru_RU.utf8 | =Tc/postgres         +
           |          |           |            |            | postgres=CTc/postgres+
           |          |           |            |            | test=Cc/postgres
(1 строка)

postgres=# \q

Необходимая база и пользователь созданы.

Проверьте подключение к базе данных:

psql -U test -d dbtest

Пароль пользователя test:
psql (11.2)
Введите "help", чтобы получить справку.

test=> \q

Установка PostgreSQL

$ sudo pacman -S postgresql

Инициализируем кластер с нужной локалью (она должна быть доступна в системе)

Обратите внимание, что в данном примере используем ru_RU.UTF-8

$ sudo su - postgres -c "initdb --locale ru_RU.UTF-8 -E UTF8 -D '/var/lib/postgres/data'"

Если получили ошибку «Невозможно создать директорию, недостаточно прав», то изменяем владельца PGROOT директории и пробуем еще раз

$ sudo chown -R postgres:postgres /var/lib/postgres/

Запускаем сервер PostgreSQL

$ systemctl start postgresql

Проверяем запустилось ли:

$ systemctl status postgresql

Дополнительно его можно добавить в автозагрузку

$ systemctl enable postgresql

Пользователи (роли)

Вместо привычного понятия пользователей, PostgreSQL использует концепцию ролей. Несмотря на определенные отличия, в целом можно определять роли как пользователей базы данных. 

В зависимости от настройки, роль можно рассматривать как пользователя базы данных или как группу пользователей. Роли могут владеть объектами базы данных (например, таблицами) и выдавать другим ролям разрешения на доступ к этим объектам. Также можно предоставить одной роли членство в другой роли (схоже с добавлением пользователя в группу), чтобы одна роль могла использовать привилегии другой роли.

Как отмечалось выше, во время установки была автоматически создана роль postgres. Вы можете работать с СУБД из-под нее. Для этого переключитесь на сессию данного пользователя:

sudo su - postgres

После чего запустите консоль Postgres:

psql

После завершения работы вы сможете выйти из консоли Postgres командой :

postgres=# \q

Так как для каждой созданной роли Postgres предполагает наличие базы данных с таким же именем и по умолчанию подключается именно к ней, имеет смысл создавать новую роль для каждой базы. Кроме того, если имя роли совпадает с именем пользователя, созданного в системе Linux, подключение к БД также упрощается.

Создание новой роли

Создать роль из консоли системы (не psql) можно с помощью команды:

createuser -P --interactive

Ключ позволит сразу задать пароль пользователю, а запустит интерактивный режим для указания дополнительных параметров.

Система поочередно запросит имя новой роли, ее пароль и повтор пароля, а также позволит указать привилегии: сделать ли роль суперпользователем, должны ли быть права на создание баз данных и создание других ролей. Нажимайте y / n и Enter для выбора.

Создать роль из консоли Postgres можно с помощью команды CREATE ROLE.

При использовании этой команды без дополнительных опций пользователь будет создан без каких-либо привилегий, поэтому желательно сразу при создании роли указывать нужные привилегии. Полный список доступных опций можно просмотреть, выполнив:

\h CREATE ROLE

Чтобы создать новую роль выполните:

CREATE ROLE имя_роли WITH LOGIN CREATEDB CREATEROLE;

Далее задайте новому пользователю пароль:

\password имя_роли

Просмотр существующих ролей

Чтобы просмотреть созданных пользователей и их привилегии, перейдите в консоль Postgres:

psql

И выполните команду:

\du

Пример вывода:

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tmweb     | Create role, Create DB                                     | {}
 tweb      | Create role, Create DB                                     | {}

Нажмите q, чтобы закрыть вывод, и \q, если нужно выйти из консоли Postgres.

dropuser имя_роли

Либо команду в консоли Postgres:

DROP ROLE имя_роли;

Смена пароля пользователя

Для смены пароля одной из ролей подключитесь к Postgres от суперпользователя (postgres или другой роли с такими привилегиями), после чего выполните:

ALTER USER имя_роли WITH PASSWORD 'новый_пароль';

Обратите внимание, что эта операция сохранится в файле  вместе с паролем в открытом виде. Из соображений безопасности рекомендуется удалить соответствующую запись из файла

Как правило, файл размещается в директории . Проверить его расположение можно в терминале системы с помощью: 

grep postgres /etc/passwd | cut -d ':' -f 6

Откройте файл, указав корректный путь к нему:

sudo nano /var/lib/postgresql/.psql_history

Удалите запись с паролем и сохраните изменения.

Резервные копии (экспорт и импорт дампа)

При установке PostgreSQL на сервер устанавливаются утилиты и , с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них ().

Создание резервной копии

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

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_базы

Параметры:

  • — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
  • — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
  • — формат, в котором будет сохранен дамп; указывается буквами c, t или p: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — архив .tar), ‘p’ (plain — текстовый файл без сжатия, как правило, .sql);
  • — путь сохранения для файла дампа и имя файла;
  • — имя базы данных, для которой создается резервная копия.

Например:

pg_dump -h localhost -U tmweb -F c -f /home/user/backups/dump.tar.gz tmweb

После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb).

Восстановление из дампа

Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью :

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры:

  • — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
  • — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
  • — формат, в котором был сохранен дамп; необходимо указать ‘с’ для архива .tar.gz и ‘t’ для архива .tar;
  • — имя базы данных, в которую импортируется дамп;
  • — путь к файлу дампа и имя файла.

Например:

pg_restore -h localhost -U tmweb -F c -d new_db /home/user/backups/dump.tar.gz

Для импорта дампов в формате .sql используется cat:

cat путь_к_дампу | psql -h хост -U имя_роли имя_базы

Параметры:

  • — путь к файлу дампа и имя файла;
  • — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
  • — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
  • — имя базы данных, в которую импортируется дамп.

Например:

cat /home/user/backups/dump.sql | psql -h localhost -U tmweb new_db

PgAdmin: Визуальный редактор для PostgresSQL

Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.

  • Для запуска редактора запустите PgAdmin 4 в меню Пуск
  • Для доступа нужно ввести пароль суперпользователя postgres
  • В панели Servers вы можете раскрыть список активных БД.
  • В панели управления возможно быстро создать нового пользователя и группу, предоставить ему права. Для этого Откройте меню Object -> Create -> Create Login/Group.
  • Для создания новой базы данных достаточно выбрать: Database в меню Object -> Create. В новом поле указать имя базы и владельца.

По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.

Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.

Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.

Query Tool: использование SQL запросов в PostgreSQL

Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.

  • Выберите базу данных, в панели Tools откройте Query Tool
  • Создадим таблицу сотрудников:

Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.

После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.

Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.

Здесь вы можете заполнить данные в таблице.

После заполнения данных выполним инструментом Query простой запрос на выборку:

Тюнинг PostgreSQL 9.4.2.

Дальше вбиваем себе в голову следующее: перед любым сохранением новых настроек, делайте резервные копии файлов:

  • pg_hba.conf
  • postgresql.conf
  • pgpass.conf

которые лежат здесь:

C:\Program Files\PostgreSQL\9.4.2-1.1C\data

Если Вы ошибётесь хоть в одной букве, после обновления конфигурации PostgreSQL не запуститься. Выяснить что же стало причиной будет сложно, даже смотря в журналы Windows. Поэтому не меняйте много параметров сразу и делайте резервные копии.

Для правки конфига есть удобный инструмент, доступный прямо из главного окна pgAdmin. Вот он:

Что мы здесь меняем:

  • shared_buffers — Количество памяти, выделенной PgSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PgSQL. Делим доступную ОЗУ на 4. В нашем случае это 8Gb.
  • effective_cache_size — Оценка размера кэша файловой системы. Считается так: ОЗУ — shared_buffers. В нашем случае это 32Gb — 8Gb = 24Gb. Но лично я оставляю этот параметр ещё ниже, где-то 20Gb — всё-таки ОЗУ нужна не только для PostgreSQL.
  • random_page_cost = 1.5 — 2.0 для RAID, 1.1 — 1.3 для SSD. Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.
  • temp_buffers = 256Mb. Максимальное количество страниц для временных таблиц. То есть это верхний лимит размера временных таблиц в каждой сессии.
  • work_mem — Считается так: ОЗУ / 32..64 — в нашем случае получается 1Gb. Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая  часть сессий почти всегда висит в ожидании.
  • bgwrite_delay — 20ms. Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на  checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.
  • synchronous_commit — off. ОПАСНО! Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.

Это далеко не всё, что удалось узнать из Интернета и статей на https://its.1c.ru. НО! Даже этих настроек хватит, чтобы видимо ускорить работу 1С:Предприятие на PostgreSQL.

В этом конкретном случае после перехода на PostgreSQL пользователи стали жаловаться, что 1С начала тормозить ещё сильнее, чем в файловом варианте.  Но после этого тюнинга база «полетела». Теперь все наслаждаются быстрой работой. Однако есть и свои минусы в виде блокировок. Останавливаться на это мы не планируем, будем копать дальше и выкладывать полезные изменения конфигурации PostgreSQL сюда.

Если с базой данных возникли какие-то проблемы, возможно, Вам поможет внутреннее или внешнее тестирование.

Базы данных 1С можно публиковать на веб-серверах!

Connect to the PostgreSQL database server via psql

In PostgreSQL, a user account is referred to as a role. By default, PostgreSQL uses ident authentication.

It means that PostgreSQL will associate its roles with the system accounts of Linux. If a role exists in PostgreSQL, the same Linux user account with the same name is able to log in as that role.

When you installed PostgreSQL, the installation process created a user account called associated with the default role.

To connect to PostgreSQL using the role, you switch over to the account on your server by typing:

It’ll prompt for the password of the current user. You need to provide the password and hit the keyboard.

Then, you can access the PostgreSQL using the by typing the following command:

You’ll access the postgres prompt like this:


From here, you can interact with the PostgreSQL like issuing a query.

To quit the PostgreSQL prompt, you run the following command:

This above command will bring you back to the postgres Linux command prompt.

To return to your regular system user, you execute the  command like this:

Настройки на Master

В данной статье мы будем настраивать серверы с IP-адресами 192.168.1.10 (первичный или master) и 192.168.1.11 (вторичный или slave).

Переходим на сервер, с которого будем реплицировать данные (мастер) и выполняем следующие действия.

Создаем пользователя в PostgreSQL

Входим в систему под пользователем postgres:

su — postgres

Создаем нового пользователя для репликации:

createuser —replication -P repluser

* система запросит пароль — его нужно придумать и ввести дважды. В данном примере мы создаем пользователя repluser.

Выходим из оболочки пользователя postgres:

exit

Настраиваем postgresql

Открываем конфигурационный файл postgresql.conf.

а) если используем Red Hat / CentOS:

vi /var/lib/pgsql/9.6/data/postgresql.conf

б) если используем Debian / Ubuntu:

vi /etc/postgresql/9.6/main/postgresql.conf

* для postgresql версии 9.2 путь может быть другой — без 9.6 (/var/lib/pgsql/data/postgresql.conf или /etc/postgresql/main/postgresql.conf).

Редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.10’ wal_level = hot_standby max_wal_senders = 2 max_replication_slots = 2 hot_standby = on hot_standby_feedback = on

* где

  • 192.168.1.10 — IP-адрес сервера, на котором он будем слушать запросы Postgre; 
  • wal_level указывает, сколько информации записывается в WAL (журнал операций, который используется для репликации) — hot_standby указывает на хранение дополнительной информации, она нужна для выполнения запросов на резервном сервере в режиме только для чтения; 
  • max_wal_senders — количество планируемых слейвов; 
  • max_replication_slots — максимальное число слотов репликации (данный параметр не нужен для postgresql 9.2 — с ним сервер не запустится); 
  • hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления; 
  • hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.

Открываем конфигурационный файл pg_hba.conf.

а) если используем Red Hat / CentOS:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

б) если используем Debian / Ubuntu:

vi /etc/postgresql/9.6/main/pg_hba.conf

Добавляем следующие строки:

host replication repluser 127.0.0.1/32 md5 host replication repluser 192.168.1.10/32 md5 host replication repluser 192.168.1.11/32 md5

* данной настройкой мы разрешаем подключение к базе данных replication пользователю repluser с локального сервера (localhost и 192.168.1.10) и сервера 192.168.1.11.

Перезапускаем службу postgresql:

systemctl restart postgresql-9.6

* в данном примере настройка выполняется для postgresql версии 9.6.

PostgreSQL Apt Repository

If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

The PostgreSQL Apt Repository supports the current LTS versions of Ubuntu:

  • 20.04
  • 18.04
  • 16.04
  • amd64
  • arm64 (18.04 and newer)
  • i386 (18.04 and older)
  • ppc64el

To use the apt repository, follow these steps:

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

Copy Script

For more information about the apt repository, including answers to frequent questions, please see the PostgreSQL Apt Repository page on the wiki.

Texcaller

Description License Pricing Publisher  

Texcaller is a convenient interface to the TeX command line tools that handles all kinds of errors without much fuzz. It is written in plain C, is fairly portable, and has no external dependencies besides TeX.

These PostgreSQL functions are simple wrappers around the Texcaller C interface library functions, bringing TeX typesetting into the world of relational databases.

Invalid TeX documents are handled gracefully by simply returning NULL rather than aborting with an error. On failure as well as on success, additional processing information is provided via NOTICEs.

Open source Volker Grabsch View

Note: The PostgreSQL Global Development Group do not endorse or recommend any products listed, and cannot vouch for the quality or reliability of any of them.

Related

  • Tutorial

    Django — это мощная веб-система, помогающая создать приложение или сайт Python с нуля. Django включает упрощенный сервер разработки для локального тестирования кода, однако для серьезных производственных задач требуется более защищенный и мощный веб-сервер. В этом руководстве…

  • Tutorial

    TLS или протокол безопасности транспортного уровня и предшествующий ему протокол SSL используются для заключения обычного трафика в защищенную оболочку с шифрованием. Используя эту технологию, серверы могут безопасно отправлять информацию клиентам без перехвата сообщений и…

  • Tutorial

    WordPress — это одна из самых популярных на сегодня систем с открытым исходным кодом для создания веб-сайтов и блогов в сети Интернет. Ее используют 63% веб-сайтов, которые пользуются системой управления контентом (CMS). На сайты, работающие на WordPress, приходится 36% всех…

  • Tutorial
    Дистанционный доступ к GUI-приложениям с помощью Docker и Caddy в Ubuntu 18.04

    Автор выбрал фонд Free and Open Source Fund для получения пожертвования в рамках программы Write for DOnations. Несмотря на рост популярности облачных сервисов, необходимость использования…

Утилиты управления PostgreSQL через командную строку

Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.

  • Запустите командную строку. Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:

  • Перейдите в каталог bin выполнив команду:

Основные команды PostgreSQL:

  • Проверка установленной версии СУБД:
  • Для создания новой базы данных воспользуйтесь утилитой createdb:(где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
  • Проверить список активных баз: (пароль)
  • С помощью инструмента createuser cоздадим нового пользователя: (где operator -имя нового пользователя)
  • Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): . С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: . Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
  • Для выводы списка пользователей и ролей в СУБД выполните команду:

Настройка Postgresql в Ubuntu

Вы знаете как установить Postgresql Ubuntu, но этого недостаточно для начала полноценной работы. Первым делом, откройте терминал и переключите его на пользователя postgres с помощью команды:


Эта учетная запись создается во время установки программы и на данный момент вы можете получить доступ к системе баз данных только с помощью нее. По умолчанию postgress использует концепцию ролей для аутентификации и авторизации.

Это очень похоже на учетные записи Unix, но программа не различает пользователей и групп, есть только роли. Сразу после установки Postgresql пытается связать свои роли с системными учетными записями, если для имени системной учетной записи существует роль, то пользователь может войти в консоль управления и выполнять позволенные ему действия. Таким образом, после переключения на пользователя postgres вы можете войти в консоль управления:

И посмотреть информацию о соединении:

Чтобы выйти наберите:

Теперь давайте рассмотрим как создать другие роли и базы данных.

Создание роли Postgresql

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

Скрипт задаст лишь два вопроса, имя новой роли и нужно ли делать ее суперпользователем.

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

Точно также как имена ролей сопоставляются с системными пользователями, имя базы данных будет подбираться по имени пользователя. Например, если мы создали пользователя segiy, то по умолчанию система попытается получить доступ к базе данных segiy. Мы можем ее очень просто создать:

Дальше, чтобы подключиться к этой базе данных нам нужно войти от имени одноименного пользователя:

Заходим в консоль и смотрим информацию о подключении:

Все верно сработало. Мы подключились с помощью роли segiy к базе segiy. Если нужно указать другую базу данных, вы можете сделать это с помощью опции -d, например:

Все сработало верно, при условии, что все компоненты были настроены как описано выше.

Создание таблиц

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

CREATE TABLE имя таблицы (имя_колонки1 тип_колонки ( длина ) ограничения ,имя_колонки2 тип_колонки ( длина ),имя_колонки3 тип_колонки ( длина ));

Как видите, сначала мы задаем имя таблицы, затем описываем каждый столбец. Столбец должен иметь имя, тип и размер, также можно задать ограничения для данных, которые там будут содержаться. Например:

CREATE TABLE playground ( equip_id serial PRIMARY KEY, type varchar (50) NOT NULL, color varchar (25) NOT NULL, location varchar(25) check (location in (‘north’, ‘south’, ‘west’, ‘east’, ‘northeast’, ‘southeast’, ‘southwest’, ‘northwest’)), install_date date );

Мы создали таблицу детской площадки для описания оборудования, которое на ней есть. Сначала идет идентификатор equip_id, который имеет тип serial, это значит что его значение будет автоматически увеличиваться, ключ primary key значит, что значения должны быть уникальны.

Следующие колонки — обычные строки, для них мы задаем длину поля, они не могут быть пустыми (NOT NULL). Следующий столбец тоже строка, но она может содержать только одно из указанных значений, последний столбец — дата создания.

Вы можете вывести все таблицы, выполнив команду:

Здесь мы видим что кроме нашей таблицы, существует еще одна переменная — playground_equip_id_seq. В ней содержится последнее значение этого поля. Если нужно вывести только таблицы, выполните:

Вставка и удаление данных

У нас есть таблица, но пока что она пуста. Мы можем добавить в нее некоторые данные. Давайте добавим пункт, описывающий качели. Для этого используются такие команды:

INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2015-08-16’);

Заметьте, что имена столбцов не обязательно заключать в кавычки, а вот имена значений — обязательно. Теперь смотрим что получилось:

Удалять записи можно по любому критерию, например, удалим записи, поле type которых имеет значение slide:

И снова смотрим что получилось:

Оптимизация и тюниг PostgreSQL

В предыдущей статье о MariaDB, мы показывали, как можно привести практически к идеалу параметры конфигурационного файла my.cnf с помощью тюнеров. Для PostgreSQL существует, хотя правильнее сказать существовала такая утилита как PgTun, но к сожалению она уже давно не обновляется. В тоже время есть масса онлайн сервисов, с помощью которых вы можете настроить оптимальную конфигурацию для вашего PostgreSQL. Мне нравится сервис pgtune.leopard.in.ua.

Интерфейс очень прост. Вам нужно указать параметры вашего сервера (профиль, процессоры, память, тип дисков) и нажать кнопку “Generate”. В результате вам будет предложен вариант конфигурационного файла postgresql.conf с рекомендуемыми значениями основных параметров СУБД.

Например, для VPS SSD сервера с 2 Гб оперативной памятью, 2 CPU для запуска нескольких сайтов рекомендуются следующие настройки в postgresql.conf:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 2 GB
# CPUs num: 2
# Connections num: 20
# Data Storage: ssd
max_connections = 20
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

И это на самом деле не единственный ресурс, на момент написания статьи, были достпны аналогичные сервисы:

  • Cybertec PostgreSQL Configurator
  • PostgreSQL Configuration Tool

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

Шаг 6 — Создание и удаление таблиц

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

Во-первых, создайте таблицу для хранения данных. Например, таблицу, где описано оборудование для детских площадок.

Базовый синтаксис этой команды выглядит следующим образом:

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

Вы можете узнать больше о том, как создавать и управлять таблицами в Postgres, здесь.

Чтобы продемонстрировать процесс, создайте простую таблицу:

Эти команды создадут таблицу с описью оборудования для установки на игровых площадках. Она начинается с идентификатора оборудования, который имеет тип . Это тип данных представляет собой целое число с автоматическим инкрементированием. Вы также указали для этого столбца ограничение в виде , что означает, что значения должны быть уникальными и не равны null.

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

Следующие две команды создают столбцы и оборудования соответственно, каждый из которых не может быть пустым. После этого команда создает столбец и ограничение, требующее, чтобы значение было одним из восьми возможных вариантов. Последняя команда создает столбец даты, которая указывает дату установки оборудования.

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

Ваша таблица игрового оборудования готова, но здесь есть что-то под названием с типом данных . Это представление типа который присвоен столбцу Оно отслеживает следующий номер последовательности и создается автоматически для столбцов данного типа.

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


С этим читают