Thread: Reading WALs
On my built in logical replication I see that sometimes it tooks a long time to be updated with master. The question is how can I see what is being done on replica ? I know I can have a WAL reader, I tried WAL2JSON, it listen all data which comes from master, but as I understood when I use that plugin that data is discarded, so, not replicated. Is that correct ? Can I see what is coming to replica server and apply that change to server as usual ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/14/20 6:48 AM, PegoraroF10 wrote: > On my built in logical replication I see that sometimes it tooks a long time > to be updated with master. The question is how can I see what is being done > on replica ? I know I can have a WAL reader, I tried WAL2JSON, it listen all > data which comes from master, but as I understood when I use that plugin > that data is discarded, so, not replicated. > > Is that correct ? Can I see what is coming to replica server and apply that > change to server as usual ? See what is happening: https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-SUBSCRIPTION > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
pg_Stat_Subscription does not anwer what I want. This view just show if replication is being processed but not exactly what it´s doing. Is it replicating a huge table which an erroneous proess updated several times ? Replication process is recreating what index and that is spending how much time ? What WAL, table, operation is the one being processed ? So I would like to have a listener on replica server just to see all WAL info is coming, just that. Then I can go back to production and see what´s could run better, if application, triggers and so on. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 3/14/20 10:11 AM, PegoraroF10 wrote: > pg_Stat_Subscription does not anwer what I want. This view just show if > replication is being processed but not exactly what it´s doing. > Is it replicating a huge table which an erroneous proess updated several > times ? > Replication process is recreating what index and that is spending how much > time ? > What WAL, table, operation is the one being processed ? > > So I would like to have a listener on replica server just to see all WAL > info is coming, just that. Any of the below helpful?: https://www.postgresql.org/docs/12/logicaldecoding-example.html > Then I can go back to production and see what´s could run better, if > application, triggers and so on. > > > > -- > Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
On 2020-03-14 14:48, PegoraroF10 wrote: > On my built in logical replication I see that sometimes it tooks a long time > to be updated with master. The question is how can I see what is being done > on replica ? I know I can have a WAL reader, I tried WAL2JSON, it listen all > data which comes from master, but as I understood when I use that plugin > that data is discarded, so, not replicated. > > Is that correct ? Can I see what is coming to replica server and apply that > change to server as usual ? You can have multiple logical decoding streams in parallel that track their position independently of each other. So you can have a logical replication stream and wal2json next to each other, and they won't interfere with each other. So what you are considering is possible and safe. (I don't know whether it will give you satisfactory insights.) -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services