Установка Postgres + PostGIS на VPS Linux

[dab]

Статья из серии "howto". Нужна ли вообще?
http://gis-lab.info/qa/postgis-vps-install.html
Приглашаю к обсуждению.

===================
P.S. от 27.03.2014: статья опубликована.

[Denis Rykov]

Ставил Postgres много раз, но никогда прежде не требовалось инициализировать кластер вручную, все это делалось автоматом при установке. Тут этого не происходит?

[dab]

Ставил Postgres много раз, но никогда прежде не требовалось инициализировать кластер вручную, все это делалось автоматом при установке. Тут этого не происходит?

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

[Denis Rykov]

Так точно:

Код: Выделить всё

dbvega=# SHOW LC_COLLATE;
 lc_collate 
------------
 en_US.utf8
(1 row)

Но пока еще ни разу не сталкивался с какими-то проблемами, связанными с локалью. Что касается статьи, то мне кажется стоит прояснить момент с переменной окружения PGDATA - для чего ее нужно указывать, ну и поподробнее про то для чего нужно создавать кластер с нужной локалью.

[dab]

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

Принято. Спасибо.

[KolesovDmitry]

Статья из серии "howto". Нужна ли вообще?

Хорошая статья. На мой взгляд как раз и нужны в первую очередь такие, которые расчитаны на начального пользователя.

[rhot]

1) Не хватает информации о том, как из БД сделать геоБД. Зачем вообще ставили PostGIS?
2) Что именно даёт русская локаль?

[dab]

Но пока еще ни разу не сталкивался с какими-то проблемами, связанными с локалью. Что касается статьи, то мне кажется стоит прояснить момент с переменной окружения PGDATA - для чего ее нужно указывать, ну и поподробнее про то для чего нужно создавать кластер с нужной локалью.

2) Что именно даёт русская локаль?

Был неправ. Видимо некоторая инертность мышления у меня имеется...
Не смог найти что именно даёт русская локаль. И без настройки русской локали, при работе с кластером по умолчанию, я не обнаружил проблем ни с сортировкой ни с функциями upper, lower ... Соответственно установка PGDATA также становится неактуальной.
Буду править статью в сторону упрощения. Спасибо за вопросы.

1) Не хватает информации о том, как из БД сделать геоБД. Зачем вообще ставили PostGIS?

Не понял, что значит "сделать геоБД"? По данному "рецепту" получаем рабочий сервер PostGIS, дальнейшие действия выходят за рамки темы статьи. Я например, далее использую SQL Manager от EMS.
PostGIS ставил для своей пространственной БД. Мой первый опыт с СУБД на Linux сервере. Внятного русскоязычного описания последовательности действий для VPS, а не Desktop не нашел. А что находил - только "сбивало с пути" - русская локаль, тому пример.

[Denis Rykov]

Еще небольшое замечание - pgadmin3 на сервере зачем? И вот еще: мне кажется нужно убрать весь этот компонент, относящийся к VPS - скриншоты, упоминания про него, как я понял ничего специфичного там нет, а статью переименовать в "Установка Postgres + PostGIS в Linux", а в статье указать, что рассмотрена установка на примере Ubuntu.

[Denis Rykov]

Согласен с rhot - не хватает информации о том, как добавить функционал PostGIS в свою БД. Вот небольшая вырезка по этому вопросу из одной моей доки, мне кажется что-то подобное нужно включить в статью и обратить внимание на то, что в последних версиях PostGIS можно добавить в БД через экстеншены (я не пользовался ими, но это гораздо проще, чем запускать *.sql файлы):

В Debian GNU/Linux 7.0 в репозиториях доступен PostGIS 1.5. Для установки
выполняем следующую команду:

sudo aptitude install postgresql-9.1-postgis


Добавление функций PostGIS в базу данных
----------------------------------------

Чтобы внутри базы данных были доступны функции PostGIS, их необходимо туда
загрузить. Делаем это от имени пользователя `postgres` (у пользователя
`pguser` на выполнение данных инструкций недостаточно прав):

sudo su - postgres -c "psql -d dbvega -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql"
sudo su - postgres -c "psql -d dbvega -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql"
sudo su - postgres -c "psql -d dbvega -c 'ALTER TABLE geometry_columns OWNER TO pguser'"
sudo su - postgres -c "psql -d dbvega -c 'ALTER TABLE spatial_ref_sys OWNER TO pguser'"
sudo su - postgres -c "psql -d dbvega -c 'ALTER TABLE geography_columns OWNER TO pguser'"

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

psql -d dbvega -U pguser -c "SELECT PostGIS_Full_Version();"

или с удалённого:

psql -d dbvega -h 10.22.0.9 -U pguser -c "SELECT PostGIS_Full_Version();"

В результате чего должен быть получен ответ вида:

-------------------------------------------------------------------------------------------------------
POSTGIS="1.5.3" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS

[Denis Rykov]

Под суперпользователем postgres я бы тоже не рекомендовал работать. Если не против, то вот моя инструкция по установке Postgres в Debian, может стоит что-то взять отсюда:

