Parus16.ru

Парус №16
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Создать базу данных Postgresql

Создать базу данных Postgresql

Прежде всего требуется подключиться к серверу по SSH.

Далее подключиться к базе от имени пользователя postgres (по умолчанию существует только он)

could not change directory to «/root»
psql (9.2.24)
Type «help» for help.

Если возникла ошибка, подобная приведенной выше — нужно перейти в любой каталог, в который postgres имеет доступ.

Оказавшись к консоли можно создать базу данных

CREATE DATABASE

Затем пользователя, которому присвоить пароль.

postgres=# CREATE USER appadmin WITH PASSWORD 'jkfdk8H0Indgshy6hk';

CREATE ROLE

Пользователи в PostgreSQL являются ролями. Для определенной базы созданному пользователю задаются нужные привилегии.

Например, полный доступ:

postgres=# GRANT ALL PRIVILEGES ON DATABASE app to appadmin;

GRANT

Также требуется скорректировать значения в одном из конфигурационных файлов

В файле содержится информация о методах аутентификации. Изначально работа с базой возможна только в случае если подключение выполняет системный пользователь с именем идентичным пользователю сервера баз данных.

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

В файле потребуется заменить в двух строках, начинающихся с host тип аутентификации с ident на md5. Это последнее значение в каждой строке.

# TYPE DATABASE USER ADDRESS METHOD

# «local» is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 ident
host all all ::1/128 md5

Для вступления в силу изменений нужно перезапустить сервис

Установка postgresql на Centos и Debian

Если postgresql на сервере еще нет, его нужно установить.

Centos 7

yum install postgresql-server postgresql-devel postgresql-contrib

Debian 9

После установки на CentOS требуется выполнить начальную настройку. Скрипт подобен mysql_secure_instalation в MySQL.

Initializing database … OK

Для Debian / Ubuntu сервис запускается автоматически, для Centos его нужно стартовать и добавить в автозагрузку

Если сайт, работающий с Postgresql переносится на другой сервер — требуется создать, а потом развернуть на другой машине дамп базы.

Призрак Басенджи

Потом переходим к вкладке “Columns”, и привязываем ключ к имени поля city таблицы, которую мы создали, выбрав “name”.

После этого мы увидим, что наш первичный ключ создан и добавлен в панель.

Теперь мы определили имя таблицы, имена столбцов, их типы и простое ограничение (первичный ключ) для предотвращения дублирования записей с одним и тем же городом во время добавления записи в таблицу. Результат нашей работы можешь можешь посмотреть, перейдя в обозреватель объектов и развернув таблицу cities.

4. Добавляем таблицы в БД через SQL Query Tool

Щелчок на значке SQL создаст еще одно окно, в котором SQL-запрос должен записываться.

Теперь перепишем вышеприведенный код SQL в окно «SQL Editor». Прежде чем выполнить команду, кликаем по кнопке «Execute pgScript», чтобы убедиться, что подключились к серверу нужной базы данных и база данных существует. В нашем случае это база данных «firstDB».

Если отсутствует соединение с б.д. firstDB, то можно прокрутить выпадающий список с базами данных, и в списке выбираем “<new connection>”. В появившемся диалоговом окне соединения к базам данных теперь можно выбрать подходящую базу данных.

Потом вернемся в обозреватель объектов и правой кнопкой мыши кликаем на узел “Tables”, в меню выбираем Refresh. Теперь можем видеть, что у нас появилась еще одна таблица Weather с 5 столбцами и с созданным первичным ключом.

Читайте так же:
Видео выживание под водой

5. Добавляем данные в таблицу

Тем не менее, это не является эффективным способом ввода данных. Это полезно для добавления простых тестовых данных. С другой стороны, мы можем использовать инструмент SQL Query еще раз, чтобы ввести данные в режиме batch. Ниже приводится команда SQL для вставки трех записей в таблицу weather.

Давай сотрем команды SQL в редакторе SQL, щелкнув на кнопку “clear edit window” на панели инструментов, и перепишем вышеприведенный код SQL в окно редактора SQL, и после этой работы кликнем по кнопке “Execute pgScript” для запуска команды.

Если открыть таблицу weather в обозревателе объектов, то увидим, что три записи уже добавлены.

6. Запрашиваем данные через графический Query Builder

Теперь мы можем сделать связь ( join ) между двумя таблицами, чтобы мы могли получить данные из обеих таблиц путем связывания поля name в таблице cities с полем city в таблице weather. Для создания связи ( join ) между отношениями, перетащим столбец (столбец “name” таблицы cities ) из одного отношения в другую (столбец “city” таблицы weather ).

Построение проекта Django с БД Postgre SQL и импорт данных из SQLite

Допустим, есть у нас такая ситуация, что проект на Django установлен а использованием локальной БД SQLite и нам необходимо это все экспортировать в реляционную БД на примере PostgreSQL.

Реляционные системы управления базами данных являются ключевым компонентом многих веб-сайтов и приложений. Они обеспечивают структурированный способ хранения, организации и доступа к информации.

PostgreSQL или Postgres — это система управления реляционными базами данных, которая обеспечивает реализацию языка запросов SQL. Это популярный выбор для многих небольших и крупных проектов и имеет то преимущество, что он совместим со стандартами и обладает множеством дополнительных функций, таких как надежные транзакции и параллелизм без блокировок чтения.

Установка и настройка PostgreSQL на Ubuntu

Репозитории Ubuntu по умолчанию содержат пакеты Postgres, поэтому их можно установить с помощью apt-системы. Перед этим обновим свой локальный индекс пакета. Затем установим пакет Postgres вместе с пакетом -contrib, который добавит некоторые дополнительные утилиты и функции

Командная строка терминала теперь будет начинаться с postgres@yourserver.

Теперь заходим в режим команд SQL и интерфейса PostgreSQL

После команды выше командная строка теперь будет начинаться с postgres=#.

Создаем базу данных для проекта, обратите внимание, что в режиме SQL запрос должен оканчиваться «;»

Создаем пользователя для БД проекта

Мы устанавливаем кодировку по умолчанию для UTF-8, которую ожидает Django. Мы также устанавливаем схему изоляции транзакций по умолчанию «read commit», которая блокирует чтение из незафиксированных транзакций. Наконец, мы устанавливаем часовой пояс. По умолчанию наши проекты Django будут настроены на использование UTC

Теперь все, что нам нужно сделать, это предоставить нашим пользователям права доступа к базе данных, которую мы создали

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

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

Настройка проекта Django на использование

PostgreSQL

Теперь, когда наша база данных настроена, мы можем установить Django. Для большей гибкости мы установим Django и все его зависимости в виртуальной среде Python

Создание и настройка виртуальной среды

Создаем виртуальную среду для хранения зависимостей Python проекта Django, набрав команду

Читайте так же:
Бесплатные видео заставки для ютуб

Это установит локальную копию Python и pip[pip3] в каталог myprojectenv

Прежде, чем создать проект Django нужно сначала активировать виртуальную среду, которую мы создали шагом выше

После этой команды строка запроса будет похожа на (myprojectenv)user@host.

В окружении проекта устанавливаем пакеты Django и psycopg2, который является бэкэндом для PostgreSQL

Замечение. Тут еще один момент, связанный с psycopg2. Начиная с версии 2.8 данный пакет будет переименован в psycopg2-binary и поэтому нужно дополнительно установить данный пакет с этим названием

В противном случае, может возникнуть предупреждение такого рода

Дополнительно про это можно узнать по ссылке.

Создание и настройка проекта Django

Теперь мы можем запустить проект Django в нашей директории myprojectenv. Это создаст дочерний каталог с именем проекта myproject , в котором будет еще одна папка с тем же названием для хранения самого кода и создаст сценарий управления в текущем каталоге. Не забудьте добавить точку в конце команды, чтобы она была правильно настроена

Теперь нам надо отредактировать файл настроек myproject/myproject/settings.py проекта на то, чтобы проект, вместо базы SQLite, который стоит по умолчанию начал работать с базой Postgre, который мы выше настроили и для этого открываем этот файл настроек и ищем там строки

и заменяем это все на

Сохраняем и закрываем этот файл.

Миграция баз данных на использование Postgre и запуск проекта

Теперь, когда настройки Django настроены, мы можем перенести наши структуры данных в нашу базу данных и протестировать сервер.

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

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

На этом интеграция и запуск закончена. Остается любоваться результатом через адрес сервера и порт 8001, на котором запущен наш проект Django с БД Postgree

Импорт данных из SQLite в PostgreSQL

в проекте Django

Окей. Выше мы рассмотрели, как интегрировать проект Django с Postgre, но что если в SQLite есть уже данные, которые нужно перенести в Postgre SQL?

Выгружаем существующие данные из SQLite, причем, в настройках settings.py нужно настроить на использование SQLite

Изменяем settings.py на использование Postgres backend. Выше мы это уже рассматривали как делать.

Убедитесь, что вы можете подключиться к PostgreSQL. Затем

Работаем с PostgreSQL из psql

Apr 11, 2016 23:51 · 2249 words · 11 minute read postgres перевод

Мне настолько понравилась эта статья, что я решил её перевести. Получилось весьма вольно, но сама суть сохранена.

Я уже больше 4 лет работаю с PostgreSQL. В начале использовал графические программы, сейчас же обхожусь только встроенными утилитами командной строки. Так получается более эффективно.

Этот пост разделён на несколько секций согласно задачам, которые можно решать с помощью psql:

Настройка psql

psql отлично настроена и из коробки, однако я бы хотел остановиться на 2х вещах, которые использую каждый день и которые делают psql более удобным инструментом. Первая по важности — получение более удобочитаемового вывода. По умолчанию psql старается представить данные как таблицу:

Пока ширина таблицы меньше ширины экрана всё нормально, но потом строки начнут переноситься и вывод станет просто отвратительным:

Чтобы избежать этого воспользуемся командой активации “расширенного дисплея” (expanded display):

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

Читайте так же:
Видеорегистратор угол обзора 170 градусов

Ещё одна хитрость: можно включать такой режим только когда он действительно нужен ( x auto ). В таком случае если таблица помещается по ширине, то будет табличный вывод, в противном случае — построчный.

Вторая возможность — указать как будет выводиться значение NULL. По умолчанию оно неотличимо от пустой строки.

Это можно исправить задав в psql символ для визуализации: pset null ¤ . Теперь можно быть уверенным, что если в строке пусто, то это пустая строка, а не NULL.

Хотел бы рассказать ещё об одной фиче psql. Если вам, как и мне, нравятся SQL выражения в ALL-CAPS, то это можно настроить в автодополнении командой set COMP_KEYWORD_CASE upper . Теперь, когда будете набирать SQL-запрос, последовательность sel<tab> будет автоматически преобразовываться в SELECT<space> .

Конечно, прописывать все эти команды каждый раз при запуске psql будет слишком утомительным, так что я предлагаю прописать их один раз в

/.psqlrc, который выполняется при каждой новой сессии psql.

Это лишь самая малость команд для настройки psql. Гораздо больше вы можете найти в th и psql’s doc.

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через h .

h без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис SELECT или ALTER TABLE . Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. h alter поможет в этом и предоставит более чем подробную информацию. psql использует пагинацию для большого вывода, так что там работает поиск. Наберите / + текст для поиска + <enter> и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно. Поискав NULL, находится вот такая команда: ALTER [ COLUMN ] column_name < SET | DROP >NOT NULL . Теперь можно составить нужную команду, не заглядывая в Google.

Как видите, h даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть ? .

Ваша песочница

В psql заданы настройки по умолчанию для подключения к базе данных. Чтобы подключиться к локальной БД достаточно набрать psql db_name . Вы можете не указывать даже имя базы данных. psql трактует это как подключение к БД с именем текущего пользователя. Это позволяет быстро попасть в psql-сессию.

Мне удобно бывает просто набрать psql и проверить синтаксис запроса или прочитать справку без необходимости соединения с какой-нибудь реальной БД.

Описание БД

psql имеет достаточное количество специальных команд, которые помогают проще ориентироваться в базе данных. Для списка таблиц наберите dt , для списка индексов — di , представлений — dv и т.д. Приятно, что все эти команды параметром принимают шаблон, по которому будет производиться фильтрация. Т.е. если вас интересуют только таблицы, начинающиеся на user, то выполните dt user* . Там, где я работаю, частенько используются схемы. Грубо говоря это пространство имён для таблиц. По умолчанию есть только одна схема public. Когда вы создаёте там таблицу foo, обратиться к ней можно будет через public.foo. В нашей же компании для каждого клиента используется своя схема. Иногда мне нужно проверить есть ли в схеме клиента определённая таблица (например, users). Для этого мне достаточно набрать dt *.users , и я получу список всех таблиц с указанием схем.

Читайте так же:
В чем преимущества электронной почты

Для получения информации о конкретном объекте таблицы или представления служит команда d . Она показывает следующие вещи,

  • список столбцов вместе с их типами и значениями по умолчанию
  • индексы
  • ограничения
  • внешние ключи
  • триггеры

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

Когда работаете со своими собственными функциями, то простого вывода сигнатуры оказывается мало. Тут на помощь приходит команда ef для редактирования. Передайте ей имя редактируемой функции в качестве первого параметра, и она откроется в $EDITOR. Если определение функции было задано с помощью CREATE OR REPLACE FUNCTION , то после сохранения и закрытия редактора она будет обновлена. Если же вам нужно только посмотреть описание функции, то закройте редактор с помощью ненулевого кода возврата (для vim это :cq ). Таким образом она не будет обновлена и выполнена. psql в таком случае понимает, что что-то пошло не так, и не будет ничего делать.

Построение запросов

psql превосходно подходит для построения небольших запросов, однако для многострочных и вложенных надо использовать более адекватный инструмент. Например, e — она открывает последний запрос в вашем любимом редакторе, который может уже обеспечить и подсветку синтаксиса, и автодополнение, и прочие полезные вещи. Для выполнения достаточно сохранить этот псевдо-файл и выйти (ну прям как с git). e удобно использовать для итеративного построения большого запроса.

В режиме такого редактирования можно также сохранить запрос в какой-нибудь отдельный файл на диске. Открыть же его можно с помощью команды i filename.sql . psql прочитает запрос из filename.sql и выполнит его.

e работает только с уже существующими файлами. Передавая имя в качестве аргумента, вы откроете файл в текстовом редакторе и выполните содержимое при выходе. Но как быть когда вы только начинаете писать новый запрос? Выходить из psql, создавать файл и возвращаться обратно слишком сложно. Можно выполнить команду оболочки прямо из psql с помощью ! , например, ! touch foo.sql .

i и e существенно упрощают работу над сложными sql запросами. Лично у меня открыто 2 окна side-by-side: один с vim, а другой с psql, где я выполняю только i query.sql . Переключаюсь — правлю, переключаюсь — выполняю. Прошли те времена, когда я копипастил из java-приложений с помощью незнакомых сочетаний горячих клавиш.

Сравнение вывода

Когда вы запускаете команду с помощью psql, результат оказывается в том же терминале. Команда o позволяет указать имя файла, куда будет записан вывод. o output.txt перенаправит вывод, а o вернёт всё обратно.

Ну и зачем это надо? Некоторые используют для экспорта данных (позже покажу способ получше). Я же использую o для сравнение чего-либо тяжёлого, например, планов выполнения с помощью ! vimdiff .

Теперь я могу просмотреть оба плана выполнения одновременно и легко выявить различия.

Клонирование базы данных

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

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

Читайте так же:
Видео гонки с рулем и педалями

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

Для восстановления удалим существующую и склонируем обратно.

Получение данных

Зачастую нам нужно поделиться данными из postgres с сотрудниками, руководством или клиентами, причём желательно в каком-нибудь удобочитаемом формате типа CSV или Excel. Вы уже подготовили запрос, свели всё в одну таблицу, осталось только куда-нибудь это выгрузить. Погуглив psql csv export, можно найти 2 способа. Первый более примитивный.

CSV фйалы представляют собой просто строки со значениями, которые разделены запятыми. Таким образом, указав psql как форматировать вывод, можно получить похожую структуру. Тут есть одна большая проблема — если ваши данные содержат запятую, то её нужно экранировать, а т.к. количество полей может быть огромным, то такой способ ведёт в тупик.

Правильным способом будет выгрузка в CSV с помощью мета-команды copy или SQL команды COPY . выглядит это примерно так:

COPY накладывает несколько ограничений. Во-первых, путь к файлу должен быть абсолютным. Во-вторых, вы можете писать только на локальную файловую систему. То есть подключиться к удалённой БД и выгрузить данные на локальный компьютер не получится. И тут на помощь приходит copy, которая представляет из себя всего лишь более удобную оболочку для COPY. Запрос выше можно переписать так:

Обратите внимание, что используется относительный путь до файла. Также можно задать и другие параметры CSV. Единственное ограничение состоит в том, что команда должна быть одной строкой. Причина в том, что окончанием выражения для команды с является перевод строки, а не точка с запятой. Это не касается e — там управление передаётся в редактор. Под капотом copy выполняет всё тот же COPY, перенаправляя вывод в STDOUT вместо файла. Далее psql забирает со STDOUT и записывает в локальный файл. Если вам нужно выгрузить данные в Excel, то убедитесь, что задали правильную кодировку. Он не дружит с UTF-8, так что лучше откатиться до latin1 (2015 год на дворе, я солидарен с негодованиями автора):

Если sql-запрос у вас находится в файле, то использовать его для команды copy не получится. Вам придётся скопировать всё тело запроса в выражение в команде, удалив переносы строк.

Наверно, я вам уже надоел, так что закругляюсь. Напоследок хотелось бы ещё рассказать про утилиту psql2csv. Она позволяет запускать запрос из файла и получать отформатированный CSV в STDOUT (который можно потом перенаправить в файл):

В случае запроса из файла:

Параметры вызова утилиты совпадают с параметрами psql. Вы можете заменить вызов psql на psql2csv, передав запрос в качестве аргумента, а на выходе получить валидный CSV. Но это ещё не всё — почему бы не подать вывод на вход какой-нибудь другой утилите?! psql2csv также принимает аргументы —no-header —encoding=ENCODING для совместимости с Excel.

Я уже очень привык работать с PostgreSQL через командную строку. Всё, что раньше делал в pgAdmin, можно делать и тут, причём быстрее. Я надеюсь, что эта статья убедила вас сделать psql основным инструментом для работы с PostgreSQL, показав удобство и гибкость.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector