Re: Staging Database

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

On 08/07/2013 03:38 AM, BladeOfLight16 wrote:

On Wed, Aug 7, 2013 at 4:43 AM, Luca Ferrari <fluca1978@xxxxxxxxxxx> 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