PostgreSQL: Ist es besser, verwenden Sie mehrere Datenbanken mit einem schema oder einer Datenbank mit mehreren Schemata?

Nach dieser Kommentar auf eine meiner Frage, ich bin am überlegen ob es besser ist, mit einer Datenbank mit X-Schemata oder Umgekehrt.

Meine situation: ich entwickle eine web-Applikation, wo, wenn die Leute registrieren, erstelle ich (eigentlich) eine Datenbank (Nein, es ist nicht ein Soziales Netzwerk: jeder muss Zugang zu seinen eigenen Daten und sehen nie die Daten des anderen Benutzers).

Das ist der Weg, den ich in der vorherigen version verwendet meine Anwendung (die laufen immer noch auf MySQL): über die Plesk-API, für jede Anmeldung, ich weiß:

  1. Erstellen Sie einen Datenbank-Benutzer mit eingeschränkten rechten;
  2. Erstellen Sie eine Datenbank zugegriffen werden kann, nur durch die bisher erstellten user und der superuser (für die Wartung)
  3. Die Datenbank füllen

Nun, ich werde tun müssen, um das gleiche mit PostgreSQL (das Projekt wird immer reifer und MySQL... nicht erfüllen alle Bedürfnisse).

Brauche ich, um alle Datenbanken/schemas unabhängige sicherungen: pg_dump funktioniert perfekt in beide Richtungen, und das gleiche für die Benutzer konfiguriert werden können, um den Zugriff nur einem schema oder einer Datenbank.

So, vorausgesetzt, Sie sind erfahrene PostgreSQL-Benutzer als mir, was Sie denken, ist die beste Lösung für meine situation, und warum?

Wird es performance-Unterschiede, die mit $x-Datenbank anstelle von $x schemas? Und was die Lösung sein wird, besser zu pflegen in die Zukunft (Zuverlässigkeit)?

Alle meine Datenbanken/schemas wird immer haben die gleiche Struktur!

Für die sicherungen-Problem (mit pg_dump), ist vielleicht besser mit einer Datenbank und viele schemas, dumping alle Schemata auf einmal: die Wiederherstellung wird ganz einfach das laden der Haupt-dump in eine Entwicklungs-Maschine und dann dump und restore einfach nur das schema benötigt: es gibt einen zusätzlichen Schritt, aber verwirft alle das schema scheint schneller als dumping Sie eins nach dem anderen.

UPDATE 2012

Gut, die Anwendung, die Struktur und das design so stark geändert in den letzten zwei Jahren. Ich bin immer noch mit der one db with many schemas Ansatz, aber noch habe ich eine Datenbank für jede version meiner Anwendung:

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

Für backups, ich bin dumping jede Datenbank regelmäßig, und bewegen Sie die sicherungen auf dem Entwicklungs-server.

Ich bin auch mit der PITR/WAL-backup aber, wie ich schon sagte, es ist nicht wahrscheinlich, werde ich wiederherstellen müssen alle Datenbank - auf einmal... also wird es wohl entlassen werden dieses Jahr (in meiner situation nicht der beste Ansatz ist).

Der man-db-viele-schema-Ansatz funktionierte sehr gut für mich, da nun, auch wenn die Anwendung Struktur völlig verändert:

Fast hätte ich es vergessen: alle meine Datenbanken/schemas wird immer haben die gleiche Struktur!

...nun, jedes schema hat seine eigene Struktur, die sich dynamisch ändern die Reaktion auf Benutzer-Datenfluss.

  • "alle meine Datenbanken/schemas wird immer die gleiche Struktur!" meinst du, Sie haben alle die gleiche Struktur? Oder noch nie?
  • Sorry, ja, Sie haben alle die gleiche Struktur für immer: wenn ich ein zu ändern, die ich ändern werde sind alle von Ihnen 😉
  • Wenn Sie über 1000 Kunden, das bedeutet, dass Sie haben, um update-1000-schema?
  • ja, aber ich update nur die Tabellen-Struktur, nicht die Daten.
  • So, was haben Sie in gehen für schließlich? Eine Frage, obwohl, obwohl die Leistung von Abfragen, etc. gesteuert werden kann mit tablespaces, schemas, die in einem vergleichbaren Leistung von multi-db-vs multi-schema, eine Auswirkung auf die WAL-logs???
  • gut, das design der Applikation wurde radikal geändert in der Zeit... lassen Sie mich update meine Frage mit ein paar details
  • Wie garantieren Sie die Sicherheit zwischen zwei schemas? Eine DB-Verbindung kann die Aktualisierung mehrere Schemas - dies ist ein Segen und kahl wie gut

InformationsquelleAutor Strae | 2009-07-20
  1. 98

    Einer PostgreSQL - "schema" ist in etwa das gleiche wie ein MySQL "Datenbank". Viele Datenbanken auf PostgreSQL-installation bekommen kann problematisch; viele schemas funktioniert ohne Probleme. So Sie definitiv möchten, gehen Sie mit einer Datenbank und mehreren schemas in die Datenbank.

    • Diese. Postgres kann man nicht mit einer Abfrage über Datenbanken, die ziemlich unerfreulich sein kann.
    • "Viele Datenbanken auf PostgreSQL-installation bekommen kann problematisch" -- bitte klären; ist es problematisch, generell oder in diesem speziellen Fall, und warum?
    • "Der häufigste Anwendungsfall für die Verwendung von mehreren schemas in einer Datenbank ist der Aufbau einer software-as-a-service-Anwendung, wobei jeder Kunde hat ein eigenes schema. Während diese Technik scheint überzeugend, wir empfehlen dringend, gegen ihn, wie es verursacht hat zahlreiche Fälle von betrieblichen Problemen. Zum Beispiel, sogar eine moderate Anzahl von Schemata (> 50) können schwerwiegende Auswirkungen auf die Leistung von Heroku Datenbank-snapshots-tool" devcenter.heroku.com/articles/heroku-postgresql
    • Interessanterweise scheint das Gegenteil zu sein Fazit aus kquinn ist (angenommen) Antwort.
    • Für diejenigen, die Lesen es, bis Ende 2015. Es ist ein dblink Postgres-Erweiterung für das Abfragen über die Datenbanken jetzt (das ist eine Antwort an @mattb Kommentar).
    • Für diejenigen, die Lesen es nach 2014, S. foreign data wrappers ab v9.3, insbesondere die postgres_fdw ermöglicht das Abfragen über Pg-Datenbanken (IMO besser als dblink).
    • Mit einer Datenbank mit vielen Schemata machen es praktisch unmöglich, dump einem einzigen schema von denen, though. Ich bin mit einem einzigen PostgreSQL-Datenbank mit mehr als 3000 schemas und pg_dump nur schlägt mit einer out of memory " - Fehlermeldung, wenn Sie versuchen, dump einem einzigen schema. Ich Frage mich, ob das anders sein hatte ich 3000 Datenbanken statt.
    • Warum kann es problematisch? Ich kann sehen, wie mehrere PostgreSQL-Cluster auf einem server bekommen kann problematisch jedoch mehrere Datenbanken (mit einem schema die einzelnen, in der Regel nur darum, Zeug in public) in einem cluster sollten in Ordnung sein.
    • Graben in, dies ist ein Recht interessanter Artikel über die Materie, influitive.io/... und auch rob.conery.io/2014/05/28/eine-bessere-id-generator für postgresql behebt einige der Probleme, denen Sie in ausgeführt werden kann. Der erste Artikel hat einen Kommentar entspricht, die zu viele-schema ist-viele-Tabellen Zusammenhänge von Josh Berkus (medium.com/@jberkus/...)

    InformationsquelleAutor kquinn
  2. 23

    Werde ich auf jeden Fall gehen, für die man-db-viele-Schemata-Ansatz. Dies ermöglicht mir, dump der Datenbank, aber die Wiederherstellung nur einem sehr leicht, in vielerlei Hinsicht:

    1. Dump der db (alle schema), laden Sie den dump in eine neue db-dump nur das schema, die ich brauche, und wieder zurück in die Haupt-db.
    2. Dump-schema einzeln, eins nach dem anderen (aber ich denke, die Maschine wird leiden auf diese Weise mehr - und ich gehe davon aus wie 500-schemas!)

    Ansonsten googeln um, die ich gesehen habe, dass es kein automatisches Verfahren zum duplizieren einer-schema (mittels eines als Vorlage), aber viele deuten das so:

    1. Erstellen Sie eine template-schema
    2. Wenn Sie benötigt werden, zu duplizieren, benennen Sie Sie mit neuen Namen
    3. Dump es
    4. Benennen Sie Sie wieder
    5. Wiederherstellung der dump -
    6. Die Magie ist getan.

    Habe ich geschrieben, zwei Zeilen in Python, das zu tun; ich hoffe, Sie können helfen, jemanden (in-2-Sekunden-schriftliche-code, verwenden Sie es nicht in der Produktion):

    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]

# Temperary 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 previous 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()
    InformationsquelleAutor Strae
  3. 9

    Ich würde sagen, gehen Sie mit mehreren Datenbanken UND mehreren schemas 🙂

    Schemas in PostgreSQL sind viel wie packages in Oracle, in Fall, dass Sie sind vertraut mit den. Datenbanken sind dafür gedacht, zu unterscheiden zwischen ganze Sätze von Daten, während die Schemata sind mehr wie Daten-Entitäten.

    Zum Beispiel könnten Sie haben eine Datenbank für eine gesamte Anwendung mit den Schemata "UserManagement", "LongTermStorage" und so weiter. "UserManagement" würde, dann enthalten die "User" - Tabelle, sowie alle gespeicherten Prozeduren, Trigger, Sequenzen, etc. benötigt werden für die Benutzerverwaltung.

    Datenbanken sind ganze Programme, schemas Komponenten.

    • ... und so habe ich 1 Datenbank, mit der innerhalb des schemas: $customer1_user_schema, $customer2_user_schema, $customer3_user_schema, $customer1_documents_schema, $customer2_documents_schema, $customer3_documents_schema? Mh... dont scheinen eine zuverlässige Art und Weise... und was ist mit performance? Und was ist mit dem code meiner Anwendung (wird php und python)? so viele schemas..
    • Ich lese das so: jeder Kunde hat seine Datenbank customer1_database, customer2_database und innerhalb dieser Datenbanken müssen Sie user_schema, documents_schema.
    InformationsquelleAutor
  4. 3

    Einer Anzahl von schemas sollte leichter als eine Reihe von Datenbanken, obwohl ich kann nicht finden, einen Verweis, die dies bestätigt.

    Aber wenn Sie wirklich wollen, um die Dinge sehr unterschiedlichen (statt refactoring der web-Anwendung so, dass ein "Kunde" - Spalte wird Hinzugefügt, um Ihre Tabellen), können Sie immer noch wollen, verwenden Sie separate Datenbanken: ich geltend machen, dass Sie leichter zu machen, stellt eine Besondere Kunden-Datenbank diese Weise-ohne zu stören die anderen Kunden.

    InformationsquelleAutor Troels Arvin
  5. 3

    In einer PostgreSQL-Kontext empfehle ich die Nutzung einer db mit mehreren schemas, wie Sie können (z.B.) UNION ALL über schemas, aber nicht über Datenbanken. Aus diesem Grund, eine Datenbank ist wirklich völlig isoliert von anderen Datenbanken und schemas sind nicht isoliert von anderen schemas innerhalb derselben Datenbank.

    Wenn Sie -aus irgendeinem Grund - haben zum konsolidieren von Daten über schemas in der Zukunft, wird es leicht sein, dies zu tun, die sich über mehrere schemas. Mit mehreren Datenbanken, die Sie brauchen würde, mehrere db-verbindungen und sammeln und Zusammenführen der Daten aus jeder Datenbank "manuell" durch Anwendung von Logik.

    Letztere haben Vorteile in einigen Fällen, aber zum größten Teil ich denke, das eine Datenbank-mehrere-schemas Ansatz ist mehr nützlich.

    InformationsquelleAutor emax
  6. -4

    Holen Sie sich die Dinge klar:

    Ersten, die meisten der Zeit, die Sie möchten, um einige Datenbank schreibgeschützt und einige Lesen/schreiben. So halten Sie das schema als read-only gehalten werden können, die auf verschiedenen Datenbanken und Lesen/schreiben Sie das schema in einer anderen Datenbank, obwohl ich schlage vor, Sie halten eine maximale 25-30 schema in einer Datenbank, wie Sie nicht wollen, erstellen Sie eine Last auf die Datenbank für Protokolle für alle-schema.

    Hier ist ein Artikel, wenn Sie mehr Lesen möchten.

    • Wenn Sie können, bitte formulieren Sie diese Antwort, um deutlicher zu sein
    • Ich, die zweite f.khantsis.
    InformationsquelleAutor Danish Shaikh
Schreibe einen Kommentar