Как подключиться к postgresql в windows
Установка и базовая настройка 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). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name=»Postgre Port» dir=in action=allow protocol=TCP localport=5432
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке 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.
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.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Подключиться к командной строке базы данных PostgreSQL Windows
В наших предыдущих статьях мы говорили о сервере PostgreSQL и базах данных, созданных с его помощью. Однако ранее мы заявляли, что установка этой СУБД в системе Windows 10 немного сложнее, чем в операционной системе Linux. Поэтому большинство пользователей также находят использование баз данных PostgreSQL в Windows 10 довольно сложной задачей. Чтобы облегчить их трудность, мы хотели научить их первому этапу использования баз данных PostgreSQL, то есть подключению к базам данных PostgreSQL. К настоящему времени вы бы поняли цель этого обсуждения. Он направлен на то, чтобы познакомить вас с механизмом подключения к базе данных PostgreSQL при использовании командной строки Windows 10.
Примечание. Вы также можете поэкспериментировать с базами данных PostgreSQL при использовании SQL Shell или psql; однако цель этой статьи была иной.
Как подключиться к базе данных PostgreSQL через интерфейс командной строки (CLI) Windows 10?
Для подключения к любой желаемой базе данных PostgreSQL через Windows 10 CLI вам нужно будет выполнить следующие пять шагов, а шестой — необязательный. Однако мы хотели бы заявить здесь, что вы должны сначала внимательно прочитать все эти шаги, а затем начать их выполнять, чтобы вы могли правильно выполнить желаемую задачу, не совершая никаких ошибок.
Шаг 1. Настройте переменную среды в вашей системе Windows 10
Прежде чем использовать командную строку Windows 10 для доступа к среде PostgreSQL, вам необходимо настроить переменную среды вашей системы. В противном случае вы столкнетесь с проблемами при доступе к среде PostgreSQL в вашей системе. Чтобы настроить требуемую переменную среды, вам нужно ввести «env» в строке поиска Windows 10 и нажать на результат «Изменить системные переменные среды».
После выбора этой опции на вашем экране появится окно «Свойства системы». Вам нужно щелкнуть третью вкладку под названием «Дополнительно» в этом окне. На этой вкладке вам нужно найти кнопку Environment Variables и щелкнуть по ней.
Затем перейдите в раздел «Системные переменные» и дважды щелкните параметр «Путь», расположенный там. Это позволит вам добавить путь к каталогу bin вашего сервера PostgreSQL в переменную среды PATH, чтобы к среде PostgreSQL можно было легко получить доступ из командной строки Windows 10.
В переменной среды PATH вам нужно нажать кнопку «Создать», чтобы вы могли добавить к ней новый путь.
Здесь вам нужно скопировать и вставить путь к каталогу bin вашего сервера PostgreSQL в указанное место. После добавления этого пути вы можете продолжить, нажав кнопку «ОК». После этого вы также можете закрыть окно «Свойства системы».
Шаг 2: Получите доступ к командной строке через систему Windows 10
После того, как вы правильно настроили необходимую переменную среды для своего сервера PostgreSQL, следующим шагом будет запуск командной строки в Windows 10. Для этого вам нужно снова использовать панель поиска Windows 10 и искать в ней «cmd». Вы сразу же увидите результат командной строки, по которому вам нужно щелкнуть, чтобы запустить его.
Окно командной строки по умолчанию в нашей системе Windows 10 показано ниже. Первоначально никакие команды не будут выполняться, но на следующем шаге вы увидите, как запускать команды в командной строке Windows 10.
Шаг 3: Войдите в среду PostgreSQL через командную строку
Теперь вам нужно получить доступ к среде PostgreSQL через командную строку Windows 10. Для этого вам нужно запустить команду, показанную ниже:
Здесь «postgres» представляет собой имя пользователя по умолчанию для сервера PostgreSQL. Во время установки сервера PostgreSQL и даже после этого вы можете создать нового пользователя. Однако, поскольку мы не создавали нового пользователя для нашего сервера PostgreSQL, нам нужно будет получить доступ к среде PostgreSQL через пользователя, созданного по умолчанию во время установки. Если вы также не создали нового пользователя, ваше имя пользователя по умолчанию также будет «postgres».
Когда вы запустите вышеупомянутую команду, вам будет предложено ввести пароль вашей учетной записи. Для учетной записи пользователя по умолчанию вам будет предложено создать пароль для доступа к ней во время установки сервера PostgreSQL; поэтому в этом случае мы предоставим тот пароль, который мы установили для учетной записи пользователя по умолчанию во время установки сервера PostgreSQL в нашей системе Windows 10.
Вскоре после предоставления правильного пароля для указанной учетной записи пользователя вы сразу же войдете в среду PostgreSQL, оставаясь в командной строке Windows 10.
Шаг 4: Создайте образец базы данных PostgreSQL
Прежде чем мы попытаемся подключиться к базе данных PostgreSQL, мы сначала создадим образец базы данных, используя следующую команду. Если у вас уже есть нужная база данных PostgreSQL, созданная в вашей системе Windows 10, вы можете пропустить этот шаг.
# CREATE DATABASE sampleDB WITH ENCODING ‘UTF8′ LC_COLLATE=‘English_United States’ LC_CTYPE=‘English_United States’;
Здесь sampleDB представляет собой имя образца базы данных PostgreSQL, которую мы пытаемся создать с помощью этой команды.
Если вы правильно указали все параметры вышеупомянутой команды, то эта команда будет успешно выполнена, что приведет к ответу «СОЗДАТЬ БАЗУ ДАННЫХ» в командной строке, как показано на изображении ниже:
Поскольку это был всего лишь образец базы данных PostgreSQL, и мы не собирались делать с ней что-либо еще, а просто хотели показать вам, как вы можете подключиться к этой базе данных через командную строку Windows 10; следовательно, мы не создали никаких таблиц в этой базе данных, т. е. наша примерная база данных PostgreSQL пуста.
Шаг 5: Подключитесь к недавно созданной базе данных PostgreSQL
Для подключения к только что созданному образцу базы данных PostgreSQL команда, которую необходимо выполнить, очень проста и выглядит следующим образом:
Здесь sampleDB соответствует базе данных, к которой мы хотим подключиться с помощью командной строки Windows 10. Вы можете заменить его именем нужной базы данных PostgreSQL, к которой вы хотите подключиться.
Если запрошенная база данных существует и других ошибок нет, то вы сможете успешно подключиться к указанной базе данных, но вам не будет представлено подтверждение в командной строке, как показано ниже:
Шаг 6: Выполните запросы PostgreSQL в вашей базе данных PostgreSQL (необязательно)
После успешного подключения к желаемой базе данных PostgreSQL при использовании командной строки Windows 10 вы можете выполнять все запросы по вашему выбору для управления вашими данными. Однако, если вы хотите выйти из среды PostgreSQL после подключения к нужной базе данных, все, что вам нужно сделать, это выполнить следующий запрос, и вы выйдете из среды PostgreSQL.
Заключение
Эта статья дала пользователям хорошее начало при подключении к базам данных PostgreSQL при использовании командной строки Windows 10. Мы надеемся, что после изучения этого руководства вы не столкнетесь с какими-либо проблемами, по крайней мере, при подключении к своим базам данных PostgreSQL в Windows 10, особенно если вы собираетесь использовать для этой цели командную строку.
Подключение к источнику данных PostgreSQL (мастер импорта и экспорта SQL Server)
В этом разделе показано, как подключаться к источникам данных PostgreSQL со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server.
Подробные требования и необходимые условия для подключения к базе данных PostgreSQL выходят за рамки этой статьи Майкрософт. В ней предполагается, что у вас уже установлено клиентское программное обеспечение PostgreSQL и вы можете успешно подключиться к целевой базе данных PostgreSQL. Для получения дополнительных сведений обратитесь к администратору базы данных PostgreSQL или к документации по PostgreSQL.
Получение драйвера ODBC для PostgreSQL
Установка драйвера ODBC с помощью построителя стека
Запустите построитель стека, чтобы добавить драйвер ODBC для PostgreSQL (psqlODBC) в вашу установку PostgreSQL.
Скачивание последней версии драйвера ODBC
Или скачайте установщик Windows для последней версии драйвера ODBC для PostgreSQL (psqlODBC) прямо с этого FTP-сайта — https://www.postgresql.org/ftp/odbc/versions/msi/. Извлеките содержимое ZIP-файла и запустите файл MSI.
Подключение к PostgreSQL с помощью драйвера ODBC для PostgreSQL (psqlODBC)
Указываемые параметры (драйвер ODBC для PostgreSQL)
Параметры подключения для этого поставщика данных и драйвера ODBC одинаковы независимо от того, является ли PostgreSQL источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.
Чтобы подключиться к PostgreSQL с помощью драйвера ODBC для PostgreSQL, соберите строку подключения, используя указанные ниже параметры и их значения. Полный формат строки подключения приведен после списка параметров.
Вы можете получить помощь в построении строки подключения. Кроме того, вместо указания строки подключения вы можете предоставить существующее имя DSN (имя источника данных) или создать новое. Дополнительные сведения об этих возможностях см. в разделе Подключение к источнику данных ODBC.
Драйвер
Имя драйвера ODBC — PostgreSQL ODBC Driver(UNICODE) или PostgreSQL ODBC Driver(ANSI).
Server
Имя сервера PostgreSQL.
порт.
Порт, используемый для подключения к серверу PostgreSQL.
База данных
Имя базы данных PostgreSQL.
Uid и Pwd
Uid (идентификатор пользователя) и Pwd (пароль) для подключения.
Формат строки подключения
Ниже приведен формат типичной строки подключения.
Ввод строки подключения
Введите строку подключения в поле ConnectionString либо введите имя DSN в поле Dsn на странице Выбор источника данных или Выбор назначения. После того как вы введете строку подключения, мастер проанализирует ее и отобразит отдельные свойства и их значения в списке.
В приведенном ниже примере используется следующая строка подключения:
Ниже показан экран, который появляется после ввода строки подключения.
Другие поставщики данных и дополнительные сведения
Сведения о подключении к PostgreSQL с помощью поставщика данных, не представленного в этом списке, см. в разделе Строки подключения PostgreSQL. Этот сторонний сайт также содержит дополнительные сведения о поставщиках данных и параметрах подключения, описанных на этой странице.
Настройка и начало работы с PostgreSQL
Загрузка и установка PostgreSQL
PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать о нем как можно быстрее.
EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер пакетов твоего дистрибутива.
Установщики включают в себя разные компоненты.
Вот самые важные из них:
Windows
Скачав установщик, запусти его как любой другой исполняемый файл. Процесс довольно прямолинеен, но некоторые вещи все же заслуживают внимания.
Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты. Если у тебя нет веской причины что-то менять — оставляй все как есть.
По умолчанию PostgreSQL создает суперпользователя с именем postgres (воспринимай его как учетную запись администратора сервера базы данных).
Во время установки тебе нужно будет указать пароль для суперпользователя (root).
Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли. Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.
После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.
Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.
Используй данные, которые ты вводил на предыдущих шагах.
Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.
Ниже список вариантов установки для других операционных систем.
macOS
Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.
После запуска у тебя появится сервер PostgreSQL, готовый к использованию. Завершить работу сервера можно просто закрыв приложение.
GNU/Linux
Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши из выбранного графического диспетчера пакетов.
Альтернативно, можно использовать установку через терминал. Ты можешь обратиться к документации твоего дистрибутива для получения дополнительных сведений.
Ubuntu
Fedora
openSUSE
Запуск оболочки PostgreSQL
После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.
Открой терминал и введи:
После этого нужно будет ввести пароль суперпользователя, который ты выбрал во время установки.
Как только пароль установлен, база данных PostgreSQL готова к работе!
Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.
Понимание модели клиент-сервер
Я уже упоминал PostgreSQL Server как важный компонент базы данных. Но что такое сервер в этом контексте и зачем он нам нужен?
Для начала тебе необходимо понимать модель клиент-сервер.
Почти все СУБД (PostgreSQL, MySQL и другие) следуют клиент-серверной модели. В ней база данных находится на сервере, и клиент отправляет запросы на сервер, который их обрабатывает.
Для разработки любого бекэнда, тебе нужен локальный сервер для экспериментов и тестирования. Этот локальный сервер аналогичен удаленному, но работает прямо на твоем компьютере.
С точки зрения клиента удаленный и локальный сервер идентичны. После разработки и тестирования ты можешь заставить свой продукт взаимодействовать с удаленным сервером вместо локального, просто изменив пару параметров.
Некоторые базы данных не используют эту модель, например SQLite, которая хранит все в простом файле на диске. Это хорошо работает для небольших приложений, но для большинства реальных приложений тебе понадобится архитектура клиент-сервер.
Мета-команды PostgreSQL
Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд. Это не SQL запросы, а команды специфичные для PostgreSQL.
В других системах управления базами данных есть их аналоги, но их синтаксис немного отличается.
Список всех баз данных
Ввод этой мета-команды в оболочке Postgres выведет:
Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.
Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.
Подключаемся к базе данных PostgreSQL
Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы). Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.
Полностью в терминале у тебя получится что-то такое:
Получить список всех таблиц в базе данных
Перед выполнением этой команды вам необходимо войти в базу данных.
Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных руководствах) и владельца.
Владелец (owner) — это пользователь, который создал таблицу.
Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.
Список пользователей и ролей
Обрати внимание, что первый столбец называется — роль (role name). И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей.
В PostgreSQL пользователи и роли практически одинаковы.
У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание других новых ролей.
Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.
Здесь мы видим только одну роль, суперпользователя по умолчанию.
В реальном мире все будет иначе, потому что использовать только суперпользователя все время опасно. Вместо этого создают другие роли с меньшими привилегиями. Это гарантирует, что никто не совершит нежелательных действий по ошибке.
Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.
Твой первый SQL оператор
Наконец, мы все настроили и готовы к работе и знаем основные мета-команды, специфичные для PostgreSQL.
Теперь приступим к изучению языка запросов SQL.
Я покажу тебе несколько базовых примеров, чтобы разобраться в структурированном языке запросов и получить представление о SQL. А более подробно мы рассмотрим операции CRUD это в следующей статье.
Создание новой базы данных
Команды и ключевые слова SQL обычно пишутся в верхнем регистре.
На самом деле это не является обязательным требованием, и обычно они нечувствительны к регистру.
То есть ты мог бы написать
И все сработало бы нормально.
Но при написании операторов SQL обычно предпочтительнее прописные буквы. Это хорошая практика, потому что она может помочь тебе визуально отличить ключевые слова SQL от других частей оператора, таких как имена таблиц и столбцов.
Для мета-команд PostgreSQL точка с запятой не нужна.
Создание таблиц
После того как ты создал новую базу данных, можно приступать к созданию таблиц.
Теперь, когда ты подключился к базе данных (обратите внимание, что приглашение оболочки SQL теперь включает имя активной базы данных), ты готов создать свою первую таблицу.
Таблица создается с помощью команды CREATE TABLE, за которой следует список столбцов таблицы и их типы данных в круглых скобках:
После создания таблицы перейдем к добавлению данных.
Вставка данных в таблицы PostgreSQL
Чтобы добавить данные в таблицу, используют команду INSERT INTO следующим образом:
Посмотрим на команду INSERT INTO подробнее:
Выборка данных из SQL таблицы
Теперь, когда ты добавил в таблицу первую запись, ты можешь использовать SQL для получения содержимого таблицы.
Для ситуаций когда ты хочешь выбрать все столбцы которые есть в таблице, ты можешь поставить звездочку вместо списка полей.
Звездочка означает: выбрать все столбцы. Результат останется прежним.
Ты должен обратить внимание на то, как команда SELECT выбирает столбцы и строки. Столбцы указываются в виде списка и разделяются запятыми. Затем команда переходит к выбору запрошенных строк.
Если условия не указаны (как в этом случае), будут выбраны все строки в таблице.
Позже мы увидим, как использовать условия с командой WHERE для создания эффективных запросов.
Обновление данных в PostgreSQL
Представь, что ты запустил свое потрясающее приложение для магазина и получили первый заказ на один из продуктов.
Первое, что нужно сделать — это обновить доступное количество в вашем инвентаре, чтобы в дальнейшем у вас не возникли проблемы с отсутствием товара на складе.
Для обновления данных ты можешь использовать команду UPDATE :
После SET — пишем имена столбцов, которые хотим обновить.
За ними — знак равенства и новое обновленное значение.
Также ты можешь обновить сразу несколько столбцов, разделив их запятыми:
Но стоп, какие строки обновляются этой командой?
Удаление данных из SQL таблицы
Теперь рассмотрим случай, когда ты прекратил продажу определенного продукта и захотел полностью удалить его из своей базы данных.
Для этого можно использовать команду DELETE :
Удаление таблиц в PostgreSQL
Если вдруг ты решил изменить структуру базы и для этого нужно удалить всю таблицу, то тебе подойдет команда DROP TABLE :
Это приведет к удалению всей таблицы products из базы данных.
Я бы не позавидовал тому, кто “случайно” удалит не ту таблицу из базы данных.
Удаление баз данных PostgreSQL
Точно так же ты можешь удалить из системы всю базу данных:
Заключение
Поздравляю, у тебя все получилось!
Ты установил и запустили PostgreSQL. Ты изучил основные команды SQL и проделали с ними несколько интересных вещей.
Эти несколько простых команд — основа, которую ты будешь использовать большую часть времени при взаимодействии с базами данных, поэтому тебе следует пойти и потренироваться и изучить самостоятельно. В следующий раз мы погрузимся глубже и обсудим Базы данных, роли и таблицы в PostgreSQL