RE: PostgresSQL 10 on remote server - Socket is not connected (0x00002749/10057)

[Tanja Savic]

Hi,

Thank you for your replies.

I can connect to remote server using psql and it works normally, I can do the 
select, function calls..

But in pgAdmin, even SELECT 1 doesn't work.

Also, the weirdest thing is that it actually worked for a brief moment, because 
I logged using pgAdmin and created some simple function  which I can call 
without a problem when I'm logged using psql.
But to call it from pgAdmin, it gives me the error again (Socket is not 
connected (0x2749/10057))

BR,
Tanja


-Original Message-
From: Adrian Klaver  
Sent: Wednesday, January 22, 2020 5:15 PM
To: Tanja Savic ; 
pgsql-general@lists.postgresql.org
Subject: Re: PostgresSQL 10 on remote server - Socket is not connected 
(0x2749/10057)

On 1/22/20 2:18 AM, Tanja Savic wrote:
> Hi,
> 
> I'm running a pgAdmin from Windows 10 :
> 
> pgAdmin Version: 4.17
> 
> Python Version:**3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 21:26:53) [MSC
> v.1916 32 bit (Intel)]
> 
> and I'm able to connect to the remote server. I can see schemas and 
> tables, but when I want to run a query I get this error:
> 
> could not send data to server: Socket is not connected
> (0x2749/10057) could not send SSL negotiation packet: Socket is 
> not connected (0x2749/10057)

What are the connection settings?

What is the query that fails?

Can you connect to the remote using psql?

> 
> Running queries on local DB instance is ok, error happens only when I 
> connect to the remote server.
> 
> DB Version : PostgresSQL 10.10
> 
> I do not remember doing some changes that could cause this error. Is 
> there any solution for this problem, cause I didn't googled out any?
> 
> Thank you for you time.
> 
> Best,
> 
> Tanja
> 


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

Re: Does converting an indexed varchar to text rewrite its index? Docs say so, tests say no.

[Laurenz Albe]

On Thu, 2020-01-23 at 08:55 -0800, Mike Lissner wrote:
> I think the docs say that if you convert a varchar to text, it'll rewrite the 
> index,
> but my test doesn't seem to indicate that. Is the test or the documentation 
> wrong?
> 
> If the docs, I'll be happy to make a fix my first contribution to postgresql. 
> :)
> 
> Here are the docs:
> 
> (https://www.postgresql.org/docs/10/sql-altertable.html)

We'd be happy about a documentation patch.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Clarification of documentation detail for upgrading minor version

[Phil Fisher]

Thanks to Adrian I have managed to get around this issue.  Essentially
I had to go old school and create a tar archive of the install on
another comparable system, copy that around and then install via an
untar.  (the repo access was not available as it is a server behind
many FW and in a secure vDatacentre).
The one issue I ended up with (since the system runs under systemd)
was that the directory /var/run/postgresql did not exist for some
reason and I had to create it as when I tried to restart it there was
a failure message.  I am not sure if that was an artefact from moving
from 9.6.6 to 9.6.16 or not but I mention it in case someone has a
similar issue when starting up after the "upgrade".

Phil

On Thu, 23 Jan 2020 at 15:46, Adrian Klaver  wrote:
>
> On 1/23/20 3:28 AM, Phil Fisher wrote:
> > Hello
> >
> > I have a 9.6.6 system that misses some features.  It is installed on a
> > RHEL7 OS.  Reading the 9.6 documentation to upgrade I find the
> > following:
> >
> >   "
> >
> > 18.6. Upgrading a PostgreSQL Cluster
> >
> > This section discusses how to upgrade your database data from one
> > PostgreSQL release to a newer one.
> >
> > PostgreSQL major versions are represented by the first two digit
> > groups of the version number, e.g., 8.4. PostgreSQL minor versions are
> > represented by the third group of version digits, e.g., 8.4.2 is the
> > second minor release of 8.4. Minor releases never change the internal
> > storage format and are always compatible with earlier and later minor
> > releases of the same major version number, e.g., 8.4.2 is compatible
> > with 8.4, 8.4.1 and 8.4.6. To update between compatible versions, you
> > simply replace the executables while the server is down and restart
> > the server. The data directory remains unchanged — minor upgrades are
> > that simple."
> >
> > Can someone please clarify what actions should be taken for this (see
> > penultimate sentence)?  If I install (yum upgrade for example) then I
> > believe all updated files will be updated and that does not seem to be
> > the message in the above.  While I can install to a different area
> > (and since I will be using the PGDG repo it WILL be different) I could
>
> Are you currently using the PGDG repos?
>
> If so then the files will be installed in the same locations. They will
> just be the new versions.
>
> > do this and manually copy the 'bin' directory.  However, I would have
> > expected to have to copy the 'libs' directory as well and perhaps
> > others (excluding doc etc.).  I will be moving to a 9.6.16 version so
> > that seems to comply with the restrictions mentioned in the
> > documentation (and for the avoidance of good people saying use a later
> > version and do a major upgrade that is not currently
> > possible/feasible).
> >
> > Grateful for any detailed advice on the correct path for the above.
> > Thanks.
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Phil Fisher
DevOPS/Linux Engineer

T: +44 1473 731230 (office)

i2N Ltd
A NEC Company
Unit 3 Appletree Business Centre
Folly Lane
Copdock
Suffolk
IP8 3JQ

Tel:  +44 (0)1429 558255 (Hartlepool Service Desk)
Please consider the environment before printing this email

-- 
This email is sent on behalf of Northgate Public Services (UK) Limited and 
its associated companies including Rave Technologies (India) Pvt Limited 
(together "Northgate Public Services") and is strictly confidential and 
intended solely for the addressee(s). 
If you are not the intended 
recipient of this email you must: (i) not disclose, copy or distribute its 
contents to any other person nor use its contents in any way or you may be 
acting unlawfully;  (ii) contact Northgate Public Services immediately on 
+44(0)1442 768445 quoting the name of the sender and the addressee then 
delete it from your system.
Northgate Public Services has taken reasonable 
precautions to ensure that no viruses are contained in this email, but does 
not accept any responsibility once this email has been transmitted.  You 
should scan attachments (if any) for viruses.


Northgate Public Services 
(UK) Limited, registered in England and Wales under number 00968498 with a 
registered address of Peoplebuilding 2, Peoplebuilding Estate, Maylands 
Avenue, Hemel Hempstead, Hertfordshire, HP2 4NW.  Rave Technologies (India) 
Pvt Limited, registered in India under number U31900MH1998PTC117068 with a

registered address of PLOT CS 445, 3 rd Floor, A-wing Madhu Corporate Park

Ltd, Pandurang Budhkar Marg, Mumbai -400013

Re: PostgresSQL 10 on remote server - Socket is not connected (0x00002749/10057)

[Adrian Klaver]

On 1/24/20 2:31 AM, Tanja Savic wrote:

Hi,

Thank you for your replies.

I can connect to remote server using psql and it works normally, I can do the 
select, function calls..

But in pgAdmin, even SELECT 1 doesn't work.

Also, the weirdest thing is that it actually worked for a brief moment, because 
I logged using pgAdmin and created some simple function  which I can call 
without a problem when I'm logged using psql.
But to call it from pgAdmin, it gives me the error again (Socket is not 
connected (0x2749/10057))


From the pgAdmin issue tracker(you will need Postgres community account 
to see below):


https://redmine.postgresql.org/issues/4970

The relevant part:

"An error occured opening the socket after Windows Update from 17.09 to 
19.09:


could not send data to server: Socket is not connected 
(0x2749/10057) could not send startup packet. Socket is not 
connected (0x2749/10057)


"

"There is discussion here on this - 
https://answers.microsoft.com/en-us/windows/forum/windows_10-networking/some-programsapps-unable-to-connect-after-windows/689f7204-63fa-4f03-857a-3e4b0ff7d0ce
One of the solution was to reset tcpip stack. I would suggest you to try 
it once.

Kindly let us know if it fixed for you."

There was no response from the issue submitter so I assume the above worked.



BR,
Tanja





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

Re: Clarification of documentation detail for upgrading minor version

[Adrian Klaver]

On 1/24/20 12:27 AM, Phil Fisher wrote:
Please reply to list also.
Ccing list.


Hi Adrian

no the version currently installed came from a RHEL repo I believe so
binaries etc. are not in same place, /usr/local IIRC.

If there is a an easy/simple way of describing an approach it would be
good as I am being pressured to complete this work today.
(While understanding that is not how it works ...)

Phil

On Thu, 23 Jan 2020 at 15:46, Adrian Klaver  wrote:


On 1/23/20 3:28 AM, Phil Fisher wrote:

Hello

I have a 9.6.6 system that misses some features.  It is installed on a
RHEL7 OS.  Reading the 9.6 documentation to upgrade I find the
following:

   "

18.6. Upgrading a PostgreSQL Cluster

This section discusses how to upgrade your database data from one
PostgreSQL release to a newer one.

PostgreSQL major versions are represented by the first two digit
groups of the version number, e.g., 8.4. PostgreSQL minor versions are
represented by the third group of version digits, e.g., 8.4.2 is the
second minor release of 8.4. Minor releases never change the internal
storage format and are always compatible with earlier and later minor
releases of the same major version number, e.g., 8.4.2 is compatible
with 8.4, 8.4.1 and 8.4.6. To update between compatible versions, you
simply replace the executables while the server is down and restart
the server. The data directory remains unchanged — minor upgrades are
that simple."

Can someone please clarify what actions should be taken for this (see
penultimate sentence)?  If I install (yum upgrade for example) then I
believe all updated files will be updated and that does not seem to be
the message in the above.  While I can install to a different area
(and since I will be using the PGDG repo it WILL be different) I could


Are you currently using the PGDG repos?

If so then the files will be installed in the same locations. They will
just be the new versions.


do this and manually copy the 'bin' directory.  However, I would have
expected to have to copy the 'libs' directory as well and perhaps
others (excluding doc etc.).  I will be moving to a 9.6.16 version so
that seems to comply with the restrictions mentioned in the
documentation (and for the avoidance of good people saying use a later
version and do a major upgrade that is not currently
possible/feasible).

Grateful for any detailed advice on the correct path for the above.
Thanks.




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







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

Re: Clarification of documentation detail for upgrading minor version

[Adrian Klaver]

On 1/24/20 6:40 AM, Phil Fisher wrote:

Thanks to Adrian I have managed to get around this issue.  Essentially
I had to go old school and create a tar archive of the install on
another comparable system, copy that around and then install via an
untar.  (the repo access was not available as it is a server behind
many FW and in a secure vDatacentre).


So did you copy a RHEL repo install or a PGDG repo one?


The one issue I ended up with (since the system runs under systemd)
was that the directory /var/run/postgresql did not exist for some
reason and I had to create it as when I tried to restart it there was
a failure message.  I am not sure if that was an artefact from moving
from 9.6.6 to 9.6.16 or not but I mention it in case someone has a
similar issue when starting up after the "upgrade".

Phil

On Thu, 23 Jan 2020 at 15:46, Adrian Klaver  wrote:


On 1/23/20 3:28 AM, Phil Fisher wrote:

Hello

I have a 9.6.6 system that misses some features.  It is installed on a
RHEL7 OS.  Reading the 9.6 documentation to upgrade I find the
following:

   "

18.6. Upgrading a PostgreSQL Cluster

This section discusses how to upgrade your database data from one
PostgreSQL release to a newer one.

PostgreSQL major versions are represented by the first two digit
groups of the version number, e.g., 8.4. PostgreSQL minor versions are
represented by the third group of version digits, e.g., 8.4.2 is the
second minor release of 8.4. Minor releases never change the internal
storage format and are always compatible with earlier and later minor
releases of the same major version number, e.g., 8.4.2 is compatible
with 8.4, 8.4.1 and 8.4.6. To update between compatible versions, you
simply replace the executables while the server is down and restart
the server. The data directory remains unchanged — minor upgrades are
that simple."

Can someone please clarify what actions should be taken for this (see
penultimate sentence)?  If I install (yum upgrade for example) then I
believe all updated files will be updated and that does not seem to be
the message in the above.  While I can install to a different area
(and since I will be using the PGDG repo it WILL be different) I could


Are you currently using the PGDG repos?

If so then the files will be installed in the same locations. They will
just be the new versions.


do this and manually copy the 'bin' directory.  However, I would have
expected to have to copy the 'libs' directory as well and perhaps
others (excluding doc etc.).  I will be moving to a 9.6.16 version so
that seems to comply with the restrictions mentioned in the
documentation (and for the avoidance of good people saying use a later
version and do a major upgrade that is not currently
possible/feasible).

Grateful for any detailed advice on the correct path for the above.
Thanks.




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







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

Time zone "GST" not recognized.

[nikhil raj]

Hi ALL,

Required help with the time zone. i am getting error on the GST time line.
is tht a bug or postgres doesn't support the GST time zone.


select CURRENT_TIMESTAMP AT TIME ZONE 'GST'
ERROR: time zone "GST" not recognized SQL state: 22023


Postgres -:11.2
Ubuntu:-18.04

Re: Time zone "GST" not recognized.

[Tom Lane]

nikhil raj  writes:
> Required help with the time zone. i am getting error on the GST time line.
> is tht a bug or postgres doesn't support the GST time zone.

There is no such time zone abbreviation according to the IANA
time zone database [1], which is what we rely on.

regards, tom lane

[1] http://www.iana.org/time-zones

Re: Time zone "GST" not recognized.

[Adrian Klaver]

On 1/24/20 8:59 AM, nikhil raj wrote:

Hi ALL,

Required help with the time zone. i am getting error on the GST time 
line. is tht a bug or postgres doesn't support the GST time zone.



select CURRENT_TIMESTAMP AT TIME ZONE 'GST'
ERROR: time zone "GST" not recognized SQL state: 22023


Try GMT:

test_(postgres)# select CURRENT_TIMESTAMP AT TIME ZONE 'GMT';
  timezone

 2020-01-24 17:17:52.754861

For list of recognized abbreviations:

https://www.postgresql.org/docs/11/view-pg-timezone-abbrevs.html




Postgres -:11.2
Ubuntu:-18.04




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

Re: Time zone "GST" not recognized.

[Adrian Klaver]

On 1/24/20 9:19 AM, Adrian Klaver wrote:

On 1/24/20 8:59 AM, nikhil raj wrote:

Hi ALL,

Required help with the time zone. i am getting error on the GST time 
line. is tht a bug or postgres doesn't support the GST time zone.



select CURRENT_TIMESTAMP AT TIME ZONE 'GST'
ERROR: time zone "GST" not recognized SQL state: 22023


Try GMT:

test_(postgres)# select CURRENT_TIMESTAMP AT TIME ZONE 'GMT';
   timezone

  2020-01-24 17:17:52.754861


Aah, is this what you are looking for?:

https://24timezones.com/time-zone/gst

It seems to be an alias for UTC+04, though not recognized by IANA. That 
would be:


Asia/Dubai or Asia/Muscat

Unless you are talking about:

https://24timezones.com/time-zone/gst_georgia



For list of recognized abbreviations:

https://www.postgresql.org/docs/11/view-pg-timezone-abbrevs.html




Postgres -:11.2
Ubuntu:-18.04







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

continuation character for meta commands in psql

[Matt Zagrabelny]

Greetings,

I've searched the internet and also the pgsql-general list for an answer to
this question, but couldn't find one.

Is there a continuation character for meta commands in psql?

I've seen mention that a caret (^) could be used, but it didn't work
correctly and the psql man page makes no mention of it.

I'd like to do something like:

\set PROMPT1 'long prompt code' \
'other long prompt configurations' \
'and more still'

in my .psqlrc file.

What do you folks think?

Thanks for any help or guidance!

-m

Re: continuation character for meta commands in psql

[Adrian Klaver]

On 1/24/20 9:42 AM, Matt Zagrabelny wrote:

Greetings,

I've searched the internet and also the pgsql-general list for an answer 
to this question, but couldn't find one.


Is there a continuation character for meta commands in psql?

I've seen mention that a caret (^) could be used, but it didn't work 
correctly and the psql man page makes no mention of it.


I'd like to do something like:

\set PROMPT1 'long prompt code' \
'other long prompt configurations' \
'and more still'

in my .psqlrc file.

What do you folks think?

Thanks for any help or guidance!


See:

https://www.postgresql.org/docs/12/app-psql.html

Meta-Commands



-m



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

Re: continuation character for meta commands in psql

[Tom Lane]

Matt Zagrabelny  writes:
> Is there a continuation character for meta commands in psql?

No.

regards, tom lane

Re: Time zone "GST" not recognized.

[nikhil raj]

 Hi Adrian,

Thanks for the quick response.

Please can you help me over here So Postgres is not supporting GST ( Gulf
Standard Time) time zone .  As if we have a entries of few things with
timestamp with time zone  from application end.

example :- In 2 column we are entering these details but when client from
GST time zone i am getting 21:41:39 GST

[image: image.png]

but which i insert into the databases is showing empty column.

On Fri, Jan 24, 2020 at 11:09 PM Adrian Klaver 
wrote:

> On 1/24/20 9:19 AM, Adrian Klaver wrote:
> > On 1/24/20 8:59 AM, nikhil raj wrote:
> >> Hi ALL,
> >>
> >> Required help with the time zone. i am getting error on the GST time
> >> line. is tht a bug or postgres doesn't support the GST time zone.
> >>
> >>
> >> select CURRENT_TIMESTAMP AT TIME ZONE 'GST'
> >> ERROR: time zone "GST" not recognized SQL state: 22023
> >
> > Try GMT:
> >
> > test_(postgres)# select CURRENT_TIMESTAMP AT TIME ZONE 'GMT';
> >timezone
> > 
> >   2020-01-24 17:17:52.754861
>
> Aah, is this what you are looking for?:
>
> https://24timezones.com/time-zone/gst
>
> It seems to be an alias for UTC+04, though not recognized by IANA. That
> would be:
>
> Asia/Dubai or Asia/Muscat
>
> Unless you are talking about:
>
> https://24timezones.com/time-zone/gst_georgia
>
> >
> > For list of recognized abbreviations:
> >
> > https://www.postgresql.org/docs/11/view-pg-timezone-abbrevs.html
> >
> >>
> >>
> >> Postgres -:11.2
> >> Ubuntu:-18.04
> >>
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Time zone "GST" not recognized.

[Tom Lane]

nikhil raj  writes:
> Please can you help me over here So Postgres is not supporting GST ( Gulf
> Standard Time) time zone .  As if we have a entries of few things with
> timestamp with time zone  from application end.

You could set up a private timezone abbreviation configuration.

https://www.postgresql.org/docs/current/datetime-config-files.html

regards, tom lane

Re: Time zone "GST" not recognized.

[Adrian Klaver]

On 1/24/20 9:57 AM, nikhil raj wrote:

Hi Adrian,

Thanks for the quick response.

Please can you help me over here So Postgres is not supporting GST ( 
Gulf Standard Time) time zone .  As if we have a entries of few things 
with timestamp with time zone  from application end.


example :- In 2 column we are entering these details but when client 
from GST time zone i am getting 21:41:39 GST


What is the type definition for the database column you are entering the 
data in?


Are you entering these by hand?

What happens if you use Asia/Dubai or Asia/Muscat as the time zone?



image.png

but which i insert into the databases is showing empty column.

On Fri, Jan 24, 2020 at 11:09 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 1/24/20 9:19 AM, Adrian Klaver wrote:
 > On 1/24/20 8:59 AM, nikhil raj wrote:
 >> Hi ALL,
 >>
 >> Required help with the time zone. i am getting error on the GST
time
 >> line. is tht a bug or postgres doesn't support the GST time zone.
 >>
 >>
 >> select CURRENT_TIMESTAMP AT TIME ZONE 'GST'
 >> ERROR: time zone "GST" not recognized SQL state: 22023
 >
 > Try GMT:
 >
 > test_(postgres)# select CURRENT_TIMESTAMP AT TIME ZONE 'GMT';
 >    timezone
 > 
 >   2020-01-24 17:17:52.754861

Aah, is this what you are looking for?:

https://24timezones.com/time-zone/gst

It seems to be an alias for UTC+04, though not recognized by IANA. That
would be:

Asia/Dubai or Asia/Muscat

Unless you are talking about:

https://24timezones.com/time-zone/gst_georgia

 >
 > For list of recognized abbreviations:
 >
 > https://www.postgresql.org/docs/11/view-pg-timezone-abbrevs.html
 >
 >>
 >>
 >> Postgres -:11.2
 >> Ubuntu:-18.04
 >>
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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