Re: Connection issue

[Ricardo Martin Gomez]

Hi,
Can you read the event viewer?

Obtener Outlook para Android<https://aka.ms/ghei36>


From: Adrian Klaver 
Sent: Wednesday, February 6, 2019 1:43:24 PM
To: Maximilian Tyrtania
Cc: Ricardo Martin Gomez; pgsql-general@lists.postgresql.org
Subject: Re: Connection issue

On 2/6/19 8:30 AM, Maximilian Tyrtania wrote:
> Hi again,
>
>
>> On 6. Feb 2019, at 17:19, Adrian Klaver  wrote:
>>
>> On 2/6/19 7:18 AM, Maximilian Tyrtania wrote:
>>> Well, the problem being a Parallels issue is just a wild theory of mine, it 
>>> could well be something else. Honstly I don't recall updating anything in 
>>> that area. As Mr. Gomez suggested I tried to
>>
>> So from a previous post:
>>
>>> Have you recently updated any of the involved software?
>>
>> Well, sure, but after I saw I couldn't connect ...
>>
>> So what happened in the interval between the time you could connect and the 
>> time you could not?
>
> I did update parts of my app (including the plugin which in turn encapsulates 
> libpq). I did not update my parallels installation, as far as I remember. 
> Sorry if I was unclear about that.

What is the plugin?

Can you roll back the plugin upgrade to see if that is the problem?


>
>>> ping - worked
>>> telnet - did not work (infact I couldn't telnet anywhere)
>>
>> In my experience telnet is generally disabled these days.
>
> I had to enable it on my Windows box, if that's what you mean. The "telnet" 
> command works by itself, but I didnt receive anything when trying to telnet 
> to some other machine.
>

Probably because Telnet is not enabled on the server as it is not a
secure protocol:
https://en.wikipedia.org/wiki/Telnet

>
> Ah, thanks for digging that up. Hmm, sounds as if something is definetly 
> wrong on my windows box.
>
> I'll try contacting the Parallels support guys.
>
> Thanks again,
>
> Max
>


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

RE: Connection issue

[Ricardo Martin Gomez]

Hi, Can you do some tests in both (Mac and Windows ) ?

  *   telnet {IP_Server} {5432}
  *   ping {IP_Server}
  *   tracert {IP_Server}

Regards
__
Saludos
Ing. Ricardo Martín Gomez
DBA - SysAdmin

De: Adrian Klaver 
Enviado: martes, 5 de febrero de 2019 12:44
Para: Maximilian Tyrtania
Cc: pgsql-general@lists.postgresql.org
Asunto: Re: Connection issue

On 2/5/19 7:18 AM, Maximilian Tyrtania wrote:
> Hi,
>
>> On 5. Feb 2019, at 15:57, Adrian Klaver  wrote:
>>
>> On 2/5/19 1:48 AM, Maximilian Tyrtania wrote:
>>> For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) 
>>> from my Windows installation (run via Parallels on my Mac), no matter what 
>>> app I use (tried psql, PGAdmin and my own app).
>>
>> Has it ever connected?
>
> Yes, it connected without issues last week.
>
>> Have you recently updated any of the involved software?
>
> Well, sure, but after I saw I couldn't connect I downloaded the current 
> version of PGAdmin and psql and couldn't connect with them either.
>
>> More below.
>>
>>> When trying to connect from PGAdmin 4 the server logs say:
>>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG:  0: 
>>> Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de 
>>> Port=50262
>>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:  
>>> BackendInitialize, postmaster.c:4249
>>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG:  08006: 
>>> konnte Daten vom Client nicht empfangen: Connection reset by peer
>>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT:  
>>> pq_recvbuf, pqcomm.c:978
>>> ("konnte Daten vom Client nicht empfangen" means "couldn't receive data 
>>> from client")
>>> PGAdmin says the usual " Unable to connect to server:..."
>>> No issue connecting from the Mac.
>>> I see in the logfile that other windows users did connect successfully from 
>>> their windows boxes so I am ready to believe that maybe my Parallels 
>>> somehow disconnects the connection quickly or something.
>>> Another strange thing is that if I try to connect from my Xojo app (which 
>>> uses libpq internally) it says:SSL error: unknown protocol. I had no issue 
>>> connecting last week from that installation so I am at my wits end...
>>
>> Where is the Xojo app running?
>>
>> Is the pg_hba.conf on the server set up to allow connections from the client 
>> machines?
>
> Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a 
> pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac 
> with the same credentials. I wonder if this is maybe some Parallels issue.

Well if you could connect from Parallels last week, you upgraded it and
now you cannot connect then I would suspect it. This assumes that the
Xojo app is also running in the Parallels instance. Given the SSL error
you might see if they changed anything in that area.

>
> Thanks,
> Max
>


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

Re: insert into: NULL in date column

[Ricardo Martin Gomez]

Hi,
In MYSQL Null date is equal '01/01/1900' or '01/01/1970', I don't remember but 
you can also use the same logic for the check_constraint.
Regards

Obtener Outlook para Android


From: Rich Shepard 
Sent: Saturday, January 12, 2019 1:54:47 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: insert into: NULL in date column

On Sat, 12 Jan 2019, David G. Johnston wrote:

> NULL isn't the problem - a check constraint can resolve to unknown in
> which case it behaves the same as if it resolved as true (i.e., its
> basically a  IS NOT FALSE test in the backend). This is actually a
> nice feature of check constraints since for nullable columns you don't
> have to write "col IS NULL OR "

David,

   Thanks for correcting me.

> The problem is that check constraints are only applied at time of data
> change. If you insert a record whose date is 3 days from now the check
> constraint passes today and (in theory) for the next couple of days. After
> which the constraint fails - but you are INFORMED ONLY IF THE RECORD IS
> INSERTED AGAIN. So basically you will not see a problem until you attempt
> to restore your data on some future date and much of your data fails to
> restore because those dates are no longer in the future.

   I thought that the check constraint applied at data entry, too. If not,
then I'll have either wxPython or SQLAlchemy ensure that the next_contact
date is later than the contact date.

> If you want to check for a future date you should probably also store the
> date you are comparing against and have the check constraint reference
> both fields.

   The contact date is always entered in a new row, but the next_contact date
might not be if there's nothing scheduled.

Best regards,

Rich

Re: insert into: NULL in date column

[Ricardo Martin Gomez]

Hi, one question.
Do you put explicit "NULL" in the column value?
Other option is.
You have to delete the column "next_contact" in your INSERT clause.
So, if the column has a default value, this value Will be inserted. Else Null 
value will be inserted.

Regards

Obtener Outlook para Android

De: Adrian Klaver
Enviado: viernes, 11 de enero 22:09
Asunto: Re: insert into: NULL in date column
Para: Rich Shepard, pgsql-general@lists.postgresql.org


On 1/11/19 4:00 PM, Rich Shepard wrote: > On Fri, 11 Jan 2019, Ken Tanzer 
wrote: > >> I think the problem is actually that you're trying to represent 
your NULL >> dates with '', which PG doesn't like. > > Ken, > >   That's 
certainly how I saw the error message. > >> cat test.csv >> >> 
my_text,my_date,my_int >> 'Some Text','1/1/18',3 >> 'More Text,,2 >> 
'Enough','',1 >> >> CREATE TEMP TABLE my_test (my_text TEXT, my_date DATE, 
my_int INT); >> >> \copy my_test FROM test.csv WITH CSV HEADER >> >> ERROR:  
invalid input syntax for type date: "''" >> CONTEXT:  COPY my_test, line 4, 
column my_date: "''" >> >> >> You'll note that it breaks on the last line, 
which is like yours, not >> the one before it. > >   Huh! I'll leave off the 
quote marks and see if that makes a > difference ... > tomorrow morning. Since 
dates are treated as strings I thought their > absence > also needed the 
quotes. Stay tuned to this mail list for test results. Dates are not treated as 
strings they are treated as dates. There is built casting for strings that are 
valid dates though: create table date_test(dt_fld date); insert into date_test 
values('01/11/19'); INSERT 0 1 --Trying MySQL 'null':) insert into date_test 
values('00/00/'); ERROR: date/time field value out of range: "00/00/" 
LINE 1: insert into date_test values('00/00/'); insert into date_test 
values(''); ERROR: invalid input syntax for type date: "" LINE 1: insert into 
date_test values(''); As pointed out you are being told '' is not a valid date. 
> > Thanks, > > Rich > > -- Adrian Klaver adrian.kla...@aklaver.com

Re: Can a broken Postgresql data directory be reconstructed without losing data?

[Ricardo Martin Gomez]

You must to restore a database from a backup.

Regards.

Obtener Outlook para Android


From: A A 
Sent: Sunday, April 15, 2018 5:25:05 AM
To: pgsql-general@lists.postgresql.org
Subject: Can a broken Postgresql data directory be reconstructed without losing 
data?


I'm trying to restore a database from a broken data directory that have lost 
many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql 
version and then copy (with overwrite) the broken datadir to the new one. here 
the remaining datadir that I'm working on :

   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status

Re: hardcode password in connect string

[Ricardo Martin Gomez]

Hello David,
I think so, the parameter you need in psql command is -w mipassword.
But, you have to understand that use this form or set PGPASSWORD aren't safer.
Exist a file (pg_hba.conf) for this process ir task.
Regards

Obtener Outlook para Android


From: David Gauthier 
Sent: Friday, April 13, 2018 3:51:33 PM
To: James Keener
Cc: pgsql-generallists.postgresql.org
Subject: Re: hardcode password in connect string

PGPASSWORD env var works fine.
Thanks !


On Fri, Apr 13, 2018 at 2:46 PM, James Keener 
> wrote:
Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html

Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html

Jim


On April 13, 2018 2:43:01 PM EDT, David Gauthier 
> wrote:
Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't like to enter 
passwords.  I used to just disable passwords (working behind a firewall), but 
this one is different.  I see nothing in the interactive connect string where I 
can enter the password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded passwords 
are supported in perl/dbi, pg driver

Thanks !

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.