Что такое код pg_delete

Содержание

pg_delete

(PHP 4 >= 4.3.0, PHP 5)

pg_delete — Deletes records

Описание

pg_delete() deletes records from a table specified by the keys and values in assoc_array. If options is specified, pg_convert() is applied to assoc_array with the specified options.

Список параметров

PostgreSQL database connection resource.

Name of the table from which to delete rows.

An array whose keys are field names in the table table_name, and whose values are the values of those fields that are to be deleted.

Any number of PGSQL_CONV_FORCE_NULL, PGSQL_DML_NO_CONV, PGSQL_DML_EXEC or PGSQL_DML_STRING combined. If PGSQL_DML_STRING is part of the options then query string is returned.

Возвращаемые значения

Возвращает TRUE в случае успешного завершения или FALSE в случае возникновения ошибки. Returns string if PGSQL_DML_STRING is passed via options.

Примеры

Пример #1 pg_delete() example

Эта функция является ЭКСПЕРИМЕНТАЛЬНОЙ. Поведение этой функции, ее имя и относящаяся к ней документация могут измениться в последующих версиях PHP без уведомления. Используйте эту функцию на свой страх и риск.

Смотрите также

  • pg_convert() — Convert associative array values into suitable for SQL statement

Резервное копирование PostgreSQL

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date «+%Y-%m-%d»).sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump —schema-only users > /tmp/users.schema.dump

Только данные

pg_dump —data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

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

pg_delete

(только PHP 4 CVS)

pg_delete — удаляет записи.

Описание

long pg_delete (resource connection, string table_name, array assoc_array [, int options])

pg_delete() удаляет условие записи assoc_array, которое имеет field=>value. Если option специфицирован pg_convert(), оно применяется к assoc_array со специфицированной опцией.

Пример 1. pg_delete

Примечание: эта функция является экспериментальной.

15 полезных команд PostgreSQL

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

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

Получение информации о базе данных

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

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :

В результате получим информацию вида 40 Mb .

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

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

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

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

Активность пользователя

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

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.

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

«КРОК», Москва, Санкт-Петербург, Троицк, Челябинск, Воронеж, Иркутск, Краснодар, Нижний Новгород, Самара, Пермь, от 120 000 до 240 000 ₽

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

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

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

Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER :

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :

В результате всё прошло без ошибок:

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

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

Количество уникальных строк по указанному полю:

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Илон Маск рекомендует:  Перемещение image'a по форме во время работы программы

Начнём транзакцию с помощью команды BEGIN .

Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .

А чтобы применить — команду COMMIT .

Просмотр и завершение исполняемых запросов

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

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

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

Изменим расположение на другое с помощью команды:

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

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

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

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

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

PostgeSQL не стартует: pg_resetxlog: could not create pg_control file: File exists

У клиента не стартует PostgeSQL. Как объяснет админ: отвалился шлейф системного диска, сервак отрубился. После перезапуска служба не стартует.

Пару раз такое было, когда они сервак перезагружали во время работы. Тогда помогали команды:
pg_controldata «E:\PG_SQL_DATA»
pg_resetxlog.exe -o 17008016 -x 19349186 -f «E:\PG_SQL_DATA»

сейчас после второй команды лог не очищается, а валится ошибка:
pg_resetxlog: could not create pg_control file: File exists

Что сделать можно? Пробовал удалять pg_control — только хуже становится.

Как вообще на сервере могло произойти событие «отвалился шлейф системного диска»? У сервера корпус вообще в сборе или админа рожа просит кирпича?

Диск на ошибки проверяли? прав у учетки от которой запускается постгри хватает?
База поди на системном диске?

Бэкапов я так понимаю нет?

«Сервер» под столом.

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

АйТи бубен

Инструменты пользователя

Инструменты сайта

Содержание

PostgreSQL

PostgreSQL (произносится «Постгре-Эс-Кю-Эль», коротко называется «Постгрес») — свободная объектно-реляционная система управления базами данных (СУБД система управления базами данных). Использует порт 5432/tcp/udp. PostgreSQL использует только один механизм хранения данных под названием Postgres storage system (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны, в отличии от MySQL, в котором InnoDB и BDB являются единственными типами таблиц, которые поддерживают транзакции.

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

MVCC — одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.

Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.

pg_hba.conf идентификация пользователей

pg_hba.conf — настройка политики доступа к базам данных и идентификации пользователей сервера PostgreSQL .

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

Примеры записей pg_hba.conf:

Кодировка БД PostgreSQL и locale

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера — соответственно он не может создать две базы в разных кодировках — кодировка всегда одна для всего сервера и всех его БД.

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.

Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.

Клиенты администрирования PostgreSQL

psql — PostgreSQL interactive terminal.

В директории /usr/share/doc/postgresql* можно найти дополнительную информацию по запуску.

Посмотреть и удалить активные запросы

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

SELECT запросы можно снимать из ОС командой kill

Транзакции в PostgreSQL

В PostgreSQL Транзакция — это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с >

Выполним транзакцию для test777:

Мониторинг, логи, размер БД PostgreSQL

Лог файлы

Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.

Мониторинг

Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту watch:

Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам, запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:

«пользователь», «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:

Views сборщик статистики

Представления (Views) сборщика статистики.

Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

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

Стандартные Statistics Views. Вывести все представления каталога

Вывести соотношение hit/read. При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.

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

Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

Уровни блокировок таблиц

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.

Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.

Automatic Vacuuming

Автоматическая сборка мусора (Automatic Vacuuming).

Синтаксис VACUUM:

Синтаксис ANALYZE:

Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов. То есть, если не хочется настраивать автоматическую сборку мусора, то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине, что иначе не получится заново использовать дисковое пространство, которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в PostgreSql postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.

Илон Маск рекомендует:  Что такое код ncurses_delay_output

Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:

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

Практика показала, что без более-менее регулярных запусков vacuum full analyze производительность системы постепенно падает, причем чем дальше, тем больше. Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом, чтобы в ней не оставалось «дырок» от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы), что может привести к проблемам на популярном сервере — начнет скапливаться очередь запросов, ожидающих доступа к базе, каждый запрос требует памяти, память кончается, начинается запись в Как посмотреть информацию о swap?, из-за отсутствия памяти сам vacuum тоже начинает использовать swap и все начинает работать очень-очень медленно. Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы, без монопольной блокировки.

Чтобы определить необходимость использования индекса для какой-либо таблицы, PostgreSQL должен иметь статистику по этой таблице. Эта статистика собирается при использовании VACUUM ANALYZE или просто ANALYZE. Используя статистику, оптимизатор узнает о том как много строк в таблице и если он должен использовать индексы, то он может принимать лучшие решения. Статистика также влияет на определение оптимального порядка соединений таблиц и метода соединения. При изменении содержимого таблицы должен периодически выполнятся сбор статистики.

Получение >

lcr_id — колонка автоинкрементная (lcr_id | integer | not null default nextval(‘df_lcr_list_lcr_id_seq’::regclass)). Запрос возвращает значение колонки lcr_id для вставленной записи, в этом случае id записи.

Системные таблицы pg_

Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.

Имя таблицы Назначение таблицы
1 pg_aggregate aggregate functions
2 pg_am index access methods
3 pg_amop access method operators
4 pg_amproc access method support procedures
5 pg_attrdef column default values
6 pg_attribute table columns («attributes»)
7 pg_authid authorization identifiers (roles)
8 pg_auth_members authorization identifier membership relationships
9 pg_cast casts (data type conversions)
10 pg_class PostgreSQL System Catalogs tables, indexes, sequences, views («relations»)
11 pg_constraint check constraints, unique constraints, primary key constraints, foreign key constraints
12 pg_conversion encoding conversion information
13 pg_database databases within this database cluster Хранятся имена доступных баз данных
14 pg_depend dependencies between database objects
15 pg_description descriptions or comments on database objects В таблице хранятся описания объектов, для которых была применена функция COMMENT (расширение PostgreSQL). Например COMMENT ON TABLE mytable IS ‘Эта строка будет сохранена в системной таблице pg_description.’;
16 pg_enum enum label and value definitions
17 pg_foreign_data_wrapper foreign-data wrapper definitions
18 pg_foreign_server foreign server definitions
19 pg_index additional index information
20 pg_inherits table inheritance hierarchy
21 pg_language languages for writing functions
22 pg_largeobject large objects
23 PostgreSQL pg_listener asynchronous notification support Используется механизмом LISTEN/NOTIFY. pg_listener существует в версиях PostgreSQL меньше 9.
24 pg_namespace schemas
25 pg_opclass access method operator classes
26 pg_operator operators
27 pg_opfamily access method operator families
28 pg_pltemplate template data for procedural languages
29 pg_proc functions and procedures
30 pg_rewrite query rewrite rules
31 pg_shdepend dependencies on shared objects
32 pg_shdescription comments on shared objects
33 pg_statistic planner statistics
34 pg_tablespace tablespaces within this database cluster
35 pg_trigger triggers Триггеры хранятся в системной таблице pg_trigger, что позволяет получить информацию о существующих триггерах на программном уровне.
36 pg_ts_config text search configurations
37 pg_ts_config_map text search configurations’ token mappings
38 pg_ts_dict text search dictionaries
39 pg_ts_parser text search parsers
40 pg_ts_template text search templates
41 pg_type data types
42 pg_user_mapping mappings of users to foreign servers
Представления (View) Назначение
43 pg_cursors open cursors
44 pg_group groups of database users
45 pg_indexes indexes
46 pg_locks блокировки в PostgreSQL currently held locks Содержит информацию о блокировках. Уровни блокировок таблиц .
47 pg_prepared_statements prepared statements
48 pg_prepared_xacts prepared transactions
49 pg_roles database roles
50 pg_rules rules
51 pg_settings parameter settings
52 pg_shadow database users Существует для обратной совместимости, она имитирует каталог, который существовал в PostgreSQL до версии 8.1.
53 pg_stats planner statistics
54 pg_tables tables
55 pg_timezone_abbrevs time zone abbreviations
56 pg_timezone_names time zone names
57 pg_user database users Информативный характер о пользователях, пароль содержится в таблице pg_shadow
58 pg_user_mappings user mappings
59 pg_views views

Partitioning (Партицирование)

Partitioning (партицирование, секционирование).

Postgresql как ограничить общий размер WAL-файлов?

# какие адреса слушать, замените на IP сервера
listen_addresses = ‘10.0.3.245’

# опционально: не дожидаемся fsync на реплике при синхронной репликации
# synchronous_commit = remote_write

# это нужно, чтобы работал pg_rewind
wal_log_hints = on

max_wal_senders = 8
wal_keep_segments = 64

# если хотим синхронную репликацию на одну любую реплику
# synchronous_standby_names = ‘*’

— каким образом ограничить не только количество WAL-сегментов, но максимально занимаемый ими объем?

  • Вопрос задан более года назад
  • 1655 просмотров

Один сегмент WAL обычно имеет фиксированный размер 16мб (опция компиляции postgresql with-wal-segsize, если вы её указывали — вы должны об этом знать, плюс она требует initdb делать заново). Поэтому их количество пересчитывается в объём банальным умножением.

Ограничить максимальный занимаемый объём wal сверху — нельзя. Такой единой гайки нет, даже если вас устраивает переход базы в readonly по достижении этого лимита. И есть довольно много гаек, которые при своём использовании могут попросить базу не вычищать старые wal ещё какое-то время.

Гайки, на которые надо обращать внимание:
wal_keep_segments — база оставляет на диске не меньше этого числа wal, что позволяет реплике штатно на некоторое время терять мастера и затем догонять
replication slots — если вы сделаете слот репликации и его никто не будет читать — база будет сохранять wal пока не закончится диск. Если вы используете слоты репликации, то у вас в мониторинге обязана быть отдельная проверка что слот вычитывается
max_wal_size — несмотря на название — объём wal, по накоплению которого происходит checkpoint. (либо по таймеру checkpoint_timeout смотря что случится раньше). Реально объём wal может быть выше, т.к. автоматический checkpoint намеренно не выполняется моментально, а размазан во времени.
min_wal_size — этот объём wal всегда будет на диске занят и будет переписываться по кругу. Как гарантия того, что на диске есть место под столько wal
archive_command — если включен и команда возвращает ненулевой статус возврата — то будет накапливать wal без ограничений. wal будут удалены (если только ещё не нужны какой из других настроек) когда archive_command получает от команды 0 код возврата.

Если не хочется много денег тратить на резерв свободного места на хороших SSD — разместите на SSD саму базу данных, а WAL перенесите (симлинком директории pg_xlog (до 10.0) или pg_wal (10.0 и выше)) на отдельные HDD. WAL пишутся строго последовательно, вполне возможно жить с WAL на механических дисках и держать хорошую нагрузку.
Плюс если работаете не с деньгами, можно сделать synchronous_commit = off. Что увеличит производительность всех пишущих транзакций. Но в случае фатального краха железа вы потеряете последние 3*wal_writer_delay транзакции (т.е. до 3*200мс = 600мс).

Спасибо, очень развернутый ответ!

В конкретно моей ситуации есть виртуалка с Postgresql, которая в ближайшее время собирается перенестись на железо. Планирую поступить так: на виртуалке разрешить потоковую репликацию, через pg_basebackup stream затянуть данные на железный сервер. После того как будет проверено, что данные консистентны и доступны — погасить виртуалку. Проблема в том, что на виртуалке свободно около 28Gb (базы весят в сумме около 90Gb) и нужно настроить репликацию так, чтобы свободное место не съелось за ближайшие несколько дней.

Тогда не трогайте настройки мастера кроме как max_wal_senders (если был выключен) (для basebackup необходимо минимум 2 коннекта). Ну и чуть-чуть wal_keep_segments всё-таки можно поднять.
По-умолчанию мастер не будет дожидаться реплику. Если мастеру хочется удалить wal — он их удалит как ни в чём не бывало. Если реплика этот wal не прочитала ещё — то получит потом отлуп и надо будет pg_basebackup делать заново. (для изменения этого поведения как раз replication slots и появились)

pg_basebackup -X stream открывает два соединения: по одному копирует базу, по второму подписывается на все новые wal как рядовая реплика. Поэтому во время его работы мастер работает как ни в чём не бывало и удаляет wal как обычно. А вот на реплике wal будут копиться. И стартовать реплика может немного не сразу, пока все эти wal проигрывает. Неприятный момент — за новыми wal к мастеру реплика пойдёт только когда разгребёт все локально записанные wal.

В общем, делается в screen pg_basebackup с ключом —progress, когда он доработал — открывается в другой консоли tail -f лога будущей реплики, запускается реплика и ждём ready to accept connections и starting streaming. Если посыпались ошибки wal segment already removed — стопаем реплику, на мастере поднимаем keep segments и пробуем налить реплику ещё раз. (у нас в dataegret обычно изначально keep segments задран)

Postgres Различия между pg_delete и «DELETE FROM. «

и используйте: «Удалить из. «.

В чем разница между обеими последовательностями? с какой эффективностью? ¿Какой менее вероятно получить ошибки?

Честно говоря, я только предполагаю, что я никогда не использовал pg_delete(), но я бы сказал, что он создает запрос DELETE FROM. за кулисами и отправляет его на сервер.

Учитывая это, разница между ними заключается в том, что с помощью DELETE FROM. вы пишете SQL вручную, а с помощью pg_query() вы используете функцию PHP, которая делает это за вас (хотя и ценой меньшей гибкости).

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

4gophers

Go и SQL базы данных

Это первый туториал из серии материалов про работу с данными в веб приложениях.

В этом посте мы погрузимся в работу с SQL базами данных. Я объясню работу с стандартным пакетом database/sql , приведу примеры рабочих приложений и продемонстрирую несколько хитростей для более красивого структурирования кода.

Для начала, вам необходимо установить драйвера для работы с database/sql .

В этом посте мы будем использовать Postgres и замечательный драйвер pg. Тем не менее, весь код из этого туториала должен нормально работает и с другими драйверами, включая MySQL и SQLite. По ходу я буду указывать на специфические для Postgres моменты(которых будет не очень много).

Основы

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

Прежде всего, нам нужно создать эту самую таблицу для книг, как показано ниже:

После этого, необходимо настроить свое Go окружение, создать папку bookstore и файл main.go:

Давайте начнем с простого кода, который будет выполнять запрос SELECT * FROM books и выводить результат в консоль.

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

Первый интересный момент, это импортирование пакета драйвера. Мы ничего не используем напрямую из этого пакета, а это означает, что компилятор Go ругнется, если вы попробуете импортировать пакет как обычно. Но нам необходим вызов функции init() этого пакета для того, чтобы драйвер зарегистрировал себя для использования в database/sql . Мы можем обойти это ограничение используя пустой алиас для импортируемого пакета. И это означает, что pq.init() будет выполняться, но благодаря алиасу мы избавимся от ошибок во время компиляции. Такая практика является стандартом для большинства SQL драйверов в Go.

Далее, мы определим наш тип для книги, в котором поля и типы полей будут зависеть от таблицы books. Тут стоит уточнить, что мы можем безопасно использовать string и float32 , так как мы указали NOT NULL для колонок в нашей таблице. Если в таблице есть поля, которые могут содержать NULL, то следует использовать типы sql.NullString и sql.NullFloat64 (тут можно глянуть рабочий пример). Вообще, если у вас есть возможность, старайтесь избегать полей, в которых могут быть NULL значения.

В функции main() мы инициализируем экземпляр sql.DB с помощью вызова sql.Open() . Мы указываем название нашего драйвера(в нашем случае это «postgres») и строку соединения, формат которой должен быть описан в документации к драйверу. Важное замечание, sql.DB это не соединение с базой, это некоторая абстракция над пулом соединений. Вы можете менять максимальное количество открытых и простаиваемых соединений в пуле с помощью методов db.SetMaxOpenConns() и db.SetMaxIdleConns() соответственно. И обратите внимание, что sql.DB можно безопасно использовать в конкурентных приложениях(которыми являются и веб-приложения).

Рассмотрим использованные стандартные паттерны:

  1. Мы получаем данные из таблицы, используя метод DB.Query() и присваиваем результат переменной rows . После этого, мы пользуемся defer rows.Close() , чтобы наверняка закрыть сет с результатами до выхода из функции. Очень важно не забывать закрывать сет. Все время, пока открыт сет, используемое соединение невозможно вернуть в пул. Если вдруг что-то пойдет не так и ваш сет не будет закрываться, то соединения в пуле могут быстро закончиться. Еще одна ловушка в том(и это оказалось для меня сюрпризом), что defer должен идти после проверки на ошибки DB.Query() . Если DB.Query() вернет ошибку, то вместо сета будет получен nil и при вызове rows.Close() стрельнет паника.
  2. Для итерации по строкам мы используем rows.Next() . Этот метод подготавливает строку для использования метода rows.Scan() . Не забывайте, что по завершению итерации по всем строкам сет автоматически закрывается и соединение возвращается в пул.
  3. Мы используем метод rows.Scan() , чтобы скопировать значения всех полей из строки в созданный нами экземпляр Book . Далее, мы проверяем была ли ошибка при работе метода rows.Scan() и добавляем новый экземпляр Book в слайс bks , который мы создали ранее.
  4. После итераций с помощью rows.Next() мы вызываем rows.Err() . Этот метод возвращает любую ошибку, которая произошла во время выполнения итераций. Этот момент достаточно важен, он позволяет убедиться, что мы прошлись по всему сету без ошибок.

Если все хорошо и мы нормально заполнили на слайс bks , то теперь мы итерируемся по нему и выводим информацию в консоль.

Если вы запустите код, то должно получиться что-то такое:

Использование в веб-приложении

Давайте изменим наш код, что бы получилось RESTful веб-приложение с 3 роутами:

  • GET /books – Список всех книг в магазине
  • GET /books/show – Информация о конкретной книге по ISBN
  • POST /books/create – Добавление новой книги в магазин

Мы уже реализовали основную логику необходимую для GET /books . Давайте адаптируем ее для использования в HTTP хендлере booksIndex() нашего приложения.

И в чем же тут отличия?

  • Мы используемые функцию init() для настройки нашего пула соединений и указываем его в качестве значения глобальной переменной db . Мы используем глобальную переменную, которая предоставляет доступ к пулу соединений, чтобы иметь возможность использовать ее в разных HTTP хендлерах, но это не единственный возможный способ. Так как sql.Open() не проверяет соединение, то мы вызываем DB.Ping() , чтобы убедиться, что все работает нормально.
  • В хендлере booksIndex мы возвращаем 405 Method Not Allowed ответ для всех не GET запросов. Дальше мы работаем с нашими данными. Все работает как в примере выше, за исключением что ошибки теперь возвращаются как HTTP ответ и нет выхода из программы. В результате мы записываем описания книг как обычный текст в http.ResponseWriter .

Запускаем приложение и делаем запрос к нему:

Выборка одной строки

Для GET /books/show нам нужно реализовать получение одной книги из базы по ее ISBN, который будет указываться как параметр в запросе:

Для этого мы добавим хендлер bookShow() :

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

После этого, мы используем метод Request.FormValue() для получения параметров из строки запроса. В случае если нет необходимых параметров, то мы получаем пустую строку и возвращаем ответ 400 Bad Request .

Тут мы подходим к самому интересному. Метод DB.QueryRow() работает аналогично DB.Query() , но получает только одну строку.

Так как у нас есть некоторый ненадежный данные от пользователя(переменная isbn ), то в нашем SQL запросе нужно использовать плейсхолдеры для параметров, сами значения мы указываем как аргументы после строки запроса.

Если чуть углубиться, то можно обнаружить, что db.QueryRow (а также db.Query() и db.Exec() ) создают «подготовленные выражения»(prepared statement) в базе данных и выполняют запросы, подставляя параметры в плейсхолдеры этих выражений. Это означает, что все три метода безопасны в плане SQL-инъекций, если пользоваться ими правильно. Вот что говорит нам википедия:

Подготовленные выражения устойчивы к SQL инъекциям, поскольку значения параметров, которые передаются позже с использованием другого протокола, не нужно ескейпить. Если оригинальное выражение построено не на основании внешнего ввода, то инъекции не может произойти. В зависимости от базы данных, плейсхолдеры указываются по разному. В Postgres используется нотация $N , но в MySQL, SQL Server и в некоторых других используется символ ? .

Окей, давайте вернемся к нашему коду.

После получения строки с помощью DB.QueryRow() , мы используем row.Scan() для копирования значений в наш новы объект Book . Важно, что мы не узнаем про ошибки выполнения запроса в методе DB.QueryRow() , пока не вызовем метод row.Scan() .

Если ваш запрос не нашел ни одной строки, то вызов row.Scan() вернет ошибку sql.ErrNoRows . Мы выполняем проверку на эту ошибку и, если ничего не найдено, возвращаем 404 Not Found ответ. Если возникают другие ошибку, то возвращаем 500 Internal Server Error .

Если все хорошо, то мы записываем в http.ResponseWriter информацию по запрашиваемой книге.

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

Выполнение выражений

Для нашего роута POST /books/create мы создадим хендлер booksCreate() , в котором будем использовать DB.Exec() для выполнения выражения INSERT . Вы можете использовать схожий подход для UPDATE , DELETE или других операций, которые не подразумевают получение результата в виде строк таблиц.

Код выглядит так:

Думаю, вы уже находите много знакомого в этом коде.

В хендлере booksCreate() мы проверяем, действительно ли пришел POST запрос и получаем параметры из запроса с помощью request.FormValue() . Мы проверяем наличие всех необходимых параметров, а цену еще и конвертируем в float с помощью strconv.ParseFloat() .

После этого, мы используем db.Exec() с указанием полученных парметров, аналогично как мы делали это ранее. Важно, что DB.Exec() , DB.Query() и DB.QueryRow() , — это функции которое могут принимать переменное число параметров.

Метод db.Exec() в качестве результата возвращает объект, который удовлетворяет интерфейс sql.Result. При необходимости, этот результат можно использовать или не учитывать, используя пустой идентификатор.

Интерфейс sql.Result предоставляет метод LastInsertId() , который используется для получения последнего значения автоинкремента. Также, можно использовать метод RowsAffected() , который возвращает число строк, затронутых в запросе(удаленных, обновленных, новых и т.д.). В нашем случае, используется второй описанный метод, мы получаем количество строк и формируем сообщение.

Стоит отметить, что не все драйвера поддерживают методы LastInsertId() и RowsAffected() и при их вызове вы получите ошибку. К примеру, драйвер pq не поддерживает метод LastInsertId() и, если вам необходим подобный функционал, то прийдется использовать подход вроде этого.

Давайте проверим роут /books/create с передачей необходимых параметров в POST:

Использование DB.Prepare()

Возможно, вам стало интересно, почему не используется DB.Prepare() .

Как я объяснял выше, методы методы DB.Query() , DB.Exec() и DB.QueryRow() создают подготовленные выражения в базе данных, запускают их с указанными параметрами и затем закрывают(точнее деаллоцируют) эти выражения.

Недостатки использования такого подхода очевидны. У нас аж три обращения к базе данных на каждый HTTP запрос. Чтобы избежать этого, мы можем воспользоваться DB.Prepare() (например, в функции init() ).

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

Для веб-приложений, в которых задержка имеет большое значение, возможно стоит заморочиться и добавить некоторый мониторинг, который будет реинициализировать подготовленные выражения. Но в таком приложении, как наше, это слишком большой оверхед и нам достаточно использовать DB.Query() как есть.

В этом треде описанная проблема обсуждается несколько глубже.

Рефакторинг

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

Но этот туториал уже и так достаточно большой, поэтому оставим это для следующего поста — «Practical Persistence in Go: Organising Database Access» (в скором времени).

Дополнительные инструменты

Пакет Sqlx от Jason Moiron предоставляет расширенный функционал для работы с базой, включая именованные плейсхолдеры, и автоматический маршалинг результатов запроса в структуры.

Если вам интересны более ORM-ориентированные подходы, то взгляните в сторону Modl того же автора или gorp от James Cooper.

Пакет null может помочь вам при работе с null-значениями.

Postgres won’t start after deleting pg_xlog files

Using Debian Wheezy, Postgresql 9.3

My database went down because the partition where it keeps the WAL files got full. So, I deleted everything inside ./pg_xlog/ , because I didn’t know what they were (yea, incredibly stupid of me). Now the Postgres service won’t start, though the problem, according to syslog:

I’m not entirely sure whether the problem is that it can’t find the proper pg_tblspc or the total lack of checkpoint WAL files. The actual path to where the databases are stored is /dados/PG_9.3_201306121 . What can I do to make the service start again?

EDIT1: Okay, I’ve managed to get the thing back online. Some databases got corrupt. I’ve managed to DROPDB two of them (couldn’t even connect to them without them forcing a service restart). I tried doing it to another one that got corrupt, but the error was related to xlog again. I’ve tried doing a clean restore over it, but the restore was incomplete. Then, I’ve created a new database and tried to restore an older backup of this database. It also came incomplete.

Now I can’t drop any databases, nor create new ones, I always get a xlog flush request not satisfied error. I’ve tried running pg_resetxlog , but it didn’t seem to do anything. Another thing the error shows is cannot write to block 1 of pg_tblspc/16385/PG_9.3_201306121/36596452/11773 , write error may be permanent .

EDIT2: Part of the problem above was with that 11773 file. I’ve renamed it to 11773.corrupt and now the database allows me to create and drop again.

Илон Маск рекомендует:  Что такое код cpdf_fill
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL