Before copying anything, we need to determine what to copy. In some instances, it might be necessary to copy the entire database for disaster-recovery purposes. At other times, such a copy would waste resources. We need to differentiate between these two scenarios.
Once we've done this, we should decide what to do when we don't want to copy the whole database. We need to know which tables to copy and where to send them. To accomplish this, we will build a very small spreadsheet in this section to keep track of the resources we will need for all of our table and database replicas.
We're going to build a spreadsheet. This spreadsheet will specify the type of replica we want to maintain, as well as where it will reside. Have a spreadsheet program available before starting.
Follow these steps to determine replication resource requirements:
- Create a spreadsheet with six columns labeled
Source Server,Target Server,Type,DB Name,Table, andSet. - Under the
Source Servercolumn, list the role or name of the PostgreSQL server that provides the data. - Under the
Target Servercolumn, list the role or name of the PostgreSQL server that receives the data. - Under the
Typecolumn, select eitherReplicato copy the whole database orLogicalto copy individual tables. - Under the
DB Namecolumn, enter the name of the database where tables reside on the source server. If you are usingReplicaforType, enterAllhere. - Under the
Tablecolumn, enterAllfor every table in the listed database, or enter a single table name. If you are copying multiple individual tables, create a single row for each table. - Under the
Setcolumn, enter a name for the set of tables being copied. Do this only if usingLogicalfor theTypecolumn. - Create at least one row in the spreadsheet for a Disaster Recovery (DR) copy of every source server in your PostgreSQL clusters.
The spreadsheet we're making only requires six columns to fit this recipe. Feel free to include any other relevant information when making your own. In fact, we suggest that you retain this document for reference purposes and revisions.
We begin by listing the name or role of the server where all the data will originate. This Source Server column will help us—and everyone else—to keep track of where the original data resides. If a server is listed too often in this column, we may want to reconsider removing some replicas so that we don't overwhelm it.
Next, we need to decide where to send the data. The Target Server column lets us define where the tables will reside after being replicated. This allows us to formally dictate how many copies will live in how many locations. There are some limitations based on the type we define for this replica entry.
When listing the type of replication, we have only two options. We can either mirror the entire database as a Replica, or single tables in the case of a Logical copy. Any target server can only appear once if its value in the Type column is Replica. Otherwise, a server might receive several Logical sources.
Then, we need to list DB Name where we can find the table to copy. If we are copying the entire database as a Replica, this value will always be All. Otherwise, we should list a single database name.
Next, which table will we copy? In the case of a Replica type, this value will be All. Otherwise, should we copy the entire listed database or an inventory of specific tables? To mirror every table in the database, enter All here. Otherwise, use the name of the table (including its schema) that we want to include.
Finally, if we are copying a list of individual tables or a named database, we should name the replica as Set. Replication utilities commonly use these set names to address the objects being copied, so we can define any sets we plan to use.
The final step we've listed is to determine where we require at least one copy of the entire database. This replica will be an online copy that we can switch to in the case of server or data center failure. In a truly high availability architecture, this is not optional.
With all of these entries, our spreadsheet might look something like this:
In this particular example, we have our Disaster Recovery copy of the database and another full replica for departments to query without disturbing the primary system. Then, we copy three tables to the reporting database for our Business Intelligence or Marketing teams to integrate into their customer activity reports.