Налаштування та початок роботи з PostgreSQL

Минулого разу я познайомив тебе з основами реляційних баз даних та SQL. Тепер настав час налаштувати робоче середовище. Ти будеш використовувати PostgreSQL як свою СУБД, тому для початку її потрібно завантажити та встановити.

Завантаження та встановлення PostgreSQL

PostgreSQL підтримує всі основні операційні системи. Процес установки простий, тому я постараюся розповісти про нього якнайшвидше.

Для Windows та Mac ти можеш завантажити інсталятор із веб-сайту EDB.

EDB більше не надає пакети для GNU/Linux. Натомість вони рекомендують використовувати диспетчер пакетів твого дистрибутива.

Установники включають різні компоненти.

Ось найважливіші з них:

• Сервер PostgreSQL (очевидно)
• pgAdmin, графічний інструмент для управління базами даних
• Менеджер пакетів для завантаження та встановлення додаткових інструментів та драйверів

Windows

Завантаживши установник, запусти його як будь-який інший файл, що виконується. Процес досить прямолінійний, але деякі речі все ж таки заслуговують на увагу.

Діалогове вікно "Вибрати компоненти" дозволяє вибірково встановлювати компоненти. Якщо в тебе немає вагомої причини щось міняти – залишай усе як є.

За замовчуванням PostgreSQL створює суперкористувача з ім'ям postgres (сприймай його як обліковий запис адміністратора сервера бази даних).

Під час встановлення тобі потрібно буде вказати пароль для суперкористувача (root).

Пізніше ти зможеш створити інших користувачів та призначати їм окремі доступи та ролі. Ми повернемося до цього пізніше, а зараз тобі знадобиться обліковий запис суперкористувача, щоби почати використовувати СУБД.

Щоб запустити сервер розробки на комп'ютері або localhost , необхідно призначити йому порт.

Порт за замовчуванням – 5432.Якщо ти встановлюєш PostgreSQL вперше, він швидше за все вільний. Якщо виявиться, що порт вже зайнятий іншим екземпляром PostgreSQL, ти можеш вказати інше значення, наприклад 5433.

Після завершення інсталяції ти зможеш запустити SQL Shell, який постачається з Postgres.

Крок за кроком ти вибереш сервер, яку базу даних використовувати, порт, ім'я користувача та пароль.

Використовуйте дані, які ти вводив на попередніх кроках.

Вітаю! Налаштування для Windows завершено, і незабаром ми почнемо писати перші запити SQL.

Нижче список варіантів інсталяції для інших операційних систем.

macOS

Для MacOS у тебе є різні варіанти. Можна завантажити інсталятор із сайту EDB і запустити його.

Крім того, можна використовувати Postgres.app, простий додаток для macOS.

Після запуску з'явиться сервер PostgreSQL, готовий до використання. Завершити роботу сервера можна, просто закривши програму.

Ви також можете використовувати Homebrew , менеджер пакетів для macOS.

GNU/Linux

Ти можеш знайти PostgreSQL у репозиторіях більшості дистрибутивів Linux. Встановити його можна одним натисканням миші з вибраного графічного диспетчера пакетів.

Альтернативно можна використовувати установку через термінал. Ти можеш звернутися до документації дистрибутива для отримання додаткових відомостей.

Ubuntu

sudo apt-get install postgresql

Fedora

sudo yum install postgresql-server postgresql-contrib

openSUSE

sudo zypper install postgresql postgresql-server postgresql-contrib

Arch

sudo pacman -S postgresql

Запуск оболонки PostgreSQL

Після встановлення PostgreSQL потрібно запустити оболонку (shell), за допомогою якої ти отримаєш можливість керувати базою даних.

Відкрий термінал і введи:

psql – це оболонка Postgres, аргумент –U використовується для вказівки користувача.

Оскільки ти ще не створював інших користувачів, ти увійдеш до системи як суперкористувач postgres.

Після цього потрібно буде ввести пароль суперкористувача, який вибрав під час установки.

Як тільки пароль встановлений, база даних PostgreSQL готова до роботи!

Якщо сервер PostgreSQL з якоїсь причини не запускається, можеш спробувати запустити його вручну.

sudo systemctl start postgres

Розуміння моделі клієнт-сервер

Я вже згадував PostgreSQL Server як важливий компонент бази даних. Але що таке сервер у цьому контексті і навіщо він нам потрібний?

Для початку тобі потрібно розуміти модель клієнт-сервер.

Майже всі СУБД (PostgreSQL, MySQL та інші) слідують клієнт-серверної моделі. У ній база даних знаходиться на сервері, і клієнт надсилає запити на сервер, який їх обробляє.

Під клієнтом тут мається на увазі бекенд нашої програми, а запити - це SQL операції, такі як SELECT, INSERT, UPDATE і DELETE.

Для розробки будь-якого бекенда, тобі потрібен локальний сервер для експериментів та тестування. Цей локальний сервер аналогічний до віддаленого, але працює прямо на твоєму комп'ютері.

З погляду клієнта віддалений та локальний сервер ідентичні. Після розробки та тестування ти можеш змусити свій продукт взаємодіяти з віддаленим сервером замість локального просто змінивши пару параметрів.

Деякі бази даних не використовують цю модель, наприклад SQLite, яка зберігає все у простому файлі на диску. Це добре працює для невеликих програм, але для більшості реальних програм тобі знадобиться архітектура клієнт-сервер.

Мета-команди PostgreSQL

Тепер, коли ти все налаштував і готовий розпочати роботу з базою даних, залишилося розібрати кілька мета-команд. Це не SQL запити, а команди специфічні для PostgreSQL.

В інших системах управління базами даних є їх аналоги, але їхній синтаксис трохи відрізняється.

Всім мета-команд передує зворотна коса риса \ , за якою слідує фактична команда.

Список усіх баз даних

Щоб отримати список усіх баз даних на сервері, ти можеш використовувати команду \l.

Введення цієї мета-команди в оболонці Postgres виведе:

List  of databases Name  |
Owner
|
Encoding
|
Collate
| Ctype  |
Access
privileges
-------------+----------+----------+-------------+-------------+----------------------- postgres  | postgres  | UTF8  | en_US.UTF-8
| en_US.UTF-8
| template0  | postgres  | UTF8  | en_US.UTF-8
| en_US.UTF-8
|
=c/postgres  +
|
|
|
|
| postgres=CTc/postgres template1  | postgres  | UTF8  | en_US.UTF-8
| en_US.UTF-8
|
=c/postgres  +
|
|
|
|
| postgres=CTc/postgres

Це список всіх наявних баз даних та службова інформація, така як власник бази даних, кодування та права доступу.

На даний момент ми поки що нічого не створили, а бази даних, які ти бачиш на екрані - створюються за умовчанням при установці Postgres.

• postgres – це просто порожня база даних.
• «template0» та «template1» - це службові бази даних, які є шаблоном для створення нових баз.

Тобі поки що не варто турбуватися про них. Якщо хочеш вивчити всі деталі, перевір офіційну документацію.

Підключаємось до бази даних PostgreSQL

Деякі команди SQL вимагають, щоб ти спочатку увійшов до бази даних (наприклад, для створення нової таблиці). Ти можеш вибрати, до якої бази даних входити при запуску SQL Shell.

Коли ти знаходишся всередині оболонки (shell), то можеш використовувати команду \c (або \connect ), за якою слідує ім'я бази даних. Якби ти мала іншу базу даних під назвою hello_world , то підключитися до неї можна було б так:

Цілком у терміналі в тебе вийде щось таке:

postgres=# hello_world You now connected to database  "hello_world" as user  "postgres". hello_world=#

Зверніть увагу, що запрошення оболонки змінилося з postgres на hello_world. Це означає, що тепер ти підключений до бази даних hello_world, а не postgres.

Отримати список усіх таблиць у базі даних

Як і у випадку зі списком існуючих баз даних, ти можеш отримати список таблиць усередині конкретної бази даних за допомогою команди \dt.

Перед виконанням цієї команди вам необхідно увійти до бази даних.

Припустимо, ти вже знаходишся всередині бази hello_world і в ній є таблиця з ім'ям my_table . Набравши \dt, ти отримаєш таке:

List of relations Schema | Name | Тип | Owner --------+----------+-------+---------- public | my_table | table | postgres

Ти можеш побачити ім'я таблиці та деяку іншу інформацію, таку як схема (ми обговоримо схеми у складніших посібниках) та власника.

Власник (owner) – це користувач, який створив таблицю.

Якщо ти створюєш інших користувачів та використовуєш їх для створення таблиць, то в останньому стовпці будуть саме вони.

Список користувачів та ролей

Як ти вже знаєш, при установці Postgres створюється суперкористувач з ім'ям postgres. Список всіх користувачів бази даних можна вивести на екран, використовуючи команду \dg .

List roles Role name | Attributes | Member of ------------+----------------------------------- -------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |  <>

Зверніть увагу, що перший стовпець називається роль (role name). І весь висновок на екран називається "список ролей" (List of roles), а не список користувачів.

У PostgreSQL користувачі та ролі практично однакові.

У ролей є атрибути, які визначають їх дозволи, такі як створення баз даних і навіть створення інших нових ролей.

Будь-яка роль з атрибутом LOGIN може розглядатися як користувач.

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

У реальному світі все буде інакше, тому що використовувати тільки суперкористувача весь час небезпечно. Замість цього створюють інші ролі з меншими привілеями.

Якщо одна з ролей має доступ тільки на читання даних, то за допомогою цієї ролі буде неможливо видалити таблицю або поле.

Твій перший оператор SQL

Нарешті ми всі налаштували і готові до роботи і знаємо основні мета-команди, специфічні для PostgreSQL.

Тепер приступимо до вивчення мови запитів SQL.

Я покажу тобі кілька базових прикладів, щоб розібратися в структурованій мові запитів і отримати уявлення про SQL.

Створення нової бази даних

Перше, що тобі потрібно дізнатися при вивченні баз даних, - це як створити базу даних.

CREATE
DATABASE store;

Команди та ключові слова SQL зазвичай пишуться у верхньому регістрі.

Насправді це не є обов'язковою вимогою і зазвичай вони нечутливі до регістру.

Тобто ти міг би написати

create
database store;

І все спрацювало б нормально.

Але при написанні операторів SQL зазвичай краще великі літери. Це хороша практика, тому що вона може допомогти тобі візуально відрізнити ключові слова SQL від інших частин оператора, таких як імена таблиць і стовпців.

Зауважте, що всі стандартні команди в PostgreSQL повинні закінчуватися точкою з комою ;

Для мета-команд PostgreSQL точка з комою не потрібна.

Створення таблиць

Після того, як ти створив нову базу даних, можна приступати до створення таблиць.

Але, для початку, підключимося до нової бази даних за допомогою команди \c, за якою слідує ім'я бази даних:

