Re: Postgres architecture for multiple instances

On 23/02/15 15:53, Samuel Smith wrote:
> On 02/21/2015 05:25 PM, David Steele wrote:
>> On 2/21/15 6:08 PM, Adrian Klaver wrote:
>>>
>>> Currently the built in replication solutions work at the cluster level,
>>> not at the database level. There are third party tools, Slony and
>>> Bucardo come to mind, that can work at a more focused level.
>>
>> Again, it depends in what kind of access your customers have to the
>> database. Logical backups can be done on a per-database basis (and are
>> very practical for small datasets), but physical backups (which are more
>> efficient) must backup the entire cluster (except for the third-party
>> solutions noted above that have their own challenges).
>>
>> You said you don't want to give a lot of information, but the optimal
>> solution depends on how your customers connect to their database:
>>
>> 1) Entirely through an application or API
>> 2) Directly to the db via psql, PgAdmin, JDBC, etc.
>>
>> Without that information it's hard to give really solid advice.
>>
>
> Sorry, to be more specific our application is on top of a java
> framework. Similar to Ruby On Rails. Customers don't interface with
> the database at all, only through the website which provides our
> services.
>
> --Sam
>
>
In that case I'd go for one instance of pg.

With either

1. different DB's or ,
2. or same db, but fields identifying which customer is allowed access.

Depending what factors are important in your situation.

The second option might be best, if there is a lot in common between
customers (tables of data that are not customer specific, and/or common
pg configuration options).

Cheers,
Gavin