Re: Take your postgresSql on the road, and live to tell of it.

On 8/7/07, Owen Hartnett <owen(at)clipboardinc(dot)com> wrote:
> At 2:15 PM -0700 8/7/07, Ben wrote:
> >How many users do you have? Have you considered giving each user a
> >schema in which to make their changes? It sounds like you don't
> >really have a multi-master replication issue, which makes things
> >easier.
>
> Maybe I'm not understanding the strategy, but I don't see what this
> buys me, as I have to end up with a single database schema that has
> incorporated all the changes. If I can "record" all the SQL a user
> does from the checkpoint on, then I can "psql <" it in to the main
> database. Once I've combined their data into the database that sits
> on the server, I don't need their database copies anymore.

I'm not sure how you're planning to do this. PostgreSQL doesn't use
SQL statements for Point in Time Recovery, it uses WAL logs applied
against a database that's an exact physical copy of the database at a
previous time. Are you going to write your own application that will
let you save each SQL statement before it's applied to the user's
local database?

And if so, are you then going to have an individual database for each
user? That might work.

My way isn't all that much harder to do. It just lets you store all
the data in one database and share it out with all the users.

So it really depends on what you want in the end. With one database,
it would be much easier to run a query across all your data at once.
With individual databases you have very strong isolation between the
data sets.

Either way would work, each has its own advantages and disadvantages.