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

PostgreSQL керує кількома ізольованими базами даних в межах одного екземпляра сервера, кожна з яких має власну схему, ролі та привілеї. Щоб отримати список усіх баз даних, виконайте l всередині psql або зробіть запит SELECT datname FROM pg_catalog.pg_database; з будь-якого сеансу. Щоб переключитися між базами даних, необхідно відкрити нове з’єднання — PostgreSQL забезпечує суворе прив’язування сеансу до бази даних без еквівалента команди USE всередині сеансу.

Цей посібник охоплює всі доступні методи для перерахування та підключення до баз даних PostgreSQL: від необроблених команд psql і запитів до системного каталогу до рядків підключення, міркувань щодо pg_hba.conf та шаблонів роботи з кількома базами даних, що використовуються у виробничих середовищах.

Чому переключення баз даних у PostgreSQL працює інакше

Більшість розробників, які прийшли з MySQL, очікують команду USE database_name;. PostgreSQL навмисно її не має. Кожен сеанс PostgreSQL прив’язується рівно до однієї бази даних під час підключення, і це прив’язування є незмінним протягом усього часу існування сеансу. Це архітектурне рішення, засноване на процесній моделі PostgreSQL: серверний процес (postgres) завантажує системний каталог бази даних у спільну пам’ять під час запуску, і перемикання каталогів у середині сеансу все одно вимагало б повного перезапуску процесу.

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

Перелік усіх баз даних у PostgreSQL

Метод 1: Мета-команда l у psql

Найшвидший спосіб перерахувати бази даних — це мета-команда l (псевдонім: list) всередині інтерактивного сеансу psql.

psql -U postgres

Після підключення:

l

Це виводить відформатовану таблицю, подібну до:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 myapp_db  | appuser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 analytics | analyst  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | analyst=CTc/analyst
 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 |

Стовпці розкривають більше, ніж просто назви: Encoding важливе при міграції даних між серверами, Collate впливає на порядок сортування та поведінку індексів, а Access privileges використовують нотацію ACL PostgreSQL (C = CONNECT, T = TEMPORARY, c = CREATE).

Щоб отримати розширені відомості, включаючи табличний простір і ліміти підключень, використовуйте:

l+

Метод 2: Запит до системного каталогу pg_database

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

SELECT
    datname        AS database_name,
    pg_catalog.pg_get_userbyid(datdba) AS owner,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate     AS collation,
    datctype       AS ctype,
    datconnlimit   AS connection_limit,
    pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(datname)) AS size
FROM pg_catalog.pg_database
WHERE datistemplate = false
ORDER BY datname;

Фільтрація datistemplate = false виключає template0 та template1 з результатів — це системні шаблони, а не робочі бази даних. Стовпець datconnlimit є критично важливим у спільних середовищах: значення -1 означає необмежену кількість, тоді як будь-яке позитивне ціле число обмежує кількість одночасних підключень до цієї бази даних.

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

Метод 3: Перелік баз даних без входу в psql

Для shell-скриптів і конвеєрів автоматизації можна отримати список баз даних без входу в інтерактивний сеанс:

psql -U postgres -c "l"

Або для чистого, придатного для парсингу скриптами виводу:

psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

Прапорець -t пригнічує заголовки стовпців і кількість рядків, повертаючи лише необроблені значення — ідеально підходить для передачі в grep, awk або масиви Bash.

Щоб експортувати список у файл:

psql -U postgres -t -A -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > db_list.txt

-A вимикає вирівнювання стовпців, виводячи по одній назві бази даних на рядок.
Переключення між базами даних у PostgreSQL
Оскільки неможливо переключити бази даних у межах активного сеансу, правильний підхід полягає в завершенні поточного з’єднання та встановленні нового, спрямованого на потрібну базу даних. Є кілька способів зробити це ефективно.
Метод 1: Вихід і повторне підключення з оболонки
Всередині psql завершіть поточний сеанс:
q
Потім підключіться до цільової бази даних:
psql -U postgres -d target_database
Метод 2: Використання c (Connect) всередині psql
Це найпрактичніший метод для інтерактивної роботи. Мета-команда c закриває поточне з’єднання та відкриває нове до вказаної бази даних — все в межах одного термінального сеансу.
c target_database
Також можна одночасно змінити користувача та хост:
c target_database admin_user localhost 5432
Синтаксис: c [database [username [host [port]]]]

Коли ви виконуєте c, psql відобразить підтвердження:

You are now connected to database "target_database" as user "postgres".

Важливий крайній випадок: Якщо цільова база даних не існує або поточний користувач не має привілею CONNECT, c завершиться невдачею і поверне вас до попереднього з’єднання. Це безпечніше, ніж здається — ви не залишитеся без з’єднання, але в скриптах необхідно обробляти це, перевіряючи статус виходу.

Метод 3: Підключення як інший користувач

Щоб підключитися до бази даних під певною роллю:

