You understood correctly. Since streaming replication keeps the replica a physical copy of the primary database cluster, you can only replicate between one primary and one replica.
There are two options:
Keep three clusters on the replica machine, one for each primary cluster.
Use logical replication.
With logical replication, you have to make sure that ...
It looks like AWS DMS (Database Migration Service) supports migrating data from external SQL instances. I was able to set up a migration task that replicates data and changes from Heroku to RDS. Whether it actually works for me is yet to be seen ;)
--- EDIT ---
Looks like services like DMS don't work with Heroku, because they require more privileged roles ...
The rolling index build procedure is recommended. Here is what they say in the doc:
To minimize the impact of building an index on replica sets and sharded clusters, use a rolling index build procedure as described on Build Indexes on Replica Sets.
for more info:
"Sharding" involves servers running in parallel, not replicating one from another.
The most practical way to copy a schema without the data is via mysqldump --no-data. However, there is no automation for that.
Bluntly put, you have to reinvent the sharding wheel. There is very little that addresses the many issues of sharding.
That said, I suggest you ...
The database generates WAL for the entire database instance. It is the logical sender's job to sort them out to restrict and construct what gets sent. I don't think there is a way to get the logical sender to work ahead. You should look at why it is falling behind, not why there is a lot of WAL.
Mysql (community) Version 8.0.17-1.sles12 - OpenSUSE tumbleweed 2019.10.02
mysql> SET GLOBAL expire_logs_days = 4;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds
cannot be used together. Please use binlog_expire_logs_seconds to set the expire
time (expire_logs_days is deprecated)
It's infuriating that the only mention of this seemingly crucial piece of configuration is in the release notes from a major version ago...
Added a write-ahead log (WAL) heartbeat (that is, running dummy queries) for replication from a PostgreSQL source. This feature was added so that idle logical replication slots don't hold onto old WAL logs, which can ...
As you already found out your master database is too busy for single replication worker to handle all of the changes.
You need to cluster your tables - but make sure you do it in such way that tables with foreign keys are handled with the same worker, otherwise you might get into situation where foreign key constraint will prevent data from being inserted ...
For anyone who will encounter a similar issue here's what worked for me:
Run the following on your merge publication database :
@publication = 'MyPublication',
@article = 'MyArticle',
@property = 'published_in_tran_pub',
@value = 'true';
For more info about the problem read this article :https://docs....
The fastest way will be pg_upgrade with the -k option.
This way. you can get away with a few minutes down time, which is required to move the database metadata.
If you cannot afford a few minutes down time, you will have to resort to a trigger based replication method like Slony. This way, you can replicate the database to a current version and switch the ...
You probably want a cluster such as MySQL InnoDB Cluster (from Oracle) or MariaDB Galera Cluster or Percona XtraDB Cluster in each region for high availability. You can write to any of the nodes in the cluster and the transaction will be automatically replicated to the other nodes. Note that both Percona XtraDB Cluster and MariaDB Galera Cluster are based on ...
I underestimated how busy the master database was and that's because in July I observed replication working without errors. Apparently July is the month of holidays so the database sustained minimally less load for the problem not to manifest.
One of my colleagues pointed out that there is multiple processes writing to that database concurrently hence maybe ...
This adds to @Miroslav's answer except that the hostname found in /etc/hostname had to match the hostname found in /etc/hosts (dont forget to reboot when changed).
I was using a common /etc/hosts file for all my servers but wasn't updating the /etc/hostname file to match.