Тепер, коли ти підключився до бази даних (зауважте, що запрошення оболонки SQL тепер включає ім'я активної бази даних), ти готовий створити свою першу таблицю.

Таблиця створюється за допомогою команди CREATE TABLE, за якою слідує список стовпців таблиці та їх типи даних у круглих дужках:

CREATE
TABLE products ( id INT, name TEXT, quantity INT )

Це створить таблицю під назвою products , яка містить 3 стовпці:

• id типу INT (ціле число)
• name типу TEXT (рядок)
• quantity також типу INT

Після створення таблиці перейдемо до додавання даних.

Вставка даних у таблиці PostgreSQL

Щоб додати дані до таблиці, використовують команду INSERT INTO таким чином:

INSERT
INTO products (id, name, quantity)  VALUES (1, 'first product', 20);

Подивимося на команду INSERT INTO докладніше:

• Команда INSERT INTO означає, що ви маєте намір вставити нові дані
• products - це ім'я таблиці в базі даних, до якої хочеш вставити дані
• (id, name, quantity) - це список стовпців у нашій таблиці, розділених комами. Тобі не потрібно вказувати всі стовпці (інакше який у сенс?). У деяких випадках ви хочете вибірково вставляти дані у деякі стовпці. Інші стовпці будуть автоматично заповнені значеннями за промовчанням.
• VALUES (1, 'first product', 20) - це фактичні дані, які будуть вставлені в таблицю. "1" - це id, "first product" - це name, "20" - це quantity.

Вибірка даних із SQL таблиці

Тепер, коли ти додав до таблиці перший запис, ти можеш використовувати SQL, щоб отримати вміст таблиці.

Вибір даних здійснюється за допомогою команди SELECT , і це виглядає наступним чином:

SELECT id, name, quantity  FROM products;

Ми використовуємо команду SELECT. За нею слідує список стовпців, які ми хочемо отримати.

Потім ми використовуємо команду FROM для вказівки, з якої таблиці брати дані. На цей раз це таблиця products.

ID | name | quantity ---+---------------+---------- 1 | перший продукт | 50

Для ситуацій коли ти хочеш вибрати всі стовпці, які є в таблиці, ти можеш поставити зірочку замість списку полів.

SELECT
*
FROM products;

Зірочка означає: вибрати усі стовпці. Результат залишиться тим самим.

Ви повинні звернути увагу на те, як команда SELECT вибирає стовпці та рядки. Стовпці вказуються у вигляді списку і поділяються комами. Потім команда переходить до вибору рядків.

Якщо умови не вказані (як у цьому випадку), будуть вибрані всі рядки таблиці.

Згодом ми побачимо, як використовувати умови з командою WHERE для створення ефективних запитів.

Оновлення даних у PostgreSQL

Уяви, що ти запустив свій чудовий додаток для магазину і отримали перше замовлення на один з продуктів.

Перше, що потрібно зробити - це оновити доступну кількість у вашому інвентарі, щоб надалі у вас не виникли проблеми з відсутністю товару на складі.

Для оновлення даних ти можеш використовувати команду UPDATE:

UPDATE products  SET quantity=49
WHERE id=1;

Давайте розберемося з тим, як працює UPDATE.

Починаємо ми з ключового слова UPDATE , за яким слідує ім'я таблиці.

Потім ми використовуємо SET для встановлення нових значень для наших стовпців.

Після SET – пишемо імена стовпців, які хочемо оновити.

За ними – знак рівності та нове оновлене значення.

Також ти можеш оновити відразу кілька стовпців, розділивши їх комами:

UPDATE products  SET name='new name', quantity=49
WHERE id=1;

Але стоп, які рядки оновлюються цією командою?

Ти вже мали здогадатися про це.Щоб вказати, які рядки слід оновити новими значеннями, ми використовуємо команду WHERE , за якою слідує умова.

У цьому випадку ми зіставляємо рядки, використовуючи їх стовпець id і оновлюємо рядок з id 1.

Видалення даних із SQL таблиці

Тепер розглянемо випадок, коли ти припинив продаж певного продукту та захотів повністю видалити його зі своєї бази даних.

Для цього можна використовувати команду DELETE:

DELETE
FROM products  WHERE id=1;

Як і при оновленні даних, щоб визначити, які саме рядки ми хочемо видалити, нам потрібна умова WHERE.

Видалення таблиць у PostgreSQL

Якщо раптом ти вирішив змінити структуру бази і для цього потрібно видалити всю таблицю, то підійде тобі команда DROP TABLE :

DROP
TABLE products;

Це призведе до видалення всієї таблиці продуктів з бази даних.

Будь дуже обережний з командою DELETE!

Я не позаздрив би тому, хто “випадково” видалить не ту таблицю з бази даних.

Видалення баз даних PostgreSQL

Так само ти можеш видалити з системи всю базу даних:

DROP
DATABASE store;

Висновок

Вітаю, у тебе все вийшло!

Ти встановив та запустили PostgreSQL. Ти вивчив основні команди SQL і зробили з ними кілька цікавих речей.

Ці кілька простих команд - основа, яку ти використовуватимеш більшу частину часу при взаємодії з базами даних, тому тобі слід піти та потренуватися та вивчити самостійно. Наступного разу ми зануримося глибше та обговоримо Бази даних, ролі та таблиці у PostgreSQL

Встановлення та базове налаштування 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

Або ви можете створити правило, що дозволяє 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.conf на шляху C:Program FilesPostgreSQL11data.

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

Restart-Service -Name postgresql-x64-11

Докладніше про налаштування параметрів у конфігураційному файлі postgresql.conf за допомогою тюнерів дивіться у статті.

Утиліти керування PostgreSQL через командний рядок

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

Запустіть командний рядок.

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

Основні команди PostgreSQL:

• Перевірка встановленої версії СУБД: psql -V
• Для створення нової бази даних скористайтесь утилітою createdb: createdb -U postgres testdb (де postgres суперкористувач, testdb нова база даних) Введіть пароль суперкористувача.
• Перевірити список активних баз: Psql -U postgres -l (пароль)
• За допомогою інструменту createuser створимо нового користувача: 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 FilesPostgreSQL11database .

Для кожної БД існує підкаталог усередині 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 базовий комплект установки входить такий інструмент як pgAdmin . Він представляє графічний клієнт для роботи з сервером, через який ми у зручному вигляді можемо створювати, видаляти, змінювати бази даних та керувати ними. Так, на Windows після встановлення ми можемо знайти значок pgAdmin у меню Пуск та запустити його:

Після цього відкриється наступна програма pgAdmin. При відкритті також з'явиться вікно для введення пароля для підключення до сервера Postgres:

Тут необхідно ввести пароль для суперкористувача postgres, який був заданий під час встановлення PostgreSQL.

Після успішного логіну нам відкриється вміст сервера:

Зокрема, у вузлі Databases ми можемо побачити всі наявні бази даних. За замовчуванням тут є лише одна база даних – postgres.

Також у правій частині ми можемо побачити вузол Login/Group Roles, який призначений для керування користувачами та їх ролями.

І третій вузол - Tablespaces дозволяє керувати місцем зберігання файлів баз даних.

Тепер створимо свою базу даних. Для цього натиснемо правою кнопкою миші на вузол Databases. І далі в контекстному меню оберемо Create -> Database.

Після цього відобразиться вікно для створення бази даних. Введемо назву для БД, наприклад, test1 і натиснемо на кнопку Save:

Після цього в деревоподібному меню зліва відобразиться вміст створеної бази даних test1: