Записки программиста. Начало работы postgresql

Установка и первоначальная настройка PostgreSQL 10 на Debian 9

Буквально несколько дней назад разработчики PostgreSQL выпустили версию 10.0В новой версии добавлено много новшеств и сделано немало улучшений, более детально о них можно почитать в официальном пресс-релизе.

Я же опишу ниже как установить и быстро настроить PostgreSQL 10 на Debian 9

Исходные данные: Debian 9 Stretch (amd64)Задача: Установить PostgreSQL 10.x

1. Предварительная настройка сервера:

Добавляем на сервер русскую локаль, для начала проверяем её отсутствие/присутствие командой

locale -a | grep ru

если в ответ ничего нет, то запускаем

dpkg-reconfigure locales

выбираем в списке локаль ru_RU.UTF-8и жмем Yesвыбираем локаль по умолчанию en_US.UTF-8

2. Начинаем установку PostgreSQL 10:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - apt-get update apt-get install postgresql-10 -y

Краткая справка по местоположению основных файлов PostgreSQL 10:Местоположение баз данных:/var/lib/postgresql/10Местоположение логов:/var/log/postgresql/postgresql-10-main.logНастройка ротации логов:/etc/logrotate.d/postgresql-commonОсновные файлы конфигурации:/etc/postgresql/10/main/postgresql.conf/etc/postgresql/10/main/pg_hba.conf

Первым делом меняем пароль пользователя postgres:

su - postgres psql postgres=# \password postgres postgres=# \q

3. Тюнинг настроек PostgreSQL:

По умолчанию PostgreSQL принимает соединения только с локальных служб, т.к. слушает интерфейс localhost и это абсолютно правильно с точки зрения безопасности, но если Вы планируете подключения к серверу извне или из локальной сети, то Вам потребуется поменять параметр listen_addresses

Для PostgreSQL 10 открываем основной файл настроек

vi /etc/postgresql/10/main/postgresql.conf

и раскомментируем строку

listen_addresses = 'localhost'

исправим её на

listen_addresses = 'localhost,192.168.100.1'

таким образом мы укажем PostgreSQL слушать сетевые соединения на интерфейсе localhost и на нашем внутреннем интерфейсе локальной сети с IP адресом 192.168.100.1

Далее смотрим на параметр max_connections, который определяет максимальное количество одновременных соединений, которые будет обслуживать сервер PostgreSQL. В принципе, это число должно определяться исходя из требований к системе. Этот параметр в большей степени влияет на использование ресурсов. Если Вы только запустили БД, устанавливайте это значение небольшим (16…32), по умолчанию он установлен 100. Постепенно можно увеличивать max_connections (по мере необходимости — такой мерой будет получение ошибок от postgresql «too many clients»).Учтите! На поддержку каждого активного клиента, PostgreSQL тратит немалое количество ресурсов, и если Вам необходимо добиться производительности в несколько тысяч активных соединений, то стоит использовать менеджеры соединений, например: Pgpool или PgBouncer.Важно! Значение параметра max_wal_senders должно быть меньше max_connections, поэтому если Вы установили max_connections = 10, то max_wal_senders нужно поменять к примеру на 5

Смотрим на параметр shared_buffersЭтот параметр определяет, сколько памяти будет выделяться PostgreSQL для кеширования данных.В стандартной поставке значение этого параметра 128 МБ, то есть по сути мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти сервера. Учтите, слово всей доступной, то есть учитывайте память которую занимают текущие процессы и могут занять в случае роста потребления, к примеру у нас 8 ГБ ОЗУ и стоит MySQL, который в текущем состоянии занять 1 ГБ ОЗУ, а при росте количества соединений исходя из настроек может занять все 6 ГБ ОЗУ, тогда для PostgreSQL остается не так много памяти и shared_buffers никак нельзя поставить 2 ГБ. Если у Вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, Вам доступен большой объем ОЗУ или большее количество процессоров, то можно увеличивать значение shared_buffers и смотреть результат, чтобы не привести к «деградации» (падению) производительности. Выделив слишком много памяти для shared_buffers, мы можем получить ухудшение производительности, поскольку PostgreSQL также использует кэш операционной системы (увеличение данного параметра более 40% оперативной памяти может давать «нулевой» прирост производительности).

Параметр effective_cache_sizeЭтот параметр помогает планировщику postgresql определить количество доступной памяти для дискового кеширования. На основе того, доступна память или нет, планировщик будет делать выбор между использованием индексов и использованием сканирования таблицы. Это значение следует устанавливать в 50%…75% всей доступной оперативной памяти, в зависимости от того, сколько памяти доступно для системного кеша. Еще раз — этот параметр не влияет на выделяемые ресурсы — это оценочная информация для планировщика.

Параметры min_wal_size, max_wal_size, checkpoint_timeout, checkpoint_completion_target, wal_buffersСуществует несколько параметров конфигурации, связанных с WAL, которые влияют на производительность базы данных. На эти и некоторые другие настройки стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация).Более подробно следует прочитать официальную документацию или русский перевод на сайте postgrespro.ru.

