Начало работы с PostgreSQL - Видеоуроки. Менеджеры по работе с базой

Get Started With PostgreSQL

Duration 00:41:44

Get Started With PostgreSQL - Полный список уроков

Развернуть / Свернуть

Урок 1. Create a Postgres Table 00:01:45
Урок 2. Insert Data into Postgres Tables 00:04:24
Урок 3. Filter Data in a Postgres Table with Query Statements 00:03:35
Урок 4. Update Data in Postgres 00:01:55
Урок 5. Delete Postgres Records 00:02:43
Урок 6. Group and Aggregate Data in Postgres 00:06:45
Урок 7. Sort Postgres Tables 00:01:20
Урок 8. Ensure Uniqueness in Postgres 00:03:53
Урок 9. Use Foreign Keys to Ensure Data Integrity in Postgres 00:02:18
Урок 10. Create Foreign Keys Across Multiple Fields in Postgres 00:03:08
Урок 11. Enforce Custom Logic with Check Constraints in Postgres 00:02:07
Урок 12. Speed Up Postgres Queries with Indexes 00:02:33
Урок 13. Find Intersecting Data with Postgres_ Inner Join 00:04:26
Урок 14. Select Distinct Data in Postgres 00:00:52

Курс «Get Started With PostgreSQL» заставить вас сказать что вы «знаете SQL» - создание таблиц, вставки, выборки, обновления, удаления, агрегации, индексы, объединения и ограничения. По пути мы будем моделировать проблемы реального мира, чтобы вы могли увидеть, насколько мощный PostgreSQL!

24-04-2016 30-11--0001 ru 15 уроков

Если Вы начали осваивать SQL, то в процессе изучения Вам предстоит столкнуться со множеством вопросов и непонятных моментов, ответы на которые подготовил данный видеокурс. В процессе обучения будут разобраны такие темы, как: создание базы данных, ее изменение и удаление, оператор вставки INSERT, использование запроса SELECT и конструкций WHERE, операторы UPDATE и DELETE, создание различных связей между таблицами с использованием операторов...

Duration 01:26:19

24-04-2016 30-11--0001 ru 9 уроков

Duration 08:50:57

17-06-2018 30-11--0001 ru 6 уроков

Курс СУБД PostgreSQL состоит из 6 уроков, рассчитан для новичков, которые впервые встречают такое понятием как СУБД. Курс включает в себя как теоретическую, так и практическую часть. На данном курсе учащиеся спроектируют небольшую базу данных сети продуктовых магазинов, определят необходимую структуру. Функционал (индексы, представления, триггеры, функции). После прохождения курса, учащиеся будут понимать принципы проектирования БД,...

Duration 03:05:26

28-11-2018 12-09-2018 en 164 урока

Создайте 9 проектов - освойте две основные и современные технологии в Python и PostgreSQL. Всегда хотели узнать один из самых популярных языков программирования на планете? Почему бы не изучить два из самых популярных одновременно? Python и SQL используются многими технологическими компаниями, малыми и большими. Это потому, что они мощные, но чрезвычайно гибкие.

Duration 21:53:10

27-12-2018 ru 10 уроков

