database - requerimientos - Postgresql: ¿es mejor usar múltiples bases de datos con 1 esquema cada una, o 1 base de datos con múltiples esquemas?

que es pgadmin (6)

Después de este comentario a una de mis preguntas, estoy pensando si es mejor usar 1 base de datos con esquemas X o viceversa.

Mi situación: estoy desarrollando una aplicación web donde, cuando las personas se registran, creo (en realidad) una base de datos (no, no es una red social: todos deben tener acceso a sus propios datos y nunca ver los datos del otro usuario) )

Esa es la manera que utilicé para la versión anterior de mi aplicación (que todavía se ejecuta en mysql): a través de la API de Plesk, para cada registro, lo hago:

Crear un usuario de base de datos con privilegios limitados;
Cree una base de datos a la que puedan acceder solo el usuario creado anterior y el superusuario (para mantenimiento)
Poblar el db

Ahora, tendré que hacer lo mismo con postgresql (el proyecto está madurando y mysql ... no cumple todas las necesidades)

Necesito tener todas las copias de seguridad de bases de datos / esquemas independientes: pg_dump funciona perfectamente en ambos sentidos, lo mismo para los usuarios que pueden configurarse para acceder a solo 1 esquema o 1 base de datos.

Entonces, asumiendo que usted es un usuario de potsgres más experimentado que yo, ¿cuál cree que es la mejor solución para mi situación y por qué?

¿Habrá diferencias de rendimiento usando $ x db en lugar de $ x esquemas? ¿Y qué solución será mejor mantener en el futuro (confiabilidad)?

Editar : casi lo olvido: ¡todas mis bases de datos / esquemas siempre tendrán la misma estructura!

Edit2 : para el problema de copias de seguridad (usando pg_dump), es mejor usar 1 db y muchos esquemas, volcando todos los esquemas a la vez: la recuperación será bastante simple cargando el volcado principal en una máquina dev y luego volcar y restaurar solo el esquema necesario : hay 1 paso adicional, pero volcar todo el esquema parece más rápido y luego volcarlos uno por uno.

ps: lo siento si olvidé un poco de ''W'' char en el texto, mi teclado sufre ese botón;)

ACTUALIZACIÓN 2012

Bueno, la estructura y el diseño de la aplicación han cambiado tanto en los últimos dos años. Todavía estoy usando el enfoque de 1 db with many schemas , pero aun así, tengo 1 base de datos para cada versión de mi aplicación:

Db myapp_01 /_ my_customer_foo_schema /_ my_customer_bar_schema Db myapp_02 /_ my_customer_foo_schema /_ my_customer_bar_schema

Para las copias de seguridad, estoy volcando cada base de datos regularmente, luego moviendo las copias de seguridad en el servidor de desarrollo.

También estoy usando la copia de seguridad PITR / WAL pero, como dije antes, no es probable que tenga que restaurar toda la base de datos a la vez ... por lo que probablemente se desestime este año (en mi situación no es el mejor enfoque).

El enfoque 1-db-many-schema funcionó muy bien para mí desde ahora, incluso si la estructura de la aplicación ha cambiado por completo:

casi lo olvido: ¡todas mis bases de datos / esquemas siempre tendrán la misma estructura!

... ahora, cada esquema tiene su propia estructura que cambia dinámicamente al reaccionar al flujo de datos de los usuarios.

Definitivamente, elegiré el enfoque de 1-db-many-schemas. Esto me permite volcar toda la base de datos pero restaurar solo 1 muy fácilmente, de muchas maneras:

Vuelque el archivo db (todo el esquema), cargue el volcado en un nuevo archivo db, vacíe solo el esquema que necesito y restaure en el archivo principal.
Vuelque el esquema por separado, uno por uno (pero creo que la máquina sufrirá más de esta manera, ¡y estoy esperando 500 esquemas!)

De lo contrario, buscando en Google he visto que no hay un auto-procedimiento para duplicar un esquema (usando uno como plantilla), pero muchos sugieren de esta manera:

Crear un esquema de plantilla
Cuando necesite duplicar, cambie el nombre con un nuevo nombre
Arrojarlo
Cambiar el nombre de nuevo
Restaurar el basurero
La magia está hecha.

He escrito 2 filas en python para hacer eso; Espero que puedan ayudar a alguien (código escrito en 2 segundos, no lo use en producción):

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()

En un contexto postgres, recomiendo usar un archivo db con varios esquemas, como puede (por ejemplo) UNION TODO en todos los esquemas pero no en las bases de datos. Por esa razón, una base de datos está completamente aislada de otra base de datos, mientras que los esquemas no están aislados de otros esquemas dentro de la misma base de datos. Si, por alguna razón, tiene que consolidar los datos entre los esquemas en el futuro, será fácil hacerlo en varios esquemas. Con múltiples bases de datos, necesitaría múltiples conexiones de db y recopilar y fusionar los datos de cada base de datos "manualmente" mediante la lógica de la aplicación.

Estos últimos tienen ventajas en algunos casos, pero para la mayor parte, creo que el enfoque de una sola base de datos y esquemas múltiples es más útil.

Obtenga las cosas claras La mayoría de las veces le gustaría hacer que algunos Db solo lean y algunos lean / escriban Así que mantenga el esquema usado como Solo lectura puede mantenerse en Db diff Y Lea / escriba el esquema en la base de datos Diff aunque le sugiero que mantenga MAX 25-30 esquema en una base de datos ya que no desea crear una carga en la base de datos para los registros de todos los esquemas

Aquí hay un artículo si quieres leer más

Un "esquema" de PostgreSQL es más o menos el mismo que una "base de datos" de MySQL. Tener muchas bases de datos en una instalación de PostgreSQL puede ser problemático; tener muchos esquemas funcionará sin problemas. Así que definitivamente quiere ir con una base de datos y varios esquemas dentro de esa base de datos.

Varios esquemas deberían ser más livianos que varias bases de datos, aunque no puedo encontrar una referencia que lo confirme.

Pero si realmente desea mantener las cosas muy separadas (en lugar de refaccionar la aplicación web para que se agregue una columna "costomer" a sus tablas), puede que desee utilizar bases de datos separadas: afirmo que puede realizar restauraciones de la base de datos de un cliente particular de esta manera, sin molestar a los otros clientes.

Yo diría, ve con múltiples bases de datos Y múltiples esquemas :)

Los esquemas en postgres se parecen mucho a los paquetes en Oracle, en caso de que esté familiarizado con ellos. Las bases de datos están destinadas a diferenciar entre conjuntos completos de datos, mientras que los esquemas son más como entidades de datos.

Por ejemplo, podría tener una base de datos para una aplicación completa con los esquemas "UserManagement", "LongTermStorage", etc. "UserManagement" contendría entonces la tabla "User", así como todos los procedimientos, desencadenantes, secuencias, etc. almacenados que son necesarios para la gestión del usuario.

Las bases de datos son programas completos, los esquemas son componentes.