Postgres Pro
Downloads
Home   >   mailing lists

Thread: Archive# views

Archive# views

From
Satish K Biradar
Date:
Hello,

I am a newbee to Postgresql and I want to build replication between master
and slave servers(8.4). I was able to set it up but I cannot check archive#
at master and slave servers as what was the last archive# generated at
master and what is current archive# at Slave server that is being replayed.

Is there any view in PostgreSQL with which I can find the details of
Archive# generation at Master and archive# that is replaying currently or
last/recent archive# that has replayed at slave server.

So can you please provide me the commands as how to check the last archive#
is generated at master and slave(being replayed), lag between master and
slave servers.


Thanks & regards,
Satish K.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Archive-views-tp5805521.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Archive# views

From
Kapil Agarwal
Date:
Hi Satish,

You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.  


Master Server:

SELECT pg_current_xlog_location(); 


Slave server:

select pg_last_xlog_receive_location();

select now() - pg_last_xact_replay_timestamp();

Hope this help.



On Fri, May 30, 2014 at 11:57 AM, Satish K Biradar <satishbiradar1984@gmail.com> wrote:
Hello,

I am a newbee to Postgresql and I want to build replication between master
and slave servers(8.4). I was able to set it up but I cannot check archive#
at master and slave servers as what was the last archive# generated at
master and what is current archive# at Slave server that is being replayed.

Is there any view in PostgreSQL with which I can find the details of
Archive# generation at Master and archive# that is replaying currently or
last/recent archive# that has replayed at slave server.

So can you please provide me the commands as how to check the last archive#
is generated at master and slave(being replayed), lag between master and
slave servers.


Thanks & regards,
Satish K.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Archive-views-tp5805521.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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



--
 

Re: Archive# views

From
Ray Stell
Date:

On May 30, 2014, at 6:19 PM, Kapil Agarwal wrote:

Slave server:

select pg_last_xlog_receive_location();


not possible back in 8.4.  you could just compare the output of the pg_controldata command on both hosts.

Re: Archive# views

From
Satish K Biradar
Date:
Hi Kapil,

Thanks very much for your valuable time, explaining me in detail.

Could you please tell me whether these commands applicable to 8.4, what are
the versions that these commands are compatible. And one more thing would
you please provide me some information regarding hot standby (what are the
versions it is compatible, and useful views to check the status of hot
standby)

Appreciate your patience.

Thanks,
Satish K.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Archive-views-tp5805521p5805651.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Archive# views

From
Kapil Agarwal
Date:
Hi Satish,

SELECT pg_current_xlog_location(); command is applicable from 8.4 version and select pg_last_xlog_receive_location(); command is applicable from 9.0 version.

select now() - pg_last_xact_replay_timestamp(); command is applicable from 9.1 version.


Monitoring Streaming replication:

For monitoring purpose, you can use the pg_controldata command.  pg_controldata command will give you the information checkpoint lagging status of Standby Server. 

/usr/pgsql-9.1/bin/pg_controldata <primary_data_dir>
/usr/pgsql-9.1/bin/pg_controldata <standby_data_dir>

Above command will return the exact status of replication between servers with below details.

Latest checkpoint location:
Prior checkpoint location:
Latest checkpoint's REDO location:


As per definition of Hot Standby, its a method of redundancy in which primary and secondary (Backup Server) runs simultaneously. The data is mirrored to secondary so that both should contain identical data in real time. With this user, would be able to execute the Query against Database while secondary is in archive recovery mode.

This is what introduce in PG9.0. In-built Hot Standby.

You can use below link where you can refer about Hot Standby.

http://www.postgresql.org/docs/9.0/static/hot-standby.html




On Sat, May 31, 2014 at 3:34 PM, Satish K Biradar <satishbiradar1984@gmail.com> wrote:
Hi Kapil,

Thanks very much for your valuable time, explaining me in detail.

Could you please tell me whether these commands applicable to 8.4, what are
the versions that these commands are compatible. And one more thing would
you please provide me some information regarding hot standby (what are the
versions it is compatible, and useful views to check the status of hot
standby)

Appreciate your patience.

Thanks,
Satish K.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Archive-views-tp5805521p5805651.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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



--