Данный курс предназначен для изучения основ SQL: теоретических основ реляционной модели, операций реляционной алгебры, правил и назначение нормализации, использования ER диаграммы для моделирования предметной области, практического использования всех операторов SQL (операторов определения данных (Data Definition Language, DDL): CREATE, ALTER, DROP; манипуляции данными (Data Manipulation Language, DML):...

Duration 05:23:59

Последнее добавленное

ru 27-03-2019

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

en 25-03-2019

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

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

System Administration

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

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

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

local all postgres peer

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

$ sudo -u postgres psql postgres postgres

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

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

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

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

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

export PGPASSWORD=mypasswd

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

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

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

hostname:port:database:username:password

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

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

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

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

Как получить список пользователей PostgreSQL? Или можно сделать запрос к таблице pg_user.

SELECT * FROM pg_user ;

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

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

CREATE USER username WITH password "password" ;

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

createuser -S -D -R -P username

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

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

ALTER USER username WITH PASSWORD "password" ;

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

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

ALTER ROLE username WITH CREATEDB ;

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

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

psql -l

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

CREATE DATABASE dbname OWNER dbadmin ;

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

createdb -O username dbname;

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

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

GRANT CONNECT ON DATABASE dbname TO dbadmin ;

Сейчас мы с Вами рассмотрим процесс установки новой версии СУБД PostgreSQL 9.5 на операционную систему Windows 7 , а также узнаем, что нового появилось в этой версии и где ее можно скачать.

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

Что такое PostgreSQL?

PostgreSQL - это бесплатная и популярная во всем мире объектно-реляционная система управления базами данных (СУБД). PostgreSQL реализована для многих операционных систем, например, таких как: Linux, Solaris, Windows. В ней используется процедурное расширение языка SQL PL/pgSQL.

Что нового в PostgreSQL 9.5?

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

INSERT, ON CONFLICT UPDATE (UPSERT) – данная возможность позволяет обработать ситуацию, когда осуществить добавления данных через INSERT нельзя, например, из-за нарушения уникальности или недопустимости значения в одном из полей. Другими словами, вместо того чтобы выдавать ошибку можно просто пропустить выполнение оператора, т.е. ничего не делать или изменить связанные с ключевым полем данные, т.е. в случае, когда запись уже существует, вместо INSERT выполнить UPDATE;
ROLLUP, CUBE и GROUPING SETS – эти операторы расширяют возможности GROUP BY, а именно позволяют формировать отчеты, т.е. писать запросы, в которых можно добавлять строки с промежуточным и общим итогом, а также объединять результаты нескольких группировок в один набор данных. В Microsoft SQL Server аналогичные операторы существуют уже достаточно давно, и мы с Вами их подробно рассматривали вот в этом материале ;
Row-Level Security (RLS) – это так называемая «Политика безопасности на уровне строк » т.е. теперь можно ограничивать доступ к данным в таблице;
BRIN-индексы – это новый тип индексов, который предназначен для обработки очень больших таблиц, в которых определенные столбцы естественным образом отсортированы;
Ускоренная сортировка – в PostgreSQL 9.5 при сортировке теперь используется так называемый алгоритм «сокращенных ключей », который позволяет в несколько раз быстрей сортировать текстовые данные и данные типа NUMERIC.

Более подробно обо всех нововведениях в PostgreSQL 9.5 можете посмотреть на странице в «PostgreSQL Wiki » вот она What"s new in PostgreSQL 9.5 .

Где скачать PostgreSQL 9.5 для Windows 7?

После того как Вы перешли на страницу необходимо нажать «Download »

Затем Вас перенаправят на страницу выбора версии PostgreSQL, в нашем случае мы в разделе «Version 9.5.0 » выбираем «Win x86-32 » для 32 разрядной Windows или «Win x86-64 » для 64 разрядной. У меня 32 разрядная Windows 7, поэтому я и щелкаю по кнопке «Win x86-32».

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

Установка PostgreSQL 9.5 на Windows 7

Итак, давайте перейдем к установке PostgreSQL 9.5, мы, кстати, с Вами ранее уже рассматривали установку PostgreSQL, например, для начинающих я показывал, как можно установить PostgreSQL на Linux OpenSUSE 13.2 , а в материале «Установка PostgreSQL 9.4 на CentOS 7.1 » мы разбирали, как можно реализовать так называемый сервер СУБД с использованием PostgreSQL и операционной системы Linux на примере дистрибутива CentOS 7.1. Установку PostgreSQL на операционную систему Windows мы не рассматривали, поэтому сегодня мы и будем устанавливать PostgreSQL 9.5 на Windows 7.

Шаг 1

Запускаем скаченный файл (postgresql-9.5.0-1-windows.exe ). В итоге запустится программа установки и первое окно это окно «Приветствия », жмем «Next ».

Шаг 2

В следующем окне мы указываем каталог, в который необходимо установить PostgreSQL, я оставлю по умолчанию (т.е. на диск C ), жмем «Next ».

Шаг 3

Далее указываем каталог, в котором по умолчанию будут располагаться файлы баз данных. Другими словами здесь при установке Вы можете изменить директорию для хранения файлов баз данных, это может потребоваться в тех случаях, когда количество баз данных будет большое или их размер, т.е. объем будет значительным, как Вы понимаете в этих случаях лучше указать отдельный диск достаточного объема. В моем случае это тестовая установка, поэтому я оставлю по умолчанию, жмем «Next ».

Шаг 4

Затем придумываем, вводим и запоминаем пароль для пользователя postgres (необходимо вводить один и тот же пароль в два поля, для подтверждения ), именно этот пользователь является своего рода «Суперпользователем » т.е. главным администратором, жмем «Next ».

Шаг 5

Шаг 6

Потом указываем Locale (данный параметр определяет кодировку данных в базе ), если оставить по умолчанию, то кодировка данных в базах данных будет UTF-8, но если Вам необходимо чтобы кодировка была другая допустим Windows-1251, то необходимо выбрать «Russia, Russia », я оставлю по умолчанию, т.е. «Default locale », жмем «Next ».

Шаг 7

Все готово для установки, жмем «Next ».

Установка началась, процесс будет длиться буквально пару минут.

Шаг 8

Установка будет завершена и на последнем окне нам предложат запустить инструмент (Stack Builder ) для установки дополнительных компонентов PostgreSQL, если Вы не хотите ничего дополнительного устанавливать, необходимо снять соответствующую галочку, жмем «Finish ».

Запускаем pgAdmin и проверяем работу PostgreSQL 9.5

В состав инсталлятора PostgreSQL 9.5 включен pgAdmin 1.22.0, т.е. дополнительно, отдельно устанавливать pgAdmin на компьютер, на который Вы устанавливаете PostgreSQL 9.5, не нужно.

Если кто не знает pgAdmin это специальная графическая среда программирования и администрирования для СУБД PostgreSQL.

Для того чтобы запустить pgAdmin нажимаем «Пуск ->Все программы ->PostgreSQL 9.5 ->pgAdmin III »

У нас уже будет отображен локальный сервер мы к нему и должны подключиться, щелкаем двойным кликом по «PostgreSQL 9.5 (localhost:5432) ».

Затем необходимо ввести пароль, который мы придумали, когда устанавливали PostgreSQL 9.5 (чтобы не вводить пароль каждый раз можете поставить галочку «Сохранять пароль» ). Жмем «ОК ».

Если галочку «Сохранять пароль » Вы поставили, то далее появится окно с предупреждением того, что сохранение паролей небезопасно, так как пароль будет храниться в виде простого текста в файле pgpass.conf который расположен в директории профиля пользователя Windows. Жмем «ОК ».

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

На этом у меня все, удачи!

Знание – сила, и набор материалов по PostgreSQL тому подтверждение. Представляем книги и курсы, с которыми полнофункциональная СУБД станет доступной.

Эта книжка-малышка доступна в электронном и бумажном вариантах. Но важно другое: книга собрала необходимый костяк. Здесь представлена информация о кроссплатформенности, запросах, полнотекстовом поиске и о многом другом. Книгу можно смело назвать «От А до Я». Установкой и настройкой открытой СУБД на разных ОС книга не ограничивается, поэтому будьте готовы к первой практике.

2. Администрирование PostgreSQL. Базовый курс

Начинается просто: введение, история, форки и т. д. Дальше – больше: архитектура, включающая в себя разделы о структуре памяти, многоверсионности и расширяемости системы, создание БД из шаблона, табличные пространства, системный каталог, схемы, холодное и горячее резервирование. Это настоящий курс, наполненный слайдами, практическими примерами и видео-уроками.

3. Администрирование PostgreSQL. Расширенный курс

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

4. Hacking PostgreSQL. Курс для разработчиков СУБД

8 лекций и море новых знаний. Здесь есть как общие сведения о подсистемах, так и подробный разбор инструментов разработчика, расширяемости, исходного кода, физического представления данных, разделяемой и локальной памяти, а также устройства экзекутора и планировщика запросов. Лекции сопровождаются обратной связью «вопрос/ответ», примерами и картинками.

5. Документация СУБД PostgreSQL на русском языке

Без нее никуда. Наиболее лаконичная и исчерпывающая информация, которая должна быть у каждого, кто работает со свободной объектно-реляционной СУБД. Только актуальные обновляемые версии.

Tutorial

Мне хотелось создать прекрасный объемлющий мануал Getting Start без всякой воды, но включающий основные плюшки для начинающих по системе PostgreSQL в Linux.

PostgreSQL является объектно-реляционной системой управления базами данных (ОРСУБД) на основе POSTGRES, версия 4.2 , разработанной в Университете Калифорнии в Беркли департаменте компьютерных наук.

PostgreSQL является open source потомком оригинального кода Berkeley. Он поддерживает большую часть стандарта SQL и предлагает множество современных функций:

Cложные запросы
Управление конкурентным доступом с помощью многоверсионности

Кроме того, PostgreSQL может быть расширен пользователем во многих отношениях, например, путем добавления новых

типов данных
функций
операторов
агрегатных функций
индекс методов
процедурных языков

Сборка и установка

Как и все любители мейнстрима PostgreSQL мы будем конечно же собирать, а не скачивать готовые пакеты (в репозитариях Debian, например, нет последней версии). Вот лежит множество версий, скачивать конечно же лучше всего последнюю. На момент написания поста это версия 9.2.2

Wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz tar xzf postgresql-9.2.2.tar.gz
Теперь у нас есть директория с исходниками сей прекрасной базы данных.
По умолчанию файлы базы будут установлены в директорию /usr/local/pgsql, но эту директорию можно изменить задав

Prefix=/path/to/pgsql
перед командой./configure
Перед сборкой можно указать компилятор С++

Export CC=gcc
PostgeSQL может использовать readline библиотеку, если у вас её нет и нет желания её ставить просто укажите опцию

Without-readline
Надеюсь у всех есть Autotools ? Тогда вперед к сборке:

Cd postgresql-9.2.2 ./configure --without-readline sudo make install clean
Все господа! Поздравляю!

Настройка

Нам необходимо указать хранилище данных наших баз данных (кластер) и запустить её.

Есть один нюанс - владельцем директории данных и пользователь, который может запускать базу должен быть не root. Это сделано в целях безопасности системы. Поэтому создадим специального пользователя
sudo useradd postgres -p postgres -U -m
И далее все понятно

Sudo chown -R postgres:postgres /usr/local/pgsql
Важный процесс. Мы должны инициализировать кластер баз дынных. Сделать мы должны это от имени пользователя postgres

Initdb -D /usr/local/pgsql/data
Теперь нужно добавить запуск PostgreSQL в автостарт. Для этого существует уже готовый скрипт и лежит он в postgresql-9.2.2/contrib/start-scripts/linux
Этот файл можно открыть и обратить внимание на следующие переменные:

prefix - это место куда мы ставили PostgreSQL и задавали в./configure
PGDATA - это то, где хранится кластер баз данных и куда должен иметь доступ наш пользователь postgres
PGUSER - это тот самый пользователь, от лица которого будет все работать

Если все стоит верно, то добвляем наш скрипт в init.d

Sudo cp ./postgresql-9.2.2/contrib/start-scripts/linux /etc/init.d/postgres sudo update-rc.d postgres defaults
Перезапускам систему, чтобы проверить что наш скрипт работает.
Вводим

/usr/local/pgsql/bin/psql -U postgres
И если появится окно работы с базой, то настройка прошла успешно! Поздравляю!
По умолчанию создается база данных с именем postgres

# TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host all all::1/128 trust
Первая строка отвечает за локальное соединение, вторая - за соединение про протоколу IPv4, а третья по протоколу IPv6.
Самый последний параметр - это как раз таки метод авторизации. Его и рассмотрим (только основные)

trust - доступ к базе может получить кто угодно под любым именем, имеющий с ней соединение.
reject - отклонить безоговорочно! Это подходит для фильтрации определенных IP адресов
password - требует обязательного ввода пароля. Не подходит для локальных пользователей, только пользователи созданные командой CREATE USER
ident - позволяет только пользователем зарегистрированным в файле /usr/local/pgsql/data/pg_ident.conf устанавливать соединение с базой.

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

Утилиты для работы с базой

pg_config

Возвращает информацию о текущей установленной версии PostgreSQL.

initdb

Инициализирует новое хранилище данных (кластер баз данных). Кластер представляет собой совокупность баз данных управляемых одним экземпляром севера. initdb должен быть запущен от имени будущего владельца сервера (как указано выше от имени postgres).

pg_ctl

Управляет процессом работы сервера PostgreSQL. Позволяет запускать, выполнять перезапуск, останавливать работу сервера, указать лог файл и другое.

psql

Клиент для работы с базой дынных. Позволяет выполнять SQL операции.

createdb

Создает новую базу данных. По умолчанию, база данных создается от имени пользователя, который запускает команду. Однако, чтобы задать другого - необходимо использовать опцию -O (если у пользователя есть необходимые привилегии для этого). По сути - это обертка SQL команды CREATE DATABASE.

dropdb

Удаляет базу данных. Является оберткой SQL команды DROP DATABASE.

createuser

Добавляет нового пользователя базы дынных. Является оберткой SQL команды CREATE ROLE.

dropuser

Удаляет пользователя базы данных. Является оберткой SQL команды DROP ROLE.

createlang

Добавляет новый язык программирования в базу PostgreSQL. Является оберткой SQL команды CREATE LANGUAGE.

droplang

Удаляет язык программирования. Является оберткой SQL команды DROP LANGUAGE.

pg_dump

Создает бэкап (дамп) базы данных в файл.

pg_restore

Восстанавливает бэкап (дамп) базы данных из файла.

pg_dumpall

Создает бэкап (дамп) всего кластера в файл.

reindexdb

Производит переиндексацию базы данных. Является оберткой SQL команды REINDEX.

clusterdb

Производит перекластеризацию таблиц в базе данных. Является оберткой SQL команды CLUSTER.

vacuumdb

Сборщик мусора и оптимизатор базы данных. Является оберткой SQL команды VACUUM.

Менеджеры по работе с базой

Что касается менеджера по работа с базой, то есть php менеджер - это