I am setting up Postgres v14 database. I have a question related to the `max_wal_size` and `wal_keep_size` parameters. According to the documentation: `max_wal_size` ⇒ Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceed `max_wal_size` under special circumstances, such as heavy load, a failing `archive_command`, or a high `wal_keep_size` setting. `wal_keep_size` ⇒ Specifies the minimum size of past log file segments kept in the `pg_wal` directory, in case a standby server needs to fetch them for streaming replication. What will happen if the value of `wal_keep_size` is greater than the value of `max_wal_size`, and the standby server is slow and lagging in replication by more than `wal_keep_size`? `wal_keep_size` worth of data will be retained in the WAL directory on the primary. But since the WAL files size exceeded `max_wal_size`, will the checkpoint keep running until the size comes down below `max_wal_size`?

We have an application where we are receiving information seconds apart, we record this information in a table that we call events, which currently weighs 240 GB, this is the largest we have so far. Some time ago we deleted records to keep records from a specific date. Not long ago we realized that the server that stores the DB was filling up the disk, after deleting millions of millions of records, the disk space remained the same. Searching the internet we found that executing the following query ALTER TABLE tablename ENGINE = Innodb; would free up the used space of the deleted records. But we run into the problem that because we have tables that are larger than 200 G, the command takes a long time to execute, causing the new information to not be inserted in time due to the blocking that causes it. The option we find is the following: - Make a replica of the database (Master - Slave), from what we saw here we would have to make a backup of the master and pass it to the slave so that it begins to replicate from where it happened, for this the detail we have is that no we want to turn off our current server so that our service remains functional, but we also do not want to have any loss of information. We have a backup of our server a day before, we could use this, but how do we recover the information that was not inserted in a day, what does that backup have if it was created and from where was the slave configured? - Make backups of our current database and upload them to a new server so as not to use the ALTER TABLE tablename ENGINE = Innodb command, for this we would have to turn off our server. - Execute the ALTER TABLE tablename ENGINE = Innodb command, we perform a test and in the 10 GB table that we have as a test, it took almost 25 minutes to perform. Is there a more optimal way to free up space without having to cancel our service and without losing information? Or in the event that it has to be done in the shortest possible time? This query ALT

I have stuck with the replication lag problem. I am trying to refactor my DB infrastructure. The first step was read-write split. I used ProxySQL то implement it. Now I have masterDb where all INSERT and UPDATES executing and 2 replicas where I route all SELECT. But the main problem I faced with, replication lag. Because usually when you change something in DB you immediately read data and expect to have new data in the query result, but with replication lag, I receive outdated data. After googling I found this article https://www.percona.com/blog/2018/11/29/mysql-high-availability-stale-reads-and-how-to-fix-them/ and according to this info the best option in 2018 was "ProxySQL 2.0 GTID consistent reads" Any updates from 2018? Maybe you guys know a better solution? Please share!

Upon searching for this, the main cause of this error is usually when using snapshots or cloning the machine. But I did not do this. I perform mysqldump --master-data=2 on the master then restore the dump on my slave server. Then start the slave on the position indicated on the outfile. Any idea on what caused this? I am running a multi-source replica and this error is from just 1 source. here is the complete error: Got fatal error 1236 FROM MASTER WHEN reading DATA FROM BINARY LOG: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'VM1-bin.000006' at 641809774, the last event read from '/binlogdata/VM1-bin.000009' at 146836375, the last byte read from '/binlogdata/VM1-bin.000009' at 146836375.';

I have one master and one slave. Suppose IP of master is changed but the physical server is same. Now I want to update the new IP (of the master) at the slave server. At the slave server `master_info_repository` is set to `TABLE`. I want to resume the replication process from exactly the position where it has paused due to IP change. How to do this? mysql version of slave server is : `14.14 Distrib 5.7.30` and O/S is `ubuntu 18.04`.

