Как хранить файлы в базе данных postgresql

Обновлено: 03.07.2024

Каков наилучший способ хранения изображений в Postgres?

Изображения составляют около 4-6 мегапикселей каждый, и мы сохраняем до 3000. Также неплохо было бы отметить: это не веб-приложение, в котором будет почти два фронтальных доступа к базе данных сразу.

Как говорит ответ Jcoby, есть два варианта, поэтому я рекомендую:

используйте blob (Binary Large OBject): для хранения оригинального изображения, на вашем столе. См. ответ ивана (нет проблем с резервным копированием больших двоичных объектов!), дополнительные поставляемые модули PostgreSQL, инструкции и т.д.

Re jcoby answer:

Нет проблем с резервным копированием. pg_dump предоставляет опцию -b для включения больших объектов в резервную копию.

Итак, я предпочитаю использовать pg_lo_ *, вы можете догадаться.

Ответ Криса Эриксона:

В базе данных есть два варианта:

BYTEA. Сохраняет данные в столбце, экспортированном как часть резервной копии. Использует стандартные функции базы данных для сохранения и извлечения. Рекомендуется для ваших нужд.
сгустки. Хранит данные извне, обычно не экспортируется как часть резервной копии. Требуется специальные функции базы данных для сохранения и извлечения.

Я использовал столбцы bytea с большим успехом в прошлом, сохраняя 10 + gb изображений с тысячами строк. Функциональность PG TOAST в значительной степени отрицает любое преимущество, которое имеют blobs. Вам нужно будет включать столбцы метаданных в любом случае для имени файла, типа содержимого, размеров и т.д.

Быстрое обновление до середины 2015 года:

Это имеет то преимущество, что вы можете получить доступ/читать/писать/резервировать его в Postrgres и MongoDB, в зависимости от того, что дает вам больше гибкости.

Это дает вам преимущество согласованности (все связанные файлы обязательно присутствуют) и всех других ACID, в то время как они все еще существуют в фактической файловой системе, что означает, что вы можете использовать любую файловую систему, которую хотите, и веб-сервер может обслуживать их напрямую ( Кеширование ОС тоже применимо).

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

Оригинал

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

тогда, возможно, вы сможете быстро настроить веб-сервер и сохранить URL-адреса веб-сайтов в базе данных (а также локальный путь). В то время как базы данных могут обрабатывать LOB и 3000 изображений (4-6 мегапикселей, при условии 500K изображения), 1,5 гигабайта не так много, файловые системы пространства гораздо лучше разработаны для хранения больших файлов, чем база данных.

Попробуйте это. Я использую формат больших объектов двоичных файлов (LOB) для хранения сгенерированных документов PDF, некоторые из которых имеют размер 10+ МБ, в базе данных, и это прекрасно работает.

Если ваши изображения маленькие, попробуйте сохранить их как base64 в текстовом поле.

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

Это определенно правильный способ обработки миниатюр.

(OP изображения не маленькие, так что это не совсем ответ на его вопрос.)

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

Файлы конфигурации и файлы данных, используемые кластером базы данных, традиционно хранятся вместе в каталоге данных кластера, который обычно называют PGDATA (по имени переменной среды, которую можно использовать для его определения). Обычно PGDATA находится в /var/lib/pgsql/data . На одной и той же машине может находиться множество кластеров, управляемых различными экземплярами сервера.

В каталоге PGDATA содержится несколько подкаталогов и управляющих файлов, как показано в Таблице 65.1. В дополнение к этим обязательным элементам конфигурационные файлы кластера postgresql.conf , pg_hba.conf и pg_ident.conf традиционно хранятся в PGDATA , хотя их можно разместить и в другом месте.

Таблица 65.1. Содержание PGDATA

