Re: Postgresql database encryption

[Tatsuo Ishii]

> Could someone throw light on the postgresql instance wide or database wide
> encryption please? Is this possible in postgresql and been in use in
> production?.

As far as I know there's no open source solution for instance or
database wide encryption.

If commercial solutions are ok for you, there are some. Please ask me
in a private email (I don't want to spam the list).

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

Re: Code of Conduct plan

[Tatsuo Ishii]

> Two years ago, there was considerable discussion about creating a
> Code of Conduct for the Postgres community, as a result of which
> the core team announced a plan to create an exploration committee
> to draft a CoC [1].  That process has taken far longer than expected,
> but the committee has not been idle.  They worked through many comments
> and many drafts to produce a version that seems acceptable in the view
> of the core team.  This final(?) draft can be found at
> 
> https://wiki.postgresql.org/wiki/Code_of_Conduct
> 
> We are now asking for a final round of community comments.
> Please send any public comments to the pgsql-general list (only).
> If you wish to make a private comment, you may send it to
> c...@postgresql.org.
> 
> The initial membership of the CoC committee will be announced separately,
> but shortly.
> 
> Unless there are substantial objections, or nontrivial changes as a result
> of this round of comments, we anticipate making the CoC official as of
> July 1 2018.
> 
>   regards, tom lane
> 
> [1] https://www.postgresql.org/message-id/56a8516b.8000...@agliodbs.com

Do we want official translations of this? We allow local communities
do their own manual translations. However CoC is so important, I feel
like we need more for Coc. Good thing with CoC is, it is expected that
it would be stable (at least I hope so) and translation works when
it's changed is expected to be minimal, unlike the manual translation
works.

One concern is, who checks for the correctness of the translations. I
think committers could do the job since there are good number of
non-English native speakers in the group.

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

Re: Code of Conduct plan

[Tatsuo Ishii]

>> Do we want official translations of this? We allow local communities
>> do their own manual translations. However CoC is so important, I feel
>> like we need more for Coc. Good thing with CoC is, it is expected that
>> it would be stable (at least I hope so) and translation works when
>> it's changed is expected to be minimal, unlike the manual translation
>> works.
> 
> Good idea, but let's wait till the text is official; I'm not sure if
> we'll change the draft again in response to the current discussions.

Of course. I will wait for the text to be settled down.

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

Re: Call for Papers - PGConf.ASIA 2018

[Tatsuo Ishii]

Hi PostgreSQL lovers,

The call for papers for PGConf.ASIA 2018 will be closed on 31st July,
2018 (Japan time).

I am looking forward to receiving your great proposals!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,
> 
> PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are
> now accepting proposals for talks.
> 
> Join us and other users and developers from around the world at the
> home of the oldest and largest user group; Japan!
> 
> (See http://www.pgconf.asia/EN/2017/program/ for last year's
> conference program).
> 
> - About the conference:
> Location: Akihabara convention hall (Tokyo) 
> http://www.akibahall.jp/data/outline_eng.html
> Targeted number of attendees: 700 in total
> http://www.pgconf.asia/EN/2018/
> 
> - About the call for papers:
> 
> - To submit a paper, please include the following details and send to:
> pgconf-asia-2018-submission(at)pgconf(dot)asia
> 
> Title
> Abstract
> Description
> Language spoken during the talk: Japanese or English
> Language of the talk material: Japanese and/or English
> 
> - Submission deadline is midnight, 31st July, 2018 (Japan time).
> 
> - Submissions should be sent in English, Japanese, or both.
> Japanese-only submissions will be translated into English for
> discussion within the program committee.
> 
> - Presentation materials will be released the day after the conference
> and will be made available to the public. The copyright of the
> material will be retained by the author.
> 
> We ask that you share your materials under a Creative Commons
> license: https://creativecommons.org/licenses/by-nc-nd/4.0/
> 
> If your presentation material cannot be shared, please let us know.
> 
> - Talks may be recorded or photographed. In both cases, the content
> may be made public under a Creative Commons license:
> https://creativecommons.org/licenses/by-nc-nd/4.0/. The copyright
> of the material is retained by the speaker.
> 
> - Speakers will be informed of the result of the selection by the end
> of August. Speakers will be requested to submit brief biographies and
> photos to be published in the conference program.
> 
> - The exact length of each session is not decided yet (Last year it
>   was 40 minutes and we expect no big change for this year).
> 
> Suggested topic areas include but not limited to:
> 
> - Large-scale PostgreSQL deployments.
> - Migrations from other databases to PostgreSQL
> - Operations and administration
> - Performance and feature implementation
> - Replication, clustering, HA, sharding.
> - Tools and utilities for PostgreSQL
> - Benchmarking and hardware, tuning.
> - PostgreSQL community and hacking.
> - Studies, surveys on PostgreSQL ecosystem
> - Asian PostgreSQL community & user groups
> - Data warehousing
> - Location-aware and mapping software with PostGIS
> - Research and teaching with PostgreSQL
> - Case studies, including but not limited to: IoT/Cloud, Healthcare,
>   Education and Academy
> 
> - If you have any questions regarding the event, feel free to contact
> the organization committee at 
> pgconf-asia-2018-submission(at)pgconf(dot)asia(dot)
> 
> See you in Tokyo :)
> 
> PGConf.ASIA 2018 Steering Committee
> http://www.pgconf.asia/EN/2018/
> [Please feel free to redistribute this CFP]
> 

Re: Call for Papers - PGConf.ASIA 2018

[Tatsuo Ishii]

Hi PostgreSQL lovers,

The call for papers for PGConf.ASIA 2018 will be closed on 31st July,
2018 (Japan time).

I am looking forward to receiving your great proposals!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,
> 
> PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are
> now accepting proposals for talks.
> 
> Join us and other users and developers from around the world at the
> home of the oldest and largest user group; Japan!
> 
> (See http://www.pgconf.asia/EN/2017/program/ for last year's
> conference program).
> 
> - About the conference:
> Location: Akihabara convention hall (Tokyo) 
> http://www.akibahall.jp/data/outline_eng.html
> Targeted number of attendees: 700 in total
> http://www.pgconf.asia/EN/2018/
> 
> - About the call for papers:
> 
> - To submit a paper, please include the following details and send to:
> pgconf-asia-2018-submission(at)pgconf(dot)asia
> 
> Title
> Abstract
> Description
> Language spoken during the talk: Japanese or English
> Language of the talk material: Japanese and/or English
> 
> - Submission deadline is midnight, 31st July, 2018 (Japan time).
> 
> - Submissions should be sent in English, Japanese, or both.
> Japanese-only submissions will be translated into English for
> discussion within the program committee.
> 
> - Presentation materials will be released the day after the conference
> and will be made available to the public. The copyright of the
> material will be retained by the author.
> 
> We ask that you share your materials under a Creative Commons
> license: https://creativecommons.org/licenses/by-nc-nd/4.0/
> 
> If your presentation material cannot be shared, please let us know.
> 
> - Talks may be recorded or photographed. In both cases, the content
> may be made public under a Creative Commons license:
> https://creativecommons.org/licenses/by-nc-nd/4.0/. The copyright
> of the material is retained by the speaker.
> 
> - Speakers will be informed of the result of the selection by the end
> of August. Speakers will be requested to submit brief biographies and
> photos to be published in the conference program.
> 
> - The exact length of each session is not decided yet (Last year it
>   was 40 minutes and we expect no big change for this year).
> 
> Suggested topic areas include but not limited to:
> 
> - Large-scale PostgreSQL deployments.
> - Migrations from other databases to PostgreSQL
> - Operations and administration
> - Performance and feature implementation
> - Replication, clustering, HA, sharding.
> - Tools and utilities for PostgreSQL
> - Benchmarking and hardware, tuning.
> - PostgreSQL community and hacking.
> - Studies, surveys on PostgreSQL ecosystem
> - Asian PostgreSQL community & user groups
> - Data warehousing
> - Location-aware and mapping software with PostGIS
> - Research and teaching with PostgreSQL
> - Case studies, including but not limited to: IoT/Cloud, Healthcare,
>   Education and Academy
> 
> - If you have any questions regarding the event, feel free to contact
> the organization committee at 
> pgconf-asia-2018-submission(at)pgconf(dot)asia(dot)
> 
> See you in Tokyo :)
> 
> PGConf.ASIA 2018 Steering Committee
> http://www.pgconf.asia/EN/2018/
> [Please feel free to redistribute this CFP]
> 


