Postgresql: лучше ли использовать несколько баз данных с 1 схемой в каждой или 1 базу данных с несколькими схемами?

Question

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

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

Это способ, который я использовал для предыдущей версии моего приложения (которая все еще работает на MySQL): через API plesk для каждой регистрации я делаю:

1. Создать базу данных пользователя с ограниченными правами;
2. Создайте базу данных, к которой может обращаться только предыдущий созданный пользователь и суперпользователь (для обслуживания)
3. Заполните БД

Теперь мне нужно сделать то же самое с postgresql (проект становится зрелым и mysql.. не выполняет все потребности)

Мне нужно, чтобы все резервные копии баз данных / схем были независимыми: pg_dump отлично работает в обоих направлениях, одинаково для пользователей, которые могут быть настроены для доступа только к 1 схеме или 1 базе данных.

Итак, если вы являетесь более опытным пользователем potsgres, чем я, что вы считаете лучшим решением для моей ситуации и почему?

Будут ли различия в производительности при использовании схем $x db вместо $ x? И какое решение будет лучше поддерживать в будущем (надежность)?

Изменить: Я почти забыл: все мои базы данных / схемы всегда будут иметь одинаковую структуру!

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

ps: извините, если я забыл символ 'W' в тексте, моя клавиатура перенесла эту кнопку;)

ОБНОВЛЕНИЕ 2012

Что ж, структура приложений и дизайн сильно изменились за последние два года. Я все еще использую 1 db with many schemas подход, но все же, у меня есть 1 база данных для каждой версии моего приложения:

Db myapp_01
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema
Db myapp_02
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema

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

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

С тех пор подход 1-db-many-schema очень хорошо работал для меня, даже если структура приложения полностью изменилась:

я почти забыл: все мои базы данных / схемы всегда будут иметь одинаковую структуру!

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

Answer 1

"Схема" PostgreSQL примерно такая же, как "база данных" MySQL. Наличие большого количества баз данных при установке PostgreSQL может стать проблематичным; Наличие множества схем будет работать без проблем. Таким образом, вы определенно хотите использовать одну базу данных и несколько схем в этой базе данных.

Answer 2

Я бы не рекомендовал принятый ответ - несколько баз данных вместо нескольких схем по этому набору причин:

1. Если вы используете микросервисы, вы хотите обеспечить невозможность соединения между своими «схемами», чтобы данные не запутывались, и разработчики не присоединялись к другой схеме микросервиса и не задавались вопросом, почему, когда другая команда вносит изменения, их вещи больше не работает.
2. Позже вы можете перейти на отдельный компьютер с базой данных, если этого требует ваша нагрузка.
3. Если вам нужно настроить высокую доступность и / или репликацию, лучше иметь отдельные базы данных, полностью независимые друг от друга. Вы не можете реплицировать только одну схему по сравнению со всей базой данных.

Answer 3

Определенно, я пойду на подход 1-дБ-много-схем. Это позволяет мне выгружать всю базу данных, но восстановить ее очень легко разными способами:

1. Дамп базы данных (все схемы), загрузка дампа в новую базу данных, дамп только нужной мне схемы и восстановление в основной базе данных
2. Дамп схемы по отдельности, один за другим (но я думаю, что таким образом машина пострадает больше - и я ожидаю, как 500 схем!)

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

1. Создать шаблон-схему
2. Когда нужно дублировать, переименуйте его с новым именем
3. Дамп это
4. Переименовать его обратно
5. Восстановить дамп
6. Волшебство сделано.

Я написал 2 строки в Python, чтобы сделать это; Я надеюсь, что они могут кому-то помочь (за 2 секунды написанного кода, не используйте его в производстве):

import os
import sys
import pg

#Take the new schema name from the second cmd arguments (the first is the filename)
newSchema = sys.argv[1]
#Temp folder for the dumps
dumpFile = '/test/dumps/' + str(newSchema) + '.sql'
#Settings
db_name = 'db_name'
db_user = 'db_user'
db_pass = 'db_pass'
schema_as_template = 'schema_name'

#Connection
pgConnect = pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass)
#Rename schema with the new name
pgConnect.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema))
#Dump it
command = 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile
os.system(command)
#Rename back with its default name
pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template)
#Restore the previus dump to create the new schema
restore = 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile
os.system(restore)
#Want to delete the dump file?
os.remove(dumpFile)
#Close connection
pgConnect.close()

Answer 4

Я бы сказал, пойти с несколькими базами данных и несколькими схемами:)

Схемы в postgres очень похожи на пакеты в Oracle, если вы знакомы с ними. Базы данных предназначены для различения целых наборов данных, в то время как схемы больше похожи на объекты данных.

Например, у вас может быть одна база данных для всего приложения со схемами "UserManagement", "LongTermStorage" и так далее. Тогда "UserManagement" будет содержать таблицу "User", а также все хранимые процедуры, триггеры, последовательности и т. Д., Необходимые для управления пользователями.

Базы данных - это целые программы, схемы - это компоненты.

Answer 5

В postgres-контексте я рекомендую использовать одну базу данных с несколькими схемами, как вы можете (например) UNION ALL для всех схем, но не для баз данных. По этой причине база данных действительно полностью изолирована от другой базы данных, в то время как схемы не изолированы от других схем в той же базе данных. Если вам по какой-либо причине придется консолидировать данные по схемам в будущем, это будет легко сделать по нескольким схемам. С несколькими базами данных вам потребуется несколько db-соединений, а также собирать и объединять данные из каждой базы данных "вручную" с помощью логики приложения.

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

Answer 6

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

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

Answer 7

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

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

При использовании нескольких схем:

1. Чтобы позволить многим пользователям использовать одну базу данных, не мешая друг другу.
2. Организовать объекты базы данных в логические группы, чтобы сделать их более управляемыми.
3. Сторонние приложения можно поместить в отдельные схемы, чтобы они не конфликтовали с именами других объектов.

Answer 8

Получите ясность прежде всего, когда вы хотите, чтобы некоторые Db только для чтения, а некоторые для чтения / записи, поэтому сохраняйте схему, используемую как только для чтения, можно хранить в diff Db, а схему чтения / записи в базе данных Diff, хотя я бы посоветовал вам сохранить MAX 25-30 схема в одной БД, так как вы не хотите создавать нагрузку на базу данных для журналов для всей схемы

Вот одна статья, если вы хотите узнать больше