Re: Staging Database

On 08/07/2013 03:38 AM, BladeOfLight16 wrote:
> On Wed, Aug 7, 2013 at 4:43 AM, Luca Ferrari <fluca1978(at)infinito(dot)it
> <mailto:fluca1978(at)infinito(dot)it>> wrote:
>
> ...
>
>
> I wasn't very careful with my wording. Sorry about that. There will be
> updates and possibly deletions as well as additions. Furthermore, the
> public version would be read only, I believe. The client would be
> modifying data, not end users. (It's a catalog site; the client is a
> non-profit that's publishing information in their field.)

In addition to other suggestions posted here, the fact that it's
read-only when live leads to some possibilities. I'll just toss them out
as a brain-dump in no particular order - many may not be practical for
your use.

1. Rename the databases. Have the staged and live databases in the same
database instance then when time to go live just "alter database live
rename to old;" followed by "alter database staged rename to live;"

2. Have the app query through views and have a script that repoints the
views to the new data.

3. Similar to #2, make all the primary tables empty parent tables with
data in the child tables. Put the new data into tables then have a
script drop the existing children and alter-table to make the new tables
children of your primary tables.

4. Run a second instance of PostgreSQL on a different port number to get
the staged database set up the way you want. When time to go live, stop
the servers, swap port numbers and restart.

5. Use the "Connection Service File", pg_service.conf, file to
name/route your connections and point your apps to the currently live
database (i.e. have staged and live in your cluster and point to the
desired db). This avoids needing to use pg_bouncer at the expense of
needing to update the pg_service.conf file on all clients.

6. Put the data into a new schema then just rename schemas.

Perhaps more will come to me later.

Cheers,
Steve