Элемент	Описание
PG_VERSION	Файл, содержащий номер основной версии PostgreSQL
base	Подкаталог, содержащий подкаталоги для каждой базы данных
global	Подкаталог, содержащий общие таблицы кластера, такие как pg_database
pg_commit_ts	Подкаталог, содержащий данные о времени фиксации транзакций
pg_clog	Подкаталог, содержащий данные о состоянии транзакции
pg_dynshmem	Подкаталог, содержащий файлы, используемые подсистемой динамически разделяемой памяти
pg_logical	Подкаталог, содержащий данные о состоянии для логического декодирования
pg_multixact	Подкаталог, содержащий данные о состоянии мультитранзакций (используемые для разделяемой блокировки строк)
pg_notify	Подкаталог, содержащий данные состояния прослушивания и нотификации (LISTEN/NOTIFY)
pg_replslot	Подкаталог, содержащий данные слота репликации
pg_serial	Подкаталог, содержащий информацию о выполненных сериализуемых транзакциях.
pg_snapshots	Подкаталог, содержащий экспортированные снимки (snapshots)
pg_stat	Подкаталог, содержащий постоянные файлы для подсистемы статистики.
pg_stat_tmp	Подкаталог, содержащий временные файлы для подсистемы статистики
pg_subtrans	Подкаталог, содержащий данные о состоянии подтранзакций
pg_tblspc	Подкаталог, содержащий символические ссылки на табличные пространства
pg_twophase	Подкаталог, содержащий файлы состояний для подготовленных транзакций
pg_xlog	Подкаталог, содержащий файлы WAL (журнал предзаписи)
postgresql.auto.conf	Файл, используемый для хранения параметров конфигурации, которые устанавливаются при помощи ALTER SYSTEM
postmaster.opts	Файл, содержащий параметры командной строки, с которыми сервер был запущен в последний раз
postmaster.pid	Файл блокировки, содержащий идентификатор (ID) текущего управляющего процесса (PID), путь к каталогу данных кластера, время запуска управляющего процесса, номер порта, путь к каталогу Unix-сокета (пустой для Windows), первый корректный адрес прослушивания (listen_address) (IP-адрес или * , либо пустое значение в случае отсутствия прослушивания по TCP), и ID сегмента разделяемой памяти (этот файл отсутствует после остановки сервера).

Для каждой базы данных в кластере существует подкаталог внутри PGDATA /base , названный по OID базы данных в pg_database . Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги.

Каждая таблица и индекс хранятся в отдельном файле. Для обычных отношений, эти файлы получают имя по номеру файлового узла таблицы или индекса, который содержится в pg_class . relfilenode . Но для временных отношений, имя файла имеет форму t BBB _ FFF , где BBB - идентификатор серверного процесса сервера, который создал данный файл, а FFF — номер файлового узла. В обоих случаях, помимо главного файла (также называемого основным слоем), у каждой таблицы и индекса есть карта свободного пространства (см. Раздел 65.3), в которой хранится информация о свободном пространстве в данном отношении. Имя файла карты свободного пространства образуется из номера файлового узла с суффиксом _fsm . Также таблицы имеют карту видимости, хранящуюся в слое с суффиксом _vm , для отслеживания страниц, не содержащих мёртвых записей. Карта видимости подробнее описана в Разделе 65.4. Нежурналируемые таблицы и индексы имеют третий слой, так называемый слой инициализации, имя которого содержит суффикс _init (см. Раздел 65.5).

Внимание

Заметьте, что хотя номер файла таблицы часто совпадает с её OID, так бывает не всегда; некоторые операции, например, TRUNCATE , REINDEX , CLUSTER и некоторые формы команды ALTER TABLE могут изменить номер файла, но при этом сохранят OID. Не следует рассчитывать, что номер файлового узла и OID таблицы совпадают. Кроме того, для некоторых системных каталогов, включая и pg_class , в pg_class . relfilenode содержится ноль. Фактический номер файлового узла для них хранится в низкоуровневой структуре данных, и его можно получить при помощи функции pg_relation_filenode() .

Когда объём таблицы или индекса превышает 1 GB, они делятся на сегменты размером в один гигабайт. Файл первого сегмента называется по номеру файлового узла (filenode); последующие сегменты получают имена filenode.1, filenode.2 и т. д. При такой организации хранения не возникает проблем на платформах, имеющих ограничения по размеру файлов. (На самом деле, 1 ГБ — лишь размер по умолчанию. Размер сегмента можно изменить при сборке PostgreSQL , используя параметр конфигурации --with-segsize .) В принципе, карты свободного пространства и карты видимости также могут занимать нескольких сегментов, хотя на практике это маловероятно.

У таблицы, столбцы которой могут содержать данные большого объёма, будет иметься собственная таблица TOAST, предназначенная для отдельного хранения значений, которые слишком велики для хранения в строках самой таблицы. Основная таблица связывается с её таблицей TOAST (если таковая имеется) через pg_class . reltoastrelid . За подробной информацией обратитесь к Разделу 65.2.

Содержание таблиц и индексов рассматривается ниже (см. Раздел 65.6).

Табличное пространство делает сценарий более сложным. Каждое пользовательское табличное пространство имеет символическую ссылку внутри каталога PGDATA /pg_tblspc , указывающую на физический каталог табличного пространства (т. е., положение, указанное в команде табличного пространства CREATE TABLESPACE ). Эта символическая ссылка получает имя по OID табличного пространства. Внутри физического каталога табличного пространства имеется подкаталог, имя которого зависит от версии сервера PostgreSQL , как например PG_9.0_201008051 . (Этот подкаталог используется для того, чтобы последующие версии базы данных могли свободно использовать одно и то же местоположение, заданное в CREATE TABLESPACE .) Внутри каталога конкретной версии находится подкаталог для каждой базы данных, которая имеет элементы в табличном пространстве, названный по OID базы данных. Таблицы и индексы хранятся внутри этого каталога, используя схему именования файловых узлов. Табличное пространство pg_default недоступно через pg_tblspc , но соответствует PGDATA /base . Подобным же образом, табличное пространство pg_global недоступно через pg_tblspc , но соответствует PGDATA /global .

Функция pg_relation_filepath() показывает полный путь (относительно PGDATA ) для любого отношения. Часто это избавляет от необходимости запоминать многие из приведённых выше правил. Но следует помнить, что эта функция выдаёт лишь имя первого сегмента основного слоя отношения, т. е. возможно, понадобится добавить номер сегмента и/или _fsm , _vm или _init , чтобы найти все файлы, связанные с отношением.

Временные файлы (для таких операций, как сортировка объёма данных большего, чем может уместиться в памяти) создаются внутри PGDATA /base/pgsql_tmp или внутри подкаталога pgsql_tmp каталога табличного пространства, если для них определено табличное пространство, отличное от pg_default . Имя временного файла имеет форму pgsql_tmp PPP . NNN , где PPP — PID серверного процесса, а NNN служит для разделения различных временных файлов этого серверного процесса.

Как лучше всего хранить изображения в Postgres?

Изображения имеют размер около 4-6 мегапикселей каждое, а мы храним до 3000. Также стоит отметить: это не веб-приложение, максимум два внешних интерфейса будут обращаться к базе данных одновременно.

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

Нам нужно различать «исходное изображение» и «обработанное изображение», например, миниатюру.

Как говорится в ответе Jcoby, есть два варианта, поэтому я рекомендую:

используйте blob (Binary Large OBject): для хранения оригинальных изображений за вашим столом. См. Ответ Ивана (нет проблем с резервным копированием больших двоичных объектов!), Дополнительные поставляемые модули PostgreSQL , инструкции и т. Д.

использовать отдельную базу данных с DBlink : для оригинального хранилища изображений в другой (унифицированной / специализированной) базе данных. В этом случае я предпочитаю bytea , но blob примерно такой же. Разделение базы данных - лучший способ для «единого веб-сервиса изображений».

