Шпаргалка по PostgreSQL

PostgreSQL это кроссплатформенная полнофункциональная объектно-реляционная база данных. Эта небольшая заметка написана с целью собрать в одном месте небольшой туториал по установке и настройке этой СУБД. Действие происходит в ОС Ubuntu 16.04.

Установка PostgreSQL

По умолчанию PostgreSQL входит в пакет Ubuntu. Систему можно установить следующей командой:

sudo apt-get install postgresql-9.4

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

1. Создаём файл /etc/apt/sources.list.d/pgdg.list
2. Добавляем  в него следующее содержимое:

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

3. Импортируем новый ключ для подписи, выполнив

   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
     sudo apt-key add -
   sudo apt-get update
   

4. Устанавливаем последнюю версию:

   apt-get install postgresql-9.5

Настройка

После успешной установки самоё время заняться базовой настройкой СУБД PostgreSQL.

По умолчанию доступ к СУБД имеет пользователь postgres. Заходим под ним:

sudo -u postgres psql

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

CREATE DATABASE mydatabase;

Создаём отдельного пользователя с логином myuserи паролем password:

CREATE USER myuser WITH password 'password';

Чтобы дать полные привилегии над нашей новой базой данных mydatabase пользователю myuser выполняем:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Чтобы сделать нового пользователя администратором нашей СУБД, выполняем:

ALTER USER myuser WITH SUPERUSER;

Чтобы лишить его ранее предоставленных полномочий, выполним:

ALTER USER myuser WITH NOSUPERUSER;

Если пользователю нужно дать лишь права на создание новой БД (например, при запуске тестов в Django, фреймворк создаёт отдельную БД с префиксом test_):

ALTER USER myuser CREATEDB;

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

psql -h localhost mydatabase myuser

Для выхода из сеанса, необходимо выполнить команду \q

Фишки при работе с PostgreSQL

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

sudo apt-get install pgadmin3

Для тех, кто всё же предпочитает оставаться в терминале, есть интересный скрипт, написанный на Python: pgcli. Его особенностью является автодополнение и подсветка синтаксиса SQL команд.

pip install pgcli
pgcli mydatabase

или

pgcli postgres://myuser:password@localhost:5432/mydatabase

Просто и удобно.

Снятие дампа БД PostgreSQL можно сделать вот так:

pg_dump -c -h localhost -U myuser mydatabase > ./mydump.sql

Импорт в PostgreSQL делается следующим образом:

psql -h localhost -U username dbname < dump.sql

PostgreSQL масштабная и сложная СУБД, и для её настройки потребуется немало времени, если вы столкнулись с ней в работе впервые. Эта заметка лишь начало интересного пути. Постараюсь делиться опытом и знаниями по мере возможности и сил :-)