so

(ye)SQL

Volume 4, Issue 47; 28 Dec 2020

I have a small application that uses a PostgreSQL database. How does one manage “staging” versus “production” in such an environment?

I have an application with a “production” PostgreSQL database. That’s all well and good, the application interrogates (and updates) that database.

I also have a staging database where new content is added and various editorial checks are performed.

At some point, the editor gives a 👍 and the staged content is supposed to be copied over to the production database to “go live”.

In a NoSQL world, with, for example, an XML database, I’d find the documents in the staging database that were new (or newer) than the documents in the production database, and copy them over.

I can sort of imagine a couple of different ways to manage this with tables, but I’m a complete novice in the relational world so I assume there are well known techniques for this sort of thing that I just don’t know.

I could, for example, add a timestamp to every row in every table and write SQL to extract “newer” rows from the tables in the staging database and insert them in the production database. That seems like a lot of overhead. (The database is small, so “meh”, I guess it would be ok.)

I could abandon the staging database altogether and add a “published” column. But that’s going to be problematic for edits to existing resources and it’s not clear that it would be easy to manage this across several tables.

Most of what I find searching the web for solutions to this problem are posts about copying a production database to a staging server (for testing, I assume). That seems to be approached as a backup/restore process. Even if the downtime associated with a database restore wasn’t an issue, that’s not going to work here because I don’t want to lose inserts that have occurred in the production database, I just want to add to it.

It seems unlikely that I’m the first person to have this problem (although maybe I have this problem because I should “obviously” have approached this whole issue in some completely different way in a relational world).

Suggestions most humbly solicited.