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.

  • 1
    If this is only relevant to Postgres, toss other products from the tags.
    – Rick James
    Nov 16 '21 at 1:47

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.