[pgpool-hackers: 2441] Re: Amazon Aurora

[Tatsuo Ishii]

Ok, I have successfully accessed Amazon Aurora for PostgreSQL
Compatibility with the patch. The modified version of Pgpool-II is
running on an EC2 instance.

$ psql -h /tmp -p 9999 -U sraossbdg auroradb
Password for user sraossbdg: 
psql (9.2.18, server 9.6.2)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
Type "help" for help.

auroradb=> show pool_nodes;
-[ RECORD 1 ]-----+--------------------------------------------------------------------
node_id           | 0
hostname          | auroradb-1.cluster-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
port              | 5432
status            | up
lb_weight         | 0.500000
role              | primary
select_cnt        | 0
load_balance_node | true
replication_delay | 0
-[ RECORD 2 ]-----+--------------------------------------------------------------------
node_id           | 1
hostname          | auroradb-1.cluster-ro-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
port              | 5432
status            | up
lb_weight         | 0.500000
role              | standby
select_cnt        | 0
load_balance_node | false

I will push the patch along with documentation to master branch.

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

> Ok, here is the patch following the direction below.
> I will do a test using Aurora soon.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>>> Hi,
>>> 
>>> I had chance to evaluate "Amazon Aurora with PostgreSQL Compatibility"
>>> preview version. Basically the result was pretty positive: I found
>>> that Aurora is roughly 3 times faster than RDS for PostgreSQL using
>>> similar VM spec while running standard pgbench benchmarks (TCP-B like
>>> transactions).
>>> 
>>> So I start to think about how Pgpool-II deals with it.
>>> 
>>> I think most of functionaries in streaming replication mode can also
>>> work with Aurora.
>>> 
>>> The immediate problem is, there's no "primary" or "standby" servers in
>>> Aurora. Instead there is single "writer" and multiple "read
>>> replicas". The former acts like primary node in streaming
>>> replication. The latter acts like standby servers. We cannot use
>>> pg_is_in_recovery() function to find out the primary node. Writer and
>>> read replicas are assigned fixed host names. If fail over occurs, AWS
>>> magically swap the host name and IP address binding for the writer and
>>> read replicas.
>>> 
>>> My idea is, add a new flag to backend_flagN something like
>>> "AURORA_WRITER" and "AURORA_READ_REPLICA". If Pgpool-II finds the
>>> flag, it will not issue pg_is_in_recovery() in find_primary_node(). It
>>> just returns the node id which has AURORA_WRITER flags is on.
>> 
>> I think "ALWAYS_MASTER" is better than "AURORA_WRITER" because: there
>> might be other systems which handle writer (master) like Auroa. If so,
>> "ALWAYS_MASTER" would be a better name. Also I think we don't need
>> "AURORA_READ_REPLICA" flag. Instead, we can know that the node is a
>> read replica, if no "AURORA_WRITER" flags is assigned to any node in
>> the configuration file.
>> 
>>> Another problem is, we cannot call pg_current_xlog_location() or
>>> pg_last_xlog_replay_location() against Aurora. Thus, replication delay
>>> function will not work. There's no workaround for now. However,
>>> replication delay is pretty small in Aurora since the storage for DB
>>> (including for WAL) are on a same shared storage. I observed that
>>> replication delay using cloudWatch is less than 20 ms. So probably
>>> users don't care about replication delay anyway.
>>> 
>>> What do you think?
>>> 
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp
>>> _______________________________________________
>>> pgpool-hackers mailing list
>>> pgpool-hackers at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers