Как создать базу данных в PostgreSQL — CREATE DATABASE, схемы

В PostgreSQL база данных и схема — разные вещи. Разбираем иерархию объектов, как создать БД через psql и pgAdmin, зачем нужны схемы и как узнать размер базы.

Иерархия объектов: база, схема, таблица

PostgreSQL Instance (кластер)
├── База данных: postgres      (системная, для подключения по умолчанию)
├── База данных: template1     (шаблон для новых баз)
└── База данных: mydb          (ваша рабочая база)
    ├── Схема: public          (схема по умолчанию)
    │   ├── Таблица: users
    │   ├── Таблица: posts
    │   └── Таблица: comments
    └── Схема: analytics       (отдельное пространство имён)
        └── Таблица: events

База данных — полностью изолированное хранилище. Нельзя напрямую обращаться к таблице из другой базы. Схема — пространство имён внутри базы. Одна база может содержать несколько схем с одинаковыми именами таблиц без конфликтов.

Создать базу данных

-- Минимальный вариант (кодировка берётся из template1):
CREATE DATABASE mydb;

-- С полными параметрами:
CREATE DATABASE mydb
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'ru_RU.UTF-8'
  LC_CTYPE 'ru_RU.UTF-8'
  TEMPLATE template0;
-- TEMPLATE template0 нужен если меняете locale (несовместим с template1)

-- Только если не существует (PostgreSQL 9.5+):
-- В PostgreSQL нет CREATE DATABASE IF NOT EXISTS!
-- Используйте через psql или скрипт:
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb');

-- Из командной строки без входа в psql:
createdb -U postgres -O myuser -E UTF8 mydb

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

-- Список всех баз:
\l

-- Список с размерами через SQL:
SELECT datname,
  pg_size_pretty(pg_database_size(datname)) AS size,
  datcollate, datctype
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Подключиться к базе:
\c mydb

-- Переименовать базу (нельзя когда к ней есть подключения):
ALTER DATABASE mydb RENAME TO mydb_v2;

-- Удалить базу (нельзя если есть активные подключения!):
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;

-- Если есть подключения — сначала отключить всех:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid != pg_backend_pid();
-- Затем:
DROP DATABASE mydb;

Схемы — пространства имён

-- Создать схему:
CREATE SCHEMA analytics;
CREATE SCHEMA IF NOT EXISTS reports;

-- Создать схему для конкретного пользователя:
CREATE SCHEMA analytics AUTHORIZATION myuser;

-- Посмотреть все схемы:
\dn
-- Или через SQL:
SELECT schema_name, schema_owner
FROM information_schema.schemata;

-- Создать таблицу в конкретной схеме:
CREATE TABLE analytics.events (
  id SERIAL PRIMARY KEY,
  event_name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Обратиться к таблице в другой схеме:
SELECT * FROM analytics.events;

-- Удалить схему (только если пустая):
DROP SCHEMA analytics;
-- Со всеми объектами внутри:
DROP SCHEMA analytics CASCADE;

search_path — путь поиска схем

По умолчанию PostgreSQL ищет таблицы в схемах, перечисленных в search_path. Если не указать схему явно — таблица ищется сначала в схеме с именем текущего пользователя, потом в public:

-- Показать текущий путь поиска:
SHOW search_path;
-- "$user", public

-- Изменить для текущей сессии:
SET search_path = analytics, public;
-- Теперь SELECT * FROM events; найдёт analytics.events

-- Изменить для всей базы:
ALTER DATABASE mydb SET search_path = analytics, public;

-- Изменить для пользователя:
ALTER USER myuser SET search_path = analytics, public;

Размер базы данных и таблиц

-- Размер текущей БД:
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Размер конкретной БД:
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Все базы с размерами:
SELECT datname,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Размер таблицы без индексов:
SELECT pg_size_pretty(pg_relation_size('users'));

-- Размер таблицы с индексами и TOAST:
SELECT pg_size_pretty(pg_total_relation_size('users'));

-- Топ-10 таблиц по размеру:
SELECT tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total,
  pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_only,
  pg_size_pretty(pg_indexes_size(tablename::regclass)) AS indexes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC
LIMIT 10;

Очистить базу данных

-- Удалить все таблицы в схеме public (быстрее чем DELETE по таблицам):
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

-- Очистить конкретную таблицу (быстрее чем DELETE FROM):
TRUNCATE TABLE sessions;
TRUNCATE TABLE sessions RESTART IDENTITY;  -- + сброс счётчика SERIAL

Часто задаваемые вопросы

Можно ли переименовать базу данных в PostgreSQL?

Да, через ALTER DATABASE mydb RENAME TO new_name;. Ограничение: к базе не должно быть активных подключений во время переименования, включая ваше собственное. Нужно подключиться к другой базе (например postgres) и оттуда выполнить команду. Строки подключения в приложениях нужно обновить вручную.

Зачем нужны схемы в PostgreSQL?

Схемы решают несколько задач: 1) Организация — разделить таблицы по функциональным областям (public, analytics, audit). 2) Изоляция — несколько приложений могут работать в одной БД в разных схемах без конфликтов имён. 3) Права доступа — можно выдать пользователю права только на одну схему. 4) Multi-tenancy — каждый арендатор в отдельной схеме, одна база на всех.

Как скопировать базу данных PostgreSQL?

Через CREATE DATABASE с шаблоном: CREATE DATABASE mydb_copy TEMPLATE mydb; — скопирует структуру и данные. Ограничение: к исходной базе не должно быть активных подключений. Для копирования с подключениями — используйте pg_dump: pg_dump -U postgres mydb | psql -U postgres mydb_copy.