Bsdadmin.ru

Записки администратора FreeBSD

Путь на сайте

Домашняя Базы данных PostgreSQL.Конфигурация

PostgreSQL.Конфигурация

Базовая конфигурация
Конфигурация PostgreSQL основана на текстовых параметрах ключ-значение (key-value). По умолчанию,параметры хранятся в файле postgresql.conf, но к ним можно обратиться изнутри СУБД. Каждый параметр привязан к специальному контексту, который определяет, когда кластер применит измененный
параметр:
• postmaster: кластер должен быть перезапущен;
• sighup: кластер должен получить сигнал SIGHUP;
• backend: применяется ко всем новым
подключениям от клиентов;
• [super]user: применить сразу, если инициировано
[супер]пользователем.
Параметры могут быть проверены и изменены (за исключением контекста postmaster) в текущем соединении к базе при помощи команд SHOW, SET,либо выполнив запрос к каталогу pg_settings, в котором можно увидеть к какому контексту принадлежит каждый параметр и в каком конфигурационном файле он прописан (см. листинг 5 в качестве примера). Читать далее
Минимальный набор конфигурационных параметров, на которые следует обратить внимание перед стартом PostgreSQL, приведены в таблице.
2. Кроме них, нужно обратить еще внимание и на контрольный список пользователей в HBA (файл pg_hba.conf), которым разрешается доступ к базе.
 
Создаем новую базу данных
В качестве примера для этой статьи мы создадим простую базу под названием bsddb и суперпользователя bsdmagic. PostgreSQL позволяет создавать (и уничтожать) базы и пользователей как из самой СУБД (т.е. когда вы подключены к template1), так же и из командной строки. В листинге 6 показано,как создать суперпользователя и базу из командной
строки, а в листинге 7 – тоже самое, но уже из самой СУБД.
 
Л истинг 6. Создаем базу из shell-окружения
~> createuser -P bsdmagic
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
~> createdb -O bsdmagic bsddb
 
Листинг 7. Создаем базу изнутри самой СУБД
template1=# CREATE USER bsdmagic WITH SUPERUSER LOGIN ENCRYPTED PASSWORD ‘bsdmagic’;
CREATE ROLE
template1=# CREATE DATABASE bsddb WITH OWNER bsdmagic;
CREATE DATABASE
 
Листинг 8. Правила для HBA (pg_hba.conf)
# TYPE DATABASE USER ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all pgsql trust
host bsddb bsdmagic 192.168.200.0/24 md5
 
Когда база и её пользователи готовы, то самое время проверить правила подключения, содержащиеся в файле pg_hba.conf. Эти правила определяют,какой хост имеет право подключаться к кластеру, а также разрешения для конкретных пользователей подключаться к конкретным базам. Рассмотрение правил синтаксиса этого файла находится вне рамок статьи, но отмечу, что в листинге 8 определяется доступ к базе bsddb только для пользователя bsdmagic, находящегося в сети 192.168.200.0/24.
Опция md5 определяет, что пользователь должен будет ввести пароль, в то время как ключевое слово trust разрешает пользователю подключаться к базе без авторизации по паролю. Поэтому, даже если пользователю bsdmagic разрешается подключаться к базе bsddb из локальной сети и при этом также запрашивается его пароль, то для пользователя psql работают совершенно другие правила – он может соединяться к любой базе без запроса пароля, но используя локальные сокеты. 
Пора подключиться к вышеупомянутой базе bsddb и заполнить её хотя бы одной таблицей с несколькими кортежами – в листинге 9 приводится  пример создания таблицы, в которой будут храниться названия журнальных выпусков.
Листинг 9. Заполняем базу
~> psql bsdmagic -U bsdmagic -h 192.168.200.2
bsddb=# CREATE TABLE magazine(
pk serial,
id text,
month int,
issuedon date,
title text,
PRIMARY KEY(pk),
UNIQUE(id)
);
NOTICE: CREATE TABLE will create implicit sequence “magazine_pk_seq” for serial column “magazine.pk”
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “magazine_pkey” for table “magazine”
NOTICE: CREATE TABLE / UNIQUE will create implicit index “magazine_id_key” for table “magazine”
CREATE TABLE
bsddb=# INSERT INTO magazine(id, month, issuedon, title)
VALUES(‘2012-01’, 1, ‘01/01/2012’::date, ‘FreeBSD: Get Up To Date’);
INSERT 0 1
bsddb=# INSERT INTO magazine(id, month, issuedon, title)
VALUES(‘2011-12’, 12, ‘01/12/2011’::date, ‘Rolling Your Own Kernel’);
INSERT 0 1
bsddb=# INSERT INTO magazine(id, month, issuedon, title)
VALUES(‘2011-11’, 11, ‘01/11/2011’::date, ‘Speed Daemons’);
INSERT 0 1
 
