search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on Patroni). Automating with Ansible.
https://postgresql-cluster.org
MIT License
1.83k stars 418 forks source link

How to backup from standalone to cluster with minimum RTO and RPO? #763

Closed Jamic28 closed 1 month ago

Jamic28 commented 2 months ago

I have a standalone postgresql database that runs on one cloud data center, Now I deployed postgesql cluster on another data center, and my task is to transfer the database to the cluster. How can I transfer a database from one standalone to another postgres cluster? What is the best way to do it: using logical or physical backup? Thanks!

vitabaks commented 2 months ago

with minimum RTO

It depends on the possible downtime, as well as the size of the database.

Take a look at this method, maybe it will suit you - https://github.com/shayonj/pg_easy_replicate

vitabaks commented 2 months ago

Another option is to use physical replication.

To do this, simply create a new cluster as a standby cluster for your current database by adding its address to the patroni_standby_cluster variable.

Then, to switch traffic, stop the source database and perform a promote for the standby cluster:

patronictl edit-config -s standby_cluster=null --force

[!note] At the moment, this option is suitable if you use the command line to deploy a cluster, since the UI has a given function (standby cluster) not added yet.