Thread: Missing timeline history file after execution of pg_upgrade
Hi, after execution of pg_upgrade is the timeline history file (i.e. 00000002.history) in the new database cluster missing. Therefore,it is no longer possible to use this database cluster as the master for a streaming replication client. Test description: ----------------- - Running postgres database (Version 9.3.4) - Setting Up WAL Archiving for this database named "Database A" - Making a base backup of "Database A" - Crash of "Database A" - Do a recovering using a "Continuous archive backup" of "Database A" (new timeline is created) - Listing of data/pg_xlog (Database A) after recovering -- 16777216 Oct 22 13:36 000000010000000000000006 -- 16777216 Oct 22 13:36 000000010000000000000007 -- 16777216 Oct 22 13:36 000000010000000000000008 -- 41 Oct 22 13:36 00000002.history -- 16777216 Oct 22 13:36 000000020000000000000008 -- 16777216 Oct 22 13:38 000000020000000000000009 -- 16777216 Oct 22 13:54 00000002000000000000000A -- 16777216 Oct 22 13:54 00000002000000000000000B -- 16777216 Oct 22 13:54 00000002000000000000000C -- 4096 Oct 22 13:54 archive_status/ - Install a new Postgres database (Version 9.3.4) named "Database B" - Data transfer using pg_upgrade (Database A -> Database B) (pg_upgrade is not necessary, but it would be a standard procedure, that should work also with the same Postgres version) - Listing of data/pg_xlog (Database B) -- 16777216 Oct 29 06:00 00000002000000000000000D -- 16777216 Nov 5 06:00 00000002000000000000000E -- 16777216 Nov 10 14:08 00000002000000000000000F -- 4096 Oct 22 13:54 archive_status/ - Setup a streaming replication client named "Database C" on "Database B" - Error occurs: ... : could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/00000002.history":No such file or directory I have three questions: 1) Why pg_upgrade changed the timeline informations? 2) How do I get replication (Database B -> Database C) working? 3) Is this behavior intended. Is there a workaround for me? Please help me to troubleshoot this problem. I will feedback any required information. Thanks in advance Christian ________________________________ manroland web systems GmbH -- Managing Director: Joern Gosse Registered Office: Augsburg -- Trade Register: AG Augsburg -- HRB-No.: 26816 -- VAT: DE281389840 Confidentiality note: This eMail and any files transmitted with it are confidential and intended solely for the use of the individual or entityto whom they are addressed. If you are not the intended recipient, you are hereby notified that any use or disseminationof this communication is strictly prohibited. If you have received this eMail in error, then please delete thiseMail. ! Please consider your environmental responsibility before printing this eMail ! ________________________________
On Mon, Nov 10, 2014 at 01:48:40PM +0000, christian.echerer@manroland-web.com wrote: > I have three questions: > 1) Why pg_upgrade changed the timeline informations? > 2) How do I get replication (Database B -> Database C) working? > 3) Is this behavior intended. Is there a workaround for me? You need to create new standbys after the upgrade --- pg_upgrade does not retain the WAL files from the previous cluster, as the format might have changed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On 06.12.2014, bruce@momjian.us wrote: > You need to create new standbys after the upgrade --- pg_upgrade does not retain the WAL files from the previous cluster,as the format might have changed. I created a new standby (pg_start_backup(), rsync, ...) after the execution of pg_upgrade on the master. On the master isthe timeline history missing and i get the following error message when I try to set up a streaming client: ... : could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/00000002.history":No such file or directory Short test description (Postgres-Version 9.3.4) -------------------------------------- - Do a recovering on the Master (new timeline is created) - Execute pg_upgrade on the Master (timeline informations are missing) - Try to setup a new streaming client on the master - Error occurs: ... : could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/00000002.history":No such file or directory A longer description can be found in the mail from 11.10.2014. How do I get streaming replication working again? My current workaround is to restore the master with the help of pg_restore, after updating the database server version. Ittakes quite a long time, however, and is therefore a problem. Thanks in advance Christian ________________________________ manroland web systems GmbH -- Managing Director: Joern Gosse Registered Office: Augsburg -- Trade Register: AG Augsburg -- HRB-No.: 26816 -- VAT: DE281389840 Confidentiality note: This eMail and any files transmitted with it are confidential and intended solely for the use of the individual or entityto whom they are addressed. If you are not the intended recipient, you are hereby notified that any use or disseminationof this communication is strictly prohibited. If you have received this eMail in error, then please delete thiseMail. ! Please consider your environmental responsibility before printing this eMail ! ________________________________
Just to confirm that this problem exists: we upgraded recently from 9.3 to 9.4 using pg_upgrade, having the slave rebuilt as prescribed. Directly after starting up the slave, we started getting the above mentioned error. Replication to this slave works otherwise. Is there a way to get the history file back? -- View this message in context: http://postgresql.nabble.com/Missing-timeline-history-file-after-execution-of-pg-upgrade-tp5826326p5836521.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Hi, On Mon, Feb 2, 2015 at 11:07 AM, <christian.echerer@manroland-web.com> wrote: > > Short test description (Postgres-Version 9.3.4) > -------------------------------------- > - Do a recovering on the Master (new timeline is created) > - Execute pg_upgrade on the Master (timeline informations are missing) > - Try to setup a new streaming client on the master > - Error occurs: ... : could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/00000002.history":No such file or directory > > A longer description can be found in the mail from 11.10.2014. > > How do I get streaming replication working again? > > My current workaround is to restore the master with the help of pg_restore, after updating the database server version.It takes quite a long time, however, and is therefore a problem. The path to reproduce the problem is quite simple: - setup the master and the replica setup, and promote the replica, creating a new timeline (timeline 2 by default) - pg_upgrade the promoted replica. This would create a new cluster with the same timeline, but without the history file. - create the streaming-only replica. The streaming replication will never start, since the timeline file is not there. The error messages are: ERROR: could not open file "pg_xlog/00000002.history": No such file or directory FATAL: could not receive timeline history file from the primary server: ERROR: could not open file "pg_xlog/00000002.history": No such file or directory Note that this problem does not occur for the streaming-only replica of the newly created cluster (with a timeline 1), even when there is no timeline history file in the original datadir. -- Regards, Alexey Klyukin
On Tue, Feb 3, 2015 at 10:41 PM, Alexey Klyukin <alexk@hintbits.com> wrote: > The path to reproduce the problem is quite simple: > > - setup the master and the replica setup, and promote the replica, > creating a new timeline (timeline 2 by default) > - pg_upgrade the promoted replica. This would create a new cluster > with the same timeline, but without the history file. > - create the streaming-only replica. The streaming replication will > never start, since the timeline file is not there. The error messages > are: > > ERROR: could not open file "pg_xlog/00000002.history": No such file > or directory > FATAL: could not receive timeline history file from the primary > server: ERROR: could not open file "pg_xlog/00000002.history": No > such file or directory > > Note that this problem does not occur for the streaming-only replica > of the newly created cluster (with a timeline 1), even when there is > no timeline history file in the original datadir. In our case I solved the problem by manually creating a 00000002.history file on master. It consists of the previous timeline (obviously 1), the location when the new timeline has been created (lost) and the text reason, which can be arbitrary. As for the location, I don't think it matters much, since we don't have branched timelines and not going to switch to timeline 1 again, I've put the value of the 'Prior checkpoint location' from pg_controldata on the replica, but I think I could just put all 0 with the same effect. Nevertheless, the question is should pg_upgrade move the timeline history file from the old server to the new one, and, to be more specific, are there any cases when moving such file is not recommended? Kind regards, -- Alexey Klyukin
On Wed, Feb 4, 2015 at 09:23:56AM +0100, Alexey Klyukin wrote: > On Tue, Feb 3, 2015 at 10:41 PM, Alexey Klyukin <alexk@hintbits.com> wrote: > > The path to reproduce the problem is quite simple: > > > > - setup the master and the replica setup, and promote the replica, > > creating a new timeline (timeline 2 by default) > > - pg_upgrade the promoted replica. This would create a new cluster > > with the same timeline, but without the history file. > > - create the streaming-only replica. The streaming replication will > > never start, since the timeline file is not there. The error messages > > are: > > > > ERROR: could not open file "pg_xlog/00000002.history": No such file > > or directory > > FATAL: could not receive timeline history file from the primary > > server: ERROR: could not open file "pg_xlog/00000002.history": No > > such file or directory > > > > Note that this problem does not occur for the streaming-only replica > > of the newly created cluster (with a timeline 1), even when there is > > no timeline history file in the original datadir. > > > In our case I solved the problem by manually creating a 00000002.history file > on master. It consists of the previous timeline (obviously 1), the location when > the new timeline has been created (lost) and the text reason, which can be > arbitrary. As for the location, I don't think it matters much, since > we don't have > branched timelines and not going to switch to timeline 1 again, I've > put the value > of the 'Prior checkpoint location' from pg_controldata on the replica, > but I think > I could just put all 0 with the same effect. > > Nevertheless, the question is should pg_upgrade move the timeline history file > from the old server to the new one, and, to be more specific, are > there any cases when > moving such file is not recommended? Sorry I am just getting to this --- you are right it is a bug. I was unaware that pg_upgrade passed the timeline from the old cluster, but it does so when setting the WAL starting address with pg_resetxlog -l, which includes the timeline as the first eight hex digits. I think there are two options: 1) force pg_resetxlog -l to pass timeline 1 2) create a WAL history file to match the old cluster's timeline (!= 1) I don't think we want to be doing #2, so I have developed the attached patch for #1, which I should backpatch to 9.4. Do we store the timeline in any user tables that might be transfered? Anywhere else? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Attachment
On Thu, May 14, 2015 at 10:35:29PM -0400, Bruce Momjian wrote: > > > > Nevertheless, the question is should pg_upgrade move the timeline history file > > from the old server to the new one, and, to be more specific, are > > there any cases when > > moving such file is not recommended? > > Sorry I am just getting to this --- you are right it is a bug. I was > unaware that pg_upgrade passed the timeline from the old cluster, but it > does so when setting the WAL starting address with pg_resetxlog -l, > which includes the timeline as the first eight hex digits. > > I think there are two options: > > 1) force pg_resetxlog -l to pass timeline 1 > 2) create a WAL history file to match the old cluster's timeline (!= 1) > > I don't think we want to be doing #2, so I have developed the attached > patch for #1, which I should backpatch to 9.4. Do we store the timeline > in any user tables that might be transfered? Anywhere else? Patch applied and backpatched through 9.0. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +