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

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

17 Авг. 2016, Шпаргалки, 5122 просмотров

PostgreSQL это кроссплатформенная полнофункциональная объектно-реляционная база данных.

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

Установка PostgreSQL

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

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

  1. Создаём файл /etc/apt/sources.list.d/pgdg.list
  2. Добавляем  в него следующее содержимое:
  3. Импортируем новый ключ для подписи, выполнив
  4. Устанавливаем последнюю версию:

Настройка

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

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

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

Этот пост — краткая инструкция для начинающих, для тех кто впервые установил PostgreSQL. Здесь вся необходимая информация для того, чтобы начать работу с PostgreSQL.

Подключение к СУБД

Первое, что нужно сделать — получить доступ к PostgreSQL, доступ в качестве суперпользователя.
Настройки аутентификации находятся в файле pg_hba.conf.

  1. # TYPE DATABASE USER ADDRESS METHOD
  2. local all postgres peer

Эта строка говорит о том, что пользователь postgres может подключаться к любой базе данных локальной СУБД PostgreSQL через сокет. Пароль при этом вводить не надо, операционная система передаст имя пользователя, и оно будет использовано для аутентификации.
Подключаемся:

  1. $ sudo -u postgres psql postgres postgres

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

  1. # TYPE DATABASE USER ADDRESS METHOD
  2. hostssl all all 0.0.0.0/0 md5

Метод аутентификации md5 означает, что для подключения придется ввести пароль. Это не очень удобно, если вы часто пользуетесь консолью psql. Если вы хотите автоматизировать какие-то действия, то плохая новость в том, что psql не принимает пароль в качестве аргумента. Есть два пути решения этих проблем: установка соответствующей переменной окружения и хранение пароля в специальном файле .pgpass.

Установка переменной окружения

Сразу скажу, что лучше этот способ не использовать, потому что некоторые операционные системы позволяют просматривать обычным пользователям переменные окружение с помощью ps. Но если хочется, то надо написать в терминале:

  1. export PGPASSWORD=mypasswd

Переменная будет доступна в текущей сессии. Если нужно задать переменную для всех сессий, то надо добавить строку из примера в файл .bashrc или .bash_profile

Хранение пароля в файле .pgpass

Если мы говорим о Linux, то файл должен находится в $HOME (/home/username). Права на запись и чтение должны быть только у владельца (0600). В файл нужно записывать строки вида:

  1. hostname:port:database:username:password

В первые четыре поля можно записать «*», что будет означать отсутствие фильтрации (полную выборку).

Получение справочной информации

\? — выдаст все доступные команды вместе с их кратким описанием,
\h — выдаст список всех доступных запросов,
\h CREATE — выдаст справку по конкретному запросу.

Управление пользователями СУБД

Как получить список пользователей PostgreSQL?

  1. \du

Или можно сделать запрос к таблице pg_user.

  1. SELECT*FROMpg_user;

Создание нового пользователя PostgreSQL

Из командной оболочки psql это можно сделать с помощью команды CREATE.

  1. CREATEUSERusernameWITHpassword’password’;

Или можно воспользоваться терминалом.

  1. createuser -S -D -R -P username

Ввод пароля будет запрошен.

Изменение пароля пользователя

  1. ALTERUSERusernameWITHPASSWORD’password’;

Изменение ролей пользователя

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

  1. ALTERROLEusernameWITHCREATEDB;

Управление базами данных

Вывод списка баз данных в терминале psql:

  1. \l

Тоже самое из терминала Linux:

  1. psql -l

Создание базы данных из psql (PostgreSQL Terminal)

  1. CREATEDATABASEdbnameOWNERdbadmin;

Создание новой базы данных при помощи терминала:

  1. createdb -O username dbname;

Настройка прав доступа к базе данных

Если пользователь является владельцем (owner) базы данных, то у него есть все права. Но если вы хотите дать доступ другому пользователю, то сделать это можно с помощью команды GRANT.

Запрос ниже позволит пользователю подключаться к базе данных. Но не забывайте о конфигурационном файле pg_hba.conf, в нем тоже должны быть соответствующие разрешения на подключение.

  1. GRANTCONNECTONDATABASEdbnameTOdbadmin;

Частые вопросы

Как узнать, существует ли база данных?

Check if database exists in postgreSQL using shell

Как узнать, существует ли пользователь?

Например, можно выполнить команду:

  1. psql postgres -tAc «SELECT 1 FROM pg_roles WHERE rolname=’USER_NAME'»

Если пользователь существует, будет возращена единица, иначе ничего не будет возвращено.

Дополнительная информация

PostgreSQL. Файл pg_hba.conf.

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

17 Авг. 2016, Шпаргалки, 5114 просмотров

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

Установка PostgreSQL

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

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

  1. Создаём файл /etc/apt/sources.list.d/pgdg.list
  2. Добавляем  в него следующее содержимое:
  3. Импортируем новый ключ для подписи, выполнив
  4. Устанавливаем последнюю версию:

Настройка

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

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

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

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

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

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

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

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

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

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

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

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

Наиболее популярной считается pgadmin3.

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

или

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

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

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

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

Запуск и завершение 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.

Добавить комментарий

Закрыть меню