РАБОТА З СУБД PostgreSQL

МІНІСТЕРСТВО ОСВІТИ, НАУКИ, МОЛОДІ ТА СПОРТУ УКРАЇНИ

НАЦІОНАЛЬНИЙ ТЕХНІЧНИЙ УНІВЕРСИТЕТ УКРАЇНИ

“КИЇВСЬКИЙ ПОЛІТЕХНІЧНИЙ ІНСТИТУТ”

 

 

РАБОТА З СУБД PostgreSQL

 

МЕТОДИЧНІ ВКАЗІВКИ

к вивченню та лабораторним роботам з дисципліни

"Обчислювальна техніка та програмування"

для студентів напряму

6.051001 - Метрологія та інформаційно-вимірювальні технології

 

 

Київ НТУУ "КПІ" 2011


Робота з СУБД PostgreSQL. Методичні вказівки до курсового проектування з дисципліни "Організація баз даних" для студентів напрямку 6.051001 - Метрологія та інформаційно-вимірювальні технології. / Укл. В.І. Павловський, Д.В. Победа, М.В. Харченко. - Чернігів: ЧДТУ, 2010.-50 с. Рос. мовою.

 

Гриф надано Методичною радою НТУУ "КПІ"

(Протокол № … від ….)

Навчальне видання

 

РОБОТА З СУБД PostgreSQL

МЕТОДИЧНІ ВКАЗІВКИ к вивченню та лабораторним роботам з дисципліни "Обчислювальна техніка та програмування"

ВВЕДЕНИЕ

PostgreSQL [12] имеет некоторые неприятные особенности!:

1. PostgreSQL чувствителен к регистру. Поэтому имена БД, таблиц, полей и значений полей в SQL следует записывать строго так, как они занесены в БД.

2. Имена БД, таблиц и полей следует заключать в двойные кавычки "…..", а строковые значения в одинарные кавычки '…'.

3. При программном доступе к БД в запросах – строковых переменных или константах, следует предусмотреть особенность представления символа ", например

String query = "SELECT * FROM \"BOOKS\" WHERE \"BookID\" < 5

AND \"BName\" = '10 минут на урок Windows 98'";

Установка PostgreSQL

Скачать эти версии можно с большого количества сайтов, например: http://www.postgresql.org/download/ http://postgresql.ru.net/download.html

СОЗДАНИЕ НОВОЙ БД

Запустите менеджер БД pgAdmin III с паролем, например qwerty. Имя пользователя postgres автоматически создавалось при развертывании PostgreSQL. Далее выполните соединение с сервером БД. Возможны 2-ва варианта:

ü с помощью меню Сервис (рисунок 2.1);

ü путем двойного нажатия мышью по имени сервера в окне Браузер объектов.

И в том, и в другом случае появится окно ввода пароля (рисунок 2.2).

 

 

Рисунок 2.1 – Меню Сервис

Рисунок 2.2 – Окно вода пароля

Создание новой БД

Примечание. Для последующих примеров будет использована демонстрационная БД «Деканат ВУЗа». Рисунок 2.3 – Контекстное меню раздела Базы

Создание новой таблицы

Аналогично новой БД, для новой таблицы необходимо указать имя, например Students, и владельца (postgres). Рисунок 2.6 – Контекстное меню раздела Таблицы

Определения первичного ключа таблицы

Рисунок 2.12 – Закладка Ограничения окна Новая таблица Примечание. Как видно из рисунка, помимо первичного ключа для таблицы можно указать внешний (вторичный) ключ, задать…

Особенности первичных ключей в PostgreSql

Работа с первичными ключами в PostgreSql имеет ряд особенностей. Мы рассмотрим некоторые из них. Следует подчеркнуть, что в PostgreSql первичный ключ может быть представлен… Если желательно, чтобы ключ был представлен реальным полем, то везде, где это возможно тип поля следует выбирать…

Внешние ключи и создание связей между таблицами

После того, как все таблицы БД созданы, необходимо связать их между собой. Для задания связей между таблицами используются первичные и внешние ключи. Для БД «Деканат ВУЗа» связь между таблицами Students и Groups задается с помощью первичного ключа таблицы Groups и внешнего ключа таблицы Students. Первичный ключ таблицы Groups к этому времени уже должен быть создан.

Ограничение внешнего ключа говорит, что значение в колонке (или группе колонок) одной таблицы должно полностью совпадать со значениями, которые существуют в некоторых строках другой таблицы. Для примера: у таблицы Students значения в колонке GrNo должны совпадать с соответствующими значениями в колонке GrNo таблицы Groups.

Чтобы создать внешний ключ нужно выбрать в окне Браузер объектов требуемую таблицу, например Students, активизировать ее контекстное меню и в нем выбрать пункт Свойства (рисунок 2.20).

Рисунок 2.20 – Контекстное меню объекта Таблица

После этого откроется окно характеристик выбранной таблицы (рисунок 2.21).

Рисунок 2.21 – Закладка Ограничения окна свойств таблицы

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

Рисунок 2.22 – Окно Новый внешний ключ. Закладка Свойства

Чтобы создать новый внешний ключ необходимо выполнить следующие действия:

− на закладке Свойства задать имя внешнего ключа, например, GrNoFK (FK от англ. Foreign Key – внешний ключ);

− выбрать из списка таблицу, на которую ссылается внешний ключ (свойство Ссылается);

− отметить свойство Совпадение полное, которое говорит о том, что значения внешнего ключа должны полностью совпадать со значениями первичного ключа;

− на закладке Колонки (рисунок 2.23) выбрать колонку исходной таблицы (Локальная колонка), которая определяет внешний ключ (для приведенного примера это колонка GrNo таблицы Students), и колонку, на которую ссылается внешний ключ (колонка GrNo таблицы Groups);

Рисунок 2.23 – Закладка Колонки окна Новый внешний ключ

− на закладке Действие (рисунок 2.24) выбрать действие, которое будет выполняться при изменении или удалении значения первичного ключа.

Рисунок 2.24 – Закладка Действие окна Новый внешний ключ

Возможные варианты действий:

а) NO ACTION – никаких дополнительных действий и ограничений;

б) RESTRICT – удаление/изменение значения первичного ключа запрещается, если на него ссылается какой-либо внешний ключ. Это действие установлено по умолчанию;

в) CASCADE – удаление строки в родительской таблице приводит к удалению всех связанных с ней строк дочерней таблицы (например, если удалить запись о группе в таблице Groups, то автоматически будут удалены все записи о ее студентах в таблице Students); изменение значения первичного ключа родительской таблицы приводит к соответствующему изменению значений внешних ключей дочерней таблицы (например, если изменить какое-либо значение в колонке GrNo таблицы Groups, то автоматически будут изменены связанные с ним значения колонки GrNo в таблице Students);

г) SET NULL – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение NULL всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы (например, если удалить/изменить значение в колонке GrNo таблицы Groups, то связанные с ним значения колонки GrNo в таблице Students автоматически будут установлены в NULL);

д) SET DEFAULT – удаление/изменение первичного ключа родительской таблицы приводит к установке в значение по умолчанию всех внешних ключей дочерней таблицы, которые ссылаются на удаленное/измененное значения первичного ключа родительской таблицы.

Чтобы увидеть результат изменений колонки, таблицы или БД необходимо выделить соответствующий объект в окне Браузер объектов и выполнить его обновление (рисунок 2.25).

Рисунок 2.25 – Обновление объекта БД

ВВОД ДАННЫХ В ТАБЛИЦУ

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

Рисунок 3.1 – Выбор таблицы для редактирования

После этого откроется окно редактирования данных выбранной таблицы (рисунок 3.2).

Рисунок 3.2 – Окно редактирования данных таблицы

Резервное копирование и восстановление БД

Резервное копирование (BackUp) БД

Рассмотрим процесс создания резервной копии средствами pgAdmin III, в котором поддерживается возможность создавать резервную копию (BackUp)… Ниже приводится описание процесса создания резервной копии БД Деканат ВУЗа. … 1. В окне Браузер объектов необходимо выбрать определенную БД, активизировать ее контекстное меню и в нем выбрать…

Восстановление (Restore) БД

Предварительное условие: Должна быть создана новая БД, например Deanery_Restore. 1. Выбрать в окне Браузер объектов новую БД, активизировать ее контекстное…

Создание SQL-дампа БД

Идея создания SQL-дампа БД заключается в создании текстового файла с командами SQL INSERT(см. подраздел 9.3), с помощью которых на сервере можно воссоздать БД в реляционной СУБД любого производителя, что обеспечивает перенос БД между разными СУБД.

Примечание 1. В принципе pgAdmin III предусматривает создание SQL‑дампа, но на практике возникают некоторые ошибки при его восстановлении. С другой стороны менеджер PgMaestro хорошо выполняет работу по созданию и восстановлению SQL‑дампа, который восстанавливается и pgAdmin III.

Восстановление SQL-дампа средствами pgAdmin III

