Postgresql: est-il préférable d'utiliser plusieurs bases de données avec 1 schéma chacune ou 1 base de données avec plusieurs schémas?

Question

Après ce commentaire à un de mes question, je suis en train de penser si il est préférable d'utiliser 1 base de données avec X schémas ou vice-versa.

Ma situation: je suis le développement d'une web-app, où, quand les gens vous inscrire, j'ai créer (en fait) une base de données (non, ce n'est pas un réseau social: tout le monde doit avoir accès à ses propres données et de ne jamais voir les données de l'utilisateur).

C'est la façon dont j'ai utilisé pour la previus verison de mon application (qui est toujours en cours sur mysql): grâce à parallels plesk panel de l'api, pour chaque enregistrement, je fais:

1. Créer une base de données de l'utilisateur avec des privilèges limités;
2. Créer une base de données accessible seulement par le précédent créé d'utilisateur et le super utilisateur (pour l'entretien)
3. Remplir la db

Maintenant, je vais avoir besoin de faire la même chose avec postgresql (le projet est d'arriver à maturité et mysql.. ne pas répondre à toutes les needes)

J'ai besoin d'avoir toutes les bases de données/schemas sauvegardes indépendantes: pg_dump fonctionne parfaitement dans les deux sens, même pour les utilisateurs qui peut être configuré pour l'accès à seulement 1 schéma ou 1 base de données.

Donc, en supposant que vous êtes plus expérimenté potsgres utilisateurs que moi, que pensez-vous est la meilleure solution pour ma situation, et pourquoi?

Aura-t-il des écarts de rendement à l'aide de $x db au lieu de $x schémas? Et quelle solution sera la mieux pour maintenir dans l'avenir (fiabilité)?

Edit: j'oubliais: toutes mes bases de données/schemas sera toujours avoir la même structure!

Edit2: Pour les sauvegardes question (à l'aide de pg_dump), est peut-être mieux à l'aide de 1 db et de nombreux schémas, les déversements de tous les schémas à la fois: la récupération sera très simple de charger le principal dump dans une machine de dev et puis dump et restore juste le schéma nécessaire: il y a 1 étape supplémentaire, mais dumping tous le schéma semble plus rapide, puis dumpin un par un.

p.s: désolé si j'ai oublié certains, " W " - char dans le texte, mon clavier souffrir de bouton ;)

Mise à JOUR 2012

Ainsi, la structure de l'application et la conception sont tellement changé lors de ces deux dernières années. Im encore à l'aide de l' 1 db with many schemas approche, mais tout de même, j'ai 1 base de données pour chaque version de mon application:

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

Pour les sauvegardes, im dumping chaque base de données régulièrement, puis en déplaçant les sauvegardes sur le serveur de dev.

Je suis également à l'aide de la PITR/WAL sauvegarde, mais, comme je l'ai dit avant, ce n'est pas probable que je vais avoir pour la restauration de la base de données à la fois.. donc il va probablement être rejeté cette année (dans ma situation n'est pas la meilleure approche).

Le 1-db-nombreux-schéma approche a très bien fonctionné pour moi, car maintenant, même si l'application de la structure est totalement changé:

j'ai presque oublié: toutes mes bases de données/schemas sera toujours avoir la même structure!

...maintenant, à chaque schéma a sa propre structure que le changement dinamycally réagir aux utilisateurs de flux de données.

Answer 1

Un "schéma" PostgreSQL est à peu près identique à une "base de données" MySQL. Avoir plusieurs bases de données sur une installation PostgreSQL peut être problématique; avoir beaucoup de schémas fonctionnera sans problème. Donc, vous voulez vraiment aller avec une base de données et plusieurs schémas au sein de cette base de données.

Answer 2

Certainement, je vais y aller pour le 1-db-nombre de schémas d'approche. Cela me permet de vider toutes les base de données, mais de restauration à seulement 1 très facile, à bien des égards:

1. Dump de la db (tous le schéma), charge le dump dans une nouvelle db, dump juste le schéma dont j'ai besoin, et de restaurer en db principaux
2. Dump le schéma séparément, un par un (mais je pense que la machine je vais plus souffrir de cette façon.. et j'attends, comme 500 schémas!)

Sinon, googler autour, j'ai vu qu'il n'y a pas d'auto-procédure pour reproduire un schéma (à l'aide de l'un comme modèle), mais beaucoup d'entre eux suggèrent de cette façon:

1. Créer un modèle de schéma
2. Quand le besoin de dupliquer, renommer avec le nouveau nom
3. Le Dump
4. Rétablissez
5. La restauration de la sauvegarde
6. La magie est fait.

J'ai écrit 2 lignes en python pour le faire, de l'espoir qu'ils puissent aider quelqu'un (en 2 secondes-écrit-code, ne pas utiliser en production):

import os
import sys
import pg

#Take the ne shcema 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()

p.s: oui, mon clavier souffrent encore de la " w " bouton, j'ai besoin d'un nouveau :)

Answer 3

Un certain nombre de schémas devrait être plus léger qu'un certain nombre de bases de données, bien que je ne trouve pas de référence qui le confirme.

Mais si vous voulez vraiment garder les choses très séparées (au lieu de refactoriser l'application Web afin qu'une colonne "costomer" soit ajoutée à vos tables), vous pouvez toujours utiliser des bases de données distinctes: J'affirme que vous pouvez plus facilement restaurer des restaurations. la base de données d'un client particulier de cette manière - sans déranger les autres clients.