Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to host several client sites using one PostgreSQL installation. What's the best way to split them -- by schema or by instance?

The factors would be what makes admin easier (especially backup/restore of individual sites), performance of the sites, and overall efficiency of memory usage.

Thanks Jason

share|improve this question

1 Answer 1

up vote 1 down vote accepted

The "best" way is heavily dependent on your use case, however back in my hosting days we used to simply give each client their own DB user which was DBO on their particular database, with appropriate permission & login restrictions (in Postgres and pg_hba.conf respectively) so they couldn't mess with anyone else's data.

Backup/Restore can be handled using pg_dump (dump each database individually so they can be easily restored without having to pick through the dump file), and performance/tuning is the same as any medium-to-large instance of Postgres.


Splitting by schema (within the same database) is also an option, but it doesn't enforce the same level of separation as a separate DB, and you obviously can't let clients create their own schemas (or do anything else that requires DBO) if you do this.


Splitting by instance (Separate Postgres server for each client) is the ultimate level of separation, but really requires an individual OS user for each client (so Postgres is running under totally separate users). This is also a huge memory/resource sink: Postgres (like most DB systems) should ideally be the only thing running on a server, and only one instance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.