Параметр work_memВажный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение егопозволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы).Если объём памяти недостаточен для сортировки некоторого результата, то серверный процесс будет использовать временные файлы. Если же объём памяти слишком велик, то это может привести к своппингу.Будьте внимательны! Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10 МБ для этого параметра скушает 100 МБ оперативной памяти.Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Чем больше max_connections тем меньше должен быть work_mem. В качестве начального значения для параметра можете взять 2–4% доступной памяти. Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ. С другой стороны, приложения для поддержки принятия решений с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 Мб. Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии. Например, при памяти 1–4 ГБ рекомендуется устанавливать 32–128 МБ.

Параметр synchronous_commitОбратите особое внимание на этот параметр! Он включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.Допустим, в Вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы. Но жизненно важно обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Параметр maintenance_work_memЗадаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция, и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии, но слишком большие значения приведут к использованию свопа.Учтите, что когда выполняется автоочистка, этот объём может быть выделен autovacuum_max_workers раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя autovacuum_work_mem.Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей БД. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 МБ.Временное увеличение maintenance_work_mem рекомендуют для ускорения загрузки больших объёмов данных в БД. Это приведёт к увеличению быстродействия CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. На скорость самой команды COPY это не повлияет, так что этот совет будет полезен, только если вы применяете какой-либо из двух вышеописанных приёмов.

Более детально о всех параметрах можно почитать в официальной документации или русский перевод на сайте postgrespro.ru.

Давайте настроим эти параметры исходя из— объема свободного ОЗУ в 1 ГБ;— максимального количества одновременных соединений 10;— количество CPU — 1;— сервер будет выполнять задачи БД для Web-приложений;— база будет располагаться на массиве RAID 1 из 2 дисков SATA HDD;

max_connections = 10 max_wal_senders = 5 shared_buffers = 256MB maintenance_work_mem = 64MB effective_cache_size = 768MB work_mem = 13100kB min_wal_size = 1GB max_wal_size = 2GB checkpoint_completion_target = 0.7 wal_buffers = 8000kB effective_io_concurrency = 2

Хочу заметить, что многие настройки PostgreSQL зависят не только от аппаратной конфигурации, но и от размера базы данных, числа клиентов и сложности запросов, так что оптимально настроить базу данных возможно только учитывая все параметры системы и приложения (например учитывать, SSD диски и влезает ли база в память). Для облегчения первоначальной настройки Pg существует онлайн конфигуратор от Алексея Васильева.

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого в конец файла /etc/postgresql/10/main/pg_hba.conf добавим:

# localnet host all all 192.168.100.0/24 md5

Далее выходим из под пользователя postgres и рестартуем PostgreSQL:

service postgresql restart

Проверяем открытые порты

netstat -ltupn |grep postgre tcp 0 0 192.168.100.4:5432 0.0.0.0:* LISTEN 32658/postgres tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 32658/postgres

Отлично! Теперь мы можем подключиться к PostgreSQL c локального сервера и из нашей локальной сети.

На этом все, до скорых встреч.

Дополнительные статьи:Начало работы с PostgreSQL 10Полное удаление PostgreSQL 10 на Debian 9Как удалить все таблицы из БД PostgreSQL и MySQL?

Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.

blog.programs74.ru

PostgreSQL 9.2 Начало! / Хабр

Запуск и завершение PostgreSQL

В этом разделе описаны два способа запуска и завершения серверного процесса PostgreSQL. Первый способ основан на применении управляющей программы pg_ctl, которая должна одинаково работать на всех компьютерах независимо от операционной системы. Предполагается, что сценарий должен выполняться системным пользователем (то есть пользователем, которому принадлежит каталог данных), которому разрешено выполнение серверного процесса postmaster.

Во втором варианте применяется сценарий SysV, находящийся в подкаталоге contrib/start-scripts основного каталога PostgreSQL. Установка сценария SysV описана в главе 2. По умолчанию сценарий называется linux, поскольку он предназначен для запуска из стартового сценария Linux, хотя в инструкциях по установке он переименовывается в сценарий postgresql в каталоге запуска служб (например, /etc/rc.d/init.d).

Самое принципиальное различие между программой pg_ctl и сценарием SysV заключается в том, что программа pg_ctl выполняется пользователем, запускающим серверный процесс postmaster (например, postgres), а сценарий SysV должен запускаться пользователем root.

Сценарий службы не является специфическим для Linux. Он совместим с большинством систем, использующих стартовые сценарии SysV. Тем не менее, если вы не работаете в системе Linux, возможно, лучше выбрать pg_ctl.

Приложение pg_ctl

В поставку PostgreSQL входит приложение pg_ctl, предназначенное для решения общих задач управления. В частности, оно позволяет запускать, завершать, перезапускать и получать информацию о состоянии PostgreSQL.

При запуске pg_ctl с ключом — -help выводится следующее описание:

pg_ctl start [-w] [-D каталог] [-s] [-1 файл] [-о "параметры"]

pg_ctl stop [-W] [-0 каталог] [-s] [-m режим_завершения]

pg_ctl restart [-w] [-D каталог] [-s] [-m режим_завершения] [-о "параметры"]

pg_ctl status [-D каталог]

Ключи приложения pg_ctl описаны ниже.