From: Tatsuo Ishii 
Subject: Call for Papers - PGConf.ASIA 2018
Date: Mon, 04 Jun 2018 13:29:20 +0900 (JST)
Message-ID: <20180604.132920.88922927875550163.t-is...@sraoss.co.jp>

> Hi,
> 
> PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are
> now accepting proposals for talks.
> 
> Join us and other users and developers from around the world at the
> home of the oldest and largest user group; Japan!
> 
> (See http://www.pgconf.asia/EN/2017/program/ for last year's
> conference program).
> 
> - About the conference:
> Location: Akihabara convention hall (Tokyo) 
> http://www.akibahall.jp/data/outline_eng.html
> Targeted number of attendees: 700 in total
> http://www.pgconf.asia/EN/2018/
> 
> - About the call for papers:
> 
> - To submit a paper, please include the following details and send to:
> pgconf-asia-2018-submission(at)pgconf(dot)asia
> 
> Title
> Abstract
> Description
> Language spoken during the talk: Japanese or English
> Language of the talk material: Japanese and/or English
> 
> - Submission deadline is midnight, 31st July, 2018 (Japan time).
> 
> - Submissions should be sent in English, Japanese, or both.
> Japanese-only submissions will be translated into English for
> discussion within the program committee.
> 
> - Presentation material

Re: Call for Papers - PGConf.ASIA 2018

[Tatsuo Ishii]

Hi PostgreSQL lovers,

The call for papers for PGConf.ASIA 2018 will be closed on 31st July,
2018 (Japan time), that is 15:00 31st July 2018 UTC.

I am looking forward to receiving your great proposals and seeing you
in Akihabara (yes, like last year, the conference venue is in the
"Electric City" Akihabara).
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
 
>> Hi,
>> 
>> PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are
>> now accepting proposals for talks.
>> 
>> Join us and other users and developers from around the world at the
>> home of the oldest and largest user group; Japan!
>> 
>> (See http://www.pgconf.asia/EN/2017/program/ for last year's
>> conference program).
>> 
>> - About the conference:
>> Location: Akihabara convention hall (Tokyo) 
>> http://www.akibahall.jp/data/outline_eng.html
>> Targeted number of attendees: 700 in total
>> http://www.pgconf.asia/EN/2018/
>> 
>> - About the call for papers:
>> 
>> - To submit a paper, please include the following details and send to:
>> pgconf-asia-2018-submission(at)pgconf(dot)asia
>> 
>> Title
>> Abstract
>> Description
>> Language spoken during the talk: Japanese or English
>> Language of the talk material: Japanese and/or English
>> 
>> - Submission deadline is midnight, 31st July, 2018 (Japan time).
>> 
>> - Submissions should be sent in English, Japanese, or both.
>> Japanese-only submissions will be translated into English for
>> discussion within the program committee.
>> 
>> - Presentation materials will be released the day after the conference
>> and will be made available to the public. The copyright of the
>> material will be retained by the author.
>> 
>> We ask that you share your materials under a Creative Commons
>> license: https://creativecommons.org/licenses/by-nc-nd/4.0/
>> 
>> If your presentation material cannot be shared, please let us know.
>> 
>> - Talks may be recorded or photographed. In both cases, the content
>> may be made public under a Creative Commons license:
>> https://creativecommons.org/licenses/by-nc-nd/4.0/. The copyright
>> of the material is retained by the speaker.
>> 
>> - Speakers will be informed of the result of the selection by the end
>> of August. Speakers will be requested to submit brief biographies and
>> photos to be published in the conference program.
>> 
>> - The exact length of each session is not decided yet (Last year it
>>   was 40 minutes and we expect no big change for this year).
>> 
>> Suggested topic areas include but not limited to:
>> 
>> - Large-scale PostgreSQL deployments.
>> - Migrations from other databases to PostgreSQL
>> - Operations and administration
>> - Performance and feature implementation
>> - Replication, clustering, HA, sharding.
>> - Tools and utilities for PostgreSQL
>> - Benchmarking and hardware, tuning.
>> - PostgreSQL community and hacking.
>> - Studies, surveys on PostgreSQL ecosystem
>> - Asian PostgreSQL community & user groups
>> - Data warehousing
>> - Location-aware and mapping software with PostGIS
>> - Research and teaching with PostgreSQL
>> - Case studies, including but not limited to: IoT/Cloud, Healthcare,
>>   Education and Academy
>> 
>> - If you have any questions regarding the event, feel free to contact
>> the organization committee at 
>> pgconf-asia-2018-submission(at)pgconf(dot)asia(dot)
>> 
>> See you in Tokyo :)
>> 
>> PGConf.ASIA 2018 Steering Committee
>> http://www.pgconf.asia/EN/2018/
>> [Please feel free to redistribute this CFP]
>> 
> 

Re: Code of Conduct plan

[Tatsuo Ishii]

>>> Do we want official translations of this? We allow local communities
>>> do their own manual translations. However CoC is so important, I feel
>>> like we need more for Coc. Good thing with CoC is, it is expected that
>>> it would be stable (at least I hope so) and translation works when
>>> it's changed is expected to be minimal, unlike the manual translation
>>> works.
>> 
>> Good idea, but let's wait till the text is official; I'm not sure if
>> we'll change the draft again in response to the current discussions.
> 
> Of course. I will wait for the text to be settled down.

Now that CoC is out,

https://www.postgresql.org/about/policies/coc/

I would like to start the translation work.  Can somebody suggest me
how I can proceed?

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

Re: Incremental aggregate/rollup strategy advice

[Tatsuo Ishii]

> * Full queries will take too long, and will scale poorly. So, MATERIALIZED
> VIEW is unappealing. So, rollup tables as it's possible to update them
> incrementally.

F.Y.I. There is a proposal to implemnt incremental updation against
MATERIALIZED VIEW. It is still in WIP patch but currently it supports
count and sum.

https://www.postgresql.org/message-id/20190628195620.c306e3003a83bb85a12f54c5%40sraoss.co.jp

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

Re: Incremental aggregate/rollup strategy advice

[Tatsuo Ishii]

> Thank you for your response, I have followed the discussion on Hackers with
> interest. I hope that your efforts are a great success! In my case, I need
> to find a solution available in shipping versions of Postgres. But, since
> you've joined in, I'm curious: What is the advantage of a materialized view
> over a real table? It seems like the update semantics and mechanics are
> more straightforward with a table.

In my understanding, views and materialized views provide users more
flexible and easy way to access base tables. In RDB, base tables are
usually heavily normalized and may not be easy for applications to
extract information. By defining views, apps would have convenient and
intuitive way to get information from base tables.

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

Re: pgpool, pgmaster and pgslave migration to ubuntu 18.04

[Tatsuo Ishii]

> Hi,
> 
> I'm exploring the options to migrate postgresql master, slave along with
> pgpool from ubuntu14.04 to 18.04. Please help me understand the following.
> 
> 1. What are the available options for migrating from one version of O/S to
> the other (Here ubuntu 14.04 to 18.04) ?
> 2. Document references.
> 3. Any best practices.
> 4. I'm assuming that migrating pgpool and pgmaster,slave can be done
> separately and there isn't any dependency between them. (I mean first I can
> separately migrate pgpool and then later migrate pgmaster/slave) is that
> correct?

As far as Pgpool-II concerns, that's generally true. I don't know what
version of Pgpool-II you are using, and trying to migrate to but if
you are using Pgpool-II dedicated extensions, be sure to upgrade them
as well. For more information regarding Pgpool-II version up, please
refer to the release notes sections.

https://pgpool.net/mediawiki/index.php/Documentation

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

RowDescription message

[Tatsuo Ishii]

According to the manualof RowDescription message
https://www.postgresql.org/docs/12/protocol-message-formats.html

  Specifies the number of fields in a row (can be zero).

