Как настроить удаленное подключение к PostgreSQL

По умолчанию, сервер баз данных pgsql разрешает подключение только с локального компьютера. Данная инструкция показывает, как разрешить подключения с удаленных систем. Она написана на примере Linux CentOS и PostgreSQL 9.5.

Для начала посмотрим путь расположения конфигурационного файла postgresql.conf:

# su — postgres -c «psql -c ‘SHOW config_file;'»

Команда выдаст, примерно, следующее:

* нас интересует строчка /db/pgsql/postgresql.conf — это путь расположения нужного нам конфигурационного файла.

Теперь открываем на редактирование основной файл конфигурации PostgreSQL:

* напоминаю, что начало пути /db/pgsql может быть другим. Мы его получили командой ps.

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

* по умолчанию, параметр закомментирован и настроен на прослушивание запросов только с локального сетевого интерфейса. В данном примере мы разрешили прослушивание запросов на всех IP-адресах (*), но, если требуется более безопасная настройка, можно просто перечислить последние через пробел.

После открываем на редактирование следующий конфигурационный файл:

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

host all all 192.168.0.10/32 password

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

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

# systemctl restart postgresql-9.5

Проверить подключение можно с удаленного компьютера следующей командой:

# psql -h 192.168.0.15 -U usersql

* где 192.168.0.15 — IP-адрес сервера баз данных; usersql — имя учетной записи, от которой идет подключение.

Установка и базовая настройка PostgreSQL в Windows 10

PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.

Установка PostgreSQL 11 в Windows 10

Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.

В процессе установки установите галочки на пунктах:

• PostgreSQL Server – сам сервер СУБД
• PgAdmin 4 – визуальный редактор SQL
• Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
• Command Line Tools – инструменты командной строки

Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).

По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.

Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.

Доступ к PostgreSQL по сети, правила файерволла

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

Запустите командную строку от имени администратора. Введите команду:

netsh advfirewall firewall add rule name=»Postgre Port» dir=in action=allow protocol=TCP localport=5432

• Где rule name – имя правила
• Localport – разрешенный порт

Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:

New-NetFirewallRule -Name ‘POSTGRESQL-In-TCP’ -DisplayName ‘PostgreSQL (TCP-In)’ -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.

Измените значение в пункте port = 5432 . Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:

Restart-Service -Name postgresql-x64-11

Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.

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

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

Запустите командную строку.

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

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

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
• Создадим таблицу сотрудников:

CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

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

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

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

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

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

Подключение к серверу базы данных PostgreSQL

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

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

Подключение к серверу баз данных PostgreSQL через psql

psql — это интерактивная терминальная программа, предоставляемая PostgreSQL. Она позволяет вам взаимодействовать с сервером баз данных PostgreSQL, например, выполнять операторы SQL и управлять объектами баз данных.

Следующие шаги показывают, как подключиться к серверу баз данных PostrgreSQL через программу psql:

Во-первых, запустите программу psql и подключитесь к PostgreSQL Database Server с помощью пользователя postgres, нажав на икноку psql, как показано ниже:

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

В-третьих, попробуйте повзаимодействовать с сервером баз данных PostgreSQL при помощи операторов SQL. Для этого вы можете воспользоваться следующим оператором:

Пожалуйста, не забудьте завершить команду точкой с запятой (;). После нажатия Enter, psql вернёт текущую версию PostgreSQL, установленную в вашей системе.

Подключение к серверу PostgreSQL через pgAdmin

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

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

Во-первых, запустите приложение pgAdmin.

Приложение pgAdmin 4 будет запущено в веб-браузере, как показано на картинке ниже:

Во-вторых, для создания сервера, щёлкните правой кнопкой мыши по узлу Servers и выберите меню Create > Server…

В-третьих, введите имя сервера, например, PostgreSQL и нажмите на вкладку Connection:

В-четвёртых, введите имя хоста и пароль для пользователя postgres, после чего щёлкните по кнопке Save:

В-пятых, щёлкните по узлу Servers, чтобы открыть иерархию сервера. Изначально, PostgreSQL содержит базу данных, которая называется postgres, как показано ниже:

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

В-седьмых, введите запрос в Query Editor, затем нажмите на кнопку Execute, после чего вы увидите результат запроса, отображающийся во вкладке DataOutput:

Подключение к серверу баз данных PostgreSQL через другие приложения

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

Замечательно! Вы научились подключаться к серверу баз данных PostgreSQL, используя различные клиентские инструменты, такие как psql и pgADMIN. Следующим шагом мы ознакомимся с примером базы данных PostgreSQL, которую вы сможете использовать для изучения и практики.