Установка и настройка сервера PostgreSQL под Linux Ubuntu

Тестировалось и работает на Ubuntu Linux (10.04 - 16.04 x86,  x86_64) и PostgreSQL 8.4 - 9.4, но оно также должно быть применимо для более старых версий (Ubuntu и PostgreSQL) и других Debian-based дистрибутивов.
Для базовой установки в командной строке введите следующие команды (или найдите перечисленные пакеты в синаптике, если вы предпочитаете работать с ним):

Оговорюсь сразу, что статья написана под 8.4. У кого более поздние версии, вам придется немного подправить пути.

sudo apt-get install postgresql postgresql-client postgresql-contrib

Если Вы устанавливаете на локальный компьютер или на сервер, где установлены иксы, то для администрирования можно установить Pgadmin:

sudo apt-get install pgadmin3

Эти команды установят сервер и клиент БД, некоторые дополнительные скрипты и графическую утилиту pgAdmin для работы с БД.
Теперь нам необходимо переустановить пароль админского аккаунта ‘postgres’ сервера, чтобы мы могли использовать его для задач системного администрирования. Введите в командной строке (подставьте вместо password пароль, который вы хотите использовать):

sudo su postgres -c psql postgres postgres=# ALTER USER postgres WITH PASSWORD 'сюда_записываем_нужный_вам_пароль'; postgres=# \q

Эти команды изменяют пароль внутри БД, теперь нам необходимо сделать тоже самое с unix пользователем ‘postgres’, замечу что пароли должны быть одинаковы:

sudo passwd -d postgres

Снимаем сроки паролей и меняем пароль, как я и говорил на идентичный, что писали в базу:

sudo su postgres  -c passwd

Введите тот же пароль, который вы использовали в прошлый раз.
Теперь мы можем использовать и программу pgAdmin, и консольный клиент (от пользователя postgres), чтобы работать с сервером БД. Но, перед запуском pgAdmin, мы должны установить админский пакет PostgreSQL, который включит лучшее логгирование и мониторинг в pgAdmin. Запустите следующую команду:

sudo postgres -c psql /usr/share/postgresql/8.4/contrib/adminpack.sql *

Наконец, нам необходимо настроить сервер для работы в сети, иначе он будет доступен только с локальной машины. Чтобы сделать это, сначала необходимо отредактировать в консоли postgresql.conf:

sudo nano /etc/postgresql/8.4/main/postgresql.conf

Меняем

listen_addresses = 'localhost'

на:

listen_addresses = '*'

И снимаем комментарий с

port = 5432

Сохраните файл нажав Ctrl +X .
Последний шаг -- мы должны определить, кто должен иметь доступ к серверу. Это настраивается с помощью файла pg_hba.conf file. (Этот шаг можно также сделать с помощью последних версий pgAdmin (1.6.x), плюс вам даже нет необходимости вычислять IP адреса и маски подсетей. Но это не исключает необходимости знать, как это работает.)

sudo nano /etc/postgresql/8.4/main/pg_hba.conf

Пример рабочего конфига:

# PostgreSQL Client Authentication Configuration File # =================================================== local all postgres ident sameuser # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local                    all                    all               ident           sameuser # IPv4 local connections: host                     all                   all               127.0.0.1/32 md5 host                     all                   all               0.0.0.0/0    md5 # IPv6 local connections: host                     all                   all                ::1/128      md5

Всё, теперь перезапустите сервер:

sudo /etc/init.d/postgresql-8.4 restart

Установка словарей PostgreSQL для русского полнотекстового поиска.

Словари можно скачать отсюда http://wiki.services.openoffice.org/wiki/Dictionaries#Russian_.28Russia.29
Скачиваете файл Ru_ru.zip, разархивируете и закидываете к себе файлы ru_RU.dic и ru_RU.aff
Словари в кодировке koi8 для работы с postgresql надо преобразовать в utf-8
Для этого на хосте выполняете команду

iconv -f koi8-r -t utf-8 < ru_RU.aff > /usr/share/postgresql/8.4/tsearch_data/russian.affix iconv -f koi8-r -t utf-8 < ru_RU.dic > /usr/share/postgresql/8.4/tsearch_data/russian.dict

