[pgpool-general: 4410] Re: pgpool only write on backend 0

[Tatsuo Ishii]

> On 05/02/2016 16:05, Tatsuo Ishii wrote:
>>> On 05/02/2016 15:54, Tatsuo Ishii wrote:
>>>>> postgres=# show pool_nodes;
>>>>>  node_id |       hostname       | port | status | lb_weight |  role  |
>>>>> select_cnt
>>>>> ---------+----------------------+------+--------+-----------+--------+------------
>>>>>  0       | serv1.lesafrance.com | 5432 | 3      | 0.500000  | slave  | 0
>>>>>  1       | serv2.lesafrance.com | 5432 | 2      | 0.500000  | master | 0
>>>>> (2 lignes)
>>>>>
>>>>> do you need pgpool.conf ?
>>>> pgpool-II recognizes that node 0 is down (status 3). That's the reason
>>>> why pgpool-II does not send "CRERATE DATABASE" to node 0.
>>>>
>>>> Best regards,
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese:http://www.sraoss.co.jp
>>> it is not down, I could connect it directly with this command.
>>>
>>> psql -hserv1.lesafrance.com -Upostgres
>> PostgreSQL on node 0 is down or not isn't a problem here.
>>
>>> Is there a step I miss ?
>> If you are sure that the database contents of node 0 and 1 are
>> identical, then you can use pcp_attach_node. Otherwise, you need to
>> use pcp_recovery_node. (or manually sync node 0 then executeok 
>> pcp_attach_node).
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
> I run pcp_recovery_node and that was not sucessfull.
> But pcp_attach_node was !!
> So now it seems to replicate again.
> Does all the database should be similar to able replication, or could I
> just replicate one database ?

All databases. Actually pcp_attach_node assumes that all databases are
in sync.

> does a pg_dump is considered as a good sync ?

In general, you need to execute pg_dumpall (or cold backup using tar
or rsync) to sync all databases.  These are simple and easy but
requires that postmaster on the target database node is not running
(and no write queries are sent to the master database node if you try
to use pg_dumpall). pcp_recovery_node uses PITR to avoid the
limitation.

However...

While one of the database nodes in state "3", what you did was just
CREATE DATABASE? Then you could issue the same query to the database
node whih was in "3" state to sync with master node.

> Anyhow many thanks for help ! I have lost all my hair today... ;)

You are welcome.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp