Re: Connection error - Mailing list pgsql-general

WA Pennant & Flag Displays - Darren wrote:
> Hi Richard,
>
> Thanks for your advice.  I implemented your tests as shown below and they
> seem to indicate there's a firewall problem (but the firewall is off).  I
> found though that I can probably retrieve the data and have discussed what
> I've been trying at the bottom of the email.  Could you please let me know
> your thoughts on what I have been doing wrong or should do in order to
> retrieve the data.

>>>Can you connect with psql locally?
>>>No
>>>Can you connect with psql via localhost?
>>>No
>>
>>Hmm - that makes the firewall options slightly less likely.
>>
>>1. Make sure PG is listening on port 5432 and that log_connections is
>>set in your postgresql.conf.
>
> Under 'Connections and Authentication' postgresql.conf displays:
> listen_addresses = '*'
> port = 5432
> I've now also added:
> log_connections = yes

Good. I take it you're not seeing any connections.

> Then check your pg_hba.conf allows
>
>>connections from localhost and your local network. Restart PG.
>
> pg_hba.conf shows (does this cover the localhost?):
> local all all all trust

On a unix machine it covers connection over unix-sockets. Not sure about
Windows.

> # IPv4 local connections:
> host    all         all         127.0.0.1/32          md5
> # IPv6 local connections:
> #host    all         all         ::1/128               md5
> host   all      all     192.168.1.230/32     md5
> host   all      all     192.168.1.240/32     md5
> host   all      all     192.168.1.210/32     md5
> host   all      all     192.168.1.211/32     md5
> host   all      all     192.168.1.220/32     md5
> host   all      remote     all  md5
> hostnossl all all 127.0.0.1/32 md5
>
>
>>2. Try telnetting to localhost, port 5432 (from the command-line in
>>Windows it should be "telnet localhost:5432" I think). If it connects,
>>hit return a couple of times and you should be disconnected.
>>PostgreSQL's logs should say something about an invalid startup packet.
>>
>>If it doesn't even connect, then suspect a firewall.
>
> Connection failed (even with Microsoft's firewall off)

Well, if you're not getting *anything* then either:
1. There is nothing on that port
2. There is some sort of firewall interfering.

> I did a backup on 5th July and this incident happened after the 19th July.
> I've found http://www.postgresql.org/docs/8.0/interactive/backup-online.html
> which seems to indicate that my data is retrievable, however I'm doing
> something wrong.  I have a new installation of postgres on another compter
> and have restored the data of the 5th July.  I have used the sample
> recovery.conf and the two pertinent lines of code together with relevant
> comments are below.

Well, there's no evidence your postgresql backend is having problems.
This means recovery is simple.

1. Make sure both the old and new machine have the same version of
PostgreSQL installed (i.e. both 8.0.x)
2. Stop Postgresql on the machine you can't connect to.
3. Zip up the entire contents of the "data" directory - you want all of
it pg_clog/xlog - all of it.
4. Unzip it to the right location on the new machine
5. Restart PG on the new machine

Done - the new machine might go through some recovery, depending whether
the backend on the old machine was shut down cleanly.

NOTE - you can't necessarily move data this way between different
architectures (e.g. x86 <=> PPC <=> 64-bit x86), nor between different
releases (e.g. 8.0.x => 8.1.x).

--
   Richard Huxton
   Archonet Ltd