Установка сервера PostgreSQL в Ubuntu 12.10

Эта заметка, по следам установки сервера PostgreSQL 9.1 в Ubuntu 12.10 Gnome shell remix и на микросервере, под управлением Ubuntu Server 12.04.

Старая заметка по теме установки posgresql версии 8.4, находится по адресу: http://gimmor.blogspot.com/2010/11/postgresql-ubuntu.html 

Требуемые пакеты программ

Сам сервер базы данных версии 9.1:

root@microserver:# apt-get install postgresql

Пакет posgresql - это метапакет, для удобной установки.

Графическая утилита управления pgAdmin:

root@microserver:# apt-get install pgadmin3

После установки сервер postgresql сразу же запускается в системе.

Конфигурация сервера PostgreSQL

Конфигурационные файлы сервера PostgreSQL находятся в /etc/postgresql/9.1/main/

Главный конфигурационный файл сервера PostgreSQL - /etc/postgresql/9.1/main/postgresql.conf

Место хранения файлов баз данных

Базы данных хранятся в файлах, из местоположение в файловой иерархии системы задается опциями, в главном конфигурационном файле:

data_directory = '/var/lib/postgresql/9.1/main' 

Место по-умолчанию - опасное место, т.к. переустановка (upgrade) системы может уничтожить базу данных (отрицательный опыт), потери прямо пропорциональны потраченным усилиям.

Рекомендация - изменить. Желательно, - отдельный раздел, защищенный RAID и резервированный.

Первые шаги после установки

Начальные правки, в моем случае, обычно касаются включения возможности доступа к субд из локальной сети, за это отвечает опция listen_addresses.

listen_addresses = '*' не самый лучший вариант, поэтому я перечисляю интерфейсы, на которых будет доступен сервис posgresql.

listen_addresses = 'localhost, home'

Сервер PostgreSQL слушает на порту port = 5432 всех интерфейсов,перечисленных в listen_addresses. Может потребоваться правка правил файрвола, если нет доступа.

Также, есть приятная возможность, включить публикацию сервера посредством Avahi (Bonjour).

bonjour = on      # включить публикацию сервиса                          

bonjour_name = '' # по умолчанию имя сервиса=имя компьютера

Оказывается, текущая сборка не поддерживает Bonjour. Придется отключить.

Обычно, изменение конфигурационного файла, требует перезапуска сервера.

root@microserver:# service postgresql restart

Сервер добавляет в систему пользователя postgres.

Сервер создает базу данных по умолчанию postgres.

Для комфортной работы, надо создать своего пользователя и свою базу данных.

PSQL - командная утилита для управления сервером PostgreSQL 

psql - оболочка, имеющая интерактивный и пакетный режимы управления сервером Postgresql. Из основного, - позволяет настраивать пользователей СУБД,  создание новых баз данных, новых таблиц, выполнение SQL-запросов интерактивно и пакетно.

postgres@microserver:$ psql

Выход из интерактивного режима

postgres# \q

Создание собственной базы данных

Сервер баз данных PostgreSQL может хранить множество баз данных. Обычно база данных создается для какой-либо цели, проекта.

postgres@microserver:$ createdb oko

для управления бд запусить psql с опцией -d oko

postgres@micoserver:~$ psql -d oko

psql (9.1.6)

Type "help" for help.

oko=# 

Получена командная строка базы данных oko.

Создание нового пользователя сервера базы данных PostgreSQL

Создание пользователя (роли) базы данных PostgreSQL выполняется командой createuser.

postgres@microserver:$ createuser iam

Чтобы сразу создать пароль пользователю:

postgres@microserver:$ createuser -P iam

Новый пользователь iam в базе данных позволит использовать psql пользователем iam@microserver, т.е. будет соответствие между системными и пользователями БД.

iam@microserver:$ psql -d oko

Удаление пользователя сервера базы данных PostgreSQL

postgres@microserver:$ dropuser iam

Резервирование базы данных и восстановление

Полный образ базы данных создается командой pg_dump:
postgres@microserver:$ pg_dump oko > oko.dump

Восстановление из полного образа базы данных
postgres@microserver:$psql oko < oko.dump

Восстановление из полного образа базы данных возможно и на другой сервер.

Встроенное средство импорта текстовых файлов
В Postgresql присутствует команда COPY, которая позволяет взять CSV файл и загрузить его в таблицу базы данных. Таблица должна существовать, поля должны совпадать.
При использовании команды COPY можно столкнуться, с тем, что сервер выдает сообщение "отказано в доступе". Установка прав на чтение всем не помогает. Надо еще добавить бит исполнения к каталогу в котором находится файл. chmod a+rwx dir

Т.е. если сделать на сервере папку с полными правами для всех, то из неё можно будет читать файлы командой COPY и писать в неё.
Проблема также известка как "postgresql COPY file permission denied"

Пример команды COPY. Выполняется под пользователем, который имеется также в БД.

$ psql -d oko --command "COPY oko.table FROM '/home/csv/csvfile.csv' DELIMITER E'\t';"
К синтаксису надо относиться очень внимательно. Особенно при вызове из скриптов.

Папка csv доступна на чтение и запись всем:

$ ls /home/
drwxrwxrwx csv


PSQL минимальные навыки

\q - выйти из psql.
\h [SQL команда] - вывести справку по команде SQL.
\g [SQL запрос]; - выполнить SQL запрос, заметьте точку с запятой в конце, означающую конец запроса
Запрос можно выполнять прямо в приглашении базы данных:
oko# SELECT version();
Нажмите q, чтобы вернуться в приглашение.
Запрос может быть не только на выборку (SELECT) но и на создание, удаление, манипуляции с таблицами, базами данных, внутренними таблицами.


Например, выполнение SQL запроса из скрипта или командной строки:
iam@microserver$ echo "\x \\\ SELECT COUNT(*) FROM oko.mytable;" | psql -d oko
Пользователь iam должен быть пользователем базы данных. Схема базы данных: oko, таблица oko.mytable, база данных oko. Обратите внимание, на 3 обратных слеша, которые экранируются. Используется стандартный ввод psql.

Можно и так, выполнить готовый sql-запрос:
iam@microserver$ psql -d oko --command "\i 'sqlscript.sql'"
а ответ просматривать в консоли интерактивно, либо использовать опцию --output='output.txt', чтобы вывести в ответ в файл. Файл будет создан на том компьютере, где запущен psql.
По умолчанию, psql выводит ответ, с использованием разметки "под таблицу", а чтобы получить сырые данные в виде CSV файла, надо добавить тройку опций: --tuples-only  --no-align --field-separator
Итого, вывод sql-запроса в csv-файл:
iam@microserver$ psql -d oko --command "\i 'sqlscript.sql'" --output='myoutput.txt' --tuples-only --no-align --field-separator=''

По умолчанию (без опций --no-align, --field-separator), разделителем выступает  ( | ), что для многих целей годиться.
Если не указать опцию, --no-align, вывод будет отформатирован визуально.

Табуляция в csv-файлах, хороша тем, что визуально разделяет поля, не очень часто встречается в строках, как например, запятая (в числах, либо символ перенаправления |, который по умолчанию).
В командной строке задается следующим образом: нажимается ctrl V, затем TAB. получиться пустое пространство, это и есть табуляция.

В интерактивном режиме задается так: \pset fieldsep '\t'

В скрипте bash (для команды psql), просто нажимается кнопка TAB.

Бинго.


Типы данных PostgreSQL

Типы данных, - основа возможностей PostgreSQL в части хранения и выборки данных.
PostgreSQL поддерживает стандартные типы данных SQL: int, smallint, real, double precision, char(N ), varchar(N ), date, time, timestamp, interval, собственные типы PostgreSQL, геометрические типы, определенные пользователем типы.

Скрипт создания структуры базы данных

При работе в консольном окружении, удобно написать скрипт создания структуры базы данных, где определить схему, таблицы, виды и пр.
Исполнив этот скрипт в любой базе данных, получим готовую структуру, которую можно заполнять данными.

Скрипт пишется на обычном SQL, правда на том, который понимает PostgreSQL.

Можно структру базы данных делать в графическом приложении pg_admin, а потом эскпортировать структуру в скрипт.

-- Так, начинаются комментарии
-- Тестовая БД, для сайта: gimmor.blogspot.com

CREATE SCHEMA oko;

CREATE TABLE oko.table (

 field1 integer,  -- поле 1
 field2 text,     -- поле 2
 field3 date,     -- поле 3
 field4 time      -- поле 4
);

Ресурсы

Manuals (англ.). http://www.postgresql.org/docs/manuals/