В Debian GNU/Linux 7.0 репозитории доступен [PostgreSQL 9.1](http://www.postgresql.org/), который
может быть установлен с помощью менеджера управления пакетами.

Установка сервера:

sudo aptitude install postgresql

Запуск, остановка, перезапуск сервера:

sudo /etc/init.d/postgresql start|stop|restart

или

sudo service postgresql start|stop|restart

В результате установки также будет установлен консольный клиент [psql](http://www.postgresql.org/docs/9.1/static/app-psql.html).


Управление пользователями на уровне хоста
-----------------------------------------

При установке PostgreSQL в системе и в БД создаётся
пользователь с именем `postgres` (суперпользователь). Все настройки доступа
к базе данных на уровне хоста располагаются в файле `/etc/postgresql/9.1/main/pg_hba.conf`.
Настройки для пользователя `postgres` по умолчанию имеют вид:

local all postgres peer

Это означает, что пользователь `postgres` (роль в БД) с локального хоста (на
котором установлен PostgreSQL) имеет доступ ко всем базам данных, используемый
метод аутентификации - `peer`. Суть этого метода заключается в том, что при
доступе к БД в качестве имени роли используется текущее имя пользователя
операционной системы.

Создание баз данных и пользователей
-----------------------------------

Обычно, работа с базами данных не осуществляется от имени `postgres` (из
соображений безопасности). Поэтому
создадим для наших задач на уровне БД отдельного пользователя, назовём его
`pguser` (пароль `topsecret`):

sudo su postgres -c "createuser -P -e pguser"
Введите пароль для новой роли:
Повторите его:
Должна ли новая роль иметь полномочия суперпользователя? (y - да/n - нет) n
Новая роль должна иметь право создавать базы данных? (y - да/n - нет) n
Новая роль должна иметь право создавать другие роли? (y - да/n - нет) n
CREATE ROLE pguser PASSWORD 'md53efb3c70124a58a615eb89f66d1a1497' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Мы создали пользователя, который не обладает правами суперпользователя,
не может создавать базы данных и другие роли
(см. [документацию](http://www.postgresql.org/docs/9.1/stat ... erole.html)).

От имени `postgres` создадим базу данных `dbvega` и сделаем пользователя `pguser`
её владельцем:

sudo su postgres -c "createdb -O pguser --encoding=UTF8 dbvega"


Доступ с локального хоста
-------------------------

Для доступа к PostgreSQL с локального хоста от имени любого пользователя, отличного
от `postgres` будем использовать парольный метод аутентификации `md5`. Для
этого в файле `pg_hba.conf` отредактируем соответствующую строку и приведём
её к виду:

local all all md5

Перезагружаем сервер:

sudo service postgresql restart

Теперь при подключении к СУБД необходимо явно указывать имя того пользователя
от которого осуществляется подключение:

psql -d dbvega -U pguser

После чего сервер будет автоматически запрашивать пароль пользователя.


Доступ с удалённых хостов
-------------------------

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

1. В файле `/etc/postgresql/9.1/main/postgresql.conf` раскомментировать
строку `listen_addresses = 'localhost'` и заменить её на
`listen_addresses = '*'`.

2. В файл `/etc/postgresql/9.1/main/pg_hba.conf` добавить строку вида:

host all all 10.22.0.0/24 md5

После чего перезапускаем сервер:

sudo service postgresql restart

Теперь с хостов, расположенных в подсети, указанной в файле `pg_hba.conf`
(`10.22.0.0/24`) к серверу можно подключиться, используя следующую команду:

psql -h 10.22.0.9 -d dbvega -U pguser

После ввода данной команды будет запрошен пароль пользователя `pguser`.
В данном примере PostgreSQL функционирует в подсети `10.22.0.0/24` по адресу
`10.22.0.9`.

[rhot]

Согласен с rhot - не хватает информации о том, как добавить функционал PostGIS в свою БД. Вот небольшая вырезка по этому вопросу из одной моей доки, мне кажется что-то подобное нужно включить в статью и обратить внимание на то, что в последних версиях PostGIS можно добавить в БД через экстеншены (я не пользовался ими, но это гораздо проще, чем запускать *.sql файлы)

Думаю, стоит всё же описать как это сделать на элементарном уровне, а потом уже упомянуть про всякие новые экстеншены.

[bolotoved]

Поскольку по сути мне сказать нечего, буду придираться к формальностям
Меня одного смущают иллюстрации? Они занимают весь экран и, например, текст между 1-й и 2-й иллюстрацией теряется. Может обрезать у картинок периферию с кнопками?

[rhot]

Хватило б одной картинки для иллюстрации интерфейса и всё.

[Denis Rykov]

Еще надо как-то обосновать для чего подключен сторонний репозитрий (нет нужной версии в официальном репозитории?) Еще судя по всему при описанном способе установки через экстеншены (как я понял вы подключаетесь к БД от имени postgres) владельцами таблиц geometry_columns и spatial_ref_sys становится именно пользователь postgres - в большинстве случаев владелец БД должен иметь доступ к этим таблицам, поэтому стоит сменить владельца на него.