— w. Приложение pg_ctl ож] [дает завершения операции перед возвратом в режим командной строки. Параметр используется с операциями start или restart; по умолчанию приложение передает команду процессу postmaster и немедленно завершается.
-W. Приложение pg_ctl не ожидает завершения операции перед возвратом в режим командной строки. Параметр используется только с операцией stop; по умолчанию приложение передает команду процессу postmaster и ожидает ее выполнения перед завершением.
-D каталог. Каталог, содержащий файлы базы данных. Данный ключ не обязателен, поскольку информация может храниться в переменной среды PGDATA. Если переменная не существует, флаг -D является обязательным.
-s. Подавление вывода pg_ctl, кроме системных ошибок. Если флаг не задан, сведения о действиях с базой данных (или запуске/завершении в зависимости от выбранной операции) выводятся на экране пользователя, выполнившего команду.
-1 файл. Имя файла, в который записываются сведения об операциях с базой данных. Параметр используется только с операцией start.
-m режим_завершения. Режим завершения postmaster (конечно, этот параметр доступен только для операций stop и restart):
- smart — перед завершением процесс postmaster ожидает отключения всех клиентов;
- fast — процесс postmaster завершается, не ожидая отключения клиентов;
- immediate — процесс postmaster прекращает работу еще быстрее, чем в режиме fast, без выполнения стандартных завершающих процедур, при следующем запуске база данных запускается в режиме восстановления (recovery) и проверяет целостность системы.
-о "параметры". Заданная строка параметров, заключенная в кавычки, напрямую передается процессу postmaster (например, флаг — i для активизации поддержки TCP/IP). Полный список флагов приведен в подразделе «Прямое обращение к postmaster» этого раздела.

ПРИМЕЧАНИЕ

Многие параметры конфигурации postmaster задаются в файле postgresql.conf, находящемся в каталоге данных PostgreSQL (например, /usr/local/pgsql/data).

Эти параметры управляют более сложными техническими аспектами работы PostgreSQL. He изменяйте их, если не уверены в правильности своих действий.

Запуск PostgreSQL в приложении pg_ctl

Чтобы запустить серверный процесс PostgreSQL postmaster, передайте pg_ctl ключ start. Помните, что приложение pg_ctl должно запускаться пользователем postgres (или другим пользователем, которому принадлежит каталог данных PostgreSQL).

В листинге 9.1 приведен пример запуска postmaster с каталогом данных /usr/ local/pgsql/data. СУБД успешно запускается, выдает время последнего завершения работы базы данных и отладочную информацию, после чего пользователь postgres возвращается к приглашению командного интерпретатора.

Листинг 9.1. Запуск PostgreSQL в приложении pg_ctl

[postgres@booktown -]$ pg_ctl -D /usr/1oca!/pgsql/data start

postmaster successfully started

DEBUG: database system was shut down at 2001-09-17 08:06:34 POT

DEBUG: Checkpoint record at (0. 1000524052)

DEBUG: Redo record at (0. 1000524052): Undo record at (0. 0): Shutdown TRUE

DEBUG: NextTransactionld: 815832: NextOid: 3628113

DEBUG: database system is in production state

[postgres@booktown -]$

Завершение PostgreSQL в приложении pg_ctl

Серверный процесс PostgreSQL postmaster можно остановить той же программой pg_ctl, которой он был запущен. Приложение pg_ctl проверяет Наличие работающего процесса postmaster, и если команда stop была выдана владельцем работающего процесса (например, пользователем postgres), сервер PostgreSQL прекращает работу.

Существуют три режима завершения серверного процесса PostgreSQL: интеллектуальный (smart), ускоренный (fast) и немедленный (immediate). Режим завершения задается ключом -т при вызове pg_ctl.

В интеллектуальном режиме (используемом по умолчанию) PostgreSQL перед завершением ждет, пока все клиенты отключатся от сервера. В ускоренном режиме PostgreSQL просто начинает стандартную процедуру завершения, не проверяя состояние клиентских подключений. В немедленном режиме стандартная процедура завершения пропускается, и при последующем перезапуске система должна пройти через режим восстановления.

ВНИМАНИЕ

Никогда не завершайте процесс postmaster командой kill -9 (kill -KILL), что приводит к потере или порче данных.

В листинге 9.2 сценарий pg_ctl завершает процесс postmaster в ускоренном режиме. Процесс postmaster прекращает работу, не дожидаясь отключения клиентов.

Листинг 9.2. Завершение PostgreSQL в приложении pg_ctl

[postgres@booktown -]$ pg_ctl -D /usr/local/pgsql/data stop -m fast

Fast Shutdown request at Mon Sep 17 09:23:39 2001 DEBUG: shutting down

waiting for postmaster to shut down…..

DEBUG: database system is shut down

done

postmaster successfully shut down

[postgres@booktown -]$

ПРИМЕЧАНИЕ

Завершение в режиме smart эквивалентно команде kil I -TERM для процесса postmaster. Режим fast эквивалентен команде kill -INT, а аналогом режима immediate является команда kill -QUIT.

Перезапуск PostgreSQL в приложении pg_ctl

Последовательные вызовы pg_ctl с операциями stop и start можно заметить одним вызовом с операцией restart. В команде также может присутствовать флаг -т, определяющий режим завершения.

Параметры, использованные при последнем запуске PostgreSQL, хранятся во временном файле postmaster.opts в каталоге данных PostgreSQL (переменная PGDATA). Файл используется при вызове pg_ctl с аргументом restart и обеспечивает сохранение предыдущих настроек при перезапуске. Не размещайте собственные параметры конфигурации в файле postmaster.opts, поскольку они будут стерты при запуске pg_ctl с аргументом start.

В листинге 9.3 приведен пример перезапуска сервера базы данных booktown пользователем postgres.

Листинг 9.3. Перезапуск PostgreSQL в приложении pg_ctl

[postgres@booktown ~]$ pg_ctl -D /usr/1oca!/pgsql/data restart

Smart Shutdown request at Mon Sep 17 08:33:51 2001

DEBUG: shutting down

waiting for postmaster to shut down…..DEBUG: database system is shut down

done

postmaster successfully shut down

postmaster successfully started

[postgres@booktown -]$

DEBUG: database system was shut down at 2001-09-17 08:33:53 PDT

DEBUG: Checkpoint record at (0. 1000524116)

DEBUG: Redo record at (0. 1000524116): Undo record at (0. 0): Shutdown TRUE

DEBUG: NextTransactionld: 815832: NextOid: 3628113

DEBUG: database system is in production state

[postgres@booktown ~J$

Проверка состояния PostgreSQL в приложении pg_ctl

При вызове с аргументом status приложение pg_ctl возвращает информацию о состоянии процесса postmaster. Хотя выполнение команды никак не отражается на состоянии данных, приложению pg_ctl должен быть известен каталог данных PostgreSQL. Если переменная среды PGDATA не существует, при вызове необходимо передать ключ -D.

Пример получения информации о состоянии сервера PostgreSQL приведен в листинге 9.4.

Листинг 9.4. Проверка состояния PostgreSQL в приложении pg_ctl

[postgres@booktown -]$ pg_ctl -D /usr/local/pgsql/data status

pg_ctl: postmaster is running (pid: 11575)

Command line was:

/usr/local/pgsql/bin/postmaster ‘-D’ ‘/usr/local/pgsql/data’

[postgres@booktown -]$

ПРИМЕЧАНИЕ

Использование переменной PGDATA заметно сокращает объем команды. Если вы всегда работаете с одним каталогом данных, присвойте значение переменной PGDATA (например, в файле /etc/profile, как рекомендовалось в главе 2), и вам не придется использовать ключ -D.

rpilot62.ru

PostgreSQL: сборка из исходников и настройка под Linux

СУБД является важнейшим компонентом многих современных систем. Поэтому совершенно естественным является желание собрать эту СУБД из исходников самостоятельно с флагами -march=native -O3, не говоря уже о куда более тонкой настойке при помощи скрипта configure. В этом случае вы также можете подобрать компилятор, который лучше оптимизирует конкретную СУБД под ваши конкретные задачи. Не исключено также, что готового бинарного пакета нужной вам версии приложения еще попросту не существует. Наконец, умение самостоятельно собирать и настраивать приложение дает куда более лучшее понимание его работы, чем простое запоминание каталогов с конфигами и логами. В силу названных причин, давайте попробуем разобраться, как собрать из исходников PostgreSQL, а затем настроить его для полноценной работы.

Я ставил свои эксперименты под Ubuntu Linux 14.04 LTS и PostgreSQL 9.5, но последовательность действий для других *nix систем и версий PostgreSQL не должна сильно отличаться. Поскольку для сборки PostgreSQL нам понадобится ряд пакетов, не требуемых во время работы СУБД, для сборки лучше создать LXC контейнер, чтобы эти пакеты не засоряли основную систему.

Перекрестная ссылка: Вас также может заинтересовать заметка Начало работы с PostgreSQL. В частности, в ней рассказывается, для чего нужны файлы pg_hba.conf и postgresql.conf, как пользоваться утилитой psql, а также как производится резервное копирование и восстановление PostgreSQL. Далее предполагается, что все это вы уже знаете.

Получаем исходники

Исходники PostgreSQL можно получить по HTTP или из Git-репозитория.

Пойдем сложным путем на случай, если вас интересует конкретная ветка или самый свежий код из master:

git clone http://git.postgresql.org/git/postgresql.git

Это займет какое-то время. Затем говорим:

cd postgresqlgit checkout (ветка)

Ура, теперь у нас есть исходники.

Сборка

Для сборки при помощи gcc говорим:

sudo apt-get install gcc make flex bison libreadline-dev zlib1g-dev \ jade

Если же вы предпочитаете clang:

sudo apt-get install clang-3.6 make flex bison libreadline-dev \ zlib1g-dev jadesudo update-alternatives --install /usr/bin/cc cc \ /usr/bin/clang-3.6 100sudo update-alternatives --install /usr/bin/c++ c++ \ /usr/bin/clang++-3.6 100

Если вы намерены собирать еще и документацию, также понадобятся:

sudo apt-get install docbook docbook-dsssl docbook-xsl libxml2-utils \ openjade1.3 opensp xsltproc

Смотрим все доступные флаги скрипта configure:

В документации можно найти более подробное их описание. Вот несколько наиболее интересных:

--enable-debug build with debugging symbols (-g)--enable-profiling build with profiling enabled--enable-coverage build with coverage testing instrumentation--enable-dtrace build with DTrace support--enable-tap-tests enable TAP tests (requires Perl and IPC::Run)--enable-cassert enable assertion checks (for debugging)

Примечание: Кое-какие подробности касательно флага --enable-coverage вы найдете в конце заметки Определение степени покрытия кода на C/C++ тестами. Подробности касательно DTrace приводятся в посте Использование DTrace на примере FreeBSD и Linux.

Пример сборки с отладочными символами и ассертами (не для прода!):

./configure --enable-tap-tests --enable-cassert --enable-debugmake -j8

Чтобы make не выводил длинный список всего, что он делает, используйте -s:

Желающие адской оптимизации могут попробовать:

CFLAGS="-O3 -march=native -flto -g" ./configure ... && make ... && ...

Сборка вообще всего, включая документацию и расширения:

Прогон регрессионных тестов:

make checkmake check-world

Если вдруг тесты почему-то свалились, здесь можно посмотреть логи:

src/test/regress/log/postmaster.logsrc/test/regress/log/initdb.log

Если что-то пошло не так, можно попробовать очистить все лишнее и начать заново, сказав git clean -dfx.

Дополнение: Во FreeBSD сборка осуществляется аналогично, только вместо перечисленных выше пакетов вам понадобятся: git-lite bison perl5 gmake, а также при сборке вместо make следует использовать gmake.

Установка

Все просто:

sudo make installsudo make install-world

Важно! Скорее всего, здесь вы захотите воспользоваться checkinstall. Если же вы разрабатываете патч для PostgreSQL, куда удобнее будет использовать флаг конфигурации --prefix и один из этих скриптов (спасибо, @kelvich!).

Если вы хотите установить только расширения, это можно сделать так:

cd contribmake -j8sudo make install

Сразу после установки не лишим будет прописать каталог /usr/local/pgsql/bin в переменную окружения $PATH.

Создаем пользователя postgres:

Под пользователем postgres:

… подготавливаем каталог, в котором будут храниться все данные:

cdmkdir data/usr/local/pgsql/bin/initdb -D /home/postgres/data

Файлы postgresql.conf и pg_hba.conf вы найдете в /home/postgres/data.

Наконец, запускаем PostgreSQL:

/usr/local/pgsql/bin/postgres -D /home/postgres/data \ >/home/postgres/logfile 2>&1 &

Видим, что порт 5432 прослушивается. В файле ./data/postmaster.pid первой строчкой записан PID master-процесса.

Пример создания БД и подключения к ней:

/usr/local/pgsql/bin/createdb my_database/usr/local/pgsql/bin/psql my_database

Остановить СУБД можно, например, так:

kill -9 `head -n 1 /home/postgres/data/postmaster.pid`

Но лучше так:

/usr/local/pgsql/bin/pg_ctl stop -D /home/postgres/data -s -m fast

После установки можно еще заполнить базу какими-то данными и провести нагрузочное тестирование (не зря же мы кудрили с флагами оптимизации?):

pgbench -j 8 -c 8 -f t.sql -T 30 my_database

… или, как вариант:

\timing onselect * from my_table where id = 100;

Стандартный бенчмарк — инициализация и сам тест:

pgbench -i my_databasepgbench -j 8 -c 8 -T 30 -P my_database

Прогон обычных регрессионных тестов:

Удалить PostgreSQL можно, вернувшись в каталог с исходниками, и сказав:

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

Автозапуск

Парни из компании 2nd Quadrant написали отличный init.d скрипт для PostgreSQL. Он мне так понравился, что я даже сделал зеркало на GitHub.

Копируем скрипт в /etc/init.d/postgresql и правим несколько первых строк:

# Installation prefixprefix=/usr/local/pgsql/

# Data directoryPGDATA=/home/postgres/data

# Who to run the postmaster as, usually "postgres". (NOT "root")PGUSER=postgres

# Where to keep a log filePGLOG=/home/postgres/logfile

Затем говорим:

sudo chmod u+x /etc/init.d/postgresqlsudo update-rc.d postgresql defaultssudo service postgresql stopsudo service postgresql start

Это уже почти здорово, но, к сожалению, пока что без ротации логов.

Ротация логов

Настраивается путем правки postgresql.conf примерно так:

logging_collector = on log_directory = '/home/postgres/logs' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 0

# чтобы в логи писались таймстемпыlog_line_prefix = '%m '

Сам PostgreSQL не умеет сжимать и удалять старые логи, но эту проблему легко решить, прописав в /etc/crontab скрипт для ротации логов из заметки Как я выбирал скриптовый язык и остановился на Python.

В качестве альтернативного варианта можно воспользоваться утилитой rotatelogs из пакета apache2-utils. В этом случае придется отредактировать строку что-то там $PGCTL start в init.d скрипте PostgreSQL. Результат должен получиться примерно таким:

su ... "... 2>&1 | rotatelogs -f -c -n 10 /var/log/postgres 86400 &"

Мне лично этот способ не нравится, так как мне хотелось бы, чтобы в именах лог-файлов были даты, а rotatelogs пишет просто номера. Кроме того, сжимать логи rotatelogs не умеет.

Заключение

Как видите, все довольно просто. Как уже отмечалось, сборка PostgreSQL для других версий Ubuntu или других *nix систем будет мало чем отличаться. Если же вдруг вас интересует сборка PostgreSQL под Windows, она описана здесь.

А часто ли вы собираете софт из исходников ради большей производительности или же в силу иных причин?

Дополнение: Также вас могут заинтересовать посты Потоковая репликация в PostgreSQL и пример фейловера и Учимся писать расширения на языке C для PostgreSQL.

Метки: PostgreSQL, СУБД.

eax.me

PostgreSQL : Документация: 9.6: 9.12. Функции и операторы для работы с сетевыми адресами : Компания Postgres Professional

9.12. Функции и операторы для работы с сетевыми адресами

В Таблица 9.36 показаны операторы, работающие с типами cidr и inet. Операторы <<, <<=, >>, >>= и && проверяют включения подсетей, рассматривая только биты сети в обоих адресах (игнорируя биты узлов) и определяя, идентична ли одна сеть другой или её подсети.

Таблица 9.36. Операторы для типов cidr и inet

ОператорОписаниеПример

<	меньше	inet '192.168.1.5' < inet '192.168.1.6'
<=	меньше или равно	inet '192.168.1.5' <= inet '192.168.1.5'
=	равно	inet '192.168.1.5' = inet '192.168.1.5'
>=	больше или равно	inet '192.168.1.5' >= inet '192.168.1.5'
>	больше	inet '192.168.1.5' > inet '192.168.1.4'
<>	не равно	inet '192.168.1.5' <> inet '192.168.1.4'
<<	содержится в	inet '192.168.1.5' << inet '192.168.1/24'
<<=	равно или содержится в	inet '192.168.1/24' <<= inet '192.168.1/24'
>>	содержит	inet '192.168.1/24' >> inet '192.168.1.5'
>>=	равно или содержит	inet '192.168.1/24' >>= inet '192.168.1/24'
&&	содержит или содержится в	inet '192.168.1/24' && inet '192.168.1.80/28'
~	битовый NOT	~ inet '192.168.1.6'
&	битовый AND	inet '192.168.1.6' & inet '0.0.0.255'
\|	битовый OR	inet '192.168.1.6' \| inet '0.0.0.255'
+	сложение	inet '192.168.1.6' + 25
-	вычитание	inet '192.168.1.43' - 36
-	вычитание	inet '192.168.1.43' - inet '192.168.1.19'

В Таблице 9.37 перечислены функции, работающие с типами cidr и inet. Функции abbrev, host и text предназначены в основном для вывода данных в альтернативных форматах.

Таблица 9.37. Функции для типов cidr и inet

ФункцияТип результатаОписаниеПримерРезультат

abbrev(inet)	text	вывод адрес в кратком текстовом виде	abbrev(inet '10.1.0.0/16')	10.1.0.0/16
abbrev(cidr)	text	вывод адрес в кратком текстовом виде	abbrev(cidr '10.1.0.0/16')	10.1/16
broadcast(inet)	inet	широковещательный адрес сети	broadcast('192.168.1.5/24')	192.168.1.255/24
family(inet)	int	возвращает семейство адреса; 4 для адреса IPv4, 6 для IPv6	family('::1')	6
host(inet)	text	извлекает IP-адрес в виде текста	host('192.168.1.5/24')	192.168.1.5
hostmask(inet)	inet	вычисляет маску узла для сетевого адреса	hostmask('192.168.23.20/30')	0.0.0.3
masklen(inet)	int	выдаёт длину маски сети	masklen('192.168.1.5/24')	24
netmask(inet)	inet	вычисляет маску сети для сетевого адреса	netmask('192.168.1.5/24')	255.255.255.0
network(inet)	cidr	извлекает компонент сети из адреса	network('192.168.1.5/24')	192.168.1.0/24
set_masklen(inet, int)	inet	задаёт размер маски для значения inet	set_masklen('192.168.1.5/24', 16)	192.168.1.5/16
set_masklen(cidr, int)	cidr	задаёт размер маски для значения cidr	set_masklen('192.168.1.0/24'::cidr, 16)	192.168.0.0/16
text(inet)	text	выводит в текстовом виде IP-адрес и длину маски	text(inet '192.168.1.5')	192.168.1.5/32
inet_same_family(inet, inet)	boolean	адреса относятся к одному семейству?	inet_same_family('192.168.1.5/24', '::1')	false
inet_merge(inet, inet)	cidr	наименьшая сеть, включающая обе заданные сети	inet_merge('192.168.1.5/24', '192.168.2.5/24')	192.168.0.0/22

Любое значение cidr можно привести к типу inet, явно или нет; поэтому все функции, показанные выше с типом inet, также будут работать со значениями cidr. (Некоторые из функций указаны отдельно для типов inet и cidr, потому что их поведение с разными типами различается.) Кроме того, значение inet тоже можно привести к типу cidr. При этом все биты справа от сетевой маски просто обнуляются, чтобы значение стало допустимым для типа cidr. К типам inet и cidr можно привести и обычные текстовые значения, используя обычный синтаксис, например: inet(выражение) или столбец::cidr.

В Таблице 9.38 приведена функция, предназначенная для работы с типом macaddr. Функция trunc(macaddr) возвращает MAC-адрес, последние 3 байта в котором равны 0. Это может быть полезно для вычисления префикса, определяющего производителя.

Таблица 9.38. Функции macaddr

ФункцияТип результатаОписаниеПримерРезультат

trunc(macaddr)

macaddr

обнуляет последние 3 байта

trunc(macaddr '12:34:56:78:90:ab')

12:34:56:00:00:00

Тип macaddr также поддерживает стандартные реляционные операторы лексической сортировки (>, <= и т. д.) и операторы битовой арифметики (~, & и |), соответствующие операциям NOT, AND и OR.

postgrespro.ru

Топ инструментов разработки для PostgreSQL

Я поспрашивал различных людей, имеющих отношение к PostgreSQL, что они используют в реальной жизни для разработки приложений, использующих PG.

Это, конечно, не строго математическая выборка, но тем не менее получился некоторый список инструментов на слуху, которые достойны того, чтобы их "пощупать", что я и собираюсь сделать в этой статье.

Если вашего инструмента нет в списке, или у вас просто есть что сказать, добро пожаловать в комментарии.

Итак, вот неформальный топ и субъективное описание.

psql

На первом месте psql, и это неудивительно. Надежный как автомат калашникова, бесплатный, стоит из коробки, что еще надо для счастья? Для редактирования запросов используется редактор, указанный в переменной окружения EDITOR, обычно ставят vim, nano или что-то в этом духе. Ну и вообще, psql — это unix-way, т.е. можно его запускать со своим редактором, своим пейджером для отображения результатов, ему можно на вход подавать sql-запрос через пайп, и вывод направлять куда надо.

Из минусов можно отметить слабенький автокомплит, а также то, что приходится заучивать неинтуитивные команды из серии \d \dt+ \sf и т.д. (впрочем, все описания команд доступны через команду \?)

Ну, и работа в консоли и в виме — это не всех устраивает почему-то :)

На самом деле, иногда хочется иметь где-нибудь слева полный список таблиц/вьюх и иметь возможность щелкнуть мышкой по нужной, чтобы посмотреть, что там вообще. Т.е. хоть какой-то GUI. Работа в psql хоть и эффективна, но напоминает работу в темной комнате с маленьким фонариком, освещающим лишь только один объект за раз.

datagrip

IDE для баз. Несмотря на то, что продукт относительно свежий, он уже используется повсеместно. В основном за счет того, что сразу встроен в мегапопулярные продукты от компании JetBrains: IntelliJ IDEA, PyCharm, PhpStorm и т.д.

Собственно, эта его встроенность одновременно является и главной киллер-фичей продукта: вы редактируете, например, php-код, в котором есть строка с sql-запросом, и внезапно понимаете, что IDE вам подсказывает (прямо в вашем коде) синтаксис SQL, названия таблиц и их полей, подчеркивает красненьким, если что-то написано не так, форматирует SQL и многое-многое другое. Конечно, в этом же IDE можно делать и то, что умеют другие GUI для баз: просматривать списки таблиц и других сущностей, отдельно делать запросы, экспорт таблиц в разные форматы и многое другое.

Из особенностей я бы отметил следующие вещи:

можно выделить несколько insert'ов и нажать "Edit as table" (см. картинку). После чего отредактировать это в удобном табличном виде вместо sql-синтаксиса, причем там же можно добавлять строки, колонки, экспортировать в csv и т.д.
Можно сравнивать результаты двух запросов. Это полезно, когда пытаешься упростить сложный запрос, и при этом ничего не сломать.
встроенность в код проработана не до конца. К примеру, при переименовывании в каком-либо интерфейсе колонки таблицы, IDE не находит нужные строки с SQL в коде (при этом автокомплит в этих строках работал), и наоборот, находит какую-то чушь.
Визуальной разработки не очень много. Т.е. вы можете сделать таблицу, но view уже не можете. Если таблица содержит какие-то id с foreign key (допустим, ссылка на некий словарь), хотелось бы при в вводе данных в таблицу выбирать значения из словаря, а не вбивать айдишки.
Если посмотреть таблицу в какой-нибудь из схем, то Datagrip посылает запрос set search_path = имясхемы, что приводит к плохим последствиям, если используется pgbouncer (а он используется почти всегда в случае с php или когда много серверов), так что для dev-разработки лучше использовать разные подключения: для работы кода — через pgbouncer, для ide — напрямую к базе.

Datagrip активно развивается, в частности, исправлены некоторые раздражающие баги с подсветкой синтаксиса.

В целом хороший современный инструмент, рекомендую.

pgAdmin

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

Как его использовать для администрирования — не очень понятно. pgAdmin'ом нельзя "заинитить" новый сервер, нельзя подправить pg_hba.conf или postgresql.conf. Видимо, имеются в виду скудные графики запросов в секунду, вывод подробностей конфигурации сервера и статистика в таблицах. Не уверен, в общем. Как вы испольуете pgAdmin для администрирования?

Как его использовать с точки зрения разработки — еще менее понятно. Субъективно, интерфейс в целом не удобен для разработки. Несмотря на то, что четвертую версию переписали на python + JS с jQuery, по сути, осталось всё то же самое.

Чтобы немного пояснить ситуацию, в голове разработчика такая картина: есть база на каком-то серваке, в ней — схемы, в схемах — таблицы и вьюхи. Т.е. таблица — максимум, 3-й уровень. А если база одна, то вообще второй уровень. Ткнул по таблице — увидел несколько первых строк.

В голове разработчика pgAdmin как-то так: "Смерть Кощеева на конце иглы, та игла в яйце, то яйцо в утке, та утка в зайце, тот заяц в сундуке, а сундук стоит на высоком дубу, и то дерево Кощей как свой глаз бережёт", а именно (см. картинку):

Есть группа серверов, в ней есть сервер, на сервере существуют базы, роли и т.д., из баз можно выбрать конкретную базу, в ней видно схемы, языки, еще бог знает что. В схемах можно выбрать нужную схему, в схеме 100500 всего, и где-то в конце списка "таблицы". В таблицах можно выбрать нужную таблицу, по ней надо кликнуть правой кнопкой мыши, там в большом списке выбираешь "view data", в этой "view data" есть "view first 100 rows" и уже там наконец-то смерть кощеева несколько строк для ознакомления.

Киллер-фичей pgAdmin является возможность дебажить хранимые процедуры pl/pgsql. Других бесплатных программ с этой возможностью я не встречал.

EMS Studio

EMS Studio, похоже, работает только под Windows. Это его главный недостаток, потому что, как известно PostgreSQL очень редко используют под виндой.

Я этот софт посмотрел только один раз под Wine, поэтому могу ошибаться, но вообще мне жутко не понравилось. Бешенное нагромождение непонятных иконок, невнятный интерфейс. Кстати, у меня под Wine заглючили всплывающие подсказки, и я играл в "угадай функциональность по картинке". Очень тяжело.

До кучи там зачем-то сделан визуальный конструктор запросов. Где вместо того, чтобы текстом написать where id = 5, надо нажать мышкой несколько кнопок и понавыбирать из выпадающего списка. Тем, кто знает SQL — это не нужно, тем кто не знает — это не поможет.

Фичи, которые называют как удобные: auto-complete с алиасами, экспорт результата выполнения запроса в SQL формате (insert), удобный GUI для экпорта базы, возможность выполнять только выделенную часть SQL.

Умеет дебаг pl/pgsql. В общем, много чего умеет, но какой-то выдающейся особенности, что отличало бы от других, я не могу назвать.

NAVICAT

Navicat — это, наверное, самая богатая фичами программа. Она умеет всё, что умеют другие GUI для БД: дизайнер объектов, просмотрщик таблиц, автокомплит, инструменты проектирования базы, отладка pl/pgsql, импорт/экспорт и так далее.

Поистине всеобъемлющий софт, который работает практически на любой ОС. Навскидку, намного удобнее EMS Studio.

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

Ценник, правда, что называется, "конский" — в два раза дороже, чем EMS. Но тут, похоже, это полностью оправдано.

PGCLI

Те, кто пробует работать с psql, сразу начинают мечтать о более богатом функционале, например, автодополнении. Для реализации этих хотелок существует pgcli.

pgcli умеет автодополнять ключевые слова, функции, таблицы, колонки, колонки в алисах. Умеет подсвечивать синтаксис, редактировать SQL в многострочном режиме без отдельного редактора и т.д.

Короче, pgcli — это, по сути, psql на стероидах.

phppgadmin

Многие из тех, кто перешел с MySQL, инстинктивно ищут аналоги phpmyadmin, и натыкаются на phppgadmin. К сожалению, phppgadmin не развивается уже несколько лет, так что о мертвых или хорошо, или ничего. В общем, промолчим, пожалуй.

webhamster.ru

PostgreSQL | Введение

Что такое PostgreSQL. Установка сервера

Последнее обновление: 15.03.2018

PostgreSQL является одной из наиболее популярных систем управления базами данных. Сам проект postgresql эволюционировал из другого проекта, который назывался Ingres. Формально развитие postgresql началось еще в 1986 году. Тогда он назывался POSTGRES. А в 1996 году проект был переименован в PostgreSQL, что отражало больший акцент на SQL. И собственно 8 июля 1996 года состоялся первый релиз продукта.

С тех пор вышло множество версий postgresql. Текущей версией является версия 10. Однако регулярно также выходят подверсии.

PostgreSQL поддерживается для всех основных операционных систем - Windows, Linux, MacOS.

Официальный сайт проекта: https://www.postgresql.org/.

PostgreSQL развивается как opensource. Исходный код проекта можно найти в репозитории на гитхабе по адресу https://github.com/postgres/postgres.

Установка

На странице https://www.postgresql.org/download/ можно найти ссылки на загрузку различных дистрибутивов для различных операционных систем. В частности, для загрузки дистрибутива для Windows надо перейти на страницу https://www.enterprisedb.com/downloads/postgres-postgresql-downloads и указать все необходимые опции для загрузки: версию postgres и операционную систему.

Запустим программу установки:

На следующем экране необходимо будет указать папку для установки. Оставим папку по умолчанию и перейдем к следующему шагу:

Далее будет предложено выбрать компоненты для установки:

Оставим все компоненты по умолчанию и перейдем к следующему шагу. Далее будет предложено выбрать папку, где будут храниться базы данных:

Оставим путь по умолчанию и перейдем к следующему шагу. Затем необходимо будет установить пароль для суперпользователя postgres:

При установке запомним пароль, так как он потребуется для подключения к серверу. Затем нужно будет установить порт, по которому будет запускаться сервер:

Далее можно будет установить локаль сервера. Оставим установку по умолчанию:

После этого мы увидим сводку по всем настройкам:

И если нас все устраивает, то можно нажать на кнопку Next, и начнется установка

И после завершения установки мы увидем следующее окно, и для выхода нажмем на кнопку Finish:

Таким образом, сервер PostgreSQL установлен, и мы можем начинать с ним работать.

metanit.com