Re: Staging Database

BladeOfLight16 wrote
> The company I work for has a client who has expressed interest in having a
> staging database for their data. Staging as in they add data, do some
> QCing, then push to a database with an identical schema to make it public.
> Fortunately, we're not doing that right now, but it's something they may
> want later on. So I wanted to at least start thinking about it. What are
> some ways of going about it? Some kind of replication set up comes to
> mind,
> but it would need to be on demand, not constant. It also could be as
> infrequent as quarterly, if that makes a difference. Preferably, it would
> only require one server with PostgreSQL installed, but I'm not ruling out
> multiple servers.

My first option to evaluate would be simple schema delineation. Have one or
more schemas inside the same database where you place "staging" data. Have
specific "staging" users that can only see those schemas and can perform the
work on them. Once all the staged data has been
validation/modified/whatever you have a function that will migrate the
staging data from the staging schemas to the production/live schemas.

If you want to use only one server than whatever you are going to do with
the staging data cannot overly tax said box otherwise that requirement is
bogus. Otherwise two boxes means at least a second postgreSQL installation
on the second box. I'm out of my experience here but to then get the data
from one box to the other you can use something like dbLink/FDW or even
dumping the data to CSV and re-importing it in production (ideally still
using the same staging tables; you have the second server with the staging
tables also so you can perform resource-intensive actions).

Normally you are only altering a small portion of the data inside the
database and so should attempt to only "manage" that subset. One question
is once all of the data has been placed into production what happens to it?
Would the "new" staging area contain ALL information even that previously
imported and unchanged or does it only reflect changes from the last
production load? How and what kind of QC is going to be performed and will
you need to build interfaces to support those activities? Would you end up
QCing the same data time-after-time even if it was unchanged.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Staging-Database-tp5766603p5766665.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.