psql -d myapp_db -U appuser -h localhost -p 5432

Або використовуючи скорочення c всередині існуючого сеансу:

c myapp_db appuser

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

Метод 4: Використання рядків підключення (формат URI)

PostgreSQL підтримує формат URI підключення libpq, який об’єднує всі параметри підключення в один рядок. Це кращий метод для конфігурації застосунків, конвеєрів CI/CD та інструментів інфраструктури як коду.

psql "postgresql://appuser:password@localhost:5432/myapp_db"

Або використовуючи схему postgres:// (обидві є дійсними):

psql "postgres://appuser:password@db.example.com:5432/analytics?sslmode=require"

Параметр ?sslmode=require забезпечує шифрування TLS для з’єднання — обов’язкова вимога для будь-якої бази даних, доступної за межами localhost. Якщо ви розміщуєте PostgreSQL на VPS або виділеному сервері, завжди поєднуйте рядки підключення з sslmode=require або sslmode=verify-full та дійсним SSL-сертифікатом.

Параметри URI підключення, на які варто звернути увагу:

Метод 5: Використання psql зі змінними середовища

Для повторних підключень до одного кластера встановіть змінні середовища, щоб уникнути повторного введення облікових даних:

export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=secret   # Use .pgpass file in production instead
export PGDATABASE=myapp_db

psql

У виробничому середовищі використовуйте файл .pgpass замість PGPASSWORD, щоб уникнути розкриття облікових даних в історії оболонки або списках процесів:

# ~/.pgpass format: hostname:port:database:username:password
localhost:5432:myapp_db:appuser:strongpassword

Встановіть правильні дозволи, інакше PostgreSQL ігноруватиме файл:

chmod 600 ~/.pgpass

Порівняння: методи переключення баз даних

Ефективне керування кількома підключеннями до баз даних

Використання pgAdmin для навігації через GUI

pgAdmin відображає всі бази даних під кожним зареєстрованим сервером у дереві об’єктів ліворуч. Натискання на базу даних і відкриття інструменту запитів автоматично обмежує всі запити цією базою даних. Це корисно для дослідницької роботи, але не підходить для автоматизації.

Підводний камінь: pgAdmin підтримує окремі слоти з’єднань для кожної бази даних. Якщо ваш сервер PostgreSQL має низьке значення max_connections (за замовчуванням 100), відкриття багатьох баз даних у pgAdmin може вичерпати пул з’єднань ще до того, як ваш застосунок навіть запуститься.

Використання PgBouncer для пулінгу з’єднань

У виробничих середовищах із частим переключенням баз даних пулер з’єднань, такий як PgBouncer, значно зменшує накладні витрати. PgBouncer працює в трьох режимах:

• Режим сеансу: Одне серверне з’єднання на клієнтський сеанс. Функціонально еквівалентний прямим з’єднанням.
• Режим транзакцій: Серверне з’єднання утримується лише під час транзакції. Найефективніший для навантажень OLTP.
• Режим операторів: З’єднання повертається після кожного оператора. Несумісний з багатооператорними транзакціями.

При запуску кількох баз даних застосунку на одному екземплярі PostgreSQL — поширений шаблон на VPS-хостингу або VPS з cPanel — PgBouncer у режимі транзакцій може зменшити кількість активних серверних процесів на порядок.

Міжбазові запити з dblink та postgres_fdw

Оскільки сеанси обмежені базою даних, для запиту даних між базами даних потрібне розширення. PostgreSQL надає два варіанти:

dblink — старіший, процедурний підхід:

SELECT * FROM dblink(
    'dbname=analytics user=analyst host=localhost',
    'SELECT user_id, event_count FROM events WHERE date = current_date'
) AS remote(user_id INT, event_count BIGINT);

postgres_fdw — сучасна, сумісна зі стандартами обгортка зовнішніх даних:

CREATE EXTENSION postgres_fdw;

CREATE SERVER analytics_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', dbname 'analytics', port '5432');

CREATE USER MAPPING FOR appuser
    SERVER analytics_server
    OPTIONS (user 'analyst', password 'secret');

CREATE FOREIGN TABLE remote_events (
    user_id INT,
    event_count BIGINT
)
SERVER analytics_server
OPTIONS (schema_name 'public', table_name 'events');

Після налаштування remote_events поводиться як локальна таблиця. postgres_fdw підтримує проштовхування предикатів, тобто умови WHERE виконуються на віддаленому сервері, а не локально — критично важлива відмінність у продуктивності для великих наборів даних.

Системні бази даних: що не можна чіпати

PostgreSQL постачається з чотирма базами даних у кожному новому кластері:

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

template0 існує як незайманий запасний варіант. Це єдиний шаблон, який можна використовувати при відновленні архіву pg_dump з іншим кодуванням або локаллю, оскільки він не має створених користувачем об’єктів, які могли б конфліктувати.

Привілеї, pg_hba.conf та збої підключення

