Thread: Hot Recovery not working

Hot Recovery not working

From
sreynolds
Date:
We have a network tunnel setup between 2 sites.  I have PostgreSQL 9.4
installed on computers on both networks. I am attempted to setup a hot
standby between the 2.  From Site 1 I can establish a connection to Site 2
via the pgAdmin tool New Server Registration button.  But I cannot establish
a connection from Site2 to Site1 in the same manner. I get an error "Server
doesn't listen.... could not connect to server: Connection refused
(0x0000274D/10061) is the server running on host "x.x.x.x" and accepting
TCP/IP connections on port 5432".

I can ping each machine from the other. I've also run tracert and get a
successful connection so I don't think it is a networking issue.  I've also
performed netstat on each to make sure servers is listening for TCP
connection on port 5432 and they both are.

I have identical entries in the pg_hba.conf and postgresql.conf files
(except for IP address - each file lists the other servers IP) - set
according to the following steps I performed- from an article I found online
(https://www.openmakesoftware.com/insights/postgresql-hot-standby-for-windows/
).


1. On Master – In pgAdmin run:
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword';
2. On Master – In pgAdmin, edit the postgresql.conf and set the following:
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
3. On Master – In pgAdmin, edit the pg_hba.conf and set the following where
5.6.7.8 is the ip address of the slave:
host replication     replicator     5.6.7.8/32           trust
4. On Master – restart the postgres windows service
5. On Slave – stop the postgres windows service
6. On Slave – open a command prompt and cd to your Postgres directory
(c:\program files\Postgresql\9.4) and run:
rd /s “c:\program files\Postgresql\9.4\data”
7. On Slave – open a command prompt and cd to your Postgres directory
(c:\program files\Postgresql\9.4) and run where 1.2.3.4 is the ip of the
Master:
pg_basebackup -h 1.2.3.4 -D “c:\program files\Postgresql\9.4\data” -U
replicator -v –P –X stream
8. On slave – In pgAdmin, edit the postgresql.conf and set the following:
hot_standby = on
9. On slave – create a text file (c:\program
files\Postgresql\9.4\data\recovery.conf) with the following contents where
1.2.3.4 is the ip of the Master:
standby_mode = 'on'primary_conninfo = 'host=1.2.3.4 port=5432
user=replicator password=mypassword'trigger_file =
'c:\temp\postgresql.trigger'
10. On Slave – start the postgres windows service



--
View this message in context: http://postgresql.nabble.com/Hot-Recovery-not-working-tp5892637.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.



Re: Hot Recovery not working

From
"Anderson, Derek"
Date:
You may want to check your firewall rules.  A misconfigured switch/firewall could allow you to ping both ways just fine
andcommunicate one way.
 

-----Original Message-----
From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of sreynolds
Sent: Monday, March 14, 2016 7:23 AM
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Hot Recovery not working

We have a network tunnel setup between 2 sites.  I have PostgreSQL 9.4 installed on computers on both networks. I am
attemptedto setup a hot standby between the 2.  From Site 1 I can establish a connection to Site 2 via the pgAdmin tool
NewServer Registration button.  But I cannot establish a connection from Site2 to Site1 in the same manner. I get an
error"Server doesn't listen.... could not connect to server: Connection refused
 
(0x0000274D/10061) is the server running on host "x.x.x.x" and accepting TCP/IP connections on port 5432".

I can ping each machine from the other. I've also run tracert and get a successful connection so I don't think it is a
networkingissue.  I've also performed netstat on each to make sure servers is listening for TCP connection on port 5432
andthey both are.
 

I have identical entries in the pg_hba.conf and postgresql.conf files (except for IP address - each file lists the
otherservers IP) - set according to the following steps I performed- from an article I found online
(https://www.openmakesoftware.com/insights/postgresql-hot-standby-for-windows/
).


1. On Master – In pgAdmin run:
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword'; 2. On Master – In pgAdmin, edit the
postgresql.confand set the following:
 
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
3. On Master – In pgAdmin, edit the pg_hba.conf and set the following where
5.6.7.8 is the ip address of the slave:
host replication     replicator     5.6.7.8/32           trust
4. On Master – restart the postgres windows service 5. On Slave – stop the postgres windows service 6. On Slave – open
acommand prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run:
 
rd /s “c:\program files\Postgresql\9.4\data”
7. On Slave – open a command prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run where
1.2.3.4is the ip of the
 
Master:
pg_basebackup -h 1.2.3.4 -D “c:\program files\Postgresql\9.4\data” -U replicator -v –P –X stream 8. On slave – In
pgAdmin,edit the postgresql.conf and set the following:
 
hot_standby = on
9. On slave – create a text file (c:\program
files\Postgresql\9.4\data\recovery.conf) with the following contents where
1.2.3.4 is the ip of the Master:
standby_mode = 'on'primary_conninfo = 'host=1.2.3.4 port=5432 user=replicator password=mypassword'trigger_file =
'c:\temp\postgresql.trigger'
10. On Slave – start the postgres windows service



--
View this message in context: http://postgresql.nabble.com/Hot-Recovery-not-working-tp5892637.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

Re: Hot Recovery not working

From
"Reynolds, Selwyn"
Date:
I will talk to my network guy. We pass SQLServer data between the 2 so would think the rules are OK but I will verify.

Thank you.
________________________________________
From: Anderson, Derek [Derek.Anderson@mspmac.org]
Sent: Monday, March 14, 2016 12:46 PM
To: Reynolds, Selwyn; pgadmin-support@postgresql.org
Subject: RE: [pgadmin-support] Hot Recovery not working

You may want to check your firewall rules.  A misconfigured switch/firewall could allow you to ping both ways just fine
andcommunicate one way. 

-----Original Message-----
From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of sreynolds
Sent: Monday, March 14, 2016 7:23 AM
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Hot Recovery not working

We have a network tunnel setup between 2 sites.  I have PostgreSQL 9.4 installed on computers on both networks. I am
attemptedto setup a hot standby between the 2.  From Site 1 I can establish a connection to Site 2 via the pgAdmin tool
NewServer Registration button.  But I cannot establish a connection from Site2 to Site1 in the same manner. I get an
error"Server doesn't listen.... could not connect to server: Connection refused 
(0x0000274D/10061) is the server running on host "x.x.x.x" and accepting TCP/IP connections on port 5432".

I can ping each machine from the other. I've also run tracert and get a successful connection so I don't think it is a
networkingissue.  I've also performed netstat on each to make sure servers is listening for TCP connection on port 5432
andthey both are. 

I have identical entries in the pg_hba.conf and postgresql.conf files (except for IP address - each file lists the
otherservers IP) - set according to the following steps I performed- from an article I found online
(https://www.openmakesoftware.com/insights/postgresql-hot-standby-for-windows/
).


1. On Master – In pgAdmin run:
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword'; 2. On Master – In pgAdmin, edit the
postgresql.confand set the following: 
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
3. On Master – In pgAdmin, edit the pg_hba.conf and set the following where
5.6.7.8 is the ip address of the slave:
host replication     replicator     5.6.7.8/32           trust
4. On Master – restart the postgres windows service 5. On Slave – stop the postgres windows service 6. On Slave – open
acommand prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run: 
rd /s “c:\program files\Postgresql\9.4\data”
7. On Slave – open a command prompt and cd to your Postgres directory (c:\program files\Postgresql\9.4) and run where
1.2.3.4is the ip of the 
Master:
pg_basebackup -h 1.2.3.4 -D “c:\program files\Postgresql\9.4\data” -U replicator -v –P –X stream 8. On slave – In
pgAdmin,edit the postgresql.conf and set the following: 
hot_standby = on
9. On slave – create a text file (c:\program
files\Postgresql\9.4\data\recovery.conf) with the following contents where
1.2.3.4 is the ip of the Master:
standby_mode = 'on'primary_conninfo = 'host=1.2.3.4 port=5432 user=replicator password=mypassword'trigger_file =
'c:\temp\postgresql.trigger'
10. On Slave – start the postgres windows service



--
View this message in context: http://postgresql.nabble.com/Hot-Recovery-not-working-tp5892637.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.


--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support