[pgpool-hackers: 2441] Re: Amazon Aurora
Tatsuo Ishii
ishii at sraoss.co.jp
Wed Jul 12 14:17:35 JST 2017
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
More information about the pgpool-hackers
mailing list