Re: SQL command in Slave Database - Monitor Replication

Поиск
Список
Период
Сортировка
От Matheus de Oliveira
Тема Re: SQL command in Slave Database - Monitor Replication
Дата
Msg-id CAJghg4+MzvvnKo0LWRqO6CE_FxXs=VWGbCREmLkpLUvnCDxiAg@mail.gmail.com
обсуждение исходный текст
Ответ на SQL command in Slave Database - Monitor Replication  (Alberto Olivares <alberto.olivares@snowflakesoftware.com>)
Ответы Re: SQL command in Slave Database - Monitor Replication
Список pgsql-admin
Дерево обсуждения

On Fri, Jul 3, 2015 at 12:03 PM, Alberto Olivares <alberto.olivares@snowflakesoftware.com> wrote:
Is there any SQL command that I can run on the slave database to check if the replication is still working or not? I know that you can check the postgresql.conf but I want to do it directly on the SQL panel.

Database:  Postgresql 9.1 on Linux

You can query pg_stat_replication (since 9.1+) on the primary server, it will return one row for each secondary connected.

If you were on 9.2+ you could even use pg_xlog_location_diff to get the lag in bytes of data sent to the standby:

    SELECT
        application_name,
        client_addr,
        pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), sent_location)) AS sent_lag
    FROM pg_stat_replication;

You can use write_location and replay_location similarly.

On 9.1 you'll have to do this math by yourself if you want or create your own pg_xlog_location_diff.

OBS: pg_stat_replication will only show standby connected through streaming replication, log shipping cannot be verified this way.

Best regards,
--
Matheus de Oliveira

В списке pgsql-admin по дате отправления: