Re: Reg: User creation script/List of privileges

[Tomas Pospisek]

On 30.03.22 05:09, Sai Ch wrote:

Hi Experts,

I am looking for a query or possibility to generate all the privileges a 
user/role has.


I need this to create a user/role from one database to another with 
exact same privileges.


Kindly, share the query or way to proceed further.

Thanks & Regards,


Maybe have a look at 
https://github.com/tpo/little_shell_scripts/blob/master/psql_access_priv_decoder

ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note that 
`pg_dump.dump` contains:


CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING = 
'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do not 
know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) I 
get:


CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?
*t

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

On 22.06.22 21:25, Adrian Klaver wrote:

On 6/22/22 12:17, Tomas Pospisek wrote:

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note 
that `pg_dump.dump` contains:


 CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING 
= 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do 
not know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1) 
I get:


 CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

 CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?


Are dumping/restoring from one version of Postgres to another?


Yes, indeed!

If from older to newer then use the new version of pg_dump(13) to dump 
the older(12) database. Then the 13 version of restore to load the 
version 13 database.


I will. Thanks a lot Adrian!
*t

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

On 22.06.22 22:18, Tomas Pospisek wrote:

On 22.06.22 21:25, Adrian Klaver wrote:

On 6/22/22 12:17, Tomas Pospisek wrote:

Hi all,

while doing `cat pg_dump.dump | psql` I get the above message. Note 
that `pg_dump.dump` contains:


 CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING 
= 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


What is exactly the problem? I understand that en_US.UTF-8 and 
en_US.utf-8 is not *exactly* the same string.


However I do not understand how the difference came to be. And I do 
not know what the "right" way is and how to proceed from here.


If I `pg_dump --create` some DB on the new server 
(13.7-1.pgdg18.04+1) I get:


 CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';

When I do the same on the old server (12.8-1.pgdg20.04+1) I get:

 CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' 
LC_CTYPE = 'en_US.UTF-8';


Any hints or help?


Are dumping/restoring from one version of Postgres to another?


Yes, indeed!

If from older to newer then use the new version of pg_dump(13) to dump 
the older(12) database. Then the 13 version of restore to load the 
version 13 database.


I will. Thanks a lot Adrian!


So I used both pg_dump and pg_restore from the newer machine. Result is 
still the same.  So I'll use Tom Lane's suggestion too and fix the 
'UTF-8' spelling in the dump file:


Tom Lane wrote:

> This is probably more about dumping from different operating systems.
> The spelling of the locale name is under the control of the OS,
> and Postgres doesn't know very much about the semantics of it
> (so I think we conservatively assume that any difference in
> spelling is significant).
>
> Best bet might be to edit the dump file to adjust the locale
> spellings to match your new system.

Many thanks to both Tom & Adrian!!!
*t

Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

[Tomas Pospisek]

On 23.06.22 20:21, Adrian Klaver wrote:

On 6/23/22 10:11, Adrian Klaver wrote:

On 6/23/22 00:37, Tomas Pospisek wrote:

On 22.06.22 22:18, Tomas Pospisek wrote:

On 22.06.22 21:25, Adrian Klaver wrote:

On 6/22/22 12:17, Tomas Pospisek wrote:




So I used both pg_dump and pg_restore from the newer machine. Result 
is still the same.  So I'll use Tom Lane's suggestion too and fix the 
'UTF-8' spelling in the dump file:


Not sure why that is necessary? Is seems this is low hanging fruit 
that could dealt with by the equivalent of lower('en_US.UTF-8') = 
lower('en_US.utf-8').


Well that was clear as mud.


:-D

My point was that I don't see why the end 
user should have to do this when it could be handled internally in the 
pg_restore code.


That would indeed be very friendly of pg_restore if it'd take that 
little task off the user :-)


+1 of course :-)

*t

can't get psql authentication against Active Directory working

[Tomas Pospisek]

Hello all,

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) 
via Active Directory.


psql (Linux) -> postgres (Linux) with authentication against Active 
Directory does work.


However the same with psql.exe on Windows does not. I get:

D:\>C:\OSGeo4W\bin\psql.exe service=the_db
psql: error: connection to server at "dbserver.example.lan
(192.168.4.104), port 5432 failed: could not initiate GSSAPI
security context: No credentials were supplied, or the credentials
were unavailable or inaccessible: Internal credentials cache error

psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I 
have tried with a different psql.exe without GSS support and it would 
tell me that it does not support GSS).


The .pg_service.conf file in the users $HOME directory looks like this:

[the_db]
host=dbserver.example.lan
port=5432
user=u...@example.lan
gssencmode=require

This same pg_service.conf does work for psql (Linux).

Also getting a Kerberos ticket for the service on Windows does work:

D:\> klist get postgres/dbserver.example@example.lan
[...]
This will list the ticket

But when using psql.exe it will not get a ticket for the service nor 
will it apparently use the existing service ticket.


I have tried to trace psql.exe with Window's Process Monitor and I can't 
see it accessing no keytab file (I'm not sure whether a keytab file 
exists at all under Windows or if psql.exe doesn't instead need to 
access some Windows service). I see that psql.exe will open and close a 
TCP connection to dbserver.example.lan, however as far as I can see that 
connection is completely irrelevant for the aquisition of a Kerberos 
ticket for the service since that is a business purely between psql.exe 
and Active Directory or respectively between psql.exe and the 
credentials cache. And there is no other TCP connection being opened to 
anywhere from psql.exe.


What I find suspicious about the error above is "... the credentials 
were unavailable or inaccessible: Internal credentials cache error", 
since that looks like either psql.exe can't access the (inexisting) 
keytab file, or it can't access Window's Kerberos service.


Also, I see that psql.exe is trying to access a ccapiserver.exe which 
does not exist. Should psql.exe be able to access that ccapiserver.exe 
file? That means is the OSGeo4W QGIS installer, that also installs all 
things necessary for psql missing that ccapiserver.exe executable?


* has anybody ever succeeded in authenticating with psql.exe against 
Active Directory?

* can you maybe tell me what's wrong from the error message above?
* how can I proceed from here?

Thanks a lot for any pointers and/or help!!!
*t

PS: Any way to make GSS more talkative? At this moment all that I can 
get as logs is the above "computer says no".

Re: can't get psql authentication against Active Directory working

[Tomas Pospisek]

On 18.02.23 17:16, Erik Wienhold wrote:

On 18/02/2023 15:02 CET Tomas Pospisek  wrote:

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux)
via Active Directory.

psql (Linux) -> postgres (Linux) with authentication against Active
Directory does work.

However the same with psql.exe on Windows does not. I get:

  D:\>C:\OSGeo4W\bin\psql.exe service=the_db
  psql: error: connection to server at "dbserver.example.lan
  (192.168.4.104), port 5432 failed: could not initiate GSSAPI
  security context: No credentials were supplied, or the credentials
  were unavailable or inaccessible: Internal credentials cache error

psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I
have tried with a different psql.exe without GSS support and it would
tell me that it does not support GSS).

The .pg_service.conf file in the users $HOME directory looks like this:

  [the_db]
  host=dbserver.example.lan
  port=5432
  user=u...@example.lan
  gssencmode=require

This same pg_service.conf does work for psql (Linux).


On Windows the service file is not read from $home/.pg_service.conf but
$env:appdata/postgresql/.pg_service.conf (or 
%appdata%/postgresql/.pg_service.conf
when using cmd.exe.)


Thank you Erik,

the location of the .pg_service.conf file is not the problem, as I am 
seeing that psql.exe is reacting to its contents, that is authenticating 
differently depending on whether gssencmode is set or not (maybe I noted 
its location wrongly, since i've written the post from my notes, as I do 
not have access to the machines in question all the time).


Do you have authentication from psql.exe against Active Directory working?
*t

Re: can't get psql authentication against Active Directory working

[Tomas Pospisek]

Hi Stephen,

first: thanks a lot for replying!

On 20.02.23 15:17, Stephen Frost wrote:


* Tomas Pospisek (t...@sourcepole.ch) wrote:

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via
Active Directory.


Looks like you're trying to do a bit more than that as you're using
GSSAPI (not SSPI, which would be the more typical method on Windows..)
and you're asking for an encrypted connection.  Is there a reason you're
going down this particular route..?


The reason I'm using the GSSAPI is it's the only documented way to do 
authentication from psql.exe against Active Directory that was able to 
find. Even now I am unable to find *any* documentation on how to go 
about doing auth from psql.exe to AD with SSPI. Would you have any 
pointers to documentation or a howto (or a blog post or a stackoverflow 
answer or...).


The reason I am apparently asking for encrypted communications is that I 
apparently misinterpreted what `gssencmode`: I thought that it would 
force the use of GSS but apparently it's forcing a connection encryption?


How do I force psql.exe to use GSS and/or SSPI then (instead of 
username/password authentication that it seems to be doing by default).


Maybe the reason I'm using GSS is because what I really want to achieve 
is to authenticate from QGIS with AD. QGIS (from osgeo4w) is shipping a 
libpq that is linked against the kerberos library, so that's what I 
have. I have no idea how libpq needs to be compiled so as to be able to 
do SSPI?


Also, just in case I would find out how to get my hand (or build) a 
psql/libpsql that *can* do SSPI - would I be able to swap out the 
existing libpq for the libpq with SSPI support and dependend executable 
would still be supposed to work?


Or am I completely misunderstanding everything?


psql (Linux) -> postgres (Linux) with authentication against Active
Directory does work.


That's good.  One thing to know about Linux vs. Windows when it comes to
Kerberos (and GSSAPI/SSPI) is that the default service name is different
between them.  See the libpq parameter krbsrvname documentation here:

https://www.postgresql.org/docs/current/libpq-connect.html


I quote that document:

"krbsrvname

Kerberos service name to use when authenticating with GSSAPI. This 
must match the service name specified in the server configuration for 
Kerberos authentication to succeed. (See also Section 21.6.) The default 
value is normally postgres, but that can be changed when building 
PostgreSQL via the --with-krb-srvnam option of configure. In most 
environments, this parameter never needs to be changed. Some Kerberos 
implementations might require a different service name, such as 
Microsoft Active Directory which requires the service name to be in 
upper case (POSTGRES).

"

I'm using the postgres server from apt.postgresql.org. I do not know 
whether it has been built with `--with-krb-srvnam` but as far as I can 
find out it was not.


Could you please give me a working example? Assuming I need to set 
`krbsrvname=POSTGRES` then where would I have to set that? In 
pg_service.conf? Or in krb5.conf? Or somewhere else?



However the same with psql.exe on Windows does not. I get:

 D:\>C:\OSGeo4W\bin\psql.exe service=the_db
 psql: error: connection to server at "dbserver.example.lan
 (192.168.4.104), port 5432 failed: could not initiate GSSAPI
 security context: No credentials were supplied, or the credentials
 were unavailable or inaccessible: Internal credentials cache error


That's a bit of an odd message to get if krbsrvname is this issue
though.


psql.exe from the OSGeo4W QGIS Installer *does* include GSS support. (I have
tried with a different psql.exe without GSS support and it would tell me
that it does not support GSS).


Providing the specific messages would possibly be helpful here..


The message I get when libpq is not compiled against GSS is:

gssencmode value "require" invalid when GSSAPI support is not 
compiled in



The .pg_service.conf file in the users $HOME directory looks like this:

 [the_db]
 host=dbserver.example.lan
 port=5432
 user=u...@example.lan
 gssencmode=require

This same pg_service.conf does work for psql (Linux).

Also getting a Kerberos ticket for the service on Windows does work:

 D:\> klist get postgres/dbserver.example@example.lan
 [...]
 This will list the ticket


Is this using MIT klist or Windows klist though?


It's using Windows klist.


But when using psql.exe it will not get a ticket for the service nor will it
apparently use the existing service ticket.


As mentioned above, GSSAPI and SSPI aren't the same thing and what I
suspect you're seeing here is that the Windows klist is using SSPI but
the psql you have is built with GSSAPI.  There used to be a Kerberos For
Windows utility that would make the bridge b

Re: can't get psql authentication against Active Directory working

[Tomas Pospisek]

Hi Stephen,

again thanks a lot for trying to help me!

On 21.02.23 16:29, Stephen Frost wrote:


* Tomas Pospisek (t...@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (t...@sourcepole.ch) wrote:

so I'm trying to authenticate psql (on Windows) -> postgres (on Linux) via
Active Directory.


Looks like you're trying to do a bit more than that as you're using
GSSAPI (not SSPI, which would be the more typical method on Windows..)
and you're asking for an encrypted connection.  Is there a reason you're
going down this particular route..?


The reason I'm using the GSSAPI is it's the only documented way to do
authentication from psql.exe against Active Directory that was able to find.


I mean ... https://www.postgresql.org/docs/current/sspi-auth.html


Were should the settings that are discussed in that document be 
configured? In pg_hba.conf?


That document says:

"SSPI authentication only works when both server and client are running 
Windows, or, on non-Windows platforms, when GSSAPI is available."


I interpret that phrase like this:

* there's a case where both server and client are running Windows 
(doesn't apply to me)
* there's a case where both are running non-Windows (doesn't apply to me 
either - server is Linux, client is Windows)


So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running 
Windows. When server or client are on non-Windows platforms then those 
need to use GSSAPI."


- assuming that my interpretation of that super terse docu is correct of 
course.



Even now I am unable to find *any* documentation on how to go about doing
auth from psql.exe to AD with SSPI. Would you have any pointers to
documentation or a howto (or a blog post or a stackoverflow answer or...).


Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication


Note that that document doesn't mention SSPI a single time.

The ultra terse Postgres docu is using terms such as "SAM", "negotiate 
mode", "UPN", without a reference to their definition, which leaves to 
Windows noobs like me (and equaly Windows non-noobs) without a clue. 
Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't 
seem to lead to useful hits. So the documentation seems to be geared to 
experts of both Kerberos and Active Directory and Windows technology 
alike which as far as I can tell is a very rare specimen.



The reason I am apparently asking for encrypted communications is that I
apparently misinterpreted what `gssencmode`: I thought that it would force
the use of GSS but apparently it's forcing a connection encryption?


gssencmode indeed requires a GSS encrypted connection.  Unfortunately,
there isn't a way to force GSS or SSPI be used for authentication today
(outside of GSS with encryption) from the client side, though that's
something that's been discussed and I think most people feel would be
good to have.  That said, if the server is configured in pg_hba for gss
(or sspi), then the client will try to authenticate that way.


As far as I can see it is *not possible* to configure the server for 
SSPI ("or sspi")? It's only possible to configure it for GSS *and* SSPI 
at the same time (?) assuming the server is running on a non-Windows 
platform?



How do I force psql.exe to use GSS and/or SSPI then (instead of
username/password authentication that it seems to be doing by default).


The server's hba needs to be configured for gss (if the server is Linux)
or sspi (if the server is on Windows) and then the client will be
informed that's what the server wants when it connects and it'll try to
authenticate that way.  It's not possible currently to force the client
to only try GSS or SSPI, what it will try is mostly driven by the server
side saying what the server wants the client to authenticate with.


Maybe the reason I'm using GSS is because what I really want to achieve is
to authenticate from QGIS with AD. QGIS (from osgeo4w) is shipping a libpq
that is linked against the kerberos library, so that's what I have. I have
no idea how libpq needs to be compiled so as to be able to do SSPI?


PostgreSQL, when built on Windows (including libpq), is always built
with SSPI support.  That said, if it's also built with GSSAPI, then SSPI
is preferred but you can set 'gsslib' to 'gssapi' to prefer gssapi be
used instead.  Now, if you're setting gssencmode to prefer or require
and the library is built with GSSAPI then we're going to try GSSAPI
first to try to set up the encrypted connection.


Also, just in case I would find ou

Re: can't get psql authentication against Active Directory working

[Tomas Pospisek]

Hi Stephen,

On 25.02.23 00:52, Stephen Frost wrote:


* Tomas Pospisek (t...@sourcepole.ch) wrote:

>>

On 21.02.23 16:29, Stephen Frost wrote:

* Tomas Pospisek (t...@sourcepole.ch) wrote:

On 20.02.23 15:17, Stephen Frost wrote:

* Tomas Pospisek (t...@sourcepole.ch) wrote:

>>

That [SSPI] document says:

"SSPI authentication only works when both server and client are running
Windows, or, on non-Windows platforms, when GSSAPI is available."

I interpret that phrase like this:

* there's a case where both server and client are running Windows (doesn't
apply to me)
* there's a case where both are running non-Windows (doesn't apply to me
either - server is Linux, client is Windows)

So that's an extremely terse docu that is not clear at all to me.

I'd suggest to change that phrase into:

"SSPI authentication works when both server and client are running Windows.
When server or client are on non-Windows platforms then those need to use
GSSAPI."

- assuming that my interpretation of that super terse docu is correct of
course.


No, that's not correct.  The blog post that I referenced is actually
using SSPI on the client and GSS on the server and it works because
they're compatible with each other on the wire.  Perhaps we could
improve the documentation you reference above but it's not actually
wrong as-is.  Perhaps this would be clearer:

SSPI and GSS are wireline compatible and can be mixed and matched
between clients and servers (where support for GSS is built into the
client library or the server), provided that there is an encryption
method which both will accept.

The encryption method bit is more of a historical artifact at this
point as modern systems have compatible AES-based encryption methods,
but that wasn't always the case.  It's also possible that it'll become
an issue in the future but at least for the moment most installations
have an AES-based compatible encryption method.


I have posted a suggestion for an improvement (via the form) to the 
pgsql-docs mailing list. Lets see what comes out of that.


I like the idea to document the *why* behind SSPI and GSS, however I 
don't understand enough about it to propose a documentation improvement.



Even now I am unable to find *any* documentation on how to go about doing
auth from psql.exe to AD with SSPI. Would you have any pointers to
documentation or a howto (or a blog post or a stackoverflow answer or...).


Sure, here's a blog post that I wrote about doing exactly that:

https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication


Note that that document doesn't mention SSPI a single time.


It doesn't actually need to because SSPI is the default and it all just
works..


I can confirm the "just works" part since my setup now indeed *does* work.

However the existing documentation on GSS/Kerberos/SSPI/Active directory 
is extremely terse and sparse. So being more explicit and being clearer 
would improve the situation I think. Are you able and interested to 
change the Blog article?


I'd suggest to add a paragraph like this:

Using a Postgresql client on Windows

If you use a Postgresql client on Windows then that client should
be able to automatically use Windows' SSPI API, which will do the
authentication via Active Directory without any further
configuration necessary.


I suppose I could have added in somewhere "SSPI is used on the
client for this", perhaps, but the blog post was more focused on "this
is what you do to make it work" and you don't really need to know that
the API that the client is using in this case happens to be called SSPI.


The ultra terse Postgres docu is using terms such as "SAM", "negotiate
mode", "UPN", without a reference to their definition, which leaves to
Windows noobs like me (and equaly Windows non-noobs) without a clue.


I'm certainly all for improving the documentation.  Not sure that just
spelling those out would really be enough and I'm rather confident that
trying to explain all of how Kerberos and AD work wouldn't really be
maintainable in our documentation.  Still, specific suggestion on how we
can improve would be great.


+1 - see my post to the pgsql-docs mailing list for a starter.


Searching "kerberos SAM" or "SSPI negotiate mode" on duckduckgo doesn't seem
to lead to useful hits. So the documentation seems to be geared to experts
of both Kerberos and Active Directory and Windows technology alike which as
far as I can tell is a very rare specimen.


Active Directory experts certainly should be generally familiar with
Kerberos (and the terms you reference above are actually all AD ones,
really..), so I'd argue that the documentation is mainly focused on
helping those individuals.  I do generally 

Re: Getting error while upgrading postgres from version 12 to 13

[Tomas Pospisek]

On 18.03.23 22:24, shashidhar Reddy wrote:
and adding to this the following is also showing in the error, we tried 
changing the file permissions but still no luck
FATAL:  could not load private key file 
"/etc/ssl/private/ssl-cert-snakeoil.key": key values mismatch


This sounds like the private SSL key you have configured is not the 
private key for the public key you have configured - they do not match.

*t

Re: Getting error while upgrading postgres from version 12 to 13

[Tomas Pospisek]

On Sun, Mar 19, 2023 at 2:09 AM shashidhar Reddy 
mailto:shashidharreddy...@gmail.com>> wrote:


Hello,

I am in a process of upgrading postgresql from version 12 to 13
using pg_upgrdrade but I am getting error stating

  FATAL:  database files are incompatible with server
  DETAIL:  The data directory was initialized by PostgreSQL version 13
, which is not compatible with this version 12.14 (Ubuntu
12.14-1.pgdg18.04+1)

Please help me on this issue.


You have configured the postgres v12 server to use a directory where 
there are data from a postgres v13 server. Evidently that can not work...

*t

Re: Properly handle OOM death?

[Tomas Pospisek]

On 13.03.23 21:25, Joe Conway wrote:

Hmm, well big +1 for having swap turned on, but I recommend setting 
"vm.overcommit_memory=2" even so.


I've snipped out the context here, since my advice is very unspecific: 
do use swap only as a safety net. Once your system starts swapping 
performance goes down the toilet.

*t

Re: Regarding SSL Enablement in PostgreSQL Database on different port

[Tomas Pospisek]

On 02.05.23 12:13, Magnus Hagander wrote:

On Tue, May 2, 2023 at 11:43 AM sujay kadam  wrote:


Hi PostgreSQL Team,


I want to enable SSL in PostgreSQL Database on a new port.

I don’t want the default port that is 5432 SSL enabled, but I want to configure 
another port to enable SSL on it.

As per my requirement, I cannot use the same port for normal connection and SSL 
connection.

Hence, we require a new port to be SSL enabled.


Please guide us with proper information and links to achieve the above task.


That is now how SSL in PostgreSQL works. It will always run on the
same port, and PostgreSQL will only listen on one port.

You can probably do some hacky solution to it by running something
like pgbouncer on a different port and enable SSL only in that one.
But it will be a hack. I would recommend instead reviewing your
requirements and see if you can make them work with how PostgreSQL is
designed.


Oh, I think your idea to use pgbouncer to take care of the SSL 
termination is elegant. I don't think me I'd characterize it as a hack 
if properly set up. Why do you consider it a hack?

*t

Re: error: connection to server on socket...

[Tomas Pospisek]

On 01.08.23 12:22, Amn Ojee Uw wrote:

Using the following command on my Debian 12 machine:

sudo apt-get install postgresql postgresql-15 postgresql-client-common 
postgresql-common postgresql-contrib postgresql-doc phppgadmin

and following the instruction on this 
 web page I have 
installed PostgreSQL-15.

The installation went smooth, until the following command was issued :

sudo -u postgres psql

... I get an error message that reads:

could not change directory to "/home/my_account": Permission denied
psql: error: connection to server on socket 
"/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

What can I do to resolve this issue?


When executing the `sudo` command above:

* as which user are you doing that (you can see that via `id`)?
* in which directory are you executing it  (you can see that via `pwd`)?
* what is the home directory of the postgres user (you can see that via 
`grep postgres /etc/passwd`)
* what does `ls -ld /var/run/postgresql/; ls -l 
/var/run/postgresql/.s.PGSQL.5432` show?


You also wrote:

> I tried this, but to no avail : `sudo systemctl start postgresql`

- what does `journalctl -xeu postgresql` say?
- what does `tail -n 20 
/var/log/postgresql/postgresql-*-qgisclouddb.log` show?


*t

Re: PORT 5432 ERROR

[Tomas Pospisek]

On 01.08.23 01:18, David Beck wrote:
I am receiving an error when I enter the server port number to listen 
to—installed on all available versions, both 32-bit and 64-bit when 
applicable, even the latest version, 15.3 x64 for Windows. I downloaded 
the latest version as an example, and as I was told, I used the 5432 
port as the default port needed. I get a pop-up afterward saying, 
"Warning: The specified port number is not available. Please enter a 
different port number," than roadblocks at entering the default port 
number. I found port 5434 available though it locks at a loading screen 
"loading server Postgre version 7.4. Version 10 works on port 5434 
though the server list tree spins eternally.


Any solution or assistance would be appreciated.


How/where do you enter the server port?
*t

apt-archive.postgresql.org a bit broken?

[Tomas Pospisek]

Hello,

I get this when I try to access the archive of focal packages:

```
# apt-get update
[...]
Err:6 https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg 
InRelease

  403  Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443]
[...]
E: Failed to fetch 
https://apt-archive.postgresql.org/pub/repos/apt/dists/focal-pgdg/InRelease 
 403  Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443]
E: The repository 'https://apt-archive.postgresql.org/pub/repos/apt 
focal-pgdg InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is 
therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user 
configuration details.

```

And so I can't access old, archived focal packages. I guess this isn't 
wanted behavior?


In case I should direct this problem report to some different place I'd 
be glad if you would tell me.


Thank you,
*t

Re: apt-archive.postgresql.org a bit broken?

[Tomas Pospisek]

On 05.07.24 18:27, Magnus Hagander wrote:



On Fri, Jul 5, 2024 at 6:07 PM Tomas Pospisek <mailto:t...@sourcepole.ch>> wrote:


Hello,

I get this when I try to access the archive of focal packages:

```
# apt-get update
[...]
Err:6 https://apt-archive.postgresql.org/pub/repos/apt
<https://apt-archive.postgresql.org/pub/repos/apt> focal-pgdg
InRelease
    403  Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443]
[...]
E: Failed to fetch
https://apt-archive.postgresql.org/pub/repos/apt/dists/focal-pgdg/InRelease 
<https://apt-archive.postgresql.org/pub/repos/apt/dists/focal-pgdg/InRelease>
   403  Forbidden [IP: 2600:9000:2761:4600:b:5965:3380:93a1 443]
E: The repository 'https://apt-archive.postgresql.org/pub/repos/apt
<https://apt-archive.postgresql.org/pub/repos/apt>
focal-pgdg InRelease' is not signed.
N: Updating from such a repository can't be done securely, and is
therefore disabled by default.
N: See apt-secure(8) manpage for repository creation and user
configuration details.
```

And so I can't access old, archived focal packages. I guess this isn't
wanted behavior?

In case I should direct this problem report to some different place I'd
be glad if you would tell me.


The dist name should be "focal-pgdg-archive" not "focal-pgdg".

You are right, thank you!
*t

Re: Alignment check

[Tomas Pospisek]

On 27.06.24 18:07, Marthin Laubscher wrote:

I don’t intend dissing or plugging anyone’s efforts or start a flame 
war, but I’d like to get a sense of how the PostgreSQL community feels 
about:

a) YugabyteDB, and
b) PostgreSQL on Kubernetes.

For my application I’m deeply vested in Kubernetes as a pathway to being 
cloud-agnostic and I have looked at YugabyteDB because it matches my 
application’s (distributed) architecture more closely.
But not all the ways to run PostgreSQL on Kubernetes are created equal, 
and YugabyteDB is really far behind on versions and do not support 
extensions in a way that’s useful to me.


Having no experience with it I can't comment on YugabyteDB. With respect 
to PostgreSQL on Kubernetes there are various solutions on how to run 
it, some of which are quite mature - that is, they have been around for 
quite some time, are used heavily and have a healthy maintenance 
community (see f.ex. postgres-operator [1]).


One IMHO problematic aspect of running postgres in one or more pods is 
that running a postgres cluster is already demanding as is. When a 
postgres cluster goes awry then there will be work awaiting you to get 
it all backup up and running without messing up user data... using a 
solution like postgres-operator puts another additional layer and 
wrapper around postgres so if things do not run well there's even more 
systems you have to handle. It might or might not help that you have 
additional layers doing stuff to the postgres service:


- pro: the additional software layers can contain more operational
  knowledge than you have and handle and fix operations better than you
  know how to do
- contra: or the additional software layers can hide, obscure, obstruct
  the lower layers and interfere with you trying to debug and fix stuff.

Backups and a tested procedure to get things back up and running from 
scratch can be useful then.


All that said, from operational experience: postgres by itself is very 
robust in taking care of preserving your data, so despite everything 
written above, usually you have to be messing up things **really hard** 
to make postgres lose data.


*t

[1] https://github.com/zalando/postgres-operator/

PS: Thanks to all of you that are taking care, that postgres is caring 
so well about the user's data!

very long secondary->primary switch time

[Tomas Pospisek]

Hello all,

I maintain a postgresql cluster that does failover via patroni. The 
problem is that after a failover happens it takes the secondary too long 
(that is about 35min) to come up and answer queries. The log of the 
secondary looks like this:



04:00:29.777 [9679] LOG:  received promote request
04:00:29.780 [9693] FATAL:  terminating walreceiver process due to 
administrator command
04:00:29.780 [9679] LOG:  invalid record length at 320/B95A1EE0: wanted 
24, got 0

04:00:29.783 [9679] LOG:  redo done at 320/B95A1EA8
04:00:29.783 [9679] LOG:  last completed transaction was at log time 
2021-03-03 03:57:46.466342+01


04:35:00.982 [9679] LOG:  selected new timeline ID: 15
04:35:01.404 [9679] LOG:  archive recovery complete
04:35:02.337 [9662] LOG:  database system is ready to accept connections

The cluster is "fairly large" with thousands of DBs (sic!) and ~1TB of data.

I would like to shorten the failover/startup time drastically. Why does 
it take the secondary that much time to switch to the primary state? 
There are no logs between 04:00 and 04:35. What is postgresql doing 
during those 35min?


I am *guessing* that postgresql *might* be doing some consistency check 
or replaying the WAL (max_wal_size: 16 GB, wal_keep_segments: 100). I am 
also *guessing* that startup time *might* have to do with the size of 
the data (~1T) or/and with the numbers of DBs (thousands). If that would 
be the case, then splitting the cluster into multiple clusters should 
allow for faster startup times?


I have tried to duckduck why the secondary takes that much time to 
switch to primary mode, but have failed to find information that would 
enlighten me. So any pointers to information, hints or help are very 
wellcome.


Thanks & greets,
*t

Re: very long secondary->primary switch time

[Tomas Pospisek]

Hi Tom, hi list participants,

thanks a lot for replying Tom.

On 27.04.21 22:16, Tom Lane wrote:

Tomas Pospisek  writes:

I maintain a postgresql cluster that does failover via patroni. The
problem is that after a failover happens it takes the secondary too long
(that is about 35min) to come up and answer queries. The log of the
secondary looks like this:



04:00:29.777 [9679] LOG:  received promote request
04:00:29.780 [9693] FATAL:  terminating walreceiver process due to
administrator command
04:00:29.780 [9679] LOG:  invalid record length at 320/B95A1EE0: wanted
24, got 0
04:00:29.783 [9679] LOG:  redo done at 320/B95A1EA8
04:00:29.783 [9679] LOG:  last completed transaction was at log time
2021-03-03 03:57:46.466342+01



04:35:00.982 [9679] LOG:  selected new timeline ID: 15
04:35:01.404 [9679] LOG:  archive recovery complete
04:35:02.337 [9662] LOG:  database system is ready to accept connections



The cluster is "fairly large" with thousands of DBs (sic!) and ~1TB of data.


Hm.  WAL replay is already done at the "redo done" entry.  There is a
checkpoint after that, I believe, and there may be some effort to search
for dead files as well.  Still, if your I/O subsystem is better than
a wet noodle, 35 minutes is a long time to finish that.

One thing I'm not sure about is whether we try to do the checkpoint
at maximum speed.  If you have set GUC options to throttle checkpoint
I/O hard, that could perhaps explain this.


I didn't do much customization of postgresql settings and am mostly 
running what was provided by the upstreams. These are my explicit 
settings (all the rest is defaults):


allow_system_table_mods: 'off'
autovacuum: 'off'
checkpoint_completion_target: '0.7'
effective_cache_size: 16 GB
maintenance_work_mem: 1 GB
max_connections: 200
max_wal_size: 16 GB
shared_buffers: 8 GB
shared_preload_libraries: pg_stat_statements
track_activities: 'on'
track_activity_query_size: 32 kB
track_functions: all
wal_keep_segments: 100
work_mem: 64 MB


You could possibly learn more by strace'ing the startup process to
see what it's doing.


Will do, thanks, however I'm dreading the next failover downtime :-(


Also, what PG version is that exactly?


12.6-1.pgdg20.04+1

Thankful for any help or pointers regarding the long promotion time,
*t

Re: WAL accumulating, Logical Replication pg 13

[Tomas Pospisek]

Hi Willy-Bas Loos,

On 31.05.21 17:32, Willy-Bas Loos wrote:



On Mon, May 31, 2021 at 4:24 PM Vijaykumar Jain 
> wrote:


So I got it all wrong it seems :)

Thank you for taking the time to help me!

You upgraded to pg13 fine? , but while on pg13 you have issues with
logical replication ? 


Yes, the upgrade went fine. So here are some details:
I already had londiste running on postgres 9.3, but londiste wouldn't 
run on Debian 10
So i first made the new server Debian 9 with postgres 9.6 and i started 
replicating with londiste from 9.3 to 9.6
When all was ready, i stopped the replication to the 9.6 server and 
deleted all londiste & pgq content with drop schema cascade.
Then I upgraded the server to Debian  10. Then i user pg_upgrade to 
upgrade from postgres 9.6 to 13. (PostGIS versions were kept compatible).

Then I added logical replication and a third server as a subscriber.

I was going to write that replication is working fine (since the table 
contains a lot of data and there are no conflicts in the log), but it 
turns out that it isn't.
The subscriber is behind and It looks like there hasn't been any 
incoming data after the initial data synchronization.
So at least now i know that the WAL is being retained with a reason. The 
connection is working properly (via psql anyway)


I once maybe had a similar problem due to some ports that were needed 
for replication being firewalled off or respectively the master having 
the wrong IP address of the old master (now standby server) or such.


There was absolutely no word anywhere in any log about the problem I was 
just seeing the new postgres master not starting up after hours and 
hours of waiting after a failover. I somehow found out about the 
required port being blocked (I don't remember - maybe seing the 
unanswered SYNs in tcpdump? Or via ufw log entries?).


I will also look into how to diagnose this from the system tables, e.g. 
substracting LSN's to get some quantitative measure  for the lag.




There is a path in the postgresql source user subscription folder
iirc which covers various logical replication scenarios.
That may help you just in case.

OK, so comments in the source code you mean?

Re: Fastest option to transfer db?

[Tomas Pospisek]

I'm potentiall facing the same problem and would be interested in the 
solution. Is there any particular howto you followed?


Also at some point I'd like to cut of the link between the two DBs 
promote the copy to be the master and delete the original DB. Have you 
figured out the correct step for the cut-over to happen?

*t

On 13.09.21 23:10, Israel Brewster wrote:
Ok, I have logical replication up-and-running (I guess - seemed to 
simple to be working. Shouldn’t it be complicated, requiring many steps 
and configuration changes?), maxing out one CPU core on each machine 
(more or less), and showing network throughput of around 15M. If DU 
changes are to be believed, it’s transferring data at about 
0.8GB/minute, implying something like a 8 hour transfer time.


Of course, since it is replication, it has the benefit that any data 
that comes in during that 8 hour window should also be replicated, after 
which the two systems should remain in sync allowing for zero (or nearly 
so) downtime cutover. Which is nice.


Any gotchas I need to be aware of during this initial transfer window, 
such as WAL files building up on the source machine?


---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

On Sep 13, 2021, at 10:10 AM, Michael Lewis > wrote:


What version of Postgres is the source? Can you make use of logical 
replication?

Re: postgresql11: How to use publication/subscription on primary/standby setup

[Tomas Pospisek]

On 29.09.21 11:01, Abhishek B wrote:
Is there a solution to use publication/subscription on a master-slave or 
primary-standby postgres setup without manual intervention?


Currently the DB clusters are managed by Pacemaker. Once there is a 
failover, the pacemaker is able to promote the secondary node to 
primary, but the logical replication stops working after that. Is there 
any way to automatically move the contents of pg_replslot to the new 
primary node?


I'm not sure I understand you correctly, but I'd suggest to have a look 
at patroni, which maybe does what you want.

*t

Fwd: PgAdmin is struggling and can we configure it so that it works better

[Tomas Pospisek]

Forgot to include pgsql-general@lists.postgresql.org in the Cc...


 Forwarded Message 
Subject: Re: PgAdmin is struggling and can we configure it so that it 
works better

Date: Fri, 21 Jan 2022 13:42:13 +0100
From: Tomas Pospisek 
To: Shaozhong SHI 

On 21.01.22 12:42, Shaozhong SHI wrote:

Some time, PgAdmin freezes and its response is very slow.

Some time, it gives strange display of table content.  Perhaps, it is 
related to the fact that PgAdmin is struggling with the amount of data 
it thinks that it has to display.


I just wonder whether we can configure PgAdmin so that it will work better.


You can compare the times the same query takes to display the result 
locally via PgAdmin and remotely, when you execute the same query on 
that server with psql. That will give you an idea on whether the long 
execution time is on the server or not.

*t

Re: Strange results when casting string to double

[Tomas Pospisek]

On 18.02.22 22:42, Peter J. Holzer wrote:


If there has been a glibc update (or a postgresql update) in those 480
days (Ubuntu 14.04 is out of its normal maintenance period but ESM is
still available) the running processes may well run different code than
a newly started program. So it could be a bug which has since been
fixed.


That would be visible via `lsof`. `libc. The file `...libc...so` that 
`postgres` is keeping open would have the text `DEL` (as in deleted) in 
the `FD` column of `lsof`'s output.


As opposed to a newly started program which would have `REG` (regular 
file) there.

*t

Re: Detecting schema changes via WAL logs

[Tomas Pospisek]

On 19.02.22 17:42, Cal Mitchell wrote:

Hello everyone,

I’m building an open-source data integration tool (SQLpipe) and am now 
trying to incorporate CDC into the product.


Is it possible to detect when schema changes happen via the WAL? The WAL 
doesn’t have to tell me /what/ was changed, it just needs to tell me 
/when/ something changed.


For example, anytime I run create table or some other DDL command, there 
is a begin and commit message in the log, with nothing in between. If 
there aren’t any other operations that create such “empty” logs, it 
seems possible to just re-scan the tables that are being synced for 
schema changes whenever one of these logs is encountered.


I remember having seen a similar question recently here - that is 
detecting schema changes.


Also I think having seen WAL to SQL scripts somewhere.

I might be wrong on both accounts, but maybe you want to review the last 
few months of list archives if you haven't done so already and maybe 
check postgres' Debian package repository if there aren't WAL to SQL 
scripts somewhere.


As I said, this is what my fuzzy in-brain SQL returns so please don't 
SIGKILL me if my query was too fuzzy indeen.

*t