Иллюстрированный самоучитель по PostgreSQL. Основные команды PostgreSQL Скачиваем бинарники для Windows

Сейчас мы с Вами рассмотрим процесс установки новой версии СУБД 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.

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

Выпуск WordPress 5.3 улучшает и расширяет представленный в WordPress 5.0 редактор блоков новым блоком, более интуитивным взаимодействием и улучшенной доступностью. Новые функции в редакторе […]

После девяти месяцев разработки доступен мультимедиа-пакет FFmpeg 4.2, включающий набор приложений и коллекцию библиотек для операций над различными мультимедиа-форматами (запись, преобразование и […]

Новые функции в Linux Mint 19.2 Cinnamon

Linux Mint 19.2 является выпуском с долгосрочной поддержкой, который будет поддерживаться до 2023 года. Он поставляется с обновленным программным обеспечением и содержит доработки и множество новых […]

Вышел дистрибутив Linux Mint 19.2

Представлен релиз дистрибутива Linux Mint 19.2, второго обновления ветки Linux Mint 19.x, формируемой на пакетной базе Ubuntu 18.04 LTS и поддерживаемой до 2023 года. Дистрибутив полностью совместим […]

Доступны новые сервисные релизы BIND, которые содержат исправления ошибок и улучшения функций. Новые выпуски могут быть скачано со страницы загрузок на сайте разработчика: […]

Exim — агент передачи сообщений (MTA), разработанный в Кембриджском университете для использования в системах Unix, подключенных к Интернету. Он находится в свободном доступе в соответствии с […]

После почти двух лет разработки представлен релиз ZFS on Linux 0.8.0, реализации файловой системы ZFS, оформленной в виде модуля для ядра Linux. Работа модуля проверена с ядрами Linux c 2.6.32 по […]

В WordPress 5.1.1 устранена уязвимость, позволяющая получить контроль над сайтом

Комитет IETF (Internet Engineering Task Force), занимающийся развитием протоколов и архитектуры интернета, завершил формирование RFC для протокола ACME (Automatic Certificate Management Environment) […]

Некоммерческий удостоверяющий центр Let’s Encrypt, контролируемый сообществом и предоставляющий сертификаты безвозмездно всем желающим, подвёл итоги прошедшего года и рассказал о планах на 2019 год. […]

Вышла новая версия Libreoffice — Libreoffice 6.2

Все привет сегодня хочу сделать небольшую памятку про основные команды PostgreSQL . Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа – psql. Я уверен, что данный список окажется очень вам полезен и сэкономит время на поиск по разным ресурсам. Напоминаю, что это open-source проект, на основе СУБД Postgres, вышла в 1986 году, она разрабатывается всемирной группой разработчиков PGDG, по суте это 5-8 человек, но несмотря на это она, очень интенсивно развивается, вводя все новые функции и испраляя старые баги и ошибки.

Основные команды PostgreSQL в интерактивном режиме:

• \connect db_name – подключиться к базе с именем db_name
• \du – список пользователей
• \dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним
• \di – индексы
• \ds – последовательности
• \dt – список таблиц
• \dt+ - список всех таблиц с описанием
• \dt *s* - список всех таблиц, содержащих s в имени
• \dv – представления
• \dS – системные таблицы
• \d+ – описание таблицы
• \o – пересылка результатов запроса в файл
• \l – список баз данных
• \i – читать входящие данные из файла
• \e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)
• \d “table_name” – описание таблицы
• \i запуск команды из внешнего файла, например \i /my/directory/my.sql
• \pset – команда настройки параметров форматирования
• \echo – выводит сообщение
• \set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет).
• \? – справочник psql
• \help – справочник SQL
• \q (или Ctrl+D) – выход с программы

Работа с PostgreSQL из командной строки:

• -c (или –command) – запуск команды SQL без выхода в интерактивный режим
• -f file.sql - выполнение команд из файла file.sql
• -l (или –list) – выводит список доступных баз данных
• -U (или –username) – указываем имя пользователя (например postgres)
• -W (или –password) – приглашение на ввод пароля
• -d dbname - подключение к БД dbname
• -h – имя хоста (сервера)
• -s – пошаговый режим, то есть, нужно будет подтверждать все команды
• –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от; в конце конструкции SQL)
• -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c “CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);” - выполнение команды в базе dbname.

psql -d dbname -H -c «SELECT * FROM my» -o my.html - вывод результата запроса в html-файл.

Утилиты (программы) PosgreSQL:

• createdb и dropdb – создание и удаление базы данных (соответственно)
• createuser и dropuser – создание и пользователя (соответственно)
• pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
• postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
• initdb – создание новых кластеров PostgreSQL
• initlocation – программа для создания каталогов для вторичного хранения баз данных
• vacuumdb – физическое и аналитическое сопровождение БД
• pg_dump – архивация и восстановление данных
• pg_dumpall – резервное копирование всего кластера PostgreSQL
• pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

Pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

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

Pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

• -h host - хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
• -p port - порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
• -u - пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
• -a, -data-only - дамп только данных, по-умолчанию сохраняются данные и схема.
• -b - включать в дамп большие объекты (blog’и).
• -s, -schema-only - дамп только схемы.
• -C, -create - добавляет команду для создания БД.
• -c - добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
• -O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
• -F, -format {c|t|p} - выходной формат дампа, custom, tar, или plain text.
• -t, -table=TABLE - указываем определенную таблицу для дампа.
• -v, -verbose - вывод подробной информации.
• -D, -attribute-inserts - дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

Pg_dumpall > all.sql

Восстановление таблиц из резервных копий (бэкапов):

psql - восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore - восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

Psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

Psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

Pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

psql -U postgres -d mydb -f mydb

Я думаю база данных postgresql теперь будет более понятна вам. Надеюсь данный список команд PostgreSQLбыл для вас полезным.

Описана минимальная настройка PostgreSQL на Windows без необходимости установки БД. Запускается через bat-файл. Может работать с флэшки или переносного жесткого диска.

Скачиваем бинарники для Windows

Идем на сайт PosgreSQL в раздел Download/Windows и кликаем по ссылке zip archive в абзаце для Advanced users . Выбираем нужную версию и вперед.

Файлы из архива извлекаем в желаемую директорию, например:
D:\psql

Запуск PostgreSQL без установки

Bat-скрипт для запуска на Windows без установки:

@ECHO ON
@REM Set environment variables for PostgreSQL
@SET PATH=»%CD%\bin»;%PATH%
@SET PGDATA=%CD%\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@REM Create new DB with Russian encoding only at first run
@rem %CD%\bin\initdb -U postgres -A trust -E UTF8 —locale=russian_russia
@REM Create new DB with English encoding only at first run
%CD%\bin\initdb -U postgres -A trust -E UTF8 —locale=american_usa
@%CD%\bin\pg_ctl -D %CD%/data -l logfile start
@ECHO Press Enter to stop server
@pause
@%CD%\bin\pg_ctl -D %CD%/data stop

Скрипт кладем в корень директории, где лежит PostgreSQL, например:
D:\psql\pg.bat

Обновление версии PostgreSQL

Если необходимо обновить версию PostgreSQL, то надо совершить несколько простых шагов. Скачать архив с новой версией, извлечь его в отдельную директорию и инициализировать БД. Описано выше.

Подробно перенос данных описан . Если кратко, то необходимо запустить скрипт из директории bin новейшей версии:

cd D:\psql-new\bin
pg_upgrade.exe —username=postgres
—old-datadir «D:\pgsql-old\data»
—new-datadir «D:\pgsql-new\data»
—old-bindir «D:\pgsql-old\bin»
—new-bindir «D:\pgsql-new\bin»

Меняем локаль у PostgreSQL

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

Однако в Windows установка кодировки отличной от системной является загадкой, которую придется решить. Стандартные названия кодировок система не понимает, а которые понимает не так просто найти.

Для установки кодировки «en_US.UTF-8 american_usa » командой:
initdb -U postgres -A trust -E UTF8 —locale=american_usa

Для установки кодировки «ru_RU.UTF-8 » необходимо инициализировать бд с локалью «russian_russia » командой:
initdb -U postgres -A trust -E UTF8 —locale=russian_russia

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

Полезные команды

Ниже представлены SQL-запросы, которые необходимо вводить через командную строку PostgreSQL. Запускаем командой:
D:\pgsql\bin\psql —username=postgres

Или через графический интерфейс pgAdmin3:
D:\pgsql\bin\pgAdmin3.exe

Проверка версии:
select version();

Текущие процессы:
SELECT datname,pid,query FROM pg_stat_activity;

Создание базы данных:
CREATE DATABASE test_database;

Создание и изменение пользователя:
CREATE USER developer WITH password ‘123456’;
ALTER USER developer WITH password ‘654321’;

Сделать суперпользователем:
ALTER ROLE developer SUPERUSER;

Права пользователя:
GRANT ALL privileges ON DATABASE test_database TO developer;

Импорт из дампа:
gunzip -c test_database.sql.gz | psql test_database

Python

gunzip -c test_database.sql.gz | psql test_database -U user -h localhost

gunzip - c < em > test_database . sql . gz < / em > | psql < em > test_database < / em > - U < em > user < / em > - h < em > localhost < / em >

psql test_database test_user < dump_file.sql

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

Python

CREATE SEQUENCE post_ids; /* счетчик для индекса */ CREATE TABLE posts (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("post_ids"), title CHAR(64), content TEXT);