Replication
"Database Replication" refers to dynamically copying any incoming insert/update/delete statements being made to one database to another identical database. The primary and secondary databases are considered "replicas" because at all times they contain the same data produced by the same history, though the secondary may be delayed by a small stretch of time. The secondary may be used for read-only queries in place of the primary. And the secondary may be transformed into a primary in case of fail-over.
Migration
What you are seeking is loosely called "Database Migration", also called schema migration. The idea here is to reproduce the same structure (DDL) as another database. But each produced database is independent of the others, each having different users’ data stored in them.
- A prime example example is multi-tenancy.
- Another use might be as part of a vertical-market app being licensed to various customers where on each new sale you need to establish a fresh database in a certain configuration as expected by the app.
Migration tools
Developers and DBAs used to do this migration work manually, devising their own ad-hoc solutions or habits. Fortunately we now have a choice of tools to help with this tedious though crucial chore.
Flyway
My favorite tool is Flyway. Built in Java, but wrapped as command-line executables so you can use from scripts or other tools.
Flyway runs through a collection of SQL scripts, and optionally certain Java libraries you may write. The order of execution is determined by a certain file naming scheme.
A table is automatically created within the database to track scripts run. When new scripts are encountered, Flyway detects them and appropriately runs those that have not yet been applied to that particular database.
Flyway or other such tools are extremely helpful not only in production but also in testing and development. You can recreate a throw-away database automatically, and even bring it up to any particular point in the history of its schema evolution.
Anyone considering Flyway should also consider another quite similar product: Liquibase.
Other choices exist as well, such as the commercial tool Redgate.
Templates
Some databases such as Postgres provide for templates where you can create some tables and indexes and so in a database. After that, you can create a fresh new database whose initial state is copied from that state.
Indeed, Postgres always works that way, where template0 and template1 databases are used transparently by default when you ask for a database without explicitly specifying any other template.
Templates only solve the genesis problem, getting started. You will almost certainly evolve your database later. You'll add or drop tables, columns, indexes, and so on. You may need to run code to convert or update existing data en masse. For this you'll want to use migration tools, as the templates cannot help you after that initial genesis. So in my opinion, I would not bother with defining a template, instead using migration scripts for all that initial setup.
