[pgpool-general: 7358] Re: Database does not exists after CREATE DATABASE on PgPool

[Tatsuo Ishii]

Sorry for delay.

> Hi all,
> 
> I have the following PostgreSQL cluster configuration:
> - 3 Postgres nodes with PostgreSQL 12.4-1 running on Debian Stable with
> Repmgr 5.1.0
> - 3 PgPool nodes with PgPool-II 4.1.1 on CentOS 8
> 
> PgPool is configured with master_slave_mode on, replication_mode off and
> load_balance_mode off.
> 
> This is the nodes status:
> ================================================
> bash-4.4$ psql -h 127.0.0.1 -p 5432 -U repmgr -c "show pool_nodes;"
>  node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_status_change
> ---------+-----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
>  0       | postgres1 | 5432 | up     | 0.333333  | standby | 0          |
> false             | 0                 |                   |
>        | 2020-12-07 14:16:34
>  1       | postgres2 | 5432 | up     | 0.333333  | primary | 299        |
> true              | 0                 |                   |
>        | 2020-12-07 14:16:34
>  2       | postgres3 | 5432 | up     | 0.333333  | standby | 0          |
> false             | 0                 |                   |
>        | 2020-12-07 14:16:34
> (3 rows)
> ================================================
> 
> When I create a new database I get the following errors if I try to connect
> it in the same script after the CREATE statement:
> ================================================
> # psql -f psql.sql -U testuser -h proxy -d postgres -a
> Password for user testuser:
> DROP DATABASE test;
> DROP DATABASE
> CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE
> = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
> CREATE DATABASE
> ALTER DATABASE test OWNER TO postgres;
> ALTER DATABASE
> \connect test
> psql:psql_no_sleep_without_sets.sql:4: \connect: ERROR:  unable to read
> message kind
> DETAIL:  kind does not match between master(45) slot[1] (53)
> ================================================
> 
> On the PostgreSQL slave nodes (not on the primary one!) I see this error:
> ================================================
> testuser at test FATAL:  database "test" does not exist
> ================================================
> 
> If I add a pg_sleep(0.05) after the ALTER DATABASE it works:
> ================================================
> # psql -f psql_with_sleep.sql -U testuser -h proxy -d postgres -a
> Password for user testuser:
> DROP DATABASE test;
> DROP DATABASE
> CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE
> = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
> CREATE DATABASE
> ALTER DATABASE test OWNER TO postgres;
> ALTER DATABASE
> select pg_sleep(0.05);
>  pg_sleep
> ----------
> 
> (1 row)
> 
> \connect test
> psql (11.7 (Debian 11.7-0+deb10u1), server 12.4 (Debian 12.4-1.pgdg100+1))
> WARNING: psql major version 11, server major version 12.
>          Some psql features might not work.
> You are now connected to database "test" as user "testuser".
> ================================================
> 
> The strange thing is that if I run the same script (without sleep) on the
> PosgtreSQL primary node (postgres2), bypassing PgPool, it works.

This is normal. Pgpool-II tries to connect to all PostgreSQL servers
with newly created database. Due to streaming replication delay some
of standby nodes may not have the new database created yet, and you
see the error. Workaround is inserting pg_sleep() as you already do,
or use synchronous replication mode with "remote_apply" enabled (this
will slow down write to primary, though).

> Another strange thing is that when I run the script on PgPool, the CREATE
> DATABASE statement takes 4-5 seconds, but if I run directly on PostgreSQL
> primary node it is run immediately.

Did not reproduce here. No idea.

> Could you help me to understand what's going on, please?
> Thank you very much!
> Bye

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