Join Log in
Score:0
NOC_Ninja982134 avatar Server

Should replica DB be in 'recovery' - PostgreSQL

ru flag
NOC_Ninja982134

I have two databases that are using streaming replication and appear to be in this state

postgres 16319  0.0  0.5 137949952 3077260 ?   Ss   Aug22   0:11 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
postgres 16321  0.0  0.0 249564  2080 ?        Ss   Aug22   0:00  \_ postgres: logger
postgres 16322  7.3  5.2 137950296 27899272 ?  Ss   Aug22 9031:16  \_ postgres: startup   recovering 0000000100003EA70000002C
postgres 16323  0.8  5.2 137950152 27549964 ?  Ss   Aug22 1001:55  \_ postgres: checkpointer
postgres 16324  0.0  0.1 137949928 1050960 ?   Ss   Aug22   1:40  \_ postgres: background writer
postgres 16338  0.0  0.0 251960  2328 ?        Ss   Aug22  57:32  \_ postgres: stats collector
postgres 16339 10.6  0.0 137961464 5116 ?      Ss   Aug22 13123:06  \_ postgres: walreceiver   streaming 3EA7/2C5A24F0

I've normally only noticed the 'recovering' when the DB is in a bad state. When I ran a query on the replica I got

ERROR:  canceling statement due to conflict with recovery

I'm noticing that "streaming 3EA7/2C5A24F0" and the recovery is incrementing.

The SELECT * FROM pg_stat_wal_receiver also appears to be incrementing.

I just want to make sure there's no issues w\ my DB and it's just using recovery as part of it's replication mechinisim.

129
0 + 0
ua flag
Rick James
If this is only relevant to Postgres, toss other products from the tags.
1
Reply
Score:1
Nikita Kipriyanov avatar Server
za flag
Nikita Kipriyanov

If you set up Postgres replication by sending write-ahead log as described in streaming replication manual, then yes, all replicas are in the permanent recovery state which is normal. As write transactions are performed against the master node, the replicas receive and apply logs from the master, as any Postgres server in recovery would do.

The changing number you see is the currently received transaction ID.

Please read the complete linked page to clarify details. As for me, this is clear that the standby (slave) server actually performs recovery continuously unless promoted.

0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.