В данной статье будем выполнять установку PostgreSQL 9.5 и PostGIS 2.2 на Ubuntu 14.04

Что такое PostgreSQL

Зачем вам может пригодится PostgreSQL. Это быстро развивающаяся open source база данных, легко расширяемая, имеет много полезных расширений в моем случае это PostGIS, поддерживает рекурсивные запросы, может хранить геоданные и обрабатывать их, имеет очень широкий спектр типов полей, таких как JSON поля, массивы, пользовательские типы, массивы помещенные в поля и т. д.. У PostgreSQL есть и недостатки, но меня интересуют его достоинства, а это в основном работа с пространственными данными через PostGIS.

Установка PostgreSQL

Устанавливаем PostgreSQL, так как из версии в версию может меняться формат базы данных и ее дамп, рекомендуется на всех серверах держать базу данных одной версии, в нашем случае, это будет PostgreSQL 9.5 и PostGIS 2.2.

Нужно подключить репозиторий с PostgreSQL для каждой версии Ubuntu и debian он свой и отличается по кодовому имени

  • Debian 7 (wheezy), 8 (jessie) and unstable (sid) 64/32 bit (amd64/i386)
  • Ubuntu 12.04 (precise), 14.04 (trusty), 15.10 (wily), 16.04 (xenial) 64/32 bit (amd64/i386)

Узнать кодовое имя своей системы можно выполнив в терминале: 

$ lsb_release -c

И так приступим. Нужно создать файл /etc/apt/sources.list.d/pgdg.list и прописать в нем адрес репозитория для PostgreSQL 

deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main

Если вы не знаете как это сделать можете выполнить все эти операции одной строкой 

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Создает файл источника репозитория для PostgreSQL с указанием в нем версии нашей операционной системы, в моем случае это Ubuntu 14.04 (trusty).

Устанавливаем необходимые компоненты для нашей установки 

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.5 pgadmin3

Мы импортировали ключ для работы с репозиторием. Обновляем список доступных программ. Проверяем что доступно для обновления, и обновляем. Устанавливаем postgresql-9.5 и pgadmin3 - графический инструмент для работы с PostgreSQL.

После установки, автоматически создается пользователь postgres, он нужен для работы с бд и для работы через консоль с помощью psql, postgres такой себе root для PostgreSQL.

Переключится в консоле под postgres можно двумя способами с помощью пароля пользователя postgres 

su postgres

или через root 

sudo -i -u postgres
exit   - вернутся в изначального пользователя

Как создать бд в PostgreSQL 

createdb test1
# подключится к этой бд
psql -d test1
# проверка подключения (psql)
\conninfo

Как создать таблицу

Создание таблицы схоже с MySQL (psql) 

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Просмотр имеющихся таблиц в БД

Для просмотра имеющихся таблиц в БД необходимо выполнить команду, (psql)

test1=# \dt
             Список отношений
 Схема  |    Имя     |   Тип   | Владелец 
--------+------------+---------+----------
 public | playground | таблица | postgres
(1 строка)

Добавление записей в таблицу

Пример вставки записей (psql) 

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');

Поигрались с PostgreSQL, работае? Ставим дальше)))

Установка PostGIS

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

sudo apt-get install postgresql-9.5-postgis-2.2 postgresql-contrib-9.5
# Install pgRouting package 
sudo apt-get install postgresql-9.5-pgrouting

Включаем Adminpack

Adminpack предоставляет ряд вспомогательных функций для обеспечения дополнительных функциональных возможностей. Для этого в терминале выполняем команы: 

# входим в psql под пользователем postgres
sudo -u postgres psql
# Включаем расширение adminpack
CREATE EXTENSION adminpack;

Время включить PostGIS, разработчика предупреждают, что нельзя в базе postgres включать расширение postgis, нужно создать пользовательскую базу и только в ней включать расширение postgis. 

Never install PostGIS in the postgres database, create a user database You can also enable the PostGIS extension here (or with the GUI as described below)

Как создать свою бд в psql

Создаем свою бд в psql: 

CREATE DATABASE gisdb;
# подключаемся к вновь созданной базе данных
\connect gisdb;
# включаем postgis
CREATE EXTENSION postgis;
# проверим как установилось
SELECT postgis_full_version();
# или 
SELECT postgis_version();

Результат будет такого типа: 

postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.2.1 r14555" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER
(1 row)

Подключаем pgRouting

pgRouting - это расширение, добавляющее функции роутинга и другие возможности сетевого анализа в базу данных PostGIS/PostgreSQL. 

CREATE  EXTENSION pgrouting;
SELECT * FROM pgr_version();

Выходим из psql консоли 

\q

Как открыть внешний доступ к серверу

Вам, возможно, потребуется изменить pg_hba.conf и / или pg_ident.conf, postgresql.conf, чтобы разрешить внешний доступ

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

в конце файла после локальных правил, добавьте 

hostssl    all             all             0.0.0.0/0               md5

Перезапустить сервер БД 

sudo service postgresql restart

Перезапуск сервера если у вас нет доступа к консоле сервера 

SELECT pg_reload_conf();

Проверка где находятся файлы конфигов

SELECT name, setting FROM pg_settings where category='File Locations';

Должно получится что то вроде этого 

       name        |                 setting                  
-------------------+------------------------------------------
 config_file       | /etc/postgresql/9.5/main/postgresql.conf
 data_directory    | /var/lib/postgresql/9.5/main
 external_pid_file | /var/run/postgresql/9.5-main.pid
 hba_file          | /etc/postgresql/9.5/main/pg_hba.conf
 ident_file        | /etc/postgresql/9.5/main/pg_ident.conf
(5 строк)

Включение PostGis расширений для новой базы

CREATE DATABASE new_db;
\connect new_db;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION pgrouting;

Теперь наша бд может работать с пространственными данными 

create table my_map(
    id bigserial primary key,
    name text,
    shape geometry
);
insert into  my_map( name , shape)  values  ( 'Центр СПб' , ST_Point(30.3250575, 59.9174455));

Всем удачи.