Открыть порт 5432 postgresql windows

In order to remotely access a PostgreSQL database, you must set the two main PostgreSQL configuration files:

postgresql.conf
pg_hba.conf

Here is a brief description about how you can set them (note that the following description is purely indicative: To configure a machine safely, you must be familiar with all the parameters and their meanings)

First of all configure PostgreSQL service to listen on port 5432 on all network interfaces in Windows 7 machine:
open the file postgresql.conf (usually located in C:\Program Files\PostgreSQL\9.2\data) and sets the parameter

listen_addresses = '*'

Check the network address of WindowsXP virtual machine, and sets parameters in pg_hba.conf file (located in the same directory of postgresql.conf) so that postgresql can accept connections from virtual machine hosts.
For example, if the machine with Windows XP have 192.168.56.2 IP address, add in the pg_hba.conf file:

host all all 192.168.56.1/24 md5

this way, PostgreSQL will accept connections from all hosts on the network 192.168.1.XXX.

Restart the PostgreSQL service in Windows 7 (Services-> PosgreSQL 9.2: right click and restart sevice). Install pgAdmin on windows XP machine and try to connect to PostgreSQL.

В мире современных информационных технологий удалённое подключение к базам данных стало неотъемлемой частью работы многих предприятий и организаций. В одной из предыдущих статей нашего справочника мы описали процесс установки PostgreSQL на VPS, работающий под управлением Windows Server 2016. Теперь же мы продолжим этот путь и рассмотрим процесс первоначальной настройки удалённого доступа к PostgreSQL, что в дальнейшем позволит управлять данными и обеспечивать доступ к ним из любой точки мира.

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

На нашем сервере конфигурационный файл pg_hba.conf находится в директории C:\Program Files\PostgreSQL\15\data. В конец данного файла необходимо добавить следующую строку:

host postgres postgres all md5

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

В той же директории находится файл postgresql.conf, в котором должна присутствовать строка listen_addresses = '*' в разделе CONNECTIONS AND AUTHENTICATION:

Создание правила в брандмауэре Windows

Далее, необходимо произвести некоторые настройки брандмауэра, которые в Windows Server 2016 удобнее производить через Server Manager.

Для запуска Server Manager перейдите в стартовое меню и кликните по соответствующей иконке.

После того, как менеджер откроется, перейдите Tools → Windows Firewall with Advanced Security.

В мониторе брандмауэра необходимо добавить правило для подключения к СУБД. Для этого перейдите Inbound Rules → New Rule.

В открывшемся окне мастера выберите тип Port, затем нажмите Next.

В следующем окне укажите протокол TCP и номер порта — 5432, после чего снова нажмите Next.

Затем для нашего тестового подключение мы разрешим все подключения, поэтому активируйте опцию Allow the connection и так же нажмите Next.

В следующем окне без внесения каких-либо изменений нажмите Next.

И, наконец, в заключительном окне мастера в строку Name введите имя создаваемого правила и нажмите Finish.

Таким образом, созданное правило должно будет появиться в мониторе брандмауэра.

Тестирование созданного подключения

Для проверки возможности подключения мы будем использовать виртуальный сервер, работающий под управлением операционной системы Ubuntu. Во-первых, необходимо проверить доступ к порту 5432. Для чего можно использовать утилиту telnet. Синтаксис данной утилиты применяется следующий:

telnet IP-address Port

В данном случае IP-address — IP-адрес сервера, а Port — номер порта, к которому производится подключение. В случае, если порт на удалённом сервере доступен для подключения, вывод команды telnet будет выглядеть следующим образом:

Для того, чтобы прервать установленное соединение, используйте комбинацию клавиш Ctrl Z.

Далее, необходимо установить на linux-сервер клиента, с помощью которого будет осуществляться подключение к PostgreSQL.

$ sudo apt install postgresql-client

После успешной инсталляции проверьте версию установленного софта:

$ psql --version

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

Теперь при помощи postgresql-client вы сможете подключиться к вашей СУБД, установленной на виртуальном windows-сервере. Формат команды для подключения выглядит как:

psql -U user-name -h IP-address -d DB-name

В данном случае:

• -U — ключ, который говорит о том, что подключение будет осуществлено под учётной записью, указанной после ключа;
• user-name — имя учётной записи, используемой для подключения;
• -h — ключ, указывающий на то, что подключение будет произведено к удалённому хосту;
• IP-address — IP-адрес удалённого хоста;
• -d — ключ, после которого указывается имя базы данных, к которой производится подключение;
• DB-name — имя базы данных.

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

psql -U postgres -h 194.58.71.188 -d postgres

На скриншоте ниже видно, что мы успешно подключились к СУБД, расположенной на нашей тестовой VPS с использованием терминального клиента psql в командной строке на linux-сервере.

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.

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

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

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

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

• Перейдите в каталог bin выполнив команду:
  CD C:\Program Files\PostgreSQL\11\bin

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

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

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

По умолчанию все созданные базы хранятся в каталоге 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 принимает подключения только с локального хоста, для того чтобы мы могли подключиться к СУБД удаленно — нужно внести изменения в конфигурационный файл и открыть порты в Windows.

Редактирование конфигурации PostgreSQL

Перейдите в директорию, в которой располагаются БД PostgreSQL. Для PostgreSQL 13 это следующие директории:

• x86: C:\Program Files (x86)\PostgreSQL\13\data
• x64: C:\Program Files\PostgreSQL\13\data

В директории data найдите файл pg_hba.conf и откройте его любым текстовым редактором. В конец файла добавьте следующую запись:

host postgres postgres all md5

Расшифровка записи:

Вид подключения	База данных	Пользователь	IP-адрес удаленного подключения	Метод аутентификации
host	postgres	postgres	all	md5

Мы разрешили подключения к СУБД с любого IP под стандартным пользователем postgres к стандартной базе postgres. Конечно, так делать нельзя. Это небезопасно. Администратор должен создать пользователя для удаленного подключения с ограниченными правами.

Далее необходимо убедиться в наличии строки listen_addresses = ‘*’ в файле postgresql.conf. Файл находится там же, в директории data. Откройте файл в текстовом редакторе и найдите раздел CONNECTIONS AND AUTHENTICATION.

В одной из первых строчек раздела будет указан параметр listen_addresses = ‘*’. Важно убедиться в том, что значение параметра = ‘*’. Если параметр отсутствует или имеет другое значение. Скопируйте правильное значение параметра из нашей инструкции и вставьте его в файл.

Редактирование конфигурационных файлов PostgreSQL закончено, теперь осталось настроить брандмауэр Windows.

Настройка брандмауэра Windows

Кликнете по иконке поиска, расположенной справа от кнопки Пуск и введите в строку запроса: защит… (в английской версии defend). В поисковой выдачи вы увидите: Монитор брандмауэра Защитника Windows.

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

Откроется мастер создания правила для нового входящего подключения. Выберите тип правила — для порта. Нажмите кнопку Далее.

Теперь необходимо указать протокол и порт, для которого будет настроено правило входящего доступа. По умолчанию PostgreSQL «слушает» 5432 порт. Вот его и откроем.

Далее мы разрешаем внешние подключения к порту 5432.

На следующем шаге мы все оставляем без изменений и нажимаем Далее.

На последнем шаге задаем интуитивно понятное имя подключения и описание. Сохраняем правило и проверяем, что оно принято. Мы создали правило под названием PostgreSQL ingoing. Оно разрешает входной трафик на порт 5432 с любого внешнего IP и порта.

Проверка удаленного подключения к PostgreSQL

Протестируем возможность подключения к PostgreSQL под Linux. Сначала проверим доступность 5432 порта. Сделать это удобнее всего утилитой telnet. Если telnet не установлен — скачать его можно следующей командой:

# apt install telnet

Возможно, вам понадобятся root-права для установки утилиты. Получить их можно командой: sudo su. Конечно, вы должны знать пароль от root-пользователя.

Проверим доступность 5432 порта:

# telnet 185.233.2.45 5432

Синтаксис команды следующий: telnet IP-адрес сервера Порт. В нашем случае IP-адрес сервера — 185.233.2.45, а порт — 5432.

Если порт доступен, telnet вернет следующую информацию:

Trying 185.233.2.45...
Connected to 185.233.2.45.
Escape character is '^]'. 

Чтобы прервать подключение нажмите 2 раза Enter или сочетание клавиш Ctrl + Z. Теперь, когда мы убедились в доступности 5432 порта, подключимся к PostgreSQL с помощью специального PostgreSQL клиента — psql.

Скачаем psql из репозитория:

# apt install psql

Теперь подключимся удаленно к PostgreSQL. Синтаксис команды следующий:

psql -U пользователь PostgreSQL -h IP сервера -d БД для подключения

В нашем случае команда выглядит так:

psql -U postgres -h 185.233.2.45 -d postgres

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

Команда вернула следующую информацию:

psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1), server 13.3)
WARNING: psql major version 12, server major version 13.
Some psql features might not work.
Type "help" for help.

postgres=# 

Все, мы подключились к PostgreSQL удаленно из Linux с помощью psql.

Краткое руководство по установке и первоначальной настройке системы управления базами данных PostgreSQL на ОС Windows Server 2019 Core.

В первую очередь запускаем PowerShell и подключаем модуль BitsTransfer для скачивания файлов

Import-Module -Name BitsTransfer

После этого на диске C создадим каталог Distr и перейдём в него

cd \
mkdir Distr
cd \Distr

Затем получаем ссылку для скачивания дистрибутива со страницы https://enterprisedb.com/software-downloads-postgres

и скачиваем с помощью команды

Start-BitsTransfer -Source https://get.enterprisedb.com/postgresql/postgresql-14.1-1-windows-x64.exe

Запускаем установщик

postgresql-14.1-1-windows-x64.exe

выбираем директорию, в которую будет установлена СУБД

выбираем компоненты, которые будут установлены

выбираем директорию, где будут храниться базы данных

задаём пароль пользователю postgres

оставляем порт по умолчанию — 5432

выбираем локаль: Russian, Russia

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

должно быть всё готово для установки

и после клика по кнопки Next начнётся установка

также по желанию можно установить Stack Builder (но не обязательно)

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

taskmgr

перейти во вкладку Службы и появится служба postgresql-x64-14

Для запуска консоли PostgreSQL необходимо перейти в каталог C:\Program Files\PostgreSQL\14\bin и в нём запустить утилиту psql с функцией DBA:

C:\Program Files\PostgreSQL\14\bin > psql -U postgres

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

C:\Program Files\PostgreSQL\14\data > notepad pg_hba.conf

здесь необходимо будет добавить строчку:

host		all		all		all		md5

Ещё на всякий случай в файле postgresql.conf, который находится там же,где и pg_hba.conf, проверим наличие строчки:

listen_addresses = "*"

если в отличии от звёздочки стоит другое значение, значит исправляем на звёздочку.

Для удалённого подключения к серверу в брандмауэре с помощью консоли необходимо открыть порт 5432/TCP для входящих соединений

# netsh advfirewall firewall add rule name="PostgreSQL" protocol=TCP localport=5432 dir=IN action=allow

Чтобы новые параметры вступили в силу, необходимо перезапустить службу postgresql-x64-14, либо перезагрузить машину.