[pgpool-general: 7361] Re: Executing pg_restore through pgpool2

[Tatsuo Ishii]

> Hi all,
> 
> I want to understand if there are any reason for that would not be advised
> to restore a database using pg_restore through pgpool2 and not directly
> connecting to the PostgreSQL primary node.

I assume you are using streaming replication mode of Pgpool-II.
In this mode pg_restore -C does not work because:

1) pgpool connects to all PostgreSQL nodes
2) pg_restore creates a database
3) pg_restore connects to pgpool
4) pgpool try to connect to the newly created database
5) standby PostgreSQL may not have the database yet because of replication delay
as a result you will have something like this:
pg_restore: error: could not reconnect to database: ERROR:  unable to read message kind
DETAIL:  kind does not match between main(53) slot[1] (45)

Besides this, for large database dump the overhead of pgpool will be
significantly slow down the pg_restore command.

For these reasons generally I do not recommend to execute pg_restore
through Pgpool-II.

However if you do not use -C option of pg_restore and the dump data is
relatively small, then running pg_restore via Pgpool-II is ok.

> I am asking this because trying that I am receiving the following error
> when psql tries to connect to the new database after creating it:
> 
> \connect pippo
> psql:psql_no_sleep.sql:39: \connect: ERROR:  unable to read message kind
> DETAIL:  kind does not match between main(45) slot[1] (53)
>
> The strange thing is that on the standby nodes I get a "database does not
> exist" error.
> 
> Are you aware that I'm doing something bad?
> 
> Thank you very much!

It is likely you have large replication delay or streaming replication
does not work. You can check it by using "show pool_status" command:

t-ishii$ psql -p 11000 -c "show pool_nodes" test
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.333333  | primary | 7          | false             | 0                 |                   |                        | 2020-12-17 09:33:21
 1       | /tmp     | 11003 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2020-12-17 09:33:21
 2       | /tmp     | 11004 | up     | 0.333333  | standby | 2          | true              | 0                 | streaming         | async                  | 2020-12-17 09:33:21
(3 rows)

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