Настройка и начало работы с PostgreSQL
В прошлый раз я познакомил тебя с основами реляционных баз данных и SQL. Теперь пришло время настроить рабочую среду. Ты будешь использовать PostgreSQL в качестве своей СУБД, поэтому для начала ее нужно скачать и установить.
Загрузка и установка PostgreSQL
PostgreSQL поддерживает все основные операционные системы. Процесс установки прост, поэтому я постараюсь рассказать о нем как можно быстрее.
Для Windows и Mac ты можешь загрузить установщик с веб-сайта EDB.
EDB больше не предоставляет пакеты для систем GNU/Linux. Вместо этого они рекомендуют вам использовать диспетчер пакетов твоего дистрибутива.
Установщики включают в себя разные компоненты.
Вот самые важные из них:
- Сервер PostgreSQL (очевидно)
- pgAdmin, графический инструмент для управления базами данных
- Менеджер пакетов для загрузки и установки дополнительных инструментов и драйверов
Windows
Скачав установщик, запусти его как любой другой исполняемый файл. Процесс довольно прямолинеен, но некоторые вещи все же заслуживают внимания.
Диалоговое окно «Выбрать компоненты» позволяет выборочно устанавливать компоненты. Если у тебя нет веской причины что-то менять — оставляй все как есть.
По умолчанию PostgreSQL создает суперпользователя с именем postgres (воспринимай его как учетную запись администратора сервера базы данных).
Во время установки тебе нужно будет указать пароль для суперпользователя (root).
Позже ты сможешь создать других пользователей и назначать им отдельные доступы и роли. Мы вернемся к этому позже, а сейчас тебе понадобится учетная запись суперпользователя, чтобы начать использовать СУБД.
Чтобы запустить сервер разработки на твоем компьютере или localhost , необходимо назначить ему порт.
Порт по умолчанию — 5432. Если ты устанавливаешь PostgreSQL впервые, то он скорее всего свободен. Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, ты можешь указать другое значение, например 5433.
После завершения установки ты сможешь запустить SQL Shell, поставляемый с Postgres.
Шаг за шагом ты выберешь сервер, какую базу данных использовать, порт, имя пользователя и пароль.
Используй данные, которые ты вводил на предыдущих шагах.
Поздравляю! Настройка для Windows завершена, и скоро мы начнем писать первые SQL запросы.
Ниже список вариантов установки для других операционных систем.
macOS
Для macOS у тебя есть разные варианты. Можно скачать установщик с сайта EDB и запустить его.
Кроме того, можно использовать Postgres.app , простое приложение для macOS.
После запуска у тебя появится сервер PostgreSQL, готовый к использованию. Завершить работу сервера можно просто закрыв приложение.
Кроме того, ты также можете использовать Homebrew , менеджер пакетов для macOS.
GNU/Linux
Ты можешь найти PostgreSQL в репозиториях большинства дистрибутивов Linux. Установить его можно одним щелчком мыши из выбранного графического диспетчера пакетов.
Альтернативно, можно использовать установку через терминал. Ты можешь обратиться к документации твоего дистрибутива для получения дополнительных сведений.
Ubuntu
Fedora
openSUSE
Запуск оболочки PostgreSQL
После установки PostgreSQL, нужно запустить оболочку(shell), с помощью которой ты получишь возможность управлять базой данных.
Открой терминал и введи:
psql — это оболочка Postgres, аргумент -U используется для указания пользователя.
Поскольку ты еще не создавал других пользователей, ты войдешь в систему как суперпользователь postgres .
После этого нужно будет ввести пароль суперпользователя, который ты выбрал во время установки.
Как только пароль установлен, база данных PostgreSQL готова к работе!
Если сервер PostgreSQL по какой-то причине не запускается, можешь попробовать запустить его вручную.
Понимание модели клиент-сервер
Я уже упоминал PostgreSQL Server как важный компонент базы данных. Но что такое сервер в этом контексте и зачем он нам нужен?
Для начала тебе необходимо понимать модель клиент-сервер.
Почти все СУБД (PostgreSQL, MySQL и другие) следуют клиент-серверной модели. В ней база данных находится на сервере, и клиент отправляет запросы на сервер, который их обрабатывает.
Под клиентом здесь подразумевается бекэнд нашего приложения, а запросы в — это SQL операции, такие как SELECT, INSERT, UPDATE и DELETE.
Для разработки любого бекэнда, тебе нужен локальный сервер для экспериментов и тестирования. Этот локальный сервер аналогичен удаленному, но работает прямо на твоем компьютере.
С точки зрения клиента удаленный и локальный сервер идентичны. После разработки и тестирования ты можешь заставить свой продукт взаимодействовать с удаленным сервером вместо локального, просто изменив пару параметров.
Некоторые базы данных не используют эту модель, например SQLite, которая хранит все в простом файле на диске. Это хорошо работает для небольших приложений, но для большинства реальных приложений тебе понадобится архитектура клиент-сервер.
Мета-команды PostgreSQL
Теперь, когда ты все настроил и готов приступить к работе с базой данных, осталось разобрать несколько мета-команд. Это не SQL запросы, а команды специфичные для PostgreSQL.
В других системах управления базами данных есть их аналоги, но их синтаксис немного отличается.
Всем мета-командам предшествует обратная косая черта \ , за которой следует фактическая команда.
Список всех баз данных
Чтобы получить список всех баз данных на сервере, ты можешь использовать команду \ l .
Ввод этой мета-команды в оболочке Postgres выведет:
Это список всех имеющихся баз данных и служебная информация, такая как владелец базы данных, кодировка и права доступа.
На данный момент мы пока ничего не создали, а базы данных которые ты видишь на экране — создаются по умолчанию при установке Postgres.
- postgres — это просто пустая база данных.
- «template0» и «template1» — это служебные базы данных, которые служат шаблоном для создания новых баз.
Тебе пока не стоит беспокоиться о них. Если хочешь изучить все детали, то проверь официальную документацию.
Подключаемся к базе данных PostgreSQL
Некоторые команды SQL требуют, чтобы ты сначала вошел в базу данных (например, для создания новой таблицы). Ты можешь выбрать, в какую базу данных входить, при запуске SQL Shell.
Когда ты находишься внутри оболочки (shell), то можешь использовать команду \c (или \connect ), за которой следует имя базы данных. Если бы у тебя была другая база данных под названием hello_world , то подключиться к ней можно было бы так:
Полностью в терминале у тебя получится что-то такое:
Обрати внимание, что приглашение оболочки изменилось с postgres на hello_world . Это значит, что теперь ты подключен к базе данных hello_world , а не postgres .
Получить список всех таблиц в базе данных
Как и в случае со списком существующих баз данных, ты можешь получить список таблиц внутри конкретной базы данных с помощью команды \dt .
Перед выполнением этой команды вам необходимо войти в базу данных.
Предположим, ты уже находишься внутри базы hello_world , и в ней есть таблица с именем my_table . Набрав \dt , ты получишь следующее:
Ты можешь увидеть имя таблицы и некоторую другую информацию, такую как схема (мы обсудим схемы в более сложных руководствах) и владельца.
Владелец (owner) — это пользователь, который создал таблицу.
Если ты создаешь других пользователей и используешь их для создания таблиц, то в последнем столбце будут именно они.
Список пользователей и ролей
Как ты уже знаешь, при установке Postgres создается суперпользователь с именем postgres . Список всех пользователей базы данных можно вывести на экран используя команду \dg .
Обрати внимание, что первый столбец называется — роль (role name). И весь вывод на экран называется “список ролей” (List of roles), а не список пользователей.
В PostgreSQL пользователи и роли практически одинаковы.
У ролей есть атрибуты, которые определяют их разрешения, такие как создание баз данных или даже создание других новых ролей.
Любая роль с атрибутом LOGIN может рассматриваться, как пользователь.
Здесь мы видим только одну роль, суперпользователя по умолчанию.
В реальном мире все будет иначе, потому что использовать только суперпользователя все время опасно. Вместо этого создают другие роли с меньшими привилегиями. Это гарантирует, что никто не совершит нежелательных действий по ошибке.
Если у одной из ролей есть доступ только на чтение данных, то с помощью этой роли будет невозможно удалить таблицу или поле.
Твой первый SQL оператор
Наконец, мы все настроили и готовы к работе и знаем основные мета-команды, специфичные для PostgreSQL.
Теперь приступим к изучению языка запросов SQL.
Я покажу тебе несколько базовых примеров, чтобы разобраться в структурированном языке запросов и получить представление о SQL. А более подробно мы рассмотрим операции CRUD это в следующей статье.
Создание новой базы данных
Первое, что тебе нужно узнать при изучении баз данных, — это как создать базу данных. Создать базу можно сделать с помощью команды CREATE DATABASE , за которой следует имя базы данных:
Команды и ключевые слова SQL обычно пишутся в верхнем регистре.
На самом деле это не является обязательным требованием, и обычно они нечувствительны к регистру.
То есть ты мог бы написать
И все сработало бы нормально.
Но при написании операторов SQL обычно предпочтительнее прописные буквы. Это хорошая практика, потому что она может помочь тебе визуально отличить ключевые слова SQL от других частей оператора, таких как имена таблиц и столбцов.
Заметь, что все стандартные команды в PostgreSQL должны заканчиваться точкой с запятой ; . Это часть стандарта.
Для мета-команд PostgreSQL точка с запятой не нужна.
Создание таблиц
После того как ты создал новую базу данных, можно приступать к созданию таблиц.
Но, для начала, подключимся к новой базе данных с помощью команды \c , за которой следует имя базы данных:
Теперь, когда ты подключился к базе данных (обратите внимание, что приглашение оболочки SQL теперь включает имя активной базы данных), ты готов создать свою первую таблицу.
Таблица создается с помощью команды CREATE TABLE, за которой следует список столбцов таблицы и их типы данных в круглых скобках:
Это создаст таблицу под названием products , которая содержит 3 столбца:
- id типа INT (целое число)
- name типа TEXT (строка)
- quantity также типа INT
После создания таблицы перейдем к добавлению данных.
Вставка данных в таблицы PostgreSQL
Чтобы добавить данные в таблицу, используют команду INSERT INTO следующим образом:
Посмотрим на команду INSERT INTO подробнее:
- Команда INSERT INTO означает, что вы собираетесь вставить новые данные
- products — это имя таблицы в базе данных, в которую ты хочешь вставить данные
- (id, name, quantity) — это список столбцов в нашей таблице, разделенных запятыми. Тебе не нужно указывать все столбцы (иначе какой в смысл?). В некоторых случаях вы хотите выборочно вставлять данные в некоторые столбцы. Остальные столбцы будут автоматически заполнены значениями по умолчанию.
- VALUES (1, ‘first product’, 20) — это фактические данные, которые будут вставлены в таблицу. «1» — это id , «first product» — это name , «20» — это quantity .
Выборка данных из SQL таблицы
Теперь, когда ты добавил в таблицу первую запись, ты можешь использовать SQL для получения содержимого таблицы.
Выборка данных осуществляется с помощью команды SELECT , и это выглядит следующим образом:
Мы используем команду SELECT . За ней следует список столбцов, которые мы хотим получить.
Затем мы используем команду FROM , чтобы указать, из какой таблицы брать данные. На этот раз это таблица products .
Для ситуаций когда ты хочешь выбрать все столбцы которые есть в таблице, ты можешь поставить звездочку вместо списка полей.
Звездочка означает: выбрать все столбцы. Результат останется прежним.
Ты должен обратить внимание на то, как команда SELECT выбирает столбцы и строки. Столбцы указываются в виде списка и разделяются запятыми. Затем команда переходит к выбору запрошенных строк.
Если условия не указаны (как в этом случае), будут выбраны все строки в таблице.
Позже мы увидим, как использовать условия с командой WHERE для создания эффективных запросов.
Обновление данных в PostgreSQL
Представь, что ты запустил свое потрясающее приложение для магазина и получили первый заказ на один из продуктов.
Первое, что нужно сделать — это обновить доступное количество в вашем инвентаре, чтобы в дальнейшем у вас не возникли проблемы с отсутствием товара на складе.
Для обновления данных ты можешь использовать команду UPDATE :
Давайте разберемся с тем как работает UPDATE .
Начинаем мы с ключевого слова UPDATE , за которым следует имя таблицы.
Затем мы используем SET , чтобы установить новые значения для наших столбцов.
После SET — пишем имена столбцов, которые хотим обновить.
За ними — знак равенства и новое обновленное значение.
Также ты можешь обновить сразу несколько столбцов, разделив их запятыми:
Но стоп, какие строки обновляются этой командой?
Ты уже должны были догадаться об этом. Чтобы указать, какие строки следует обновить новыми значениями, мы используем команду WHERE , за которым следует условие.
В этом случае мы сопоставляем строки, используя их столбец id , и обновляем строку с id 1.
Удаление данных из SQL таблицы
Теперь рассмотрим случай, когда ты прекратил продажу определенного продукта и захотел полностью удалить его из своей базы данных.
Для этого можно использовать команду DELETE :
Как и при обновлении данных, чтобы определить, какие именно строки мы хотим удалить, нам нужно условие WHERE .
Удаление таблиц в PostgreSQL
Если вдруг ты решил изменить структуру базы и для этого нужно удалить всю таблицу, то тебе подойдет команда DROP TABLE :
Это приведет к удалению всей таблицы products из базы данных.
Будь очень осторожен с командой DELETE !
Я бы не позавидовал тому, кто “случайно” удалит не ту таблицу из базы данных.
Удаление баз данных PostgreSQL
Точно так же ты можешь удалить из системы всю базу данных:
Заключение
Поздравляю, у тебя все получилось!
Ты установил и запустили PostgreSQL. Ты изучил основные команды SQL и проделали с ними несколько интересных вещей.
Эти несколько простых команд — основа, которую ты будешь использовать большую часть времени при взаимодействии с базами данных, поэтому тебе следует пойти и потренироваться и изучить самостоятельно. В следующий раз мы погрузимся глубже и обсудим Базы данных, роли и таблицы в PostgreSQL
Создаём свою БД на PostgreSQL из CSV
Kaggle — — платформа созданная для проведение конкурсов по исследованию данных. Организаторы выкладывают Datasets , описывают задачи , метрики по которым будут выявляться победители конкурса , призы и время проведения. Каждый желающий может выставить свою работа по этим данных , красиво описать её , показать свои умения и надеяться на победу.
Мы будем использовать Used Cars Dataset
Также мы можем посмотреть Code других участников соревнования
подчерпнуть оттуда интересную информацию
найти нестандартные подходы к обработке данных
На примере других работа , научиться чему-то новому
и даже наткнуться на боже зачем это тут ? интересную работу по »Ускорение рабочего процесса Pandas с Modin»
Заставить других сделать свою работу
Узнать ответ на интересующий тебя вопрос(есть шанс)
Перейдём к делу, Pgadmin4
pgAdmin — это платформа с открытым исходным кодом для администрирования и разработки на PostgreSQL и связанных с ней систем управления базами данных.
pgAdmin будет предложен в установке PostgreSQL, я пользуюсь 14.3. Багов и проблем не боюсь , беру самую новую версию сразу видно профессионал. Если боитесь устанавливать приложение без ведения за ручку , вам поможет интернет()_(). Уже 1000 раз было рассказывать как это делать и что за чему , так что не буду тратить наше драгоценное.
Перейдём к делу 2, Python
Python — — высокоуровневый язык программирования. и нам нужна библиотека pandas
Перейдём к делу 3, Pycharm
Pycharm — — среда разработки(IDE) созданная специально для языка программирования Python.
Предоставляет средства для анализа кода
инструменты для отладки юнит-тестов
интуитивно понятный интерфейс
очень много полезных функций для продвинутых пользователей
Начнём кодить(0)_(з)
экспорт данных + получение основной информации
для начала открываем Pycharm, создаём там новый проект и в терминале инсталлируем библиотеку pаndas Открываем терминал и пишем там pip install pandas, нажимаем enter и ждём установки.
Далее нам надо открыть для чтения наш файл —
видим что из-за 26 столбцов, Pycharm не подгружает всё таблицу( в дальнейшем исправим)
Получаем основные данные из таблицы.
Название всех столбцов
Количество значений в них
Получаем гигантский DF который я не могу передать как картинку , так что переходим сразу обработке этих данных
Очистка данных
Убирает лишние столбцы
Нам точно не нужны url ссылки, и пустая строка country , так же нам не надо описание автомобиля на 1000+ символов(description) Так что пишемс простой код
drop удаления столбцов , Axis: указывает, что столбцы или строки должны быть удалены, inplace = True, он возвращает Data Frame с удаленными столбцами или None
После этого сохраняем наш изменённый df в новый файл , что бы в дальнейшем работать только с нужными данными
Убираем выбросы
Выбросы — — это данные, которые существенно отличаются от других наблюдений. Они могут соответствовать реальным отклонениям, но могут быть и просто ошибками
Нас интересуют выбросы в колонке price, согласитесь если цена на машину будет 5 000 000 000 долларов это будет сильно менять среднее значение цены и мешать нашим вычислениям
Находим выбросы
Узнаем самые часто встречаемые цены с помощью value_counts И_И узнаем статистику по цене в нашем df с помощью describe
слева видим что у нас есть 32к значений = 0, которые стоят обрезать , и множество значений цены = около 3к
справа у нас показатели зашкаливают и выдают огромные цифры. ЧТО ТО ТУТ НЕ ТАК.
А теперь сделаем грубую и ужасную профессиональную вырезку. Я называю её «и так сойдёт»(объясняю, мы как бы не готовим данные для отчётов и т.д , а просто убираем самый явный бред)
Импортируем 2 крутые штуки seaborn
Теперь в шапку нашего кода добавляем
Строем простецкий графии
Тут мы смотря на значения Y будем постепенно обрезать наши выбросы , пока они не станут чуть-чуть адекватными( код ниже)
Убираем выбросы
да это всё можно делать с помощью IQR (но это совершенно другая история)
И с помощью value_counts, describe проверяем похоже ли это на правду
Сохраняем то что сделали
Переноcим данные в СУБД
Создаём пустую бд под экспорт
Осталось дело за малым, открываем pgAdmin4(и подключаемся к серверу)
Далее нам нужно, создать базу данных
Выбираем нашу базу данных и открываем запросник
Вводим туда простейший код
дааааааа — можно использовать CHARACTER VARYING , int4 , date . Но мы сейчас не про экономию места на диске
Далее нам надо импортировать наши данные в таблицу
Занимаемся экспортом данных
Находим и открываем sql Shell (psql) — терминальный интерфейс для PostgreSQL
просто нажимаем на enter везде кроме, Database(название вашей базы данных) и Пароль пользователя postgres. И у нас начинается подключение
Как заполнить базу данных postgresql
The first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.
Possibly, your site administrator has already created a database for your use. In that case you can omit this step and skip ahead to the next section.
To create a new database, in this example named mydb , you use the following command:
If this produces no response then this step was successful and you can skip over the remainder of this section.
If you see a message similar to:
then PostgreSQL was not installed properly. Either it was not installed at all or your shell’s search path was not set to include it. Try calling the command with an absolute path instead:
The path at your site might be different. Contact your site administrator or check the installation instructions to correct the situation.
Another response could be this:
This means that the server was not started, or it is not listening where createdb expects to contact it. Again, check the installation instructions or consult the administrator.
Another response could be this:
where your own login name is mentioned. This will happen if the administrator has not created a PostgreSQL user account for you. ( PostgreSQL user accounts are distinct from operating system user accounts.) If you are the administrator, see Chapter 22 for help creating accounts. You will need to become the operating system user under which PostgreSQL was installed (usually postgres ) to create the first user account. It could also be that you were assigned a PostgreSQL user name that is different from your operating system user name; in that case you need to use the -U switch or set the PGUSER environment variable to specify your PostgreSQL user name.
If you have a user account but it does not have the privileges required to create a database, you will see the following:
Not every user has authorization to create new databases. If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs. If you installed PostgreSQL yourself then you should log in for the purposes of this tutorial under the user account that you started the server as. [1]
You can also create databases with other names. PostgreSQL allows you to create any number of databases at a given site. Database names must have an alphabetic first character and are limited to 63 bytes in length. A convenient choice is to create a database with the same name as your current user name. Many tools assume that database name as the default, so it can save you some typing. To create that database, simply type:
If you do not want to use your database anymore you can remove it. For example, if you are the owner (creator) of the database mydb , you can destroy it using the following command:
(For this command, the database name does not default to the user account name. You always need to specify it.) This action physically removes all files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.
More about createdb and dropdb can be found in createdb and dropdb respectively.
[1] As an explanation for why this works: PostgreSQL user names are separate from operating system user accounts. When you connect to a database, you can choose what PostgreSQL user name to connect as; if you don’t, it will default to the same name as your current operating system account. As it happens, there will always be a PostgreSQL user account that has the same name as the operating system user that started the server, and it also happens that that user always has permission to create databases. Instead of logging in as that user you can also specify the -U option everywhere to select a PostgreSQL user name to connect as.
| Prev | Up | Next |
| 1.2. Architectural Fundamentals | Home | 1.4. Accessing a Database |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.
14.4.Пополнение базы данных
При первом пополнении базы данных может потребоваться вставка большого объема данных.В данном разделе содержатся некоторые предложения о том,как сделать этот процесс как можно более эффективным.
14.4.1.Отключить автокоммит
При использовании нескольких INSERT отключите автоматическую фиксацию и просто сделайте одну фиксацию в конце. (В простом SQL это означает выполнение BEGIN в начале и COMMIT в конце. Некоторые клиентские библиотеки могут делать это за вашей спиной, и в этом случае вам нужно убедиться, что библиотека делает это, когда вы этого хотите.) Если вы разрешите каждая вставка фиксируется отдельно, PostgreSQL выполняет много работы для каждой добавляемой строки. Дополнительным преимуществом выполнения всех вставок в одной транзакции является то, что в случае сбоя вставки одной строки вставка всех строк, вставленных до этой точки, будет отменена, поэтому вы не застрянете с частично загруженными данными.
14.4.2. Использовать COPY
Используйте COPY для загрузки всех строк одной командой вместо использования серии команд INSERT . Команда COPY оптимизирована для загрузки большого количества строк; он менее гибкий, чем INSERT , но требует значительно меньше накладных расходов при больших загрузках данных. Поскольку COPY — это отдельная команда, нет необходимости отключать автоматическую фиксацию, если вы используете этот метод для заполнения таблицы.
Если вы не можете использовать COPY , может помочь использовать PREPARE для создания подготовленного оператора INSERT , а затем использовать EXECUTE столько раз, сколько потребуется. Это позволяет избежать некоторых накладных расходов на повторный синтаксический анализ и планирование INSERT . Различные интерфейсы предоставляют эту возможность по-разному; ищите «подготовленные операторы» в документации по интерфейсу.
Обратите внимание, что загрузка большого количества строк с помощью COPY почти всегда быстрее, чем с помощью INSERT , даже если используется PREPARE и несколько вставок объединяются в одну транзакцию.
COPY является самым быстрым при использовании в той же транзакции, что и предыдущая команда CREATE TABLE или TRUNCATE . В таких случаях писать WAL не нужно, потому что в случае ошибки файлы, содержащие вновь загруженные данные, все равно будут удалены. Тем не менее, это соображение применимо только тогда , когда wal_level является minimal , так как все команды должны написать WAL иначе.
14.4.3.Удалить индексы
Если вы загружаете только что созданную таблицу, самым быстрым методом является создание таблицы, массовая загрузка данных таблицы с помощью COPY , а затем создание любых индексов, необходимых для таблицы. Создание индекса для уже существующих данных происходит быстрее, чем его постепенное обновление по мере загрузки каждой строки.
Если вы добавляете большие объемы данных в существующую таблицу,это может быть выигрышем,если вы откажетесь от индексов,загрузите таблицу,а затем заново создадите индексы.Конечно,производительность базы данных для других пользователей может пострадать во время отсутствия индексов.Следует также подумать дважды,прежде чем сбрасывать уникальный индекс,так как проверка на ошибки,предоставляемые уникальным ограничением будет потеряна в то время как индекс отсутствует.
14.4.4.Удалить ограничения на использование внешнего ключа
Как и в случае с индексами,ограничения внешнего ключа можно проверять «оптом» более эффективно,чем построчно.Поэтому может быть полезно отменить ограничения внешнего ключа,загрузить данные и заново создать ограничения.Опять же,существует компромисс между скоростью загрузки данных и потерей проверки ошибок,пока ограничение отсутствует.
Более того, когда вы загружаете данные в таблицу с существующими ограничениями внешнего ключа, каждая новая строка требует записи в серверном списке ожидающих событий триггера (поскольку это срабатывание триггера, который проверяет ограничение внешнего ключа строки). Загрузка миллионов строк может привести к переполнению очереди событий триггера доступной памяти, что приведет к невыносимой подкачки или даже к полному отказу команды. Поэтому может быть необходимо , а не только желательно, отбросить и повторно применить внешние ключи при загрузке больших объемов данных. Если временное удаление ограничения неприемлемо, единственным выходом может быть разделение операции загрузки на более мелкие транзакции.
14.4.5. Увеличить maintenance_work_mem
Временное увеличение переменной конфигурации maintenance_work_mem при загрузке больших объемов данных может привести к повышению производительности. Это поможет ускорить команды CREATE INDEX и ALTER TABLE ADD FOREIGN KEY . Для самого COPY это мало что даст , поэтому этот совет полезен только тогда, когда вы используете один или оба вышеперечисленных метода.
14.4.6. Увеличить max_wal_size
Временное увеличение переменной конфигурации max_wal_size также может ускорить загрузку больших данных. Это связано с тем, что загрузка большого объема данных в PostgreSQL приведет к тому, что контрольные точки будут возникать чаще, чем обычная частота контрольных точек (задается конфигурационной переменной checkpoint_timeout ).Всякий раз, когда возникает контрольная точка, все грязные страницы должны быть сброшены на диск. Временное увеличение max_wal_size во время массовой загрузки данных позволяет уменьшить количество необходимых контрольных точек.
14.4.7.Отключить архивирование и потоковую репликацию WAL
При загрузке больших объемов данных в установку, использующую архивирование WAL или потоковую репликацию, может быть быстрее создать новую базовую резервную копию после завершения загрузки, чем обрабатывать большой объем добавочных данных WAL. Чтобы предотвратить добавочное ведение журнала WAL во время загрузки, отключите архивирование и потоковую репликацию, установив для wal_level minimal значение , для archive_mode — off , а для max_wal_senders — нулевое значение. Но обратите внимание, что изменение этих настроек требует перезагрузки сервера и делает любые базовые резервные копии, сделанные ранее, недоступными для восстановления архива и резервного сервера, что может привести к потере данных.
Помимо избежать время архиватора или WAL отправителя для обработки данных WAL, делая это на самом деле сделать некоторые команды быстрее, потому что они не писать WAL вообще , если wal_level является minimal , а ток подтранзакции (или верхнего уровня транзакций) создали или усекли таблицу или индекс, которые они изменяют. (Они могут гарантировать защиту от сбоев дешевле, выполнив fsync в конце, чем написав WAL.)
14.4.8. После этого запустите ANALYZE
Всякий раз, когда вы значительно изменили распределение данных в таблице, настоятельно рекомендуется запустить ANALYZE . Это включает массовую загрузку больших объемов данных в таблицу. Запуск ANALYZE (или VACUUM ANALYZE ) гарантирует, что у планировщика будет актуальная статистика по таблице. Без статистики или устаревшей статистики планировщик может принять неверные решения во время планирования запроса, что приведет к снижению производительности любых таблиц с неточной или несуществующей статистикой. Обратите внимание, что если включен демон автоочистки, он может запускать ANALYZE автоматически; см. Раздел 25.1.3 и Раздел 25.1.6 для получения дополнительной информации.
14.4.9.Некоторые заметки о pg_dump
Сценарии дампа, сгенерированные pg_dump, автоматически применяют некоторые, но не все из приведенных выше правил. Чтобы как можно быстрее восстановить дамп pg_dump, вам нужно сделать несколько дополнительных действий вручную. (Обратите внимание, что эти пункты применяются при восстановлении дампа, а не при его создании . Те же самые пункты применяются при загрузке текстового дампа с помощью psql или при использовании pg_restore для загрузки из файла архива pg_dump.)
По умолчанию pg_dump использует COPY , и когда он генерирует полный дамп схемы и данных, он осторожно загружает данные перед созданием индексов и внешних ключей. Таким образом, в этом случае несколько рекомендаций обрабатываются автоматически. Вам остается:
Установите соответствующие (т. max_wal_size Большие, чем обычно) значения для maintenance_work_mem и max_wal_size .
Если вы используете архивирование WAL или потоковую репликацию, рассмотрите возможность их отключения во время восстановления. Для этого перед загрузкой дампа установите для archive_mode значение off , wal_level — minimal и max_wal_senders — ноль. После этого установите для них правильные значения и сделайте новую резервную копию базы.
Поэкспериментируйте с режимами параллельного дампа и восстановления как pg_dump, так и pg_restore и найдите оптимальное количество одновременных заданий для использования. Параллельное копирование и восстановление с помощью параметра -j должно дать вам значительно более высокую производительность по сравнению с последовательным режимом.
Подумайте, нужно ли восстанавливать весь дамп как одну транзакцию. Для этого передайте параметр командной строки -1 или —single-transaction в psql или pg_restore. При использовании этого режима даже самые мелкие ошибки приведут к откату всего восстановления, что может привести к потере многих часов обработки. В зависимости от того, насколько взаимосвязаны данные, это может показаться предпочтительнее ручной очистки или нет. Команды COPY будут выполняться быстрее всего, если вы используете одну транзакцию и отключите архивирование WAL.
Если на сервере базы данных доступно несколько процессоров, рассмотрите возможность использования параметра pg_restore —jobs . Это позволяет одновременно загружать данные и создавать индексы.
После этого запустите ANALYZE .
Дамп только данных по-прежнему будет использовать COPY , но он не удаляет и не воссоздает индексы, и обычно не касается внешних ключей. [14] Таким образом, при загрузке дампа только данных вы должны удалить и воссоздать индексы и внешние ключи, если хотите использовать эти методы. По-прежнему полезно увеличивать max_wal_size при загрузке данных, но не беспокойтесь об увеличении maintenance_work_mem ; скорее, вы бы сделали это при последующем создании индексов и внешних ключей вручную. И не забудьте ANALYZE когда закончите; см. Раздел 25.1.3 и Раздел 25.1.6 для получения дополнительной информации.
[14] Вы можете получить эффект отключения внешних ключей, используя параметр —disable-triggers , но помните, что это устраняет, а не просто откладывает проверку внешнего ключа, и поэтому при его использовании можно вставить неверные данные.