[pgpool-general: 2007] R: Re: R: Re: R: Re: pgpool-II online recovery via rsync: postgresql won't start after the recovery
armaeth at libero.it
armaeth at libero.it
Fri Aug 9 03:52:38 JST 2013
Dear all.
Tatsuo, http://www.pgpool.net provides the very same information for online
recovery via rsync:
>If you use pgpool_recovery, add the following lines into pgpool.conf.
>
>recovery_1st_stage_command = 'pgpool_recovery'
>recovery_2nd_stage_command = 'pgpool_recovery'
So, going back to my original question, assuming that I have autovacuum turned
off in PSQL 9.1.9 and that I really want to use the online recovery via rsync,
the provided script "pgpool_recovery" might require changes as when I attempt a
recovery with it, PSQL 9.1.9 does not start on the node being recovered:
> 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
We might think of my request as a sort of enhancement to the documentation and
to the pgpool_recovery script. I think that if the script is known to not work
with PSQL 9.1.x it might be adjusted for the benefit of those users in my own
circumstances. Alternatively I would think it to be wise to address this in the
official documentation saying that the rsync method using pgpool_recovery is
only meant for older versions of PSQL and known to not work with 9.1.x.
I hope this helps.
Thank you.
Kind Regards,
Luigi
>----Messaggio originale----
>Da: ishii at postgresql.org
>Data: 08/08/2013 0.48
>A: <armaeth at libero.it>
>Cc: <pgpool-general at pgpool.net>
>Ogg: Re: R: Re: R: Re: [pgpool-general: 1979] pgpool-II online recovery via
rsync: postgresql won't start after the recovery
>
>> 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'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'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
>>>>>
>>>
>>
>>
>
More information about the pgpool-general
mailing list