Re: Efficiently advancing a sequence without risking it going backwards.

[Paul McGarry]

On Fri, 10 Jul 2020 at 10:27, Jeremy Schneider 
wrote:

>
> OP asked for a way to call setval() with a guarantee the sequence will
> never go backwards IIUC. His code can check that the new value he wants to
> set is higher than the current value, but there’s a race condition where a
> second connection could quickly advance the sequence between the check and
> the setval() call and then cause duplicates from the next call which is bad.
>
> The ideal solution is a setval_forward_only() or setval_no_duplicates()
> function that does it atomically or something. If it were possible to
> “lock” the entire sequence to prevent any other sessions from using it at
> all, that would work too. Not locking a value, locking the whole thing.
> Very bad hack solution is renaming the sequence then renaming it back as a
> blunt form of locking... and to be clear I don’t think is a good idea I
> just was saying that technically it might work.  :)
>
> -Jeremy
>

Yes, that first paragraph is a good summary. A "setval_forward_only()" is
the sort of thing I was after.

Maybe something analogous to:
UPDATE the_seq SET last_value = number WHERE last_value < number;
with some sort of global (but short) lock as required.

Relating to some of the other replies there isn't a "requirement" (from an
application perspective) that the sequences always generate ids in
ascending order or that they don't skip numbers etc. To the application
they are just ids, as long as they are unique that is enough. However it is
an application that is used by people, so there is some external value in
having the ids going up in a way that roughly correlates to time as people
tend to expect numbers to do that sort of thing.

For a bit more background, we have our own application and homegrown
loosely coupled multi-primary DB cluster and replication system.
Each backend DB in the cluster has its own node id (0-9) and when our app
asks for a sequence value it calls a custom function which gets a normal
sequence value suffixed with the DB node ID.

So if there were two backend dbs (1 and 2) and both backend dbs had a
sequence with last_value of 1234 then our application would get a
"sequence" value of 12351 or 12352 depending on which db backend served the
request.
The resulting ids are unique across our cluster, but certainly not gapless
nor issued in strict ascending order which is fine from an application
perspective.

But as mentioned, from a human perspective there is some value in keeping
the ids issued by the cluster roughly in time order, so we have a secondary
process which liaises with all the backend nodes and pulls forwards any
sequences that fall behind other nodes. So if DB 1 happened to serve 1000
requests using the sequence while DB2 served none, the process pulls the
sequence in DB2 forward until it catches up, currently by calling nextval
in a loop.

Which all works fine. However sometimes (eg taking a node offline for
maintenance or upgrade) a sequence might get quite a long way out, and
calling nextval() 100k times seems a rather inefficient way to catch up
(but it is better to be inefficient than risk going backwards and causing a
duplicate id).

We have been using essentially this system for our cluster since Postgres 7
days, periodically we have touched base with Postgres replication
advancements (which have come a long way) but haven't yet found a
compelling reason to switch from what is working.

Paul

Re: Clustering solution ?

[Jehan-Guillaume de Rorthais]

On Wed, 15 Jul 2020 15:54:03 + (UTC)
Laurent FAILLIE  wrote:

> Hello all,
> thanks all for your replies : I started to learn Patroni.Anyway, any
> alternative with my customer want's also LoadBalancing ? Thanks
> Laurent

Same comment than my previous one here. Climb one step at a time.

First, create relevant benchmarks as close as possible from the target
application. Optimize your queries, setup, and lastly your hardware. If your
architecture does not keep up with the load, then you can consider load
balancing. And really, load balancing is hard to achieve for SGBD. It often
requires some changes on application side...or even dealing with it on
application side all together.

I've seen too many complex architectures, with too many layers, hard to
administer...for nothing. Keep it simple as long as you can.

Good luck.

Regards,

-- 
Jehan-Guillaume de Rorthais
Dalibo

Capturing just slow queries

[Tiffany Thang]

Hi,
log_min_duration_statement captures all statements including DMLs that have
exceeded the threshold. Is there a way in PG 12 to capture just select
statements excluding all DMLs and DDLs? In my environment, it's
acceptable for DMLs and DDLs to cross the threshold and we are more
interested in capturing poor performing select statements.

Thanks.

Tiff

Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

[Devraj B]

I login to my Macbook Pro using my JumpCloud password. My userid is
firstname.lastname
/Users/firstname.lastname > whoami
firstname.lastname

After connecting to VPN, from my Macbook Pro Terminal, I connect to
PostgreSQL server staging.mycompany.com having CentOS 6.10
/Users/firstname.lastname > ssh  staging.mycompany.com
[firstname.lastname@staging] $

Than I connect to root using my JumpCloud password
[firstname.lastname@staging] $ sudo su -
[sudo] password for firstname.lastname:  < I enter my JumpCloud Password
here >
[root@staging] #
(Above, I could connect to root using my JumpCloud Password. I don't know
what setup would have been done for that.)

Checked the O/S
[root@staging] # uname -a
Linux  staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15 16:51:15
UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
CentOS release 6.10 (Final)

Connected to PostgreSQL (9.6.17) to create a user exactly SAME as of my
Macbook Pro (or JumpCloud) username.
[root@staging] # psql -h localhost -U postgres
postgres=#
postgres=# select version();
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-23), 64-bit
postgres=# create user "firstname.lastname";
CREATE ROLE
postgres=# grant connect on database my_test_db to "firstname.lastname";
GRANT
postgres=# \q

>From server staging.mycompany.com I can connect to the database using user
firstname.lastname successfully.
[root@staging] #  psql -h localhost -U firstname.lastname -d my_test_db
my_test_db=>

But I want to setup JumpCloud or LDAP or any other authentication so that I
can connect PostgreSQL user  "firstname.lastname" directly from my Macbook
Pro Terminal using my JumpCloud Password. Like:-

/Users/firstname.lastname >  psql -h staging.mycompany.com -U
firstname.lastname -d my_test_db
Password for user firstname.lastname:  < I have Not given any password when
I created PostgreSQL user firstname.lastname above and trying to
authenticate here using my JumpCloud password, May be the same way as I
could connect above to root. But when I enter my JumpCloud Password here
there is an error. >
psql: error: could not connect to server: FATAL:  password authentication
failed for user "firstname.lastname"

What more to set up so that I can connect PostgreSQL (9.6.17) database
using PostgreSQL user ( with the Same name as of my JumpCloud/ Macbook Pro
user) directly from my Macbook Pro terminal using my JumpCloud Password
authentication?
What do I need to set up in pg_hba.conf ?
Please suggest in detail.
Thks

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

[Adrian Klaver]

On 7/16/20 12:04 PM, Devraj B wrote:
I login to my Macbook Pro using my JumpCloud password. My userid is 
firstname.lastname

/Users/firstname.lastname > whoami
firstname.lastname

After connecting to VPN, from my Macbook Pro Terminal, I connect to 
PostgreSQL server staging.mycompany.com  
having CentOS 6.10
/Users/firstname.lastname > ssh staging.mycompany.com 


[firstname.lastname@staging] $

Than I connect to root using my JumpCloud password
[firstname.lastname@staging] $ sudo su -
[sudo] password for firstname.lastname:  < I enter my JumpCloud Password 
here >

[root@staging] #
(Above, I could connect to root using my JumpCloud Password. I don't 
know what setup would have been done for that.)


Checked the O/S
[root@staging] # uname -a
Linux  staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15 
16:51:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

[root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
CentOS release 6.10 (Final)

Connected to PostgreSQL (9.6.17) to create a user exactly SAME as of my 
Macbook Pro (or JumpCloud) username.

[root@staging] # psql -h localhost -U postgres
postgres=#
postgres=# select version();
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-23), 64-bit

postgres=# create user "firstname.lastname";
CREATE ROLE
postgres=# grant connect on database my_test_db to "firstname.lastname";
GRANT
postgres=# \q

 From server staging.mycompany.com  I can 
connect to the database using user  firstname.lastname successfully.

[root@staging] #  psql -h localhost -U firstname.lastname -d my_test_db
my_test_db=>

But I want to setup JumpCloud or LDAP or any other authentication so 
that I can connect PostgreSQL user  "firstname.lastname" directly from 
my Macbook Pro Terminal using my JumpCloud Password. Like:-


/Users/firstname.lastname >  psql -h staging.mycompany.com 
 -U firstname.lastname -d my_test_db
Password for user firstname.lastname:  < I have Not given any password 
when I created PostgreSQL user firstname.lastname above and trying to 
authenticate here using my JumpCloud password, May be the same way as I 
could connect above to root. But when I enter my JumpCloud Password here 
there is an error. >
psql: error: could not connect to server: FATAL:  password 
authentication failed for user "firstname.lastname"


Well I would say the Postgres server has pg_hba.conf setup to require 
password authentication for host(ssl) connections. A good idea.


You need to back to part above where you shelled into psql prompt and
use ALTER ROLE:

https://www.postgresql.org/docs/12/sql-alterrole.html

and do

ALTER ROLE firstname.lastname WITH LOGIN PASSWORD 'the_password';

You need the LOGIN if you want that role to connect directly to database.

FYI, the above can be done as part of CREATE ROLE also:

https://www.postgresql.org/docs/12/sql-createrole.html

You probably want to read through above anyway to see what the options 
are and they do.




What more to set up so that I can connect PostgreSQL (9.6.17) database 
using PostgreSQL user ( with the Same name as of my JumpCloud/ Macbook 
Pro user) directly from my Macbook Pro terminal using my JumpCloud 
Password authentication?

What do I need to set up in pg_hba.conf ?
Please suggest in detail.
Thks




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

[Adrian Klaver]

On 7/16/20 1:17 PM, Devraj B wrote:

Please reply to list also.
Ccing list.

Thanks Adrian,

I had granted LOGIN to  PostgreSQL user  firstname.lastname but do Not 
want to provide a database password,
rather I wanna access the database using my JumpCloud password directly 
from my Macbook Pro using LDAP authentication or any other authentication.


Please suggest me following:


But I want to setup JumpCloud or LDAP or any other authentication so
that I can connect PostgreSQL user  "firstname.lastname" directly from
my Macbook Pro Terminal using my JumpCloud Password. Like:-


That's outside my knowledge, so someone else is going to have to jump in 
on this.




/Users/firstname.lastname >  psql -h staging.mycompany.com 

 > > -U 
firstname.lastname -d my_test_db

Password for user firstname.lastname:  < I have Not given any password
when I created PostgreSQL user firstname.lastname above and trying to
authenticate here using my JumpCloud password, May be the same way as I
could connect above to root. But when I enter my JumpCloud Password here
there is an error. >
psql: error: could not connect to server: FATAL:  password
authentication failed for user "firstname.lastname"



On Thu, Jul 16, 2020 at 2:55 PM Adrian Klaver > wrote:


On 7/16/20 12:04 PM, Devraj B wrote:
 > I login to my Macbook Pro using my JumpCloud password. My userid is
 > firstname.lastname
 > /Users/firstname.lastname > whoami
 > firstname.lastname
 >
 > After connecting to VPN, from my Macbook Pro Terminal, I connect to
 > PostgreSQL server staging.mycompany.com
 
 > having CentOS 6.10
 > /Users/firstname.lastname > ssh staging.mycompany.com

 > 
 > [firstname.lastname@staging] $
 >
 > Than I connect to root using my JumpCloud password
 > [firstname.lastname@staging] $ sudo su -
 > [sudo] password for firstname.lastname:  < I enter my JumpCloud
Password
 > here >
 > [root@staging] #
 > (Above, I could connect to root using my JumpCloud Password. I don't
 > know what setup would have been done for that.)
 >
 > Checked the O/S
 > [root@staging] # uname -a
 > Linux  staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15
 > 16:51:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
 > [root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
 > CentOS release 6.10 (Final)
 >
 > Connected to PostgreSQL (9.6.17) to create a user exactly SAME as
of my
 > Macbook Pro (or JumpCloud) username.
 > [root@staging] # psql -h localhost -U postgres
 > postgres=#
 > postgres=# select version();
 > PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.4.7
 > 20120313 (Red Hat 4.4.7-23), 64-bit
 > postgres=# create user "firstname.lastname";
 > CREATE ROLE
 > postgres=# grant connect on database my_test_db to
"firstname.lastname";
 > GRANT
 > postgres=# \q
 >
 >  From server staging.mycompany.com 
 I can
 > connect to the database using user  firstname.lastname successfully.
 > [root@staging] #  psql -h localhost -U firstname.lastname -d
my_test_db
 > my_test_db=>
 >
 > But I want to setup JumpCloud or LDAP or any other authentication so
 > that I can connect PostgreSQL user  "firstname.lastname" directly
from
 > my Macbook Pro Terminal using my JumpCloud Password. Like:-
 >
 > /Users/firstname.lastname >  psql -h staging.mycompany.com

 >  -U firstname.lastname -d my_test_db
 > Password for user firstname.lastname:  < I have Not given any
password
 > when I created PostgreSQL user firstname.lastname above and
trying to
 > authenticate here using my JumpCloud password, May be the same
way as I
 > could connect above to root. But when I enter my JumpCloud
Password here
 > there is an error. >
 > psql: error: could not connect to server: FATAL:  password
 > authentication failed for user "firstname.lastname"

Well I would say the Postgres server has pg_hba.conf setup to require
password authentication for host(ssl) connections. A good idea.

You need to back to part above where you shelled into psql prompt and
use ALTER ROLE:

https://www.postgresql.org/docs/12/sql-alterrole.html

and do

ALTER ROLE firstname.lastname WITH LOGIN PASSWORD 'the_password';

You need the LOGIN if you want that role to connect directly to
database.

FYI, the above can be done as part of CREATE ROLE also:

https://www.postgresql.org/docs/12/sql-createrole.html

You probably wan

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

[David G. Johnston]

On Thu, Jul 16, 2020 at 1:24 PM Adrian Klaver 
wrote:

> On 7/16/20 1:17 PM, Devraj B wrote:
>
> Please reply to list also.
> Ccing list.
> > Thanks Adrian,
> >
> > I had granted LOGIN to  PostgreSQL user  firstname.lastname but do Not
> > want to provide a database password,
> > rather I wanna access the database using my JumpCloud password directly
> > from my Macbook Pro using LDAP authentication or any other
> authentication.
> >
> > Please suggest me following:
> >
> >> But I want to setup JumpCloud or LDAP or any other authentication so
> >> that I can connect PostgreSQL user  "firstname.lastname" directly from
> >> my Macbook Pro Terminal using my JumpCloud Password. Like:-
>
> That's outside my knowledge, so someone else is going to have to jump in
> on this.
>

It is quite possible this can be done.  I mean, there are 11 different
authentication methods mentioned in the documentation.  But the thing is,
actually setting up the link between the external authentication method and
PostgreSQL takes knowledge and skills outside the normal DBA's skillset and
outside the responsibility of PostgreSQL.  It may be that there is
information on the Internet, or someone chimes in (though 2 business days
without a response means I wouldn't hold my breath), but the documentation
is only going to be of limited use - but is important none-the-less.

My suggestion to the OP is to just get password or other PostgreSQL-only
authentication working, possibly with the help of PostgreSQL people, and
then decide whether the convenience of what is basically single-sign-on is
worth the learning curve.

David J.

Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

[Naresh Kumar]

Yes Karsten,

Can some one help us on this please.

Regards,
Narresh

On Wed 15 Jul, 2020, 10:55 PM Karsten Hilbert  On Wed, Jul 15, 2020 at 10:03:06PM +0530, Naresh Kumar wrote:
>
> > Already I tried to connect with template0, template1, user database name,
> > postgres, nothing is working getting same error message as mentioned in
> > trail mail.
> >
> > For the second one, yes we don't have backups to restore, we don't have
> any
> > option apart from data directory(no wal files)
>
> At this point you are very likely in need of (highly)
> specialized professional help.
>
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>

Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

[Christophe Pettus]

> On Jul 16, 2020, at 18:32, Naresh Kumar  wrote:
> 
> Can some one help us on this please.

To be clear, what you almost certainly have here is serious data corruption.  
You will need to find a (paid, commercial) specialist to help you with the 
recovery.  You are unlikely to get the level of support you need on this list, 
for free.

--
-- Christophe Pettus
   x...@thebuild.com

Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

[Naresh Kumar]

Thanks Christopher, if you any such contacts can you share with us.

Regards,
Narresh

On Fri 17 Jul, 2020, 7:04 AM Christophe Pettus 
>
> > On Jul 16, 2020, at 18:32, Naresh Kumar  wrote:
> >
> > Can some one help us on this please.
>
> To be clear, what you almost certainly have here is serious data
> corruption.  You will need to find a (paid, commercial) specialist to help
> you with the recovery.  You are unlikely to get the level of support you
> need on this list, for free.
>
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>

Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

[Christophe Pettus]

> On Jul 16, 2020, at 19:08, Naresh Kumar  wrote:
> 
> Thanks Christopher, if you any such contacts can you share with us.

The community maintains this page; I'm sure you can find someone who can help 
you there:

https://www.postgresql.org/support/professional_support/

--
-- Christophe Pettus
   x...@thebuild.com

Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"

[Naresh Kumar]

Thanks, I will check it out.

On Fri 17 Jul, 2020, 7:42 AM Christophe Pettus 
>
> > On Jul 16, 2020, at 19:08, Naresh Kumar  wrote:
> >
> > Thanks Christopher, if you any such contacts can you share with us.
>
> The community maintains this page; I'm sure you can find someone who can
> help you there:
>
> https://www.postgresql.org/support/professional_support/
>
> --
> -- Christophe Pettus
>x...@thebuild.com
>
>