9
votes
Accepted
Does a MySQL replication slave need to be as powerful as the master?
The replica should be able to handle the same rate of writes as its source, or else it may fall behind. This is called replication lag.
The replica may get a chance to catch up if the writes on the ...
- 16k
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 ...
- 57.3k
7
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 ...
- 79.4k
6
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 ...
- 56.4k
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 ...
- 10.3k
6
votes
Change host IP of master in mysql replication
After a long internet surfing I got a thing. MASTER_LOG_FILE will take Relay_Master_Log_File and MASTER_LOG_POS will take Exec_Master_Log_Pos as their respective value in CHANGE MASTER TO statement. ...
- 91
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 ...
- 31.6k
5
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 ...
- 56.4k
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 ...
- 22.7k
4
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 ...
- 5,733
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-...
- 782
4
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?
...
- 56.4k
4
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 ...
- 79.4k
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 ...
- 56.4k
4
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 ...
- 16k
4
votes
Postgres max_wal_size and wal_keep_size parameter
The behavior or lag on the standby side does not influence the behavior of the primary server, as far as these parameters are concerned. If the standby falls behind by more than wal_keep_size, it is ...
- 56.4k
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.
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 ...
- 184k
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 ...
- 1,590
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 ...
- 79.4k
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 ...
- 2,186
3
votes
How to free up hard drive space after deleting millions upon millions of records in mariadb
It is correct that InnoDB tablespace files never shrink. There may be unused space within a large file after you delete a lot of rows, but the file still occupies a lot of space on your filesystem.
...
- 16k
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',
...
- 141
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 ...
- 79.4k
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, ...
- 646
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 ...
- 184k
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, ...
- 79.4k
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 ...
- 4,997
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. ...
- 26.9k
Only top scored, non community-wiki answers of a minimum length are eligible