PostgreSQL : 각각 하나의 스키마로 여러 데이터베이스를 사용하거나 여러 스키마로 데이터베이스를 사용하는 것이 더 낫습니까?

PostgreSQL : 각각 하나의 스키마로 여러 데이터베이스를 사용하거나 여러 스키마로 데이터베이스를 사용하는 것이 더 낫습니까?

---

내 질문 중 하나에 대한 이 의견 후에 X 스키마가있는 하나의 데이터베이스를 사용하는 것이 더 좋은지 또는 그 반대인지 생각합니다.

내 상황 : 사람들이 등록 할 때 (실제로) 데이터베이스를 만드는 웹 응용 프로그램을 개발 중입니다 (소셜 네트워크가 아닙니다 : 모든 사람이 자신의 데이터에 액세스해야하며 다른 사용자의 데이터를 보지 않아야합니다) .

이것이 내가 이전 버전의 응용 프로그램 (여전히 MySQL에서 실행중인)에 사용 된 방식입니다 .Plesk API를 통해 모든 등록에 대해 다음을 수행합니다.

1. 제한된 권한으로 데이터베이스 사용자를 작성하십시오.
2. 이전에 생성 한 사용자와 수퍼 유저 만 액세스 할 수있는 데이터베이스를 만듭니다 (유지 보수 용).
3. 데이터베이스를 채 웁니다

이제 PostgreSQL과 동일한 작업을 수행해야합니다 (프로젝트가 성숙 해지고 MySQL은 모든 요구를 충족시키지 못합니다).

모든 데이터베이스 / 스키마 백업을 독립적으로 수행해야합니다. pg_dump는 두 가지 방식으로 완벽하게 작동하며 하나의 스키마 또는 하나의 데이터베이스에 액세스하도록 구성 할 수있는 사용자에 대해서도 동일하게 작동합니다.

따라서 나보다 경험이 많은 PostgreSQL 사용자라고 가정하면 내 상황에 가장 적합한 솔루션은 무엇이라고 생각합니까?

$ x 스키마 대신 $ x 데이터베이스를 사용하면 성능 차이가 있습니까? 그리고 미래에 어떤 솔루션을 유지하는 것이 더 좋을까요 (신뢰성)?

모든 데이터베이스 / 스키마는 항상 같은 구조를 갖습니다!

백업 문제 (pg_dump 사용)의 경우 하나의 데이터베이스와 많은 스키마를 사용하여 한 번에 모든 스키마를 덤프하는 것이 좋습니다. 복구는 개발 머신에서 기본 덤프를로드 한 다음 필요한 스키마 만 덤프 및 복원합니다. 하나의 추가 단계이지만 모든 스키마를 덤프하면 하나씩 덤프하는 것보다 빠릅니다.

2012 업데이트

지난 2 년 동안 응용 프로그램 구조와 디자인이 크게 바뀌 었습니다. 나는 여전히 one db with many schemas접근 방식을 사용하고 있지만 여전히 각 응용 프로그램 버전마다 하나의 데이터베이스 가 있습니다 .

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

백업의 경우 각 데이터베이스를 정기적으로 덤프 한 다음 개발 서버에서 백업을 이동합니다.

나는 또한 PITR / WAL 백업을 사용하고 있지만 이전에 말했듯이 모든 데이터베이스 를 한 번 에 복원하지 않아도 될 것입니다. 그래서 올해는 해산 될 것입니다 (내 상황에서는 최선의 접근 방식이 아닙니다) ).

one-db-many-schema 접근법은 응용 프로그램 구조가 완전히 바뀌더라도 지금부터 매우 잘 작동했습니다.

나는 거의 잊었다. 모든 데이터베이스 / 스키마는 항상 같은 구조를 가질 것이다 !

... 현재 모든 스키마에는 사용자 데이터 흐름에 동적으로 반응하는 고유 한 구조가 있습니다.

---

PostgreSQL "스키마"는 MySQL "데이터베이스"와 대략 동일합니다. PostgreSQL 설치에 많은 데이터베이스가 있으면 문제가 발생할 수 있습니다. 많은 스키마가 있으면 문제없이 작동합니다. 따라서 해당 데이터베이스 내에 하나의 데이터베이스와 여러 스키마가 있어야합니다.

---

확실히, 나는 1-db-many-schemas 접근 방식으로 갈 것입니다. 이를 통해 모든 데이터베이스를 덤프 할 수 있지만 여러 가지 방법으로 하나만 쉽게 복원 할 수 있습니다.

1. db (모든 스키마)를 덤프하고, 새 db에 덤프를로드하고, 필요한 스키마 만 덤프 한 후 기본 db로 다시 복원하십시오.
2. 스키마를 하나씩 하나씩 덤프하십시오 (그러나 머신이 이런 식으로 더 많이 고통받을 것이라고 생각합니다-그리고 500 개의 스키마를 기대합니다!)

그렇지 않으면 인터넷 검색을 사용하여 스키마를 복제하는 자동 절차가없는 것을 보았지만 (하나는 템플릿으로 사용) 다음과 같이 제안합니다.

1. 템플릿 스키마 만들기
2. 복제해야 할 경우 새 이름으로 이름을 바꾸십시오.
3. 버려
4. 다시 이름을 바꿉니다
5. 덤프 복원
6. 마법이 이루어집니다.

파이썬에서 두 행을 작성했습니다. 나는 그들이 누군가를 도울 수 있기를 바랍니다 (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]

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

---

여러 데이터베이스와 여러 스키마를 사용하여 말합니다. :)

PostgreSQL의 스키마는 Oracle의 패키지와 매우 유사합니다. 데이터베이스는 전체 데이터 세트를 구별하는 반면 스키마는 데이터 엔티티와 유사합니다.

예를 들어 스키마 "UserManagement", "LongTermStorage"등을 사용하여 전체 응용 프로그램에 대해 하나의 데이터베이스를 가질 수 있습니다. "UserManagement"에는 "User"테이블과 사용자 관리에 필요한 모든 저장 프로 시저, 트리거, 시퀀스 등이 포함됩니다.

Databases are entire programs, schemas are components.

---

A number of schemas should be more lightweight than a number of databases, although I cannot find a reference which confirms this.

But if you really want to keep things very separate (instead of refactoring the web application so that a "customer" column is added to your tables), you may still want to use separate databases: I assert that you can more easily make restores of a particular customer's database this way -- without disturbing the other customers.

---

In a PostgreSQL context I recommend to use one db with multiple schemas, as you can (e.g.) UNION ALL across schemas, but not across databases. For that reason, a database is really completely insulated from another database while schemas are not insulated from other schemas within the same database.

If you -for some reason- have to consolidate data across schemas in the future, it will be easy to do this over multiple schemas. With multiple databases you would need multiple db-connections and collect and merge the data from each database "manually" by application logic.

The latter have advantages in some cases, but for the major part I think the one-database-multiple-schemas approach is more useful.

---

Get the things clear:

First, most of the time you would like to make some database read-only and some read/write. So keep the schema used as read-only can be kept on different databases and read/write the schema in a different database, although I would suggest you to keep a maximum 25-30 schema in one database as you don't want to create a load on the database for logs for all schema.

Here is one article if you want to read more.

참고URL : https://stackoverflow.com/questions/1152405/postgresql-is-it-better-to-use-multiple-databases-with-one-schema-each-or-one