I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this MySQL 8.016 -> MySQL 8.016 -> MariaDB 10.2 However, after changing master on my MariaDB instance, I got the dreaded `Error 'Character set '#255' is not a compiled character set` error. I then tried changing back to the old master, but I still got this error! I had this problem when I first got my setup to work a few months back, and the first time, I solved it by purging the `utf8mb4_0900_ai_ci` collation from my MySQL instances, and as far as I can see, they are still purged. Output from my MySQL 8.016 master instance ``` mysql> SHOW GLOBAL VARIABLES LIKE '%char%'; +--------------------------+-------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /rdsdbbin/mysql-8.0.16.R1/share/charsets/ | +--------------------------+-------------------------------------------+ 8 rows in set (0,04 sec) mysql> SHOW GLOBAL VARIABLES LIKE '%collat%'; +-------------------------------+--------------------+ | Variable_name | Value | +-------------------------------+--------------------+ | collation_connection | utf8mb4_general_ci | | collation_da

Master server MySQL 5.6.28 Ubuntu 14.04 8 core CPU Slave server Slave: MySQL 5.7.22 Ubuntu 18.04 16 core CPU Both of them have 64GB of RAM and plenty of disk space. What I did up to this point I made a dump of the master database, copied it over to the slave server and set up a Slave database there. The replication works, but it's too slow. The slave started approx. 2.5 days after the initial dump and isn't catching up. Looking at the relay log files, it seems that they're filling up too slowly (approx 1MB every few seconds). This is on a 100GB+ database. I've tried Checked the disk io with iotop - They're good on both the Master and the Slave. They're not SSDs but they don't seem to be the bottleneck. Checked network speeds with bmon - they're barely scratching the surface. Both machines are on a Gigabit network. I've tried running `scp` (with the replication in progress) and I'm getting up to 100MB/s transfer. The relay logs seem to be transfering at less than 1MB/s. Checked the CPU - both servers have plenty to spare. I made sure the `innodb` settings are the same on both servers. All tables are `innodb`. Looking at `SHOW SLAVE STATUS\G` I see that most of the time is spent waiting for new relay logs to transfer. There is no delay on the SQL side, it's always caught up. Worth mentioning that the `binlog_format` is `ROW`. tl;dr: What could be limiting the speed at which the binlogs are relayed from Master to Slave, if there is still plenty bandwidth, CPU and disk I/O available? EDIT 1: Results of `SHOW SLAVE STATUS`: ` Slave_IO_State: Waiting for master to send event Master_Host: master.server Master_User: sqlslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.285479 Read_Master_Log_Pos: 87535361 Relay_Log_File: slave-relay-bin.001588 Relay_Log_Pos: 87535479 Relay_Master_Log_File

Im trying to setup replication between `mariadb(10.1.31-MariaDB - MASTER)` and `mysql(5.7.12-log - SLAVE)`. After executed the start slave, I got the below error. Fatal error: The slave I/O thread stops because a fatal error is encountered when it tries to get the value of SERVER_UUID variable from master. Then I tried to get the value of master_uuid. ``` MariaDB [(none)]> show variables like '%server_uuid%'; Empty set (0.00 sec) ``` Then tried to setup this in mysql prompt. ``` MariaDB [(none)]> set global server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33; ERROR 1193 (HY000): Unknown system variable 'server_uuid' ``` And also I tried to setup auto.cnf in data directory. ``` [auto] server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33 chmod 644 auto.cnf ``` But still im getting the `fatal error is encountered when it tries to get the value of SERVER_UUID variable from master.`

Installed PostgreSQL using: ``` sudo yum install postgresql-server postgresql-contrib ``` PostgreSQL version: 9.2.18 When start service: ``` sudo service postgresql start ``` Got error: ``` Redirecting to /bin/systemctl start postgresql.service Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details. ``` The `journalctl -xe` detail: ``` -- Unit postgresql.service has failed. -- -- The result is failed. 7月 19 04:17:04 node2 systemd[1]: Unit postgresql.service entered failed state. 7月 19 04:17:04 node2 systemd[1]: postgresql.service failed. 7月 19 04:17:04 node2 polkitd[19772]: Unregistered Authentication Agent for unix-process:27133:53830492 (system bus name :1.405, object path /org/freedesktop/PolicyKit1/AuthenticationA 7月 19 04:19:33 node2 polkitd[19772]: Registered Authentication Agent for unix-process:27165:53845472 (system bus name :1.406 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object pat 7月 19 04:19:33 node2 systemd[1]: Starting PostgreSQL database server... -- Subject: Unit postgresql.service has begun start-up -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit postgresql.service has begun starting up. 7月 19 04:19:34 node2 pg_ctl[27187]: pg_ctl: could not start server 7月 19 04:19:34 node2 pg_ctl[27187]: Examine the log output. 7月 19 04:19:34 node2 systemd[1]: postgresql.service: control process exited, code=exited status=1 7月 19 04:19:34 node2 systemd[1]: Failed to start PostgreSQL database server. -- Subject: Unit postgresql.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit postgresql.service has failed. -- -- The result is failed. 7月 19 04:19:34 node2 systemd[1]: Unit postgresql.service entered failed state. 7月 19 04:19:34 node2 systemd[1]: postgresql.service failed. 7月 19 04:19:34 node2 polkitd[1977

To my understanding, a master-slave replication might be set up where clients can read from multiple databases. However, they can only write to the master. I believe this helps guarantee ACIDity. I also understand one-way replication seems generally preferred to two-way for the same reasons. I don't quite understand how this would work in terms of application development though. - How do you tell an application to write to one database and read from another? Does this mean this actually has to be programmed into the application layer? This isn't too big a deal. However, let's say it takes 60 seconds for that write to come back down to the local database. How does the client compensate for latency when he reloads the page and it re-queries the local db (missing that write)? This seems so error-prone! - How would this then scale? Understood that reads dominate a majority of a database's work. But what happens when you scale enough that one server can't handle all the writes? What options do you have? I'm trying to wrap my head around the subject but I'm missing answers to these basic questions.