iconv -f koi8-r -t utf-8 < en_US.aff > /usr/share/postgresql/8.4/tsearch_data/english.affix iconv -f koi8-r -t utf-8 < en_US.dic > /usr/share/postgresql/8.4/tsearch_data/english.dict

Эта команда преобразовывает кодировку словарей, переименовывает их и копирует в нужную папку. Пути зависят от установки. Найдите у себя папку tsearch_data , которая содержит словари, ее и пропишите.

Теперь создаем полнотекстовую конфигурацию для работы с русским (ну и английским) Посылаете запросы к postgresql

CREATE TEXT SEARCH DICTIONARY russian_ispell ( TEMPLATE = ispell, DictFile = russian, AffFile = russian, StopWords = russian ); CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian ); CREATE TEXT SEARCH DICTIONARY english_ispell ( TEMPLATE = ispell, DictFile = english, AffFile = english, StopWords = english ); CREATE TEXT SEARCH CONFIGURATION en ( COPY = russian ); ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR hword, hword_part, word WITH russian_ispell, russian_stem; ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR asciihword, asciiword, hword_asciipart WITH english_ispell, english_stem;

Чтобы эта полнотекстовая конфигурация работала по умолчанию прописываете в postgresql.conf

default_text_search_config = 'ru'

Ну или можете в каждой сессии посылать команду
SET default_text_search_config = 'ru';

Проверяем работу

select to_tsvector('ru', 'мама мыла раму'); to_tsvector ------------------------------------- 'мама':1 'мыло':2 'мыть':2 'рама':3 (1 запись)

Полнотекстовая конфигурация работает.
Если вы хотите использовать данный сервер в связке apache2 + php5 + postgresql, то вам необходимо установить следующие пакеты:

sudo apt-get install apache2 libapache2-mod-php5 php5 php5-common php5-gd php5-pgsql

Прошло немного времени и понадобился скриптик который будет делать бекапы баз. И так создаем каталог для бекапов:

mkdir /home/backup

Создаем скрипт с таким содержимым:

#!/bin/bash logfile="/home/backup/pgsql.log" backup_dir="/home/backup" touch $logfile databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -$ echo "Starting backup of databases " >> $logfile for i in $databases; do dateinfo=`date '+%Y-%m-%d %H:%M:%S'` timeslot=`date '+%Y%m%d%H%M'` /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1 /usr/bin/pg_dump -U postgres -i -F c -b $i -h 127.0.0.1 -f $backup_dir/$i-datab$ echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile done echo "Done backup of databases " >> $logfile tail -15 /home/backup/pgsql.log | mailx admin@youdomain.ru

Вешаем на него права:

chmod +x backup.sh

Запускать скрипт можно ручную, а кому лениво можно кроном, например каждый понедельник в 3 часа ночи:

00 3 * * 1 root /путь_до_скрипта/backup.sh

Дамп базы base1 в файл архива dump.tar.gz

pg_dump -h localhost -U base1 -F c -f dump.tar.gz base1

Загрузка архива дампа dump.tar.gz в базу vhsup

pg_restore -h localhost -U base1 -F c -d base1 dump.tar.gz

Или:

psql -d base1 -f /share/BackUPs/PostgreSQL/base1.sql

Если вам необходимо увеличить количество одновременных соединений к базе, то нужно переопределить лимиты ядра, при том что выставляемые лимиты на прямую зависят от объема памяти. Допустим у вас 16GB оперативной памяти, введем в терминале команду:

sysctl -w kernel.shmmax=17179869184 sysctl -w kernel.shmall=4194304

Для 8GB:

sysctl -w kernel.shmmax=8589934592 sysctl -w kernel.shmall=4194304

Настройки лучше сохранить в /etc/sysctl.conf для их применения после перезагрузки:

kernel.shmmax=17179869184 kernel.shmall=4194304

В древних дистрибутивах может не оказаться программы sysctl, тогда внесите изменения:

echo 17179869184 >/proc/sys/kernel/shmmax echo 4194304 >/proc/sys/kernel/shmall

Для оптимизации PostgreSQL существует утилита pgtune. Её можно установить и собрать оптимальный файл конфигурации, и если он не противоричит вашим моральным принципам, можете им заменить ваш рабочий файл конфигурации.

sudo apt-get install pgtune sudo pgtune -i /etc/postgresql/8.4/main/postgresql.conf -o /etc/postgresql/8.4/main/postgresql.conf.pgtune