Вызвать окно SQL–запроса.

Рисунок 9.8 – Вызов окна SQL–запроса

Выполнить поиск файла с SQL-дампом

Рисунок 9.9 – Выбор файла SQL-дампа

Открыть файл с SQL-дампом

Рисунок 9.10 – Открытие файла SQL-дампа

Рисунок 9.11 – Восстановление SQL-дампа

Создание дампа с помощью утилит pg_dump или pg_dumpall

Утилита pg_dump представляет собой обычное клиентское приложение PostgreSQL. Пример ее использования может выглядеть следующим образом: pg_dump… pg_dump позволяет выполнять процедуру резервного копирования БД с любого… Чтобы указать, к какому серверу следует обратиться pg_dump, в командной строке используются опции -h host и -p port. В…

Восстановление SQL-дампа БД

где infile - это имя файла (или путь к нему), который указывался в качестве параметра outfile команды pg_dump. Замечание: При запуске этой команды не создаётся БД dbname, поэтому её… Для psql подобно pg_dump можно задать сервер БД, с которым следует устанавливать соединение, и имя пользователя, под…

ОСОБЕННОСТИ ВЗАИМОДЕЙСТВИЯ СУБД Access И PostgreSQL

Ниже приведено описание удобного способа копирования таблиц Access в таблицы PostgreSQL, а также возможности подключения таблиц PostgreSQL к таблицам Access.

Предварительное условие: Драйвер PostgreSQL ODBC должен быть инсталлирован.

Подготовка соединения PostgreSQL с БД в Access

Рисунок 10.1 – Окно Администрирование 2. Создать пользовательский или системный DSN. Совет: Для индивидуального компьютера можно выбрать панель…

Копирование таблиц Access в таблицы PostgreSQL

Предварительные условия: 1. Драйвер PostgreSQL ODBC должен быть инсталлирован и должен быть создан… 2. В PostgreSQL должна быть создана БД decanat, в которую будут копироваться таблицы.

Подключение таблиц PostgreSQL к БД в Access

Предварительное условие: Драйвер PostgreSQL ODBC должен быть инсталлирован и Пользовательский DSN или Системный DSN должен быть создан. Для подключения к БД Access таблиц PostgreSQL следует: 1. Выполнить Файл>Внешние данные>Связь с таблицами (рисунок 10.11).

Характеристики PostgreSQL

http://www.scribd.com/doc/4951927/PostgreSQL-7

 

http://postgresql.ru.net/docs/overview.html#langs

 

Размер базы данных

Максимальный размер БД Неограничен
Максимальный размер таблицы 32 TB
Максимальный размер записи (строки) в таблице 1.6 TB
Максимальный размер поля в записи (строке) 1 GB
Максимальное количество записей (строк) в таблице не ограничено
Максимальное количество полей (колонок) в таблице 250 - 1600 в зависимости от типа данных в колонке
Максимальное количество индексов на таблицу не ограничено

Поддерживаемые встроенные типы данных

  Денежные типы money для хранения денежных значений  

АДМИНИСТРИРОВАНИЕ PostgreSQL

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

В предыдущих версиях PostgreSQL это можно было сделать через меню Пуск.

Рисунок 12.1 – Запуск/останов сервиса PostgreSQL в предыдущих версиях

В 9 версии PostgreSQL эту службу можно активировать и дезактивировать, используя Диспетчер задач.

Рисунок 12.2 – Останов службы PostgreSQL в версии 9

По различным вопросам администрирования и, в частности, управления пользователями см. [11].

ЛИТЕРАТУРА

1. http://postgresql.ru.net/manual/tutorial-createdb.html

2. http://postgresql.ru.net/postgis/ch04_5.html

3. http://postgresql.ru.net/docs/overview.html#index

4. http://www.scribd.com/doc/4846380/-GIN-GiST-PostgreSQL

5. http://postgresql.ru.net/manual/gist.html

6. http://postgresql.ru.net/manual/gin.html

7. http://postgresql.ru.net/manual/sql-createfunction.html

8. http://www.postgresql.org/docs/8.3/static/plpgsql.html

9. http://www.postgresql.org/docs/8.1/static/sql-grant.html

10. http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html

11. http://www.opennet.ru/links/info/685.shtml

12. http://www.citforum.ru/database/postgres/what_is/

13. http://www.nestor.minsk.by/kg/2003/48/kg34802.html

14. http://ru.wikipedia.org/wiki/PostgreSQL#.D0.A4.D1.83.D0.BD.D0.BA.D1.86.D0.B8.D0.B8

15.