Поширеним джерелом плутанини при переключенні баз даних є відмінність між привілеями на рівні ролей PostgreSQL та правилами автентифікації pg_hba.conf. Обидва повинні незалежно дозволяти з’єднання.

Перевірка на рівні ролі: Роль повинна мати привілей CONNECT для цільової бази даних:

GRANT CONNECT ON DATABASE target_database TO appuser;

Перевірка pg_hba.conf: Файл автентифікації на основі хоста (/etc/postgresql/15/main/pg_hba.conf на Debian/Ubuntu) повинен мати відповідне правило для користувача, бази даних і адреси джерела. Типовий запис:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    myapp_db        appuser         10.0.0.0/8              scram-sha-256

Після редагування pg_hba.conf перезавантажте конфігурацію без перезапуску сервера:

sudo systemctl reload postgresql

Або зсередини psql:

SELECT pg_reload_conf();

Поширений шаблон збою: Користувач має привілей CONNECT на рівні SQL, але pg_hba.conf не має відповідного правила. Повідомлення про помилку (FATAL: no pg_hba.conf entry for host) є явним, але розробники часто повністю ігнорують файл, оскільки очікують, що дозволи бази даних керуються виключно через SQL.

Практична матриця прийняття рішень

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

• Інтерактивне дослідження на локальній машині розробника: Використовуйте c dbname всередині psql. Швидко, без нового процесу.
• Shell-скрипт, що перебирає кілька баз даних: Використовуйте psql -U postgres -d $dbname -c "..." у циклі з -t -A для чистого виводу.
• Застосунок, що підключається до однієї бази даних: Використовуйте URI підключення з sslmode=require та пулом з’єднань (PgBouncer або вбудований пулінг драйвера).
• Застосунок, якому потрібні дані з двох баз даних: Реалізуйте postgres_fdw на основній базі даних замість керування двома окремими пулами з’єднань у коді застосунку.
• Перевірка RLS або ізоляції привілеїв: Використовуйте c dbname role_name для імітації цільової ролі без виходу з psql.
• Автоматизоване забезпечення / інфраструктура як код: Використовуйте змінні середовища або .pgpass з обліковим записом служби; ніколи не вбудовуйте облікові дані в скрипти.
• Виробниче навантаження з високою конкурентністю: Розгорніть PgBouncer у режимі транзакцій між застосунком і PostgreSQL. На виділеному сервері налаштуйте max_connections у postgresql.conf відповідно до ємності пам’яті вашого обладнання (кожен серверний процес використовує приблизно 5–10 MB RAM).
• Багатоорендний SaaS з базами даних для кожного орендаря: Розгляньте багатоорендність на основі схем у межах однієї бази даних замість баз даних для кожного орендаря, щоб уникнути фрагментації пулу з’єднань і спростити стратегії резервного копіювання.

Для команд, які запускають PostgreSQL разом із веб-застосунками, поєднання сервера бази даних із належно налаштованим середовищем спільного хостингу або VPS та зареєстрованим доменом для рівня застосунку завершує стандартний виробничий стек.

FAQ

Чи можна переключити бази даних без закриття сеансу psql?

Так. Використовуйте мета-команду c target_database всередині psql. Вона закриває поточне серверне з’єднання та відкриває нове до вказаної бази даних — все в межах одного термінального сеансу. За бажанням можна вказати іншого користувача, хост і порт в одній команді.

Чому PostgreSQL не має команди USE як MySQL?

Архітектура PostgreSQL прив’язує серверний процес до однієї бази даних під час запуску. Системний каталог бази даних завантажується у спільну пам’ять для цього процесу, і перемикання каталогів у середині сеансу архітектурно еквівалентне запуску нового процесу. Команда c у psql є практичним еквівалентом — вона просто робить перезапуск процесу прозорим для користувача.

Як запитувати дані з двох різних баз даних PostgreSQL одночасно?

Використовуйте розширення postgres_fdw для створення зовнішнього сервера та зовнішніх таблиць, що відображають віддалену базу даних. Після налаштування можна об’єднувати локальні та віддалені таблиці за допомогою JOIN в одному запиті. Для разових запитів dblink є простішим, але менш продуктивним і складнішим у підтримці.

Що станеться, якщо я підключуся до template1 і змінюватиму її?

Будь-які об’єкти, які ви створюєте в template1 — таблиці, розширення, схеми — будуть клоновані в кожну нову базу даних, створену за допомогою CREATE DATABASE (якщо явно не вказано TEMPLATE template0). Іноді це є навмисним (наприклад, попереднє встановлення uuid-ossp або pgcrypto), але випадкові зміни можуть пошкодити всі бази даних, створені згодом.

Як дізнатися, до якої бази даних підключений поточний сеанс psql?

Виконайте наступне всередині psql:

SELECT current_database();

Або перевірте сам рядок запрошення psql — за замовчуванням він відображає dbname=# (суперкористувач) або dbname=> (звичайний користувач), де dbname — активна база даних.