ПРИМЕЧАНИЕ 1. Сегодня использование «двойных решений» (база данных + файловая система) устарело (!). Использование «только базы данных» вместо двойного дает много преимуществ. PostgreSQL имеет сопоставимую производительность и хорошие инструменты для экспорта / импорта / ввода / вывода.

ПРИМЕЧАНИЕ 2: помните, что PostgreSQL имеет только bytea , а не имеет BLOB Oracle по умолчанию : «Стандарт SQL определяет (. ) BLOB. Формат ввода отличается от bytea, но предоставляемые функции и операторы в основном такие же», Руководство .

РЕДАКТИРОВАТЬ 2014 : Я не изменил исходный текст выше сегодня (мой ответ был 22 апреля 2012 г., теперь с 14 голосами), я открываю ответ на ваши изменения (см. «Режим Wiki», вы можете редактировать!), Для корректуры и для обновлений .
Вопрос стабильный (ответ @Ivans '08 с 19 голосами), помогите, пожалуйста, улучшить этот текст.

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

Так же, прочитал, что этот метод не работает с удаленного хоста.

Подскажите, как можно записывать и извлекать изображения из базы данных с удаленного хоста (желательно помещать туда еще и doc файлы).

ЗЫ. Разработку веду при помощи qt-sql (4.5.X)

Одна из ошибок разработчиков

Одна из ошибок разработчиков (это моё личное, хотя и очень глубокое убеждение) - это использовать БД для таких вещей, для которых её не надо использовать. К ним как раз относится хранение картинок и документов в бинарных форматов в БД. Действительно, для чего мы сохраняем информацию в БД? Для того, чтобы среди однотипной информации её можно было извлечь, задав определённые условия, т.е. грубо говоря обеспечить поиск нужной записи по каким-то уникальным признакам или агрегировать, обсчитать массив информации и т.д. Для файлов в бинарном формате ничего этого нет, СУБД превращается в простое хранилище куска неиндексируемой информации, выдаваемой по ключу. При этом хранилище весьма неэффективное - объём БД раздувается, методы извлечения и размещения имеют низкое быстродействие и жрут много ресусов, в случае краха БД восстановлению не подлежит!

Ещё один способ, если не хочется связываться с большими объектами: взять бинарный файл, пропустить его через кодировщик MIME или UUE и получившийся текст сохранить в поле с типом TEXT. Соответственно обратное получение - это декодировние. Такой способ ещё и будет более переносимым между разными СУБД.

Спасибо за ответ

По поводу лирики. Если Вы не знаете, что необходимо сделать, так зачем критиковать? Если есть возможность хранения бинарной информации в базе данных, значит это действительно нужно.
В моем проекте именно необходимо хранить БИНАРНУЮ информацию в базе данных.

ЗЫ. Спасибо за ответ.

> Если Вы не знаете, что

> Если Вы не знаете, что необходимо сделать, так зачем критиковать?
В том-то и дело, что сколько раз я сталкивался с проектами, которые хранили файлы в БД столько раз это самое хранение давало такие минусы, которые перевешивали все плюсы. Впрочем как я писал вначале предыдущего поста - это МОЁ ЛИЧНОЕ мнение. Вы же можете оставаться при своём - это ваше право.

Попробую mime поковырять

допустим, есть

Если у вас есть сервер, то

Видел я серверы документооборота, где PDF, XLS и DOC хранились непосредственно в БД в BLOB'ах. При этом СУБД далеко не последняя: Informix. При этом БД была под 100G всего-то (где кстати метаданные документов и вся кухня документооборота занимали меньше 2G - остальное были сами документы), но даже на таком мощном сервере как SunFire 880, с 8G оперативки, 4мя процессорами и SCSI-fiberchannel винтами, ворочалось очень тяжело! И клиентов-то активных было всего человек 30-40.

Читайте также: