1

If I want to promote a logical replica in PostgreSQL, do I have to do any additional setup besides stopping the replication?

I think I can see is sequence values are not replicated so I have do something like pg_dump -d dbname -U username -t '*_id_seq' > seq.sql to dump the sequence and copy to the logical replica to be promoted. Is there a better way to do this?

Improve this question
4
  • 1
    I don't think that there is anything to do apart from updating the sequences, and I don't know a better way than yours.
    – Laurenz Albe
    Commented Jul 12 at 7:03
  • @LaurenzAlbe Thank you! I'm also thinking about running something similar to this gist in case the primary database is not available, would this work too: gist.github.com/bubiche/61da7917edc14d2d2d6f14bf1662c297. I just query for the maximum value of each sequence and set the value to max_val + 1
    – Nguyen Nguyen
    Commented Jul 12 at 11:32
  • That doesn't look good; it makes too many assumptions about the database. Perhaps it works for you. I'd just query pg_sequences and use nextval().
    – Laurenz Albe
    Commented Jul 12 at 11:52
  • @LaurenzAlbe thank you! TIL
    – Nguyen Nguyen
    Commented Jul 12 at 12:04

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.