7 votes
Accepted

Post Mortem Analysis: PostgreSQL replication failed

Based on this I'd say you didn't have enough wal_keep_segments on the master, weren't using a replication slot, and either had hot_standby_feedback off or had the connection drop for long enough for ...
user avatar
  • 52.1k
6 votes
Accepted

Can we replicate or restrict specific databases in MongoDB

Replica sets in MongoDB are designed for data redundancy and failover, so all data-bearing members of a replica set currently contain the same databases & collections as at MongoDB 4.2. A member ...
user avatar
  • 9,805
5 votes
Accepted

org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery

What you see is a common experience for anyone running standbys and bigger queries on the master. There are a couple of possible causes: There are also additional types of conflict that can occur ...
user avatar
  • 28.9k
5 votes
Accepted

MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set

This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x. The default (for good technical reasons) COLLATION for the 8.0 is utf8mb4_0900_ai_ci. Note the "255" associated ...
user avatar
  • 68.7k
4 votes
Accepted

In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?

STATEMENT → ROW is absolutely a valid configuration. The thing you've probably overlooked is that Server B needs log_slave_updates enabled, or it won't replicate anything from A to C. But to ...
user avatar
4 votes
Accepted

MySQL Replication Slave Status OK but Data inconsistent

You can use pt-table-checksum and pt-table-sync to fix the data mismatch between the master and the slave. Here's a blog post for further reference: https://www.percona.com/blog/2015/08/12/mysql-...
user avatar
4 votes

Upgrading Postgres major version using logical replication

Not everything is replicated with logical replication: only INSERT, UPDATE and DELETE (from v11 on also TRUNCATE). So it is definitely not a substitute for streaming replication. Also, streaming ...
user avatar
  • 33.2k
4 votes
Accepted

Postgresql - increase WAL retention to avoid slave go out of sync with master

No, that won't help at all. You have three alternative options: increase wal_keep_size to a big enough value That is a simple, but coarse solution, in that it makes the primary always retain a ...
user avatar
  • 33.2k
3 votes
Accepted

Service mongod fails after adding security.keyFile in config file

Use full path for keyfile. Don't use ~ -character there. Your mongod is started (probably) with user 'mongod' and that user don't have home directory. Additionally keyfile must be have chmod 0400 and ...
user avatar
  • 5,198
3 votes
Accepted

Options for having same tables in 2 different data bases

What you're looking for is Transactional Replication. It allows you to replicate specific objects from one database to another.
user avatar
  • 33.5k
3 votes
Accepted

MySQL slave not able to execute long running update query

You need to stop replication, make the Slave have the same specs as the Master, then start replication. Make sure the Slave has no incoming connections. Otherwise, that will make the SQL thread on ...
user avatar
3 votes
Accepted

PostgreSQL service start failed on CentOS 7

You have 3 steps before being abble to connect to a PostgreSQL Database: Install PostgreSQL Create the data cluster ($PGDATA) Start the server You already installed PostgreSQL, you need now to ...
user avatar
  • 1,355
3 votes
Accepted

PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?

By default, CREATE SUBSCRIPTION will first copy all the existing data from the source tables. The idea is that you usually start with an empty destination database created with something like ...
user avatar
  • 33.2k
3 votes

Is it possible to configure pgbouncer to distribute queries among master and slave?

pgBouncer cannot do that, and indeed it is a questionable feature. How can you reliably tell that a statement like SELECT pg_create_physical_replication_slot('aname'); won't modify your database? ...
user avatar
  • 33.2k
3 votes

MySql replication failed with error 1236 data position > file size with relay-bin file not increasing

To avoid the problem in the future, turn on sync_binlog. What is happening is that the replication stream had not yet gotten to the Slave when the Master shutdown. The offset into the binlog had been ...
user avatar
  • 68.7k
3 votes

How to avoid replication lag in case all writes on master and reads on replica?

Replication always has lag, if only a very small amount, because your data changes are not even written to the binary log until you commit a transaction. Then the replica has to download those events ...
user avatar
  • 9,761
3 votes
Accepted

How to avoid replication lag in case all writes on master and reads on replica?

Some SELECTs exist to support the Inserts/Updates/Deletes that you are about to do. You must not send them to the Replica; they must be done in the Primary, usually inside the same transaction with ...
user avatar
  • 68.7k
3 votes
Accepted

Fix mysql replication crashed due to underlying storage corruption

You don't mention what version of MySQL you have, or what type of Replication you are using, so I am guessing from the output it is 5.7 or earlier and you're not using GTID's. In my experience this ...
user avatar
  • 1,920
2 votes
Accepted

SELECT from slave and INSERT into master in MySQL

To config Laravel to split reads and writes you can edit config/database.php this way: 'connections' => [ 'mysql' => [ 'driver' => 'mysql', 'charset' => 'utf8', ...
user avatar
2 votes

SELECT from slave and INSERT into master in MySQL

Dangerous. Read about "Critical Read". If you blindly send all SELECTs to a Slave, you could screw up transactions on the master. Or you could miss data when replication is "behind". Instead, modify ...
user avatar
  • 68.7k
2 votes
Accepted

Metrics for monitoring master-slave replication

In the primary side, you have to use the view pg_stat_replication and compare differents fields to see the drift. The function pg_xlog_location_diff give the drift in size : select pid, ...
user avatar
  • 596
2 votes

In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?

Personally, I am not comfortable with different binlog_format settings between Master and Slave (See my old post from 1.5 years ago MySQL v5.1.73 - can the binlog_format betweem Master & slave be ...
user avatar
2 votes

MYSQL DATABASE BACKUP

Plan A - Copy It is quite reasonable, even with a mixture of Engines, to stop a Slave, copy mysql's directory tree to another Slave, verify that the Slaves have my.cnf files with different server_id, ...
user avatar
  • 68.7k
2 votes
Accepted

MYSQL DATABASE BACKUP

700GB is too much for mysqldump. Restore will take too much time (but I would measure to know numbers). XtraBackup will be faster. Taking backup from a slave is OK, but you have to make sure the ...
user avatar
  • 4,827
2 votes

How does trigger file in recovery.conf work?

Any reliable failover scenario requires a third party, sometimes called a "witness", "arbitrator", "cluster monitor", "cluster manager" etc. to detect failure of one member and initiate a failover. ...
user avatar
  • 21.5k
2 votes
Accepted

MySQL replication works but slave keeps reconnecting to master

It turns out my slave's server_id was too long. I was naming these after the slaves' public IP addresses, and this IP was longer than the max value of 4294967295. I should have checked when I decided ...
user avatar
  • 131
2 votes
Accepted

MySQL chain replication timing of writes on first slave

To answer this, you need to understand the way MySQL replication works and the difference between the IO_thread and the SQL_thread. In a nutshell, the IO_thread pulls events from the master and ...
user avatar
2 votes

How does one scale out with master-slave replication?

I have to keep my answer very generic as you have not mentioned any particular database system and what type of master slave implementation you want to know about. However, they can only write to the ...
user avatar
  • 12.3k
2 votes

Post Mortem Analysis: PostgreSQL replication failed

First thing: look at the logs. You will find warnings, error, fatal, and panic messages. You can find where your logs are in your postgresql.conf file. Look for the logging_collector setting, if it'...
user avatar
  • 1,355

Only top scored, non community-wiki answers of a minimum length are eligible