Does 0 fields could actually happen?

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

Re: RowDescription message

[Tatsuo Ishii]

>> According to the manualof RowDescription message
>> https://www.postgresql.org/docs/12/protocol-message-formats.html
>> 
>>Specifies the number of fields in a row (can be zero).
>> 
>> Does 0 fields could actually happen?
> 
> Yes, e.g.:
> 
> SELECT;

Thanks. Is it a valid SQL statement according to the standard?

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

Re: pgpool-II 3.7.5 with ssl

[Tatsuo Ishii]

> Vikas Sharma  writes:
>> We use postgresql 9.6 and pgpool 3.7.5 and we are now asked to enable ssl
>> for 'in transit'. I have setup the ssl server side on the
>> postgresql dbs ( master and slave) and can see in pg_stat_ssl that the
>> master slave communication and connections from the application are showing
>> ssl = 't'
> 
>> I have set the parameters in pgpool.conf as well but not sure if the pgpool
>> is working with ssl enabled. because when I try to connect
>> with psql using pgpool I get below:
> 
>> [postgres@pgool-server ~]$ psql 'host=localhost port=5432 dbname=postgres
>> user=user1  sslmode=require'
>> psql: server does not support SSL, but SSL was required
> 
> Hm, is pgpool maybe using Unix-socket connections to the database?
> I'm not sure why pgpool would be trying to pass SSL-ness of the
> connection through to the server in that case, though.

Pgpool-II handles connection between client and Pgpool-II, and between
Pgpool-II and PostgreSQL separately. i.e. it is possible to establish
SSL connection between client and Pgpool-II while the connection
between Pgpool-II and PostgreSQL is established without SSL depending
the configuration of Pgpool-II and PostgreSQL (for example, if
Pgpool-II is configured to connect to PostgreSQL using Unix-socket,
SSL will be disabled between Pgpool-II and PostgreSQL as you said).

I think the error suggests that there's something wrong with Pgpool-II
SSL configuration. For example, if the pass to ssl key is wrong, you
see something like below in the pgpool log while pgpool is starting
up:

2019-12-11 08:53:23: pid 8506: WARNING:  could not access private key file 
"/usr/local/etc/server.keyk": No such file or directory

> Seems like
> something you should discuss with the pgpool people.

True. The issue is almost nothing to do with PostgreSQL. I recommend
to discuss in the pgpool mailing list:

https://www.pgpool.net/mailman/listinfo/pgpool-general

BTW, pgpool 3.7.5 is pretty old (released in 2018). The latest one in
3.7.x series is 3.7.12.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: pgpool High Availability Issue

[Tatsuo Ishii]

> I'm working on configuring high availability for pgpool using watchdog.
> Initially, I tried with two pgpool nodes (along with a pgmaster and
> pgslave). In this scenario, assuming pgpool node 1 was started first and
> became the leader. After sometime , the node got disconnected with  pgpool
> node 2 and pgpool node 2 as well declared itself as leader.
> 
> 
> To handle this kind of scenario, I tried provisioning an additional pgpool
> node and made a cluster with total 5 nodes (3 pgpool nodes, 1 pgmaster and
> 1 pgslave), assuming it will create a quorum to handle such situations.
> Unfortunately, the situation still remains the same. (In case of any
> disconnection between node that became leader and the first stand by node,
> both the nodes try to manage the pgmaster and slave simultaneously).
> 
> Please help me understand if this is expected behavior or some additional
> configurations are required to be made, so that two pgpool nodes don't
> become leader simultaneously.  If it's an expected behavior, how can we
> handle this ?

Definitely it's not an expected behavior unless there's something
wrong with Pgpool-II version or with Pgpool-II configuration.

To investigate the problem we need:

- exact Pgpool-II version
- pgpool.conf on all 3 pgpool nodes
- pgpool log when one of pgpool nodes went down

> (A point to note is that I'm not using elastic IP address here, instead I
> have created a network load balancer in AWS, created a target group with
> all the three pgpool nodes as targets).
> 
> Regards,
> Venkatesh.

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

Re: logical replication protocol

[Tatsuo Ishii]

> I haven't been able to find documentation on the actual messages used in the 
> logical replication protocol ('k' & 'w', lower case). I've figured things out 
> mostly by reading pg_recvlogical.c, but "Read The Fine Source" doesn't seem 
> in line with the way PG usually does it ;-)
> 
> Did I miss a doc somewhere in my searches???

The logical replication protocol builds on the primitives of the
physical streaming replication protocol as stated in the document. The
explanation of 'k' and 'w' messages can be found in the "Streaming
Replication Protocol" section.

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

Re: Pgpool is crashing when terminating user session

[Tatsuo Ishii]

> Hello all,
> 
> We are having master-slave setup with pgpool pointing, only to master
> server. Whenever i tried to terminate the long running session on db end
> using SELECT pg_terminate_backend(pid), pgpool is getting crashed.  Many
> blogs are saying this is the expected behaviour of pgpool, but my question
> is there anyway to terminate unwanted sessions on db without loosing other
> connections.
> 
> Because restarting entire system every time for a single trouble causing
> session is a big hectic for us.  Please share you ideas on this.

Those blogs are incorrect. From Pgpool-II 3.6 Pgpool-II supports
pg_terminate_backend(). Are you sure that you use "SELECT
pg_terminate_backend(pid)" from Pgpool-II session, not from a session
directly connecting to PostgreSQL?

Port 11000 is the port Pgpool-II is listening on.

[killing session]

$ psql -p 11000 test
psql (12.2)
Type "help" for help.


test=# select pg_terminate_backend(13877);
 pg_terminate_backend 
--
 t
(1 row)

[killed session]

$ psql -p 11000 test
psql (12.2)
Type "help" for help.

test=# select pg_sleep(600);
FATAL:  terminating connection due to administrator command
ERROR:  unable to forward message to frontend
DETAIL:  FATAL error occured on backend
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
test=# 


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

Re: 回复: May "PostgreSQL server side GB18030 character set support" reconsidered?

[Tatsuo Ishii]

>> 1. In this big data and mobile era, in the country with most population, 50% 
>> more disk energy consuming for Chinese characters (UTF-8 usually 3 bytes for 
>> a Chinese character, while GB180830 only 2 bytes) is indeed a harm to 
>> "Carbon Neutral",  along with Polar ice melting.
> 
> Really? I thought GB18030 uses up to 4 bytes.
> https://en.wikipedia.org/wiki/GB_18030#Encoding
> 
> --Parker:
> More preciously description should be GB18030 use 2 or 4 bytes for Chinese 
> characters.
> It's a bit complicated to explain with only words but easy with help of the 
> following graph.
> 
> Most frequently used 20902 Chinese characters  and 984 symbols in GBK is 
> encoded with 2 bytes, which is a subset of GB18030.

It does not sound fair argument unless you are going to implement only
GBK compatible part of GB18030.

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

Re: 回复: May "PostgreSQL server side GB18030 character set support" reconsidered?

[Tatsuo Ishii]

> TBH, even if you came up with a complete patch, we'd probably
> reject it as unmaintainable and a security hazard.  The problem
> is that code may scan a string looking for certain ASCII characters
> such as backslash (\), which up to now it's always been able to do
> byte-by-byte without fear that non-ASCII characters could confuse it.
> To support GB18030 (or other encodings with the same issue, such as
> SJIS), every such loop would have to be modified to advance character
> by character, thus roughly "p += pg_mblen(p)" instead of "p++".
> Anyplace that neglected to do that would have a bug --- one that
> could only be exposed by careful testing using GB18030 encoding.
> What's more, such bugs could easily be security problems.
> Mis-detecting a backslash, for example, could lead to wrong decisions
> about where string literals end, allowing SQL-injection exploits.

One of ideas to avoid the concern could be "shifting" GB18030 code
points into "ASCII safe" code range with some calculations so that
backend can handle them without worrying about the concern above. This
way, we could avoid a table lookup overhead which is necessary in
conversion between GB18030 and UTF8 and so on.

However I don't come up with such a mathematical conversion method for
now.

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

Re: 回复: May "PostgreSQL server side GB18030 character set support" reconsidered?

[Tatsuo Ishii]

> Hmm ... interesting idea, basically invent our own modified version
> of GB18030 (or SJIS?) for backend-internal storage.  But I'm not
> sure how to make it work without enlarging the string, which'd defeat
> the OP's argument.  It looks to me like the second-byte code space is
> already pretty full in both encodings.

But as he already admitted, actually GB18030 is 4 byte encoding, rather
than 2 bytes. So maybe we could find a way to map original GB18030 to
ASCII-safe GB18030 using 4 bytes.

As for SJIS, no big demand for the encoding in Japan these days. So I
think we can leave it as it is.

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

Re: 回复: May "PostgreSQL server side GB18030 character set support" reconsidered?

[Tatsuo Ishii]

> But as he already admitted, actually GB18030 is 4 byte encoding, rather
> than 2 bytes. So maybe we could find a way to map original GB18030 to
> ASCII-safe GB18030 using 4 bytes.

Here is an idea (in-byte represents GB18030, out-byte represents
internal server encoding):

if (in-byte1 is 0x00-80)/* ASCII */
   out-byte1 = in-byte1

else if (in-byte1 is 0x81-0xfe && in-byte2 is 0x40-0x7f)/* 2 bytes 
GB18030 */
   out-byte1 = in-byte1
   out-byte2 = 0x80
   out-byte3 = in-byte2 + 0x80 (should be 0xc0-0xc9)
   out-byte4 = 0x80

else if (in-byte1 is 0x81-0xfe && in-byte2 is 0x80-0xfe)/* 2 bytes 
GB18030 */
   out-byte1 = in-byte1
   out-byte2 = 0x80
   out-byte3 = 0x80
   out-byte4 = in-byte2 (should be 0x80-0xfe)

else if (in-byte1 is 0x81-0xfe && in-byte2 is 0x30-0x39)/* 4 bytes 
GB18030 */
   out-byte1 = in-byte1
   out-byte2 = in-byte2 + 0x80 (should be 0xb0-0xb9)
   out-byte3 = in-byte3
   out-byte4 = in-byte4 + 0x80 (should be 0xb0-0xb9)

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

Re: Setup Pgpool2 with Postgresql Streaming Replication

[Tatsuo Ishii]

Hi Alan,

This is not the best forum to discuss Pgpool-II related topics. I
advice you to go to the Pgpool-II dedicated forum:

https://www.pgpool.net/mailman/listinfo/pgpool-general

> Hi,
> 
> I'm following the steps from:
> 
> https://access.crunchydata.com/documentation/pgpool/4.0.0/example-cluster.html

This documentation looks pretty old. The original and the latest
documentation for Pgpool-II 4.0 is here (I assume you are using
Pgpool-II 4.0):

https://www.pgpool.net/docs/40/en/html/example-cluster.html

I strongly suggest to look into this.

> I'm at step 7.3.8.1 Set up PostgreSQL standby server:
> Ran this command on the primary server: pcp_recovery_node -h 192.168.80.90
> -p 9898 -U postgres -n 1
> And received this error: 
> ERROR:  recovery is checking if postmaster is started
> DETAIL:  postmaster on hostname:"ltpgsql12" database:"template1"
> user:"postgres" failed to start in 90 second
> 
> How can I get this command to run successfully?

Probably you have a problem with ssh settings. The newer and original
document describes far detailed steps to set up ssh settings. Please
take a look at "7.3.2. Requirements" section in the newer document.

In the mean time to confirm that the problem is related to ssh, we
need to look into the PostgreSQL log (not Pgpool-II log) on primary
PostgreSQL node. Please share it (again, you'd better to post messages
to the pgpool-general mailing list).

> Also, when I ran this command: psql -p 5433 -c "show pool_nodes"
> It shows the following pgpool2 node status, but when I did a listing of
> databases on the primary and standby servers, I don't see the databases on
> the primary replicated to the standby.  How can I setup the Postgresql
> Streaming Replication and check if it's working?

Standby status is down because you failed to execute online
recovery. You need to fix it.

> node_id | hostname  | port | status | lb_weight |  role   | select_cnt |
> load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_status_change
> -+---+--++---+-++---+---+---++-
>  0   | ltpgsql11 | 6432 | up | 0.50  | primary | 0  |
> true  | 0 |   |   
> 
> | 2020-10-21 11:56:48
>  1   | ltpgsql12 | 6432 | down   | 0.50  | standby | 0  |
> false | 0 |   |   
> 
> | 2020-10-21 11:56:48
> (2 rows)
> 
> 
> Thanks,
> Alan
> 
> 
> 
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 

Re: Need to place pgpool logs on separate directory

[Tatsuo Ishii]

> Hello all,
> 
> I want to place pgpool logs on a separate directory rather than in a syslog
> file. So I changed the below parameters, but still logging information are
> being written to the syslog file.  Any idea or suggestion why this
> behaviour?
> 
> 
> 
> *pgpool-II version 4.1.4 (karasukiboshi)*
> 
> Tried all the below combinations, none of them works
> 
> 1.
> log_destination = 'stderr'
> syslog_facility = 'LOCAL0'
> syslog_ident = 'pgpool'
> logdir = '/data/pgpool'
> 
> 2.
> log_destination = 'stderr'
> #syslog_facility = 'LOCAL0'
> #syslog_ident = 'pgpool'
> logdir = '/data/pgpool'
> 
> 3.
> log_destination = 'syslog'
> syslog_facility = 'LOCAL0'
> syslog_ident = 'pgpool'
> logdir = '/data/pgpool'

1 or 2 should work. You might want to check whether 'stderr' is
actually set to log_destination parameter by using psql:

pgpool show log_destination;

If it's set but still logs are sent to syslog, try reloading or
restarting pgpool.

By the way this is not the most appropriate forum to ask questions
regarding pgpool. Please use pgpool-general mailing list instead:

https://www.pgpool.net/mailman/listinfo/pgpool-general

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

Re: Need to place pgpool logs on separate directory

[Tatsuo Ishii]

> Thanks Tatsuo for the info. I will contact pgpool forum regarding this.
> 
> Also I tried restarting and reloading the pgpool service after each of the
> above parameter changes, still it's not working.
> 
> 
>  item |   value
>|
> description
> --++-
>  log_destination  | stderr
> | logging destination
>  logdir   | /data/pgpool
>  | PgPool status file logging directory
>  syslog_facility  | LOCAL0
> | syslog local faclity
>  syslog_ident | pgpool
> | syslog program ident string

When log_destination = stderr, you need to start pgpool with -n option
and redirect stderr to log file. Othewise log will not write to the
log file because without -n stderr is closed.

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

Does pg_ctl promote wait for pending WAL?

[Tatsuo Ishii]

Does anybody know whether a standby server waits for pending WAL
records/files while promotion is requested? I assume that no data
update is performed on the primary server while promotion.

I imagine that a standby server stops to replay WAL and promotes as
soon as SIGUSR1 signal is received.

The motivation of this question behind is I want to have a complete
copy of the primary server using promote command.

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

Re: Does pg_ctl promote wait for pending WAL?

[Tatsuo Ishii]

Hi Michael,

> To answer to your question based on the code, you can check for the
> code paths calling CheckForStandbyTrigger() in xlog.c when it comes to
> promotion detection in the WAL replay.  In short,
> WaitForWALToBecomeAvailable() tells that after the promotion is 
> detected in the startup process, then recovery would still try to
> replay as much WAL as possible from the archives or pg_wal before a
> failover.

Great. That should make my life a lot easier. I will look into the
code to confirm it.

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

Re: Regarding availability of 32bit client drivers for postgresql 13/14

[Tatsuo Ishii]

> An another question .. How does the enterprise customers using PostgreSQL can 
> subscribe to official support ?

Please take a look at this URL:
https://www.postgresql.org/support/

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: Intervals and ISO 8601 duration

[Tatsuo Ishii]

> Thanks Tom!  That helped me spell it out and understand it a little more
> clearly.  Both to understand the non-identicalness, and to see the
> specifics.  But yeah it would be nice if it was a little easier to extract!
> :)
> 
> WITH foo AS (
> WITH inters AS (
>  SELECT
>  '1 day 2 hours'::interval AS i1,
>  '26 hours'::interval AS i2
> )
> SELECT
>  *,
> EXTRACT(YEARS FROM i1)*12 + EXTRACT(MONTHS FROM i1) AS i1_months,
> EXTRACT(DAYS FROM i1) AS i1_days,
> EXTRACT(HOURS FROM i1) * 60 * 60 * 1000
> + EXTRACT(MINUTES FROM i1) * 60 * 1000
> + EXTRACT(SECONDS FROM i1) * 1000
> + EXTRACT(MICROSECONDS FROM i1)
> AS i1_msec,
> EXTRACT(YEARS FROM i2)*12 + EXTRACT(MONTHS FROM i2) AS i2_months,
> EXTRACT(DAYS FROM i2) AS i2_days,
> EXTRACT(HOURS FROM i2) * 60 * 60 * 1000
> + EXTRACT(MINUTES FROM i2) * 60 * 1000
> + EXTRACT(SECONDS FROM i2) * 1000
> + EXTRACT(MICROSECONDS FROM i2)
> AS i2_msec,
> i1=i2 AS equals
> FROM inters
> )
> SELECT
> *,
> (i1_months=i2_months AND i1_days=i2_days AND i1_msec=i2_msec) AS
> identical,
> i1_months * 30 * 24 * 60 * 60 * 1000
> + i1_days * 24 * 60 * 60 * 1000
> + i1_msec AS i1_msec_total,
> i2_months * 30 * 24 * 60 * 60 * 1000
> + i2_days * 24 * 60 * 60 * 1000
> + i2_msec AS i2_msec_total
> 
> FROM foo;
> 
> -[ RECORD 1 ]-+---
> i1| 1 day 02:00:00
> i2| 26:00:00
> i1_months | 0
> i1_days   | 1
> i1_msec   | 720
> i2_months | 0
> i2_days   | 0
> i2_msec   | 9360
> equals| t
> identical | f
> i1_msec_total | 9360
> i2_msec_total | 9360

I am not sure if I fully understand what you want to do here but I
guess you can extract "9360" part easier using "EPOCH" of EXTRACT
function.

SELECT EXTRACT(EPOCH FROM i1) AS epoch_i1, EXTRACT(EPOCH FROM i2) AS epoch_i2
FROM ( SELECT '1 day 2 hours'::interval AS i1, '26 hours'::interval AS i2) AS s;

   epoch_i1   |   epoch_i2   
--+--
 93600.00 | 93600.00
(1 row)

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Does Postgres 14 have a query cache?

[Tatsuo Ishii]

> On Sat, Feb 18, 2023 at 7:47 AM Siddharth Jain  wrote:
> 
>> I think the answer is no but wanted to confirm here. this is what my best
>> friend told me.
>>
>> [image: image.png]
>>
> 
> I find the last paragraph suspect.  The rest is basically correct.

Yeah. Pgpool-II has query cache but PgBouncer does not.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

Hi,

> Hi Guys,
> 
> Hope you are doing well.
> 
> Can someone please suggest what is one (Patroni vs PGPool II) is best for 
> achieving HA/Auto failover, Load balancing for DB servers.

I am not sure if Patroni provides load balancing feature.

> Along with this, can you please share the company/client names using these 
> tools for large PG databases?

I can't give you names but we (SRA OSS) have many customers using
PostgreSQL and some of them are using Pgpool-II.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> BUT, even if there is a solution that parses queries to make a decision it
> I would not recommend anyone to use it unless all consequences are
> understood.
> Specifically, not every read-only query could be salefy sent to a replica,
> because they could be lagging behind the primary.
> Only application (developers) could decide whether for a specific query
> they could afford slightly outdated results. Most of the popular
> application frameworks support configuring two connection strings for this
> purpose.

I think Pgpool-II users well understand the effect of replication
lagging because I've never heard complains like "hey, why my query
result is sometimes outdated?"

Moreover Pgpool-II provides many load balancing features depending on
user's needs. For example users can:

- just turn off load balancing
- turn off load balancing only for specific application name
- turn off load balancing only for specific database
- turn off load balancing if current transaction includes write query

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> But, I heard PgPool is still affected by Split brain syndrome.

Can you elaborate more? If more than 3 pgpool watchdog nodes (the
number of nodes must be odd) are configured, a split brain can be
avoided.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> Regards,
> 
> Inzamam Shafiq
> Sr. DBA
> ________
> From: Tatsuo Ishii 
> Sent: Wednesday, April 5, 2023 12:38 PM
> To: cyberd...@gmail.com 
> Cc: inzamam.sha...@hotmail.com ; 
> pgsql-general@lists.postgresql.org 
> Subject: Re: Patroni vs pgpool II
> 
>> BUT, even if there is a solution that parses queries to make a decision it
>> I would not recommend anyone to use it unless all consequences are
>> understood.
>> Specifically, not every read-only query could be salefy sent to a replica,
>> because they could be lagging behind the primary.
>> Only application (developers) could decide whether for a specific query
>> they could afford slightly outdated results. Most of the popular
>> application frameworks support configuring two connection strings for this
>> purpose.
> 
> I think Pgpool-II users well understand the effect of replication
> lagging because I've never heard complains like "hey, why my query
> result is sometimes outdated?"
> 
> Moreover Pgpool-II provides many load balancing features depending on
> user's needs. For example users can:
> 
> - just turn off load balancing
> - turn off load balancing only for specific application name
> - turn off load balancing only for specific database
> - turn off load balancing if current transaction includes write query
> 
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

>> > But, I heard PgPool is still affected by Split brain syndrome.  
>> 
>> Can you elaborate more? If more than 3 pgpool watchdog nodes (the
>> number of nodes must be odd) are configured, a split brain can be
>> avoided.
> 
> Split brain is a hard situation to avoid. I suppose OP is talking about
> PostgreSQL split brain situation. I'm not sure how PgPool's watchdog would
> avoid that.

Ok, "split brain" means here that there are two or more PostgreSQL
primary serves exist.

Pgpool-II's watchdog has a feature called "quorum failover" to avoid
the situation. To make this work, you need to configure 3 or more
Pgpool-II nodes. Suppose they are w0, w1 and w2. Also suppose there
are two PostgreSQL servers pg0 (primary) and pg1 (standby). The goal
is to avoid that both pg0 and pg1 become primary servers.

Pgpool-II periodically monitors PostgreSQL healthiness by checking
whether it can reach to the PostgreSQL servers. Suppose w0 and w1
detect that pg0 is healthy but pg1 is not, while w2 thinks oppositely,
i.e. pg0 is unhealthy but pg1 is healthy (this could happen if w0, w1,
pg0 are in a network A, but w2 and pg1 in different network B. A and B
cannot reach each other).

In this situation if w2 promotes pg1 because w0 seems to be down, then
the system ends up with two primary servers: split brain.

With quorum failover is enabled, w0, w1, and w2 communicate each other
to vote who is correct (if it cannot communicate, it regards other
watchdog is down). In the case above w0 and w1 are majority and will
win. Thus w0 and w1 just detach pg1 and keep on using pg0 as the
primary. On the other hand, since wg2 looses, and it gives up
promoting pg1, thus the split brain is avoided.

Note that in the configuration above, clients access the cluster via
VIP. VIP is always controlled by majority watchdog, clients will not
access pg1 because it is set to down status by w0 and w1.

> To avoid split brain, you need to implement a combinaison of quorum and
> (self-)fencing.
> 
> Patroni quorum is in the DCS's hands. Patroni's self-fencing can be achieved
> with the (hardware) watchdog. You can also implement node fencing through the
> "pre_promote" script to fence the old primary node before promoting the new 
> one.
> 
> If you need HA with a high level of anti-split-brain security, you'll not be
> able to avoid some sort of fencing, no matter what.
> 
> Good luck.

Well, if you define fencing as STONITH (Shoot The Other Node in the
Head), Pgpool-II does not have the feature. However I am not sure
STONITH is always mandatory. I think that depends what you want to
avoid using fencing. If the purpose is to avoid having two primary
servers at the same time, Pgpool-II achieve that as described above.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> Communication takes time – network latencies. What if during this
> communication, the situation becomes different?

We have to accept it (and do the best to mitigate any consequence of
the problem). I think there's no such a system which presuppose 0
communication latency.

> What if some of them cannot communicate with each other due to network issues?

Can you elaborate more? There are many scenarios for communication
break down. I hesitate to discuss all of them on this forum since this
is for discussions on PostgreSQL, not Pgpool-II. I am welcome you to
join and continue the discussion on pgpool mailing list.

> What if pg1 is currently primary, pg0 is standby, both are healthy, but
> due not network issues, both pg1 and w2 are not reachable to other
> nodes? Will pg1 remain primary, and w0 and w1 decide to promote pg0?

pg1 will remain primary but it is set to "quarantine" state from
pgpool's point of view, which means clients cannot access pg1 via
pgpool.

w0 and w1 will decide to promote pg0.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> I truly believe that this problem – HA – is PostgreSQL's, not 3rd
> party's. And it's a shame that Postgres itself doesn't solve this. So
> we're discussing it here.

Let's see what other subscribers on this forum say.

>> > What if pg1 is currently primary, pg0 is standby, both are healthy, but
>> > due not network issues, both pg1 and w2 are not reachable to other
>> > nodes? Will pg1 remain primary, and w0 and w1 decide to promote pg0?
>>
>> pg1 will remain primary but it is set to "quarantine" state from
>> pgpool's point of view, which means clients cannot access pg1 via
>> pgpool.
> 
> So we have a split brain here – two primaries. Especially if some
> clients communicate with PG directly. 

Clients are not allowed to communicate with PostgreSQL
directory. That's the prerequisite of using Pgpool-II.

> And even if there are no such
> clients, archive_command is going to
> work on both nodes,

What's the problem with this? Moreover you can write a logic to
disable this in the failover command.

> monitoring will show two primaries confusing
> humans (e.g, SREs) and various systems,

That's why pgpool provides its own monitoring tools. Clustering system
is different from standalone PostgreSQL. Existing PostgreSQL tools
usually only take account of stand alone PostgreSQL. Users have to
realize the difference.

> if we have many standby nodes,
> some of them might continue replicating from the old primary if they
> happen to be in the same network partition, and so on.

As of pg0 and existing standby in the same network as pg0, you can
either manually or automatically make them to follow pg0.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> And I believe that's part of what Cen was complaining about:
> 
> «
>   It is basically a daemon glued together with scripts for which you are 
>   entirely responsible for. Any small mistake in failover scripts and 
>   cluster enters  a broken state.
> »
> 
> If you want to build something clean, including fencing, you'll have to
> handle/dev it by yourself in scripts

That's a design decision. This gives maximum flexibility to users.

Please note that we provide step-by-step installation/configuration
documents which has been used by production systems.

https://www.pgpool.net/docs/44/en/html/example-cluster.html

>> However I am not sure STONITH is always mandatory.
> 
> Sure, it really depend on how much risky you can go and how much complexity 
> you
> can afford. Some cluster can leave with a 10 minute split brain where some 
> other
> can not survive a 5s split brain.
> 
>> I think that depends what you want to avoid using fencing. If the purpose is
>> to avoid having two primary servers at the same time, Pgpool-II achieve that
>> as described above.
> 
> How could you be so sure?
> 
> See https://www.alteeve.com/w/The_2-Node_Myth
> 
> «
>   * Quorum is a tool for when things are working predictably
>   * Fencing is a tool for when things go wrong

I think the article does not apply to Pgpool-II.

---
 3-Node

When node 1 stops responding, node 2 declares it lost, reforms a
cluster with the quorum node, node 3, and is quorate. It begins
recovery by mounting the filesystem under NFS, which replays journals
and cleans up, then starts NFS and takes the virtual IP address.

Later, node 1 recovers from its hang. At the moment of recovery, it
has no concept that time has passed and so has no reason to check to
see if it is still quorate or whether its locks are still valid. It
just finished doing whatever it was doing at the moment it hung.

In the best case scenario, you now have two machines claiming the same
IP address. At worse, you have uncoordinated writes to storage and you
corrupt your data.
---

> Later, node 1 recovers from its hang.

Pgpool-II does not allow an automatic recover. If node 1 hangs and
once it is recognized as "down" by other nodes, it will not be used
without manual intervention. Thus the disaster described above will
not happen in pgpool.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

> Scenario:
> S0 - Running Postgresql as primary, and also PgPool.
> S1 - Running Postgresql as secondary, and also PgPool.
> S2 - Running only PgPool.  Has the VIP.
> 
> There's no /need/ for Postgresql or PgPool on server 0 to shut down if
> it loses contact with S1 and S2, since they'll also notice that that
> S1 has disappeared.  In that case, they'll vote S1 into degraded
> state, and promote S1 to be the Postgresql primary.
> 
> A good question is what happens when S0 and S1 lose connection to S2
> (meaning that S2 loses connection to them, too).  S0 and S1 then
> "should" vote that S0 take over the VIP.  But, if S2 is still up and
> can connect to "the world", does it voluntarily decide to give up the
> VIP since it's all alone?

Yes, because S2 pgpool is not the leader anymore. In this case S2
voluntarily gives up VIP.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Patroni vs pgpool II

[Tatsuo Ishii]

>> If node 1 hangs and once it is recognized as "down" by other nodes, it will
>> not be used without manual intervention. Thus the disaster described above
>> will not happen in pgpool.
> 
> Ok, so I suppose **all** connections, scripts, softwares, backups, 
> maintenances
> and admins must go through Pgpool to be sure to hit the correct primary.
> 
> This might be acceptable in some situation, but I wouldn't call that an
> anti-split-brain solution. It's some kind of «software hiding the rogue node
> behind a curtain and pretend it doesn't exist anymore»

You can call Pgpool-II whatever you like. Important thing for me (and
probably for users) is, if it can solve user's problem or not.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: psql output in Japanese Code Page

[Tatsuo Ishii]

> Hi,
> 
> While under Code Page 932 (=Japanese) the old PSQL 9.5.5 - used to talk plain 
> English, like in \d 
> 
> This behavior has changed in PSQL 10. We are now forced to run "chcp 1252" 
> before any PSQL activity.
> 
> Does anyone know about it? Any suggestions?
> 
> I tried \encoding WIN1252. Did not work.

What's your database encoding? (\l command on psql prompt will show it).
Also what's the result of chcp (with no argument).

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

TPC-DS queries

[Tatsuo Ishii]

I played with TPC-DS and found some of them can't be executed because
of SQL errors and I am not sure why.

For example with query 36:

select  
sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
   ,i_category
   ,i_class
   ,grouping(i_category)+grouping(i_class) as lochierarchy
   ,rank() over (
partition by grouping(i_category)+grouping(i_class),
case when grouping(i_class) = 0 then i_category end 
order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as 
rank_within_parent
 from
store_sales
   ,date_dim   d1
   ,item
   ,store
 where
d1.d_year = 2000 
 and d1.d_date_sk = ss_sold_date_sk
 and i_item_sk  = ss_item_sk 
 and s_store_sk  = ss_store_sk
 and s_state in ('TN','TN','TN','TN',
 'TN','TN','TN','TN')
 group by rollup(i_category,i_class)
 order by
   lochierarchy desc
  ,case when lochierarchy = 0 then i_category end -- line 25 is here.
  ,rank_within_parent
  limit 100;
psql:query_0.sql:1935: ERROR:  column "lochierarchy" does not exist
LINE 25:   ,case when lochierarchy = 0 then i_category end

I have follwed the instruction here.
https://ankane.org/tpc-ds

PostgreSQL is master branch HEAD. For me, the SQL above looks to be
valid.

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

Re: TPC-DS queries

[Tatsuo Ishii]

> Hi,
> 
> I think that the sql is not valid. Based on the order by
> documentation, a column label cannot be used in an expression.
> 
> from    https://www.postgresql.org/docs/11/queries-order.html
>  > Note that an output column name has to stand alone, that is, it
> cannot be used in an expression.

Thanks. Yes, you are correct. The line should be something like:

   ,case when grouping(i_category)+grouping(i_class) = 0 then i_category end

> Regards
> s.
> 
> On 11.03.2019 06:30, Tatsuo Ishii wrote:
>> I played with TPC-DS and found some of them can't be executed because
>> of SQL errors and I am not sure why.
>>
>> For example with query 36:
>>
>> select
>>  sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
>> ,i_category
>> ,i_class
>> ,grouping(i_category)+grouping(i_class) as lochierarchy
>> ,rank() over (
>>  partition by grouping(i_category)+grouping(i_class),
>>  case when grouping(i_class) = 0 then i_category end
>>  order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as
>>  rank_within_parent
>>   from
>>  store_sales
>> ,date_dim   d1
>> ,item
>> ,store
>>   where
>>  d1.d_year = 2000
>>   and d1.d_date_sk = ss_sold_date_sk
>>   and i_item_sk  = ss_item_sk
>>   and s_store_sk  = ss_store_sk
>>   and s_state in ('TN','TN','TN','TN',
>>   'TN','TN','TN','TN')
>>   group by rollup(i_category,i_class)
>>   order by
>> lochierarchy desc
>>,case when lochierarchy = 0 then i_category end -- line 25 is here.
>>,rank_within_parent
>>limit 100;
>> psql:query_0.sql:1935: ERROR:  column "lochierarchy" does not exist
>> LINE 25:   ,case when lochierarchy = 0 then i_category end
>>
>> I have follwed the instruction here.
>> https://ankane.org/tpc-ds
>>
>> PostgreSQL is master branch HEAD. For me, the SQL above looks to be
>> valid.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
> 

Re: PGCon remote attendance

[Tatsuo Ishii]

> Hi pgsql-general,
> 
> Will there be any option to attend the upcoming PG conference remotely?
> 
> Will the talks and papers be posted online following the conference, if not?

You can contact the conference organizer via email.

https://www.pgcon.org/2023/contact.php

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: whether I can add a new encoding?

[Tatsuo Ishii]

> The pg_wchar.h file comments "We must avoid renumbering any backend encoding 
> until libpq's major version number is increased beyond 5", Can I add a new 
> server-side encoding, GBK18030, and place it after PG_KOI8U in PG10.6? 

What's GBK18030? Can you please give a pointer to a specification of
GBK18030?

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

[Tatsuo Ishii]

> Hello PostgreSQL Community,
> 
> I am writing to propose an alternative compilation of PostgreSQL that
> allows for a 256-byte identifier length limit, alongside the existing
> 64-byte version.
> 
> *Problem:*
> The current limit of 63 bytes can be quite restrictive, especially for
> databases that use multi-byte character sets like UTF-8. In such cases, a
> Chinese character takes up 3 bytes, limiting the name to just 21 characters.
> 
> *Use-Case:*
> In specific use-cases, table names like
> "能源消耗统计_全球主要国家石油与天然气使用量_年度碳排放与可再生能源比例表" and
> "气候变化研究_全球主要地区极端天气事件频率_年度灾害损失与应对措施表" can far exceed the current limit.
> 
> *Proposed Solution:*
> I propose that we offer an alternative compilation of PostgreSQL that
> increases the NAMEDATALEN constant to allow for 256-byte identifiers. This
> would be particularly useful for databases that make extensive use of
> multi-byte character sets like UTF-8.

Another solution would be, letting the meaning of NAMEDATALEN to be
number of *characters*, not the number of bytes. This way, you can use
up to 64 UTF-8 characters. In my understanding MySQL already does this
way. I know this requires non trivial code modifications to PostgreSQL
but would be better than to make binaries with random NAMEDATALEN
values.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Issue with pgstattuple on Sequences in PostgreSQL

[Tatsuo Ishii]

> On Monday, June 24, 2024, Ayush Vatsa  wrote:
>>
>> I was recently exploring the pgstattuple code directory and found this
>> piece of code: https://github.com/postgres/postgres/blob/master/contrib/
>> pgstattuple/pgstattuple.c#L255-L259.
>>
>> It indicates that pgstattuple supports relations, toast tables,
>> materialized views, and sequences.
>> However, when I executed a query with a sequence, I encountered the
>> following error:
>>
>> postgres=> CREATE SEQUENCE serial START 101;
>> CREATE SEQUENCE
>> postgres=> SELECT * FROM pgstattuple('serial');
>> ERROR:  only heap AM is supported
>> postgres=>
>>
>> It got stuck in this if condition - https://github.com/postgres/
>> postgres/blob/master/contrib/pgstattuple/pgstattuple.c#L326-L329
>>
>>
>> How can one use pgstattuple on sequences?
>>
> 
> As-is?  Doesn’t look like you can.

It used to work until v11.

test=# SELECT * FROM pgstattuple('serial');
-[ RECORD 1 ]--+--
table_len  | 8192
tuple_count| 1
tuple_len  | 41
tuple_percent  | 0.5
dead_tuple_count   | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 8104
free_percent   | 98.93

It stopped working by this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4b82664156c230b59607704506f5b0a32ef490a2

because pgstat_heap() rejects other than heap AM.  I think the v12
release note should have explicitly mentioned that sequences are not
supported by pgstattuple any more.

> I agree it’s a documentation bug that
> this is the case with a brief explanation of why - sequences do not produce
> dead tuples and do not behave like real tables aside from being able to be
> selected from (i.e., no SQL update/delete command).
> 
> The code should produce an explicit error for that relkind as well.

If so, then the regression test should be fixed as well. Currently
there's no test case for sequences.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: What does tcop stand for?

[Tatsuo Ishii]

> Hi, 
> 
> currently browsing the source code in src/include/tcop. What does tcop stand 
> for? Can not find any hints in the files. 

Traffic cop.

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

Re: What does tcop stand for?

[Tatsuo Ishii]

>>> currently browsing the source code in src/include/tcop. What does tcop 
>>> stand for? Can not find any hints in the files. 
> 
>>Traffic cop. 
> 
> Thanks 
> cop mean? 

A cop means a policeman. Probably the name came from that it is
responsible for the traffic control between frontend and backend.

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

Re: PGPool encrypted connections from Application

[Tatsuo Ishii]

> Does PGPool allow encrypted connections from Application? i.e. ssl
> encrypted? My company wants to encrypt all traffic in the environment
> and so enabled https connections between applications/load balance. I
> am not sure about the PGPool. If application sends encrypted
> connection to PGPool will it accept?

Yes, you can use encrypted connections with Pgpool-II. See FAQ:
https://pgpool.net/mediawiki/index.php/FAQ#How_can_I_set_up_SSL_for_pgpool-II.3F

and the manual:
http://www.pgpool.net/docs/latest/en/html/runtime-ssl.html

for more details.

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

Re: Postgres HA

[Tatsuo Ishii]

Hi,

Yes, definitely I am hanging out here.

If you have more specific questions to Pgpool-II, you are encouraged
to be subscribed to the Pgpool-II mailing list.
https://www.pgpool.net/mailman/listinfo/pgpool-general

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

> What he said, and you also may want to look at pgpool-II. I’ve had fairly 
> good luck with that and Tatsuo (the author) hangs out here occasionally too.
> ―
> Jay
> 
> Sent from my iPad
> 
>> On Jan 5, 2018, at 4:00 PM, Jehan-Guillaume (ioguix) de Rorthais 
>>  wrote:
>> 
>> On Fri, 5 Jan 2018 13:07:10 -0600
>> Azimuddin Mohammed  wrote:
>> 
>>> Hello,
>>> I am little confused with how HA works in postgres. Reading the article
>>> which state as below "*If the primary server fails and the standby server
>>> becomes the new primary, and then the old primary restarts, you must have a
>>> mechanism for informing the old primary that it is no longer the primary.
>>> This is sometimes known as STONITH (Shoot The Other Node In The Head),
>>> which is necessary to avoid situations where both systems think they are
>>> the primary, which will lead to confusion and ultimately data loss.*
>>> 
>>> *Many failover systems use just two systems, the primary and the standby,
>>> connected by some kind of heartbeat mechanism to continually verify the
>>> connectivity between the two and the viability of the primary. It is also
>>> possible to use a third system (called a witness server) to prevent some
>>> cases of inappropriate failover, but the additional complexity might not be
>>> worthwhile unless it is set up with sufficient care and rigorous testing.*
>>> *PostgreSQL does not provide the system software required to identify a
>>> failure on the primary and notify the standby database server. Many such
>>> tools exist and are well integrated with the operating system facilities
>>> required for successful failover, such as IP address migration."*
>>> 
>>> Can someone explain how the HA failback will take place
>> 
>> The failback need either to rebuild the old master as a standby (rsync,
>> pg_basebackup, restore PITR, ...) or to use pg_rewind to rewind the old 
>> master
>> to a point where it can catch up with the new master.
>> 
>> Some tools tries to automate failback using pg_rewind (patroni, repmgr), but 
>> I
>> have no experience with them.
>> 
>>> and what open source tools we can use to make sure once the primary server
>>> which failed over to slave will mark itself as slave.
>> 
>> There's a lot of open source tools to build some HA around PgSQL: Repmgr,
>> Patroni (based on etcd or zookeeper), PAF (based on Pacemaker), etc. You will
>> have to spend a lot of time to make extensive tests, understand them, pick 
>> one
>> and document your cluster.
>> 
>> Regards,
>> 
> 

Re: pgpool Connections Distributions Among Nodes

[Tatsuo Ishii]

> We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool as 
> load balancer. We have an observation where if application requests for 3 
> connections, pgpool connects to all 4 servers and I see 3 connections on each 
> of them. I was expecting it have a total of 3 connections from either of 4 
> servers but I can easily see 12 connections in all.

Yes, that's an expected behavior.

Why?

When client A connects to pgpool, it connects to all PostgreSQL (a, b,
c, d). But actually pgpool sends query to one of them (in case load
balance node is primary) or two of them (in case load balance node is
not primary). Suppose it sends to a and b. Client A logs off, but
connections to a and b remain because of connection pooling. Client B
comes in. B uses c and d. The connections to c and d remain.

So eventually pgpool needs to connect to all backend anyway.

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

Re: [pgpool-general: 9291] pgpool Connection issue: ERROR: unable to read message kind

[Tatsuo Ishii]

> Hello Everyone,
> 
> We have a Postgres setup in Azure (IAAS) with 2 DB nodes in the cluster and
> pgpool is running on each DB node. We have enabled the watchdog.
> 
> What we are doing: We are testing the connectivity to DB cluster via
> HammerDB to apply the load.
> HammerDB is connecting to Delegate IP via  port.
> 
> Issue: when we initiate the connection it gives error, but when we
> re-initiate the connection again then it is successful. What could be the
> issue
> 
> Error message we are seeing when we initiate the connection for the first
> time.
> 
> Error in Virtual User 1: Error: Connection to database failed
> connection to server at "10.35.8.4", port  failed: ERROR:  unable to
> read message kind
> DETAIL:  kind does not match between main(53) slot[1] (45)
> 
> If you want to see any configuration settings that i made, i can share.
> 
> Please help me in this regard.

The error message can be interpreted that primary PostgreSQL sent 0x53
= 'S'(parameter status message) while standby sent 0x45 = 'E' (error
response). Perhaps the parameter status cannot be processed on standby
for some reasons. In order to study the cause of error, I need a
pgpool log with debug level 1 (log_min_messages = debug1). Can you
provide it?

BTW, cross posting pgpool-general and pgsql-general is not
recommended. Please post to pgpool-general only next time.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Issue with Password Authentication for Pgpool

[Tatsuo Ishii]

> If we plan to use pool_hba.conf, then we must use the pool_passwd file to
> maintain passwords for all database users. This approach requires that
> every time a new user is created, their password is added to the pool_passwd
> file.

Not really. You can use pool_hba.conf with the auth method to
'password'. With this setting, if a passowrd is not found in
pool_passwd, then pgpool asks the password to PostgreSQL.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Issue with Password Authentication for Pgpool

[Tatsuo Ishii]

> Thanks David,
> 
> I tested the configuration by setting allow_clear_text_frontend_auth = on
> and disabling the pool_hba. I made the corresponding entry in the
> pg_hba.conf file. However, while connecting through the database port
> (5432), it prompts for the password, but when connecting through Pgpool
> (port ), it does not ask for a password.
> 
> Here is the content of pg_hba.conf:
> 
> bash
> Copy code
> # TYPE  DATABASEUSERADDRESS
> METHOD# "local" is for Unix domain socket connections onlylocal   all
>all trust# IPv4 local
> connections:
> hostall all 127.0.0.1/32trust#
> IPv6 local connections:
> hostall all ::1/128 trust#
> Allow replication connections from localhost, by a user with the#
> replication privilege.local   replication all
>trust
> hostreplication all 127.0.0.1/32trust
> hostreplication all ::1/128 trust
> hostrepmgr  repmgr  127.0.0.1/32trust
> hostrepmgr  repmgr  10.125.0.90/32 trust
> # Primary
> hostreplication repmgr  10.125.0.90/32 trust
> hostrepmgr  repmgr  10.125.0.91/32 trust
> # Standby
> hostreplication repmgr  10.125.0.91/32 trust
> hostall all 10.125.0.90/32   trust# Node 1
> hostall all 10.125.0.91/32   trust
> # Node 2#hostall all 0.0.0.0/26  trust
> hostall all 10.125.0.79/32   scram-sha-256
> hostall all 0.0.0.0/0   scram-sha-256
> 
> When I connect via the database port (5432), it prompts for the password as
> expected:
> 
> [postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 5432
> Password for user vkp:
> psql (15.3)
> Type "help" for help.
> 
> postgres=>
> postgres=>
> postgres=> exit
> 
> However, when connecting through Pgpool (port ), it does not prompt for
> the password:
> 
> [postgres@scrbtrheldbaas002 ~]$ psql -h 10.125.0.79 -U vkp -d postgres -p 
> psql (15.3)
> Type "help" for help.
> 
> postgres=>
> 
> This behavior might be related to how Pgpool handles authentication. Let me
> know if you need further investigation or configuration changes!

I guess pgpool is running on IP which is accepted by PostgreSQL using
trust auth method, which does not ask a password. Probably you set
backend_hostname to 'localhost'?  Then it matches with the line in
pg_hba.conf:

hostall all 127.0.0.1/32trust
or
hostall all ::1/128 trust

in which a password is never asked (or maybe other entries whose auth
method is trust).

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Re: Proposed Japanese Translation of Code of Conduct Policy

[Tatsuo Ishii]

> The PostgreSQL Community Code of Conduct Committee has received a draft of
> the Japanese translation of the Code of Conduct Policy for review.
> The enclosed draft contains the "Licence" section that was added into the
> CoC in the Japanese version and other fixes to align with the English
> version.
> 
> The English version of the Policy is at:
> https://www.postgresql.org/about/policies/coc/
> 
> The translation was contributed by Yugo Nagata.
> 
> The translation was reviewed by:
> 
>- Kurosawa Akira
>- Ryohei Takahashi
>- Tetsuo Sakata
> 
> The proposed Japanese translation patch and a PDF file are attached to this
> message.
> 
> If you have any comments or suggestions for the proposed translation,
> please bring them to our attention no later than 5:00 PM UTC on Monday,
> April 19, 2025.
> What time is that in my time zone?
> https://www.timeanddate.com/worldclock/converter.html?iso=20250519T17&p1=1440
> 
> Thank you!
> 
> Michael Goldberg
> PostgreSQL Community Code of Conduct Committee

Assuming the English version of CoC diff is here,
https://git.postgresql.org/gitweb/?p=pgweb.git;a=commitdiff;h=45af503ca8163620bd7eb35282e053ad030c7b47;hp=f679398efad7438da71c7f72ca5ee315296271c2
the Japanese translation diff looks good to me.
(I have not re-checked the entire translation.)

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