Обратите внимание на то, что команда psql будет запрашивать пользовательский пароль при каждом подключении,но данную процедуру можно обойти создав файл ~/.pgpass (с правами на чтение только владельцу) и вписав туда строчку в формате:server-address:port:dbname:username:password 
что в нашем случае будет выглядеть как 192.168.200.2:5432:bsddb:bsdmagic:mypassword
Поэтому, прежде, чем запросить у пользователя пароль, команда psql будет искать соответствие в файле ~/.pgpass, и, найдя нужное, использует его.
Такой механизм особенно удобен для организации скриптов, что запускаются через cron(8) или periodic(8).
 
Путь к данным
Как мы сказали ранее, PostgreSQL хранит объекты базы данных, включая таблицы (и их кортежи) в файлах в директории PGDATA (в поддиректории base, если не было задано отдельного табличного пространства). Такие файлы именуются по номерам OID, которые были присвоены объекту при его создании, несмотря на то, что некоторые обслуживающие команды могут игнорировать такое правило. Системный администратор может выяснить, какой файл соответствует конкретному объекту базы и наоборот, с помощью команды oid2name, находящейся в модуле contrib. Либо же такое это можно узнать с помощью системного каталога.
В листинге 10 показывается как команда oid-2name может быть применена для показа всех OID используемой базы (если запускается без аргументов). Кроме того можно выяснить какой OID у определенной таблицы в какой-либо конкретной базе. В примере из листинга 10 oid2name получает номер 16387 в качестве OID для базы bsddb и 16390 в качестве номера для таблицы magazine. Это означает, что в директории PGDATA/base должна быть директория под названием 16386 (это OID номер для bsddb), в которой, помимо других файлов, должен быть файл с названием 16390. В нем-то и хранятся кортежи таблицы magazine. Как вы видите, команда ls(1) показывает размер этого файла в 8Кб, несмотря на то, что хранится всего лишь пара значений. Это  размер страницы PostgreSQL по умолчанию и по этой 8Кб границе будет происходить выравнивание данных. Чтобы посмотреть, как изменяется размер файла, давайте сгенерируем несколько тысяч пустых кортежей с помощью специальной функции generate_series() как показано в Листинге 11. Размер файла на диске станет около 5.4 Мб. Считая, что размер каждой страницы 8Кб, то таблица содержит 5652480 / ( 8 * 1024 ) = 690 страниц что и подтверждается при запуске запроса к системному каталогу в листинге 11.
 
Листинг 10. Информация по файлам СУБД
~> oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
16387 bsddb pg_default
11912 postgres pg_default
11904 template0 pg_default
1 template1 pg_default
~> oid2name -H 192.168.200.2 -d bsddb -U bsdmagic -t magazine
From database “bsddb”:
Filenode Table Name
----------------------
16390 magazine
~> ls -l /postgresql/cluster1/base/16387/16390
-rw------- 1 pgsql pgsql 8192 Jan 19 14:26 /postgresql/cluster1/base/16387/16390
Листинг 11. Увеличиваем размер таблицы и выясняем размер, используемый на диске
bsddb=# INSERT INTO magazine(id, month,issuedon, title)
bsddb-# VALUES( generate_series(1,100000), 0, ‘01/01/2009’::date, ‘TEST’);
INSERT 0 100000
bsddb=# SELECT relname,relfilenode,relpages,reltuples
bsddb-# FROM pg_class WHERE relname = ‘magazine’;
relname | relfilenode | relpages | reltuples
----------+-------------+----------+-----------
magazine | 16390 | 690 | 100003
~> ls -l /postgresql/cluster1/base/16387/16390
-rw------- 1 pgsql pgsql 5652480 Jan 19 15:44 /postgresql/cluster1/base/16387/16390
Листинг 12. Выгружаем архивную копию базы в обычный текстовый файл
с помощью pg_dump
~> pg_dump -h 192.168.200.2 -U bsdmagic -f bsdmagic.backup.sql bsddb