Изброяване и превключване на бази данни в 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: backend процесът (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: Излизане и повторно свързване от Shell
В 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 или dedicated сървър, винаги комбинирайте низовете за връзка с 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, за да избегнете излагането на идентификационни данни в историята на shell или списъците с процеси:

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

Задайте правилно разрешенията, иначе PostgreSQL ще игнорира файла:

chmod 600 ~/.pgpass

Сравнение: Методи за превключване на бази данни

Ефективно управление на множество връзки към бази данни

Използване на pgAdmin за навигация чрез GUI

pgAdmin изброява всички бази данни под всеки регистриран сървър в дървото с обекти вляво. Кликването върху база данни и отварянето на Query Tool автоматично ограничава всички заявки до тази база данни. Това е полезно за изследователска работа, но не е подходящо за автоматизация.

Клопка: pgAdmin поддържа отделни слотове за връзка за всяка база данни. Ако вашият PostgreSQL сървър има ниска настройка max_connections (по подразбиране е 100), отварянето на много бази данни в pgAdmin може да изчерпи пула за връзки преди дори вашето приложение да стартира.

Използване на PgBouncer за пулиране на връзки

В производствени среди с често превключване на бази данни, пулер за връзки като PgBouncer драстично намалява натоварването. PgBouncer работи в три режима:

• Режим на сесия: Една сървърна връзка на клиентска сесия. Функционално еквивалентен на директни връзки.
• Режим на транзакция: Сървърната връзка се задържа само по време на транзакция. Най-ефективен за OLTP натоварвания.
• Режим на оператор: Връзката се освобождава след всеки оператор. Несъвместим с многооператорни транзакции.

При изпълнение на множество бази данни на приложения върху един PostgreSQL екземпляр — често срещан модел при VPS хостинг или VPS с cPanel — PgBouncer в режим на транзакция може да намали активните backend процеси с порядък.

Заявки между бази данни с 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 — модерен, съвместим със стандартите Foreign Data Wrapper:

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. На dedicated сървър, настройте max_connections в postgresql.conf спрямо капацитета на паметта на вашия хардуер (всеки backend използва приблизително 5–10 MB RAM).
• Многонаемателски SaaS с бази данни за всеки наемател: Помислете за многонаемателство, базирано на схеми, в рамките на единична база данни вместо бази данни за всеки наемател, за да избегнете фрагментацията на пула за връзки и да опростите стратегиите за архивиране.

За екипи, изпълняващи PostgreSQL заедно с уеб приложения, комбинирането на сървъра за бази данни с правилно конфигурирана среда за споделен хостинг или VPS и регистриран домейн за слоя на приложението завършва стандартния производствен стек.

ЧЗВ

Мога ли да превключвам бази данни без затваряне на psql сесията си?

Да. Използвайте мета-командата c target_database в psql. Тя затваря текущата backend връзка и отваря нова към посочената база данни, всичко в рамките на същата терминална сесия. По желание можете да укажете различен потребител, хост и порт в същата команда.

Защо PostgreSQL няма команда USE като MySQL?

Архитектурата на PostgreSQL обвързва backend процес с единична база данни при стартиране. Системният каталог на базата данни се зарежда в споделената памет за този процес и превключването на каталози по средата на сесията е архитектурно еквивалентно на стартиране на нов процес. Командата 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 е активната база данни.