psql \du no more showing "member of" column

[Luca Ferrari]

Hi all,
in version 16 psql does not show anymore the "member of" group
information when showing users with \du.

The query (still working fine) in previous versions was:

SELECT r.rolname, r.rolsuper, r.rolinherit,
 r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
 r.rolconnlimit, r.rolvaliduntil,
 ARRAY(SELECT b.rolname
   FROM pg_catalog.pg_auth_members m
   JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
   WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;


while now it is:

SELECT r.rolname, r.rolsuper, r.rolinherit,
 r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
 r.rolconnlimit, r.rolvaliduntil
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;


at least, as it is shown by `psql -E`.
I wonder why this information has been removed, I'm not able to find
this in the documentation.

Thanks,
Luca

RE: Postgresql HA cluster

[Jason Grammenos]

Thank you for the feedback,

I have used pacemaker for other purposes previously so am a little familiar 
with it. It appears that in this scenario pacemaker is being used to manage a 
floating ip as well as deal with split brain scenarios. What isn’t clear is how 
effective master-> master replication is being accomplished. Postgresql 
streaming replication to the best of my limited knowledge only replicates in 
one direction, from the active to the standby servers. The issue this presents 
to me is that once you failover from the active to the standby (or one of the 
standby’s depending on how many you have) none of the data written on the 
standby is replicated back to the formerly active server.

Let us say that I have only 2 postgresql servers (absolute minimum number) and 
I want to patch server A. Ideally, I would use a load balancer (or other 
failover mechanism like pacemaker) and repoint the floating ip to server B. Now 
traffic would “drain” down off server A, and slowly (or rapidly) move to B. 
During the move some clients would still be writing to A and some clients would 
be writing to B. Once they have all moved to B, server A would then be patched. 
Then the load balancer would be used to repoint the floating ip again back to 
A, and the process would repeat, with traffic moving back to A. Just like in 
the first half of the failover some traffic would exist on both hosts as the 
failover progresses. Once completed all the traffic would be back on A.

In the above scenario, I do not understand how streaming replication would 
handle the part of the scenario when there are clients writing to A and B. It 
would seem that something like `pgpool-ii` or `pgEdge` would be required, but 
with my limited knowledge it is unclear if or which would be appropriate.

Regards
Jason


[cid:image001.png@01D9FDA9.6A6562F0]
Jason Grammenos | Operations & Infrastructure Analyst
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.gramme...@agilitypr.com
agilitypr.com
Learn new PR tips from our free resources.

From: Олег Самойлов 
Sent: Thursday, October 12, 2023 11:08 AM
To: Jason Grammenos ; 
pgsql-general@lists.postgresql.org
Subject: Re: Postgresql HA cluster

For Postgresql HA cluster the most popular solution is streaming replication. 
There is an option how implement this. Web programmer approach is using 
haproxy+consul+patrony. The "old schoool" is using Pacemaker, all in one bottle 
and well tested. If you interesting how implement in Pacemaker, you may look at 
my project of testbed that continuously test different HA clusters by random 
failures. I don't see such for  haproxy+consul+patrony.
https://github.com/domclick/tuchanka


11.10.2023, 21:02, "Jason Grammenos" 
mailto:jason.gramme...@agilitypr.com>>:

Hello,



I am new to PostgreSQL and having to migrate a Cassandra cluster to PostgreSQL.

Have a reasonable amount of experience with MySQL and use Master -> Master 
MySQL replication quite successfully.



I am trying to understand what configuration/option to pick for PostgreSQL that 
will provide the same or similar level of operational easy as the Cassandra 
cluster. What I mean by that is : Backups, Patching (rebooting nodes to patch)

With Cassandra any single node can be patched and rebooting without incurring 
downtime. This is also true with MySQL master->master replication, which we 
have fronted by a HAproxy reverse proxy (we can fail over between the two MySQL 
node, draining the connections as we do so and then patching and reboot).



I have found the following documentation:

https://www.postgresql.org/docs/current/different-replication-solutions.html



and have heard of “Traktor”, “pgEdge” and some other third-party tools.

My difficulty is that with very little knowledge of PostgreSQL I am having a 
hard time sorting through the various options and determining which are 
potential candidates for the kind of setup I am looking for (something 
approximating MySQL master->master replication).



Any advice or resources would be much appreciated.

Regards

Jason Grammenos



[cid:image001.png@01D9FDA9.6A6562F0]

Jason Grammenos | Operations & Infrastructure Analyst
Pronouns: he/him
P: 613.232.7797 x1131
Toll-free: 866.545.3745 x1131
jason.gramme...@agilitypr.com
agilitypr.com

Learn new PR tips from our free resources.

Re: psql \du no more showing "member of" column

[Ian Lawrence Barwick]

2023年10月13日(金) 20:47 Luca Ferrari :
>
> Hi all,
> in version 16 psql does not show anymore the "member of" group
> information when showing users with \du.
>
> The query (still working fine) in previous versions was:
>
> SELECT r.rolname, r.rolsuper, r.rolinherit,
>  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
>  r.rolconnlimit, r.rolvaliduntil,
>  ARRAY(SELECT b.rolname
>FROM pg_catalog.pg_auth_members m
>JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
>WHERE m.member = r.oid) as memberof
> , r.rolreplication
> , r.rolbypassrls
> FROM pg_catalog.pg_roles r
> WHERE r.rolname !~ '^pg_'
> ORDER BY 1;
>
>
> while now it is:
>
> SELECT r.rolname, r.rolsuper, r.rolinherit,
>  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
>  r.rolconnlimit, r.rolvaliduntil
> , r.rolreplication
> , r.rolbypassrls
> FROM pg_catalog.pg_roles r
> WHERE r.rolname !~ '^pg_'
> ORDER BY 1;
>
>
> at least, as it is shown by `psql -E`.
> I wonder why this information has been removed, I'm not able to find
> this in the documentation.

FWIW the explanation is in commit 0a1d2a7df8:

  
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a1d2a7df852f16c452eef8a83003943125162c7

and also noted in the release notes:

  https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL

Regards

Ian Barwick

Re: Postgresql HA cluster

[Jehan-Guillaume de Rorthais]

On Fri, 13 Oct 2023 12:02:53 +
Jason Grammenos  wrote:

> Thank you for the feedback,
> 
> I have used pacemaker for other purposes previously so am a little familiar
> with it. 

So you might be familiar with shared-storage cluster, that are the simpler one
you could deploy (baring you have a reliable HA storage available). But it's
not a multi-primary cluster.

> It appears that in this scenario pacemaker is being used to manage a
> floating ip as well as deal with split brain scenarios.

There's also two different resource agents dealing with PostgreSQL itself:
pgsql and PAF. Both handle multi-status differently from the administration
point of view.

> What isn’t clear is how effective master-> master replication is being 
> accomplished.

There's no master-master in PostgreSQL core. There's few external solutions out
there though, but double check you real needs, the real primary-standby
capacity to answer you needs, and the various constraints M-M imply before
rushing there.

> Postgresql streaming replication to the best of my limited knowledge only
> replicates in one direction, from the active to the standby servers. The
> issue this presents to me is that once you failover from the active to the
> standby (or one of the standby’s depending on how many you have) none of the
> data written on the standby is replicated back to the formerly active server.

It depend if this is a "controlled failover" (aka. "switchover") or a real
failover triggered by some failure. If this is a controlled failover, you can
hook back your old primary as a standby with no trouble. PAF even handle this
for you.

Moreover, even with a failure scenario, there's some solutions around to quickly
fix your old primary data and get it back in production quickly as a standby
(pg_rewind, PITR/pgbackrest, etc).

You just have to plan for failure and write you procedures accordingly to get
the cluster back on feet quickly after a failover.

> Let us say that I have only 2 postgresql servers (absolute minimum number)
> and I want to patch server A. Ideally, I would use a load balancer (or other
> failover mechanism like pacemaker) and repoint the floating ip to server B.
> Now traffic would “drain” down off server A, and slowly (or rapidly) move to
> B. During the move some clients would still be writing to A and some clients
> would be writing to B.

This doesn't exist as PostgreSQL has no multi-primary solution in core. You can
do rolling upgrade, but you'll have to pause the production during the
switchover between the primary and the standby.

> In the above scenario, I do not understand how streaming replication would
> handle the part of the scenario when there are clients writing to A and B.

It will not.

> It would seem that something like `pgpool-ii` or `pgEdge` would be required, 
> but
> with my limited knowledge it is unclear if or which would be appropriate.

External multi-primary solution exists, pgpool-II, Bucardo, BDR, etc. But
you'll have to ask and evaluate these thrid party solutions yourself. 

But really, double check first why a simple primary-standby architecture doesn't
meet your needs. The simpler the architecture is, the better. Even from the
application point of view.

Regards,

Re: psql \du no more showing "member of" column

[Luca Ferrari]

On Fri, Oct 13, 2023 at 3:04 PM hubert depesz lubaczewski
 wrote:
>
> On Fri, Oct 13, 2023 at 01:39:17PM +0200, Luca Ferrari wrote:
> > at least, as it is shown by `psql -E`.
> > I wonder why this information has been removed, I'm not able to find
> > this in the documentation.
>
> Release notes show:
>
> https://why-upgrade.depesz.com/show?from=15.4&to=16&keywords=%5Cdu

Thanks, I totally missed.
Luca

Re: Question About PostgreSQL Extensibility

[felix . quintgz]

For the same reason that you can use python or perl in postgresql. It's just 
another language.
I have .net code running on several sql servers and to change the database to 
postgresql I have to reprogram them.

 On Thursday, October 12, 2023 at 09:44:08 AM GMT-4, Laurenz Albe 
 wrote:

 On Thu, 2023-10-12 at 06:09 +, Sepideh Eidi wrote:
> We have some .net assemblies and in your documents, I didn’t find any support 
> for this
> type of files that is executable in DB or not. Would you please tell me that 
> .net
> assemblies files are executable in PostgreSQL or not? Or is there any way for 
> calling
> assembly methods inside DB?

There is no support for that.  I guess somebody could write an extension PL/net,
and it looks like somebody already has: https://github.com/witblitz/pldotnet.
That code ptobably won't work with recent PostgreSQL releases, but it could be
a basis for you to work upon.

My big question for me is: Why on earth would you want to run .NET code inside a
database?  Why not run it on the client?

Yours,
Laurenz Albe

Re: psql \du no more showing "member of" column

[Lele Gaifax]

Ian Lawrence Barwick  writes:

> and also noted in the release notes:
>
>   https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL

FWIW, I noticed a small typo in the relevant entry: s/informaion/information/

ciao, lele.
-- 
nickname: Lele Gaifax | Dire che Emacs è "conveniente" è come
real: Emanuele Gaifas | etichettare l'ossigeno come "utile"
l...@etour.tn.it  |   -- Rens Troost

Re: Postgresql HA cluster

[Laura Smith]

--- Original Message ---
On Friday, October 13th, 2023 at 14:10, Jehan-Guillaume de Rorthais 
 wrote:

> But really, double check first why a simple primary-standby architecture 
> doesn't
> meet your needs. The simpler the architecture is, the better. Even from the
> application point of view.
> 


>From my perspective I do wonder why Postgres doesn't have an equivalent to 
>MySQL Group Replication.

Although you can run MySQL GR as multi-primary, most people run it as 
primary-standby.

However the difference with Postgres is that MySQL Group does leader election. 
Whilst Postgres failover/failback is a highly manual affair.

Re: psql \du no more showing "member of" column

[Pavel Luzanov]

Hi,

On 13.10.2023 16:06, Ian Lawrence Barwick wrote:

2023年10月13日(金) 20:47 Luca Ferrari :

Hi all,
in version 16 psql does not show anymore the "member of" group
information when showing users with \du.



FWIW the explanation is in commit 0a1d2a7df8:

   
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a1d2a7df852f16c452eef8a83003943125162c7

and also noted in the release notes:

   https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL


Some explanation can be found at the beginning of this article:

    https://postgrespro.com/blog/pgsql/5970285

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

JSON fields with backslashes

[Greig Wise]

Hello.  I have run into an issue when using the copy command on tables with 
json columns where Postgres seems to improperly escape backslashes under 
certain conditions thus creating invalid JSON.  Here is an example I have of 
the behavior:

create table test_json(json_data json);
insert into test_json values ('{"test1": "2011-01-01", 
"description":"test\ntest2\ntest3 test''s\n \"Quoted Phrase\" test\ntest."}’);
copy test_json to '/var/tmp/t.json’;

cat /var/tmp/t.json
{"test1": "2011-01-01", "description":"test\\ntest2\\ntest3 test's\\n \\"Quoted 
Phrase\\" test\\ntest."}

Note that the quotes within the json field have \\ in front, thus negating the 
escape of the quotes around “Quoted Phrase”.  Which then renders the whole 
thing invalid JSON.  Is this a bug?

I am using PostgreSQL V12.16.

Thanks,
Greig Wise

Re: JSON fields with backslashes

[David G. Johnston]

On Fri, Oct 13, 2023 at 2:53 PM Greig Wise  wrote:

> Hello.  I have run into an issue when using the copy command on tables
> with json columns where Postgres seems to improperly escape backslashes
> under certain conditions thus creating invalid JSON.  Here is an example I
> have of the behavior:
>
> create table test_json(json_data json);
> insert into test_json values ('{"test1": "2011-01-01",
> "description":"test\ntest2\ntest3 test''s\n \"Quoted Phrase\"
> test\ntest."}’);
> copy test_json to '/var/tmp/t.json’;
>
> cat /var/tmp/t.json
> {"test1": "2011-01-01", "description":"test\\ntest2\\ntest3 test's\\n
> \\"Quoted Phrase\\" test\\ntest."}
>
> Note that the quotes within the json field have \\ in front, thus negating
> the escape of the quotes around “Quoted Phrase”.  Which then renders the
> whole thing invalid JSON.  Is this a bug?
>

COPY doesn't output JSON, it outputs csv/tsv structured text.  In that
format the described output is correct.  If you need a different output
format you need to use a different tool.  Ideally you can just get the JSON
into whatever client software you are writing with and export it from
there.  Doing it in psql is possible but a bit tricky.  Doing it within the
server usually isn't worth the hassle.

David J.

Re: JSON fields with backslashes

[Tom Lane]

Greig Wise  writes:
> Hello.  I have run into an issue when using the copy command on tables
> with json columns where Postgres seems to improperly escape backslashes
> under certain conditions thus creating invalid JSON.

AFAICS this is COPY's normal behavior: it doubles backslashes in
the default format.  You could use CSV format, perhaps, but that
has its own quoting rules that also mean that what comes out is
not going to be json-and-nothing-but.  See "File Formats" here:

https://www.postgresql.org/docs/current/sql-copy.html

regards, tom lane

Re: psql \du no more showing "member of" column

[hubert depesz lubaczewski]

On Fri, Oct 13, 2023 at 01:39:17PM +0200, Luca Ferrari wrote:
> at least, as it is shown by `psql -E`.
> I wonder why this information has been removed, I'm not able to find
> this in the documentation.

Release notes show:

https://why-upgrade.depesz.com/show?from=15.4&to=16&keywords=%5Cdu

> Add psql command \drg to show role membership details (Pavel Luzanov)
> The Member of output column has been removed from \du and \dg because this 
> new command displays this informaion in more detail.

Best regards,

depesz

Re: JSON fields with backslashes

[Erik Wienhold]

On 2023-10-14 00:01 +0200, David G. Johnston write:
> On Fri, Oct 13, 2023 at 2:53 PM Greig Wise  wrote:
> 
> > Hello.  I have run into an issue when using the copy command on tables
> > with json columns where Postgres seems to improperly escape backslashes
> > under certain conditions thus creating invalid JSON.  Here is an example I
> > have of the behavior:
> >
> > create table test_json(json_data json);
> > insert into test_json values ('{"test1": "2011-01-01",
> > "description":"test\ntest2\ntest3 test''s\n \"Quoted Phrase\"
> > test\ntest."}’);
> > copy test_json to '/var/tmp/t.json’;
> >
> > cat /var/tmp/t.json
> > {"test1": "2011-01-01", "description":"test\\ntest2\\ntest3 test's\\n
> > \\"Quoted Phrase\\" test\\ntest."}
> >
> > Note that the quotes within the json field have \\ in front, thus negating
> > the escape of the quotes around “Quoted Phrase”.  Which then renders the
> > whole thing invalid JSON.  Is this a bug?
> >
> 
> COPY doesn't output JSON, it outputs csv/tsv structured text.  In that
> format the described output is correct.  If you need a different output
> format you need to use a different tool.  Ideally you can just get the JSON
> into whatever client software you are writing with and export it from
> there.  Doing it in psql is possible but a bit tricky.  Doing it within the
> server usually isn't worth the hassle.

in psql:

\pset format unaligned
\pset tuples_only
\o /var/tmp/t.json
select json_data from test_json limit 1;

-- 
Erik