Cascade Standby - [FATAL: the database system is starting up]

Поиск
Список
Период
Сортировка
От Murthy Nunna
Тема Cascade Standby - [FATAL: the database system is starting up]
Дата
Msg-id BD7563973EF8894D905C80593EE9A4ED370D45@MAIL01.fnal.gov
обсуждение исходный текст
Список pgsql-admin

Greetings!

 

I did some stress testing with cascade standby configuration. I ran into an issue where promoting the cascading standby to become new master causes its slave to sort of wait indefinitely in recovery. When you try to connect the salve at this point you will get the error “FATAL: the database system is starting up”. I came up with a work around for it. But I am not sure if the work around is something that will have an impact on something else later. I am seeking your blessing so I can proceed with it. Thanks for reading.

 

Scenario:

 

Postgresql 9.2.4

Master->slave1->slave2

Archive time out on master is 1200 seconds

Replication is WAL file based using pg_standby. No streaming.

Both slave1 and slave2 are hot standby(s)

 

Everything works just fine at this point. I created load in master and it goes all the way to slave2.

 

Steps to reproduce the issue:

1.      Create some heavy load on master (like a big fat insert that creates 50 million records. One insert with no commits in between).

2.      Wait for the completion of INSERT on master

3.      Promote slave1

4.      In slave2 recovery.conf, put recovery_target_timeline = 'latest' and restart the database

5.      Observe slave2 pg_log. It will apply timeline1 logs and then it will restore timeline2 history. After that it will restore one log from timeline2 and then waits forever for some logs that will never arrive. It looks for logs from timeline1 in timeline2 file naming format.

6.      Try connecting to the database, you will get an error. And in the pg_log you will see [FATAL: the database system is starting up]

 

My theory:

Slave1 has been promoted before the log that contains COMMIT record has arrived. You can still promote slave1 successfully. And also you don’t see your data from the INSERT that was done in primary. This is correct. No problem here. But when slave2 is restarted in timeline2 it needs to rollback the uncommitted data from timeline1 and that is the reason it is looking for timeline1 data but unfortunately he is already in timeline2 with new filename structure for logs. I am not sure if anyone reported this earlier and if there is a way to get around this. But following is how I got around it.

 

Work Around:

On slave2, cd to your archive directory and create timeline2 logs manually from timeline1 logs. E.g if you see a message in pg_log that pg_standby is waiting for 00000002000000220000001B then first confirm that you don’t have that file in the archive directory and then create it as below.

 

cp -p 00000001000000220000001B   00000002000000220000001B

 

Once you copy this file, you will see that it has been restored and applied but it waits for another like this. Repeat this manual copy process until you see the message “database system is ready to accept read only connections”

 

At this point it is in sync with slave1 and if you add data in slave1 it gets replicated to slave2.

 

Let me know what you think about this work around and if there is better one.

 

Thanks,

Murthy

 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Guillaume Lelarge
Дата:
Сообщение: Re: How to find table creation time
Следующее
От: "Sofer, Yuval"
Дата:
Сообщение: Postgres 9.2.8 crash sporadically on Windows