[pgpool-general: 2006] Re: R: Re: R: Re: pgpool-II online recovery via rsync: postgresql won't start after the recovery

[Tatsuo Ishii]

> Dear all,
> 
> Tatsuo, I configured the 1st and 2nd stage commands as per pgpool-II manual 
> http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html, section 
> "Online recovery with rsync.". Quoting from there
> 
>>If you use pgpool_recovery, add the following lines into pgpool.conf.
>>
>>recovery_1st_stage_command = 'pgpool_recovery'
>>recovery_2nd_stage_command = 'pgpool_recovery'

http://pgpool.projects.pgfoundry.org/pgpool-II is obsoleted (I asked
the site manager to delete them but it seems that does not happen...).
Please refer to http://www.pgpool.net.

Anyway above description only applies to the older version of
PostgreSQL that does not have PITR. Are you sure that you do not want
to use PITR? Without PITR, online recovery has sevior restriction: you
need to completely stop autovacuum while doing online recovery.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Could you please recommend what the second stage in this case should achieve? 
> Also, do you think the manual at the above link needs update?
> 
> Again, the error I get when following the manual is essentially PSQL 
> complaining as per below:
> 
> DETAIL: WAL file database system identifier is 5894553353380019103, pg_control 
> database system identifier is 5894200307068334067. 
> ...
> LOG: startup process (PID 1549) was terminated by signal 6: Aborted
> 
> Should rsync extend to pg_control too in order to avoid the inconsistency 
> reported by PSQL, please?
> 
> As per testing, I tried to extend the rsync command to the whole PG_DATA 
> folder and the recovery was successful: PSQL started on the recovered node 
> without issues, pcp reports both nodes in the pool and DB contents appear the 
> same on both nodes. Would it be advisable to rsync the whole PG_DATA folder 
> during a recovery? If so, should the 2nd stage command still be different from 
> the 1st stage one?
> 
> Thank you.
> 
> Kind Regards,
> 
> Luigi
> 
>>----Messaggio originale----
>>Da: ishii at postgresql.org
>>Data: 07/08/2013 0.39
>>A: <armaeth at libero.it>
>>Cc: <pgpool-general at pgpool.net>
>>Ogg: Re: R: Re: [pgpool-general: 1979] pgpool-II online recovery via rsync: 
> postgresql won&#39;t start after the recovery
>>
>>It seems your recovery_2nd_stage_command is same as
>>recovery_1st_stage_command. They should be different at least.
>>
>>recovery_1st_stage_command = 'pgpool_recovery'
>>recovery_2nd_stage_command = 'pgpool_recovery'
>>--
>>Tatsuo Ishii
>>SRA OSS, Inc. Japan
>>English: http://www.sraoss.co.jp/index_en.php
>>Japanese: http://www.sraoss.co.jp
>>
>>> Dear all.
>>> 
>>> Thank you Tatsuo for your reply.
>>> 
>>> Please find attached pgpool.conf and the pgpool_recovery script as per 
> your 
>>> request. I also attached the pgpool_remote_start script which is 
> essentially as 
>>> the one in the sample folder of pgpool-II with some additional logging to 
>>> central log storage.
>>> 
>>> Thank you.
>>> 
>>> Kind Regards,
>>> 
>>> Luigi
>>> 
>>>>----Messaggio originale----
>>>>Da: ishii at postgresql.org
>>>>Data: 06/08/2013 2.36
>>>>A: <armaeth at libero.it>
>>>>Cc: <pgpool-general at pgpool.net>
>>>>Ogg: Re: [pgpool-general: 1979] pgpool-II online recovery via rsync: 
>>> postgresql won&#39;t start after the recovery
>>>>
>>>>What did you exactly setup pgpool-II? Can you please show me
>>>>pgpool.conf and the first stage script and the second stage script? I
>>>>assume you are trying to setup native replication mode (not straming
>>>>replication) because the script you are showing is for native
>>>>replication mode.
>>>>--
>>>>Tatsuo Ishii
>>>>SRA OSS, Inc. Japan
>>>>English: http://www.sraoss.co.jp/index_en.php
>>>>Japanese: http://www.sraoss.co.jp
>>>>
>>>>> Hi all.
>>>>> 
>>>>>  
>>>>> 
>>>>> I am running postgresql 9.1.9 and pgpool 3.1.6 under CentOS 6.4.
>>>>> 
>>>>>  
>>>>> 
>>>>> I tried to set up online recovery as per pgpool-II user manual, section 
>>> "Online recovery with rsync".
>>>>> 
>>>>>  
>>>>> 
>>>>> For this purpose I am using the pgpool_recovery script that's under the 
>>> sample folder and available on github @:
>>>>> 
>>>>> https://github.com/iakio/pgpool-II/blob/master/sample/pgpool_recovery
>>>>> 
>>>>>  
>>>>> 
>>>>> The problem I have is that on the server being recovered postgresql 
> won't 
>>> start and log the following under pg_log:
>>>>> 
>>>>>  
>>>>> 
>>>>> LOG: database system was shut down at 2013-07-31 18:02:12 BST
>>>>> LOG: WAL file is from different database system
>>>>> DETAIL: WAL file database system identifier is 5894553353380019103, 
>>> pg_control database system identifier is 5894200307068334067.
>>>>> LOG: invalid primary checkpoint record
>>>>> LOG: invalid secondary checkpoint record
>>>>> PANIC: could not locate a valid checkpoint record
>>>>> LOG: startup process (PID 1549) was terminated by signal 6: Aborted
>>>>> 
>>>>>  
>>>>> 
>>>>> I guess that the folders being rsync'ed by the pgpool_recovery script 
> are 
>>> not comprehensive?
>>>>> 
>>>>> 
>>>>> I wa tempted to rsync the whole PG_DATA folder, but then I thought that 
>>> there are a number of things that I don't want to overwrite, such as the 
> logs 
>>> under pg_log on the failed node.
>>>>> 
>>>>>  
>>>>> 
>>>>> Would anybody be able to make any recommendation on this and make the 
>>> necessary changes to the official pgpool_recovery file in the code 
> repository, 
>>> please?
>>>>> 
>>>>>  
>>>>> 
>>>>> Thank you.
>>>>> 
>>>>>  
>>>>> 
>>>>> Kind Regards,
>>>>> 
>>>>>  
>>>>> 
>>>>> Luigi
>>>>
>>
> 
>