Базы данных и их управление
В этом разделе мы рассмотрим, как организовано хранение данных в PostgreSQL на примере СУБД Pangolin: что такое кластер баз данных, какие базы данных создаются по умолчанию, как создавать и удалять базы данных, работать с шаблонами и локализацией, а также получать информацию о размере данных.
В данном разделе для удобства восприятия некоторые выводы команд представлены в переводе на русский язык. В реальной системе вы увидите англоязычные сообщения.
Кластер баз данных
Один экземпляр СУБД обслуживает сразу несколько баз данных. Все базы данных, обслуживаемые экземпляром, называют кластером баз данных.
Кластер создается командой initdb, которая создает сразу три базы данных: template1, template0 и postgres. Процедура создания баз данных определена в Backend Interface (BKI).
Помимо исходных баз данных initdb создает необходимые для работы СУБД служебные файлы и каталоги, а также файлы конфигурации. Новые базы данных создаются путем копирования существующих.
Подробно о initdb смотрите в Справке.
Создание кластера БД
Рассмотрим процесс создания кластера на примере:
[postgres@ServerName ~]$ initdb -k
Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres". От его имени также будет запускаться процесс сервера.
Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8". Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8". Выбрана конфигурация текстового поиска по умолчанию "russian".
Контроль целостности страниц данных включен.
исправление прав для существующего каталога /pgdata/06/data... ок
создание подкаталогов... ок
выбирается реализация динамической разделяемой памяти... posix
В примере опция -k включила контроль целостности данных на страницах.
Команда initdb не будет работать, если ее запускает суперпользователь ОС root. Пользователь ОС, создающий кластер, становится исходным суперпользователем СУБД.
Каталог данных определяется либо опцией -D, либо установкой переменной окружения PGDATA. Права на каталог данных обычно устанавливают с правами на чтение и запись только для выполнившего initdb пользователя. Например:
[postgres@ServerName ~]$ ls -ld $PGDATA
drwx------ 24 postgres postgres 4096 окт 11 08:43 /pgdata/06/data
У initdb имеются опции -g и --allow-group-access, предоставляющие доступ на чтение к каталогу с данными при инициализации кластера. Это бывает необходимо для задач резервного копирования.
Опция -k необходима для включения проверки контрольных сумм на страницах данных. Также имеются опции для установки настроек локализации, если необходимо, чтобы они отличались от настроек ОС.
Базы данных
Исходные базы данных
После создания кластера с настройками по умолчанию к нему можно подключиться суперпользователем postgres. Посмотрим список баз данных с помощью метакоманды \l:
postgres@postgres=# \l
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | локаль ICU | Провайдер локали | Права доступа
-----------+----------+-----------+------------+-----------+------------+------------------+-----------------------
| postgres | postgres | UTF8 | en_US.UTF8 | en_US.UTF8| | libc |
| template0| postgres | UTF8 | en_US.UTF8 | en_US.UTF8| | libc | =c/postgres postgres=CTc/postgres
| template1| postgres | UTF8 | en_US.UTF8 | en_US.UTF8| | libc | =c/postgres postgres=CTc/postgres
(3 rows)
При создании кластера командой initdb создаются три исходные базы данных:
- postgres — эта база данных нужна для подключений суперпользователя
postgres, в ней обычно не хранят какие-либо бизнес-данные; - template1 — шаблонная база данных, необходимая для создания других баз данных, ее содержимое можно изменять;
- template0 — шаблонная база данных, содержимое которой НЕ следует изменять.
База данных template0 также является шаблоном, но не предназначена для подключения и ее не следует изменять. Базу данных template0 можно использовать для восстановления template1 при ее порче, а также она используется при восстановлении из резервных копий и при необходимости создания новой базы данных с измененной кодировкой по сравнению с кодировкой, использованной при создании кластера баз данных.
Создание и удаление базы данных
Команды SQL CREATE DATABASE и DROP DATABASE, соответственно, предназначены для создания и удаления баз данных. При создании базы данных создаются необходимые структуры в файловой системе, а в метаданных записываются сведения о новой базе данных.
Создадим новую базу данных db1:
postgres@postgres=# CREATE DATABASE db1;
CREATE DATABASE
postgres@postgres=# \l db1
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | локаль ICU | Провайдер локали | Права доступа
-----+----------+-----------+------------+------------+------------+------------------+---------------
db1 | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc |
(1 row)
Все базы данных создаются с помощью копирования содержимого существующих баз данных.
По умолчанию команда CREATE DATABASE использует шаблон template1 для копирования его содержимого во вновь создаваемую базу данных, однако это может быть изменено. Например, при создании базы данных с кодировкой, отличающейся от кодировки кластера, необходимо будет использовать шаблон template0.
Создавать базы данных имеют право суперпользователи и пользователи, имеющие атрибут CREATEDB.
Удалим базу данных:
postgres@postgres=# DROP DATABASE db1;
DROP DATABASE
postgres@postgres=# \l db1
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | локаль ICU | Провайдер локали | Права доступа
-----+----------+-----------+------------+----------+------------+------------------+---------------
(0 rows)
Удалить базу данных может суперпользователь или ее владелец (исходно — пользователь, который ее создавал). Удалить базу, к которой есть подключения, нельзя. Операция удаления невозвратная, все данные будут удалены.
Для создания и удаления баз данных можно использовать утилиты командной строки ОС createdb и dropdb.
Метаданные о базах данных
Информация о базах данных хранится в таблице глобального каталога pg_database. Посмотрим ее содержимое:
postgres@postgres=# SELECT datname, datistemplate, datallowconn, datconnlimit, datacl FROM pg_database;
datname | datistemplate | datallowconn | datconnlimit | datacl
-----------+---------------+--------------+--------------+-------------------------------------
postgres | f | t | -1 |
template1 | t | t | -1 | {=c/postgres, postgres=CTc/postgres}
template0 | t | f | -1 | {=c/postgres, postgres=CTc/postgres}
(3 rows)
В примере выше видно, что имеются три исходные базы данных. Предназначена ли база данных для использования в качестве шаблона, показывает поле datistemplate. На самом деле можно использовать любую существующую базу данных в качестве шаблона, но поле datistemplate показывает исходное предназначение базы данных в качестве шаблона.
Для шаблона template0 значение поля datallowconn установлено в true для исключения подключения к этой базе данных. Это необходимо для запрета модификации содержимого этой шаблонной базы данных. Если поле datconnlimit содержит -1, значит, ограничений по количеству подключений к этой базе данных нет, иначе в поле содержится максимально разрешенное количество соединений.
В поле datacl в виде массива содержатся права доступа к базе данных.
Работа с шаблоном template1
Подключимся к шаблонной базе данных template1 и создадим в ней расширение pg_hint_plan, позволяющее использовать подсказки (hints) планировщику запросов:
postgres@postgres=# \c template1
You are now connected to database "template1" as user "postgres".
postgres@template1=# CREATE EXTENSION pg_hint_plan;
CREATE EXTENSION
Теперь создадим новую базу данных db:
postgres@template1=# CREATE DATABASE db;
CREATE DATABASE
postgres@template1=# \c db
You are now connected to database "db" as user "postgres".
Проверим, какие расширения установлены в новой базе данных:
postgres@db=# \dx
Список установленных расширений
Имя | Версия | Схема | Описание
--------------+---------+------------+------------------------------
pg_hint_plan | 1.5 | hint_plan |
plpgsql | 1.1 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Расширение, добавленное в template1, автоматически копировалось во все новые базы данных, созданные по умолчанию (через template1).
В результате этого в template1 будет создана схема hint_plan:
postgres@template1=# \dn
Список схем
Имя | Владелец
-----------+-------------------
hint_plan | postgres
public | pg_database_owner
(2 rows)
А также будут доступны объекты из этого расширения:
postgres@template1=# \dx+ pg_hint_plan
Объекты в расширении "pg_hint_plan"
Описание объекта
---------------------------------
sequence hint_plan.hints_id_seq
table hint_plan.hints
(2 rows)
В созданной базе данных db в примере появятся объекты, скопированные из template1, и объекты расширения pg_hint_plan.
Если объекты, скопированные из template1 не нужны в новых базах данных, удалите их из самого template1, в данном случае командами DROP SCHEMA и DROP EXTENSION.
Использование БД в качестве шаблона
В качестве шаблона можно использовать любую существующую базу данных. Создадим в базе db таблицу:
postgres@db=# CREATE TABLE t_db AS SELECT 'Таблица из db'::text;
SELECT 1
Создадим новую базу данных basa, используя db в качестве шаблона:
postgres@db=# CREATE DATABASE basa TEMPLATE db;
CREATE DATABASE
Подключимся к новой базе данных:
postgres@db=# \c basa
You are now connected to database "basa" as user "postgres".
Проверим, что таблица копировалась вместе с данными:
postgres@basa=# SELECT * FROM t_db;
text
---------------
Таблица из db
(1 row)
Создание БД с измененной локализацией
Попробуем создать базу данных с кодировкой koi8r и локалью ru_RU.koi8r без указания шаблона:
postgres@db=# CREATE DATABASE db_koi8r ENCODING = 'koi8r' LOCALE = 'ru_RU.koi8r';
ERROR: new encoding (KOI8R) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.
Теперь укажем шаблон template0:
postgres@db=# CREATE DATABASE db_koi8r ENCODING = 'koi8r' LOCALE = 'ru_RU.koi8r' TEMPLATE = template0;
CREATE DATABASE
Проверим результат:
postgres@db=# \l db*
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | локаль ICU | Провайдер локали | Права доступа
----------+----------+-----------+------------+-------------+------------+------------------+---------------
db | postgres | UTF8 | en_US.UTF8 | en_US.UTF8 | | libc |
db_koi8r | postgres | KOI8R | ru_RU.koi8r| ru_RU.koi8r | | libc |
(2 rows)
Кодировка определяет способ представления символов текста в БД, LC_COLLATE — правила сортировки, LC_CTYPE — классификация символов.
По умолчанию локализация для БД определяется при создании кластера утилитой initdb. При необходимости создать БД с измененными настройками локализации необходимо использовать шаблон template0.
Локализация определяет машинное представление символов в базе данных, например UTF8 или KOI8R, а также устанавливает правила сортировки и классификации символов. Более того, от настроек локализации зависят форматы представления дат, времени, денежных единиц, сообщений сервера и клиента и многое другое.
Локализация определяется с помощью набора специальных переменных, устанавливаемых в ОС, а также настроек СУБД.
О настройках локализации в ОС можно узнать в мануале, вызвав в командной строке ОС команду man 7 locale.
Изменение настроек баз данных
Если к БД нет текущих подключений, можно менять ее параметры, а также можно переименовать эту БД.
При необходимости можно изменять параметры баз данных с помощью команды ALTER DATABASE. Настройки, связанные с локализацией, после создания базы данных изменить уже невозможно. Но, например, ограничить максимальное количество подключений к базе данных вполне можно. Этой же командой можно изменить имя существующей базы данных.
Изменим ограничение на количество подключений к базе данных db:
postgres@db=# ALTER DATABASE db CONNECTION LIMIT 5;
ALTER DATABASE
Проверим изменение:
postgres@db=# SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db' \gx
-[ RECORD 1 ]+---
datname | db
datconnlimit | 5
Определение размера базы данных
Узнать, сколько места занимает база данных в файловой системе, позволяет функция pg_database_size():
Узнать, сколько места занимает база данных в файловой системе, позволяет функция pg_database_size(). Эта функция возвращает размер в байтах. Для получения размера в удобных единицах измерения используется функция pg_size_pretty():
postgres@db=# SELECT pg_size_pretty(pg_database_size('db'));
pg_size_pretty
----------------
9655 kB
(1 row)
Метакоманда \l+ psql также предоставляет информацию о размере баз данных:
postgres@db=# \x \l+ db \x
Расширенный вывод включен.
Список баз данных
-[ RECORD 1 ]------+-----------
Имя | db
Владелец | postgres
Кодировка | UTF8
LC_COLLATE | en_US.UTF8
LC_CTYPE | en_US.UTF8
локаль ICU |
Провайдер локали | libc
Права доступа |
Размер | 9655 kB
Табл. пространство | pg_default
Описание |