Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and there are errors in the slave's log files, namely "invalid magic number 0000" and "out-of-sequence timeline ID 1 (after 2)".

Do you have any idea how can I troubleshoot this? Or why does it happen?

Details follow.

Log file error messages: (on the slave, pg_log/postgresql-Sat.log)

LOG:  entering standby mode
LOG:  redo starts at 0/1C78848
LOG:  consistent recovery state reached at 0/1C7FA30
LOG:  database system is ready to accept read only connections
LOG:  invalid magic number 0000 in log file 0, segment 1, offset 14942208
LOG:  streaming replication successfully connected to primary
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
FATAL:  terminating walreceiver process due to administrator command
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
...

Transaction logs: (on the slave -- weird, log file 0...2...1 is from January 2012, but 0...2...2 is from December 2011!?)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 20 21:33]  000000020000000000000001
├── [Dec 21  5:29]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

1 directory, 5 files
-bash-4.1$ cat pg_xlog/00000002.history 
1       000000010000000000000001        no recovery target specified

Transaction logs: (on the master)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 21  8:55]  000000020000000000000001
├── [Dec 21  5:31]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

-bash-4.1$ cat pg_xlog/00000002.history 
1       000000010000000000000001        no recovery target specified

Config files: (on the slave)

---postgresql.conf---
wal_level = hot_standby
...
hot_standby = on
...

---recovery.conf---
standby_mode = 'on'
primary_conninfo = 'host=dw0azewdbpv11danny user=replicator password=...'
recovery_target_timeline = 'latest'

Finally, on the slave, ps aux shows that:

Jan20 0:01 postgres: startup process waiting for 000000020000000000000001

Update one week later:

There seems to be a replication bug for PostgreSQL builds with gcc 4.6.0.
http://archives.postgresql.org/pgsql-general/2011-07/msg00686.php
and
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=45d792f70272ed57b932816562f31c2f79426c2a
However I'd guess that that bug would not affect 9.1?

PostgreSQL versions should be identical on master and slave, and SELECT version() looks like this on both master and slave:

-bash-4.1$ psql
psql (9.1.1)
Type "help" for help.

postgres=# SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
(1 row)

Perhaps I should also mention that the current master was a slave, originally, and the current slave was the master, originally. -- I did one failover, to test that it worked okay.

(I'll ask this question on the Postgres mailing lists.)

share|improve this question
    
may be you should check if the server date is same between standby and master node. may be you can copy 000000020000000000000001 from master to standby nodes restore path and try again. –  Hz.Root Jan 23 '12 at 12:10
    
@Hz.Root: You mean run date in Bash? Did that and dates match. Copying file 000000020000000000000001 to the standby node might work, but I'd rather not do that without actually knowing what the problem is. I suppose the problem will reappear after a while unless I really fix it. Thanks anyway :-) –  KajMagnus Jan 28 '12 at 5:51
    
Maybe there is something more principal about this error? i observe it also on a replication pair with different postgres, and gcc versions: Master: PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit Slave: PostgreSQL 9.1.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit observed problem: As KajMagnus': postgres: startup process waiting for 000000020000000000000001 ... and replication dieing avay every few days. –  user32124 Dec 26 '13 at 11:29

1 Answer 1

up vote 2 down vote accepted

This doesn't sound like the same thing. The GCC version is different and your PostgreSQL version is different. IIRC it was an issue with GCC 4.6 optimizations and was fixed. I don't believe it should have impacted your setup.

More likely something changed which caused this. Perhaps the slave was promoted to a master briefly and then demoted again? That will create problems like this all the time.

The timeline issue suggests to me that this is in fact what happened. It doesn't matter whether you write to a db or not, just completing recovery and promoting is enough to break the timeline.

share|improve this answer
    
Thanks Chris for your answer! Regrettably I don't remember much about this issue. Half a year later, or so, (that is, one year ago), I did a failover to another mahcine, and everything been working okay since. (As part of the failover I overwrote all/most old files) –  KajMagnus Sep 9 '12 at 17:21
    
Thanks for your input on the gcc bug and Postgres version 9.1 –  KajMagnus Sep 9 '12 at 17:28
    
Concerning "Perhaps the slave was promoted to a master briefly and then demoted again". When I did the failover tests, Initially I didn't know about the recovery_target_timeline parameter, and lots of failover attempts failed and I did the failover again and again. I don't know if this might have demoted and promoted the original master, but it seems reasonable that it could have happened. –  KajMagnus Sep 9 '12 at 17:30
    
Since I don't remember much about the issue, and since you've suggested a reasonable explanation, I'll accept your answer as the correct one. (Otherwise I'll never be able to accept any answer, and the question will be open forever, since I don't remember much about what happened or didn't happen). Thanks –  KajMagnus Sep 9 '12 at 17:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.