Thread: Is my standby fully connected?
I just setup my databases and got this LOG:
LOG: database system was shut down in recovery at 2015-06-01 13:31:21 EDT
LOG: entering standby mode
LOG: redo starts at 0/67000278
LOG: consistent recovery state reached at 0/67000350
LOG: record with zero length at 0/67000350
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/67000000 on timeline 1
And it always stays like this, i never get a message like:
LOG: streaming replication successfully connected to primary
Additionally, i cannot run queries such as:
psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10
...to calculate replication lag, I get the following message:
ERROR: recovery is in progress
HINT: WAL control functions cannot be executed during recovery
Can someone please tell me what is going on?
Hi,
2015-06-01 19:57 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:
I just setup my databases and got this LOG:LOG: database system was shut down in recovery at 2015-06-01 13:31:21 EDTLOG: entering standby modeLOG: redo starts at 0/67000278LOG: consistent recovery state reached at 0/67000350LOG: record with zero length at 0/67000350LOG: database system is ready to accept read only connectionsLOG: started streaming WAL from primary at 0/67000000 on timeline 1
Sounds good to me.
And it always stays like this, i never get a message like:LOG: streaming replication successfully connected to primaryAdditionally, i cannot run queries such as:psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10...to calculate replication lag, I get the following message:ERROR: recovery is in progressHINT: WAL control functions cannot be executed during recoveryCan someone please tell me what is going on?
This is just what it's supposed to be. And it tells you exactly that. You cannot use pg_current_xlog_location() on a slave.
Check pg_stat_replication view on the master.
Please, keep the list posted.
2015-06-01 20:09 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:
2015-06-01 20:09 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:
I get 1 row, a walreceiver and my slave IP,
Then all is good.
i was expecting something like:pg_current_xlog_location --------------------------0/2000000 (1 row)When I ran -> psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)
Well, you can't execute it on a slave. You can use pg_last_xlog_receive_location() and pg_last_xlog_replay_location() on a slave.
On Mon, Jun 1, 2015 at 2:04 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:Hi,2015-06-01 19:57 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:I just setup my databases and got this LOG:LOG: database system was shut down in recovery at 2015-06-01 13:31:21 EDTLOG: entering standby modeLOG: redo starts at 0/67000278LOG: consistent recovery state reached at 0/67000350LOG: record with zero length at 0/67000350LOG: database system is ready to accept read only connectionsLOG: started streaming WAL from primary at 0/67000000 on timeline 1Sounds good to me.
And it always stays like this, i never get a message like:LOG: streaming replication successfully connected to primaryAdditionally, i cannot run queries such as:psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10...to calculate replication lag, I get the following message:ERROR: recovery is in progressHINT: WAL control functions cannot be executed during recoveryCan someone please tell me what is going on?This is just what it's supposed to be. And it tells you exactly that. You cannot use pg_current_xlog_location() on a slave.Check pg_stat_replication view on the master.
--
In 9.1+ you can monitor the state of your slave easily with the standby_state field of pg_stat_replication:
SELECT standby_pid,
standby_usesysid,
standby_usename,
standby_client_addr,
standby_client_port,
standby_state
FROM pg_stat_replication;
If the standby is not connected at all the query will not return any result for that standby.
If the standby is connected possible values of standby_state are streaming (ideal state), catchup (standby has fallen behind), startup, backup.
Will J. Dunn
On Mon, Jun 1, 2015 at 2:13 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
Please, keep the list posted.
2015-06-01 20:09 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:I get 1 row, a walreceiver and my slave IP,Then all is good.
i was expecting something like:pg_current_xlog_location --------------------------0/2000000 (1 row)When I ran -> psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)Well, you can't execute it on a slave. You can use pg_last_xlog_receive_location() and pg_last_xlog_replay_location() on a slave.
On Mon, Jun 1, 2015 at 2:04 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:Hi,2015-06-01 19:57 GMT+02:00 Ivann Ruiz <ivann.jprog@gmail.com>:I just setup my databases and got this LOG:LOG: database system was shut down in recovery at 2015-06-01 13:31:21 EDTLOG: entering standby modeLOG: redo starts at 0/67000278LOG: consistent recovery state reached at 0/67000350LOG: record with zero length at 0/67000350LOG: database system is ready to accept read only connectionsLOG: started streaming WAL from primary at 0/67000000 on timeline 1Sounds good to me.
And it always stays like this, i never get a message like:LOG: streaming replication successfully connected to primaryAdditionally, i cannot run queries such as:psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10...to calculate replication lag, I get the following message:ERROR: recovery is in progressHINT: WAL control functions cannot be executed during recoveryCan someone please tell me what is going on?This is just what it's supposed to be. And it tells you exactly that. You cannot use pg_current_xlog_location() on a slave.Check pg_stat_replication view on the master.