0

I'm working on postgres replication and pgpool2, it works ok.

Suppose that, I have one master and two slave servers, when master(1) down, pgpool will promote one of two slave servers to master(2).

So, my issue is the time when I stop master(1) server, data still come to master(2) database, and I updated some fields in master(2) db, when I start again with master(1) database, will have two case occur:

  1. The master(2) server will be still kept and sync the data to master(1) database, this situation will lead to all of DDL I updated will be lost.

  2. The master(2) server will be down to slave and will be sync data(by use rsync) from master(1) database, this situation will be lead to all data from user in the time will be lost.

So, have any recommendation or the way to solve it?

Thank you.

Trung Huynh
  • 293
  • 1
  • 5
  • 13
  • 1
    Multi-master with PgPool? Huh? I think you're going to have to provide lots more detail, starting with your PgPool configuration. (Also this is off topic, I've voted to migrate to dba.SE). – Craig Ringer Sep 04 '14 at 05:34
  • Thanks, above is flow of my pgpool, it seems to me that pgpool configuration is not necessary, what does dba.SE mean? – Trung Huynh Sep 04 '14 at 06:30
  • @CraigRinger: Curretly, I applied master-slave architecture, when master stop, slave will become master, I described the problem I met. I have just read multi-master with pgpool but I think it's not suitable in my case. – Trung Huynh Sep 04 '14 at 06:53
  • If you're not doing multi-master, then I think you're just failing to re-create the old master as a replica of the new master after failover. You might want to check out http://repmgr.org/ . Or maybe you're not using synchronous replication, so not all data is replicated at failover time? – Craig Ringer Sep 04 '14 at 06:56
  • @CraigRinger: Yes, all of data replicated from master at failover time, the situation is when old master update database and start again, I need to find the way to synchronize between old master hold new schema and stale data with new master hold old schema and newest data – Trung Huynh Sep 04 '14 at 09:25
  • You can't. You must delete the old master, then make a copy of it from the new master to re-create it as a replica. repmgr will automate this for you. There's an impovement in 9.4 that makes it possible to "reset" the old master to a standby instead, but it's still fundamentally the same thing. – Craig Ringer Sep 04 '14 at 09:30
  • Yeap, it is problem I'm facing, if I update new schema on the new master, the database will be downed, I use replication and load balacing to hold database always live, if master need to be down when update schema, why would I use it? – Trung Huynh Sep 04 '14 at 10:08
  • 1
    If you're using streaming replication, DDL is replicated automatically. No downtime is required. It does not make sense that "the database will be downed". At this point, unless you show your actual configuration, error logs, etc, I don't think I can help you, so I won't reply further unless I see an update with **exact error message text**, configuration files, etc. – Craig Ringer Sep 04 '14 at 10:11

0 Answers0