How to migrate one postgres database to another cluster?

P

pqgg7nwkd42019-03-26 12:19:16

PostgreSQL

pqgg7nwkd4, 2019-03-26 12:19:16

If the server with postgresql. It has many, many databases, one per client. And one day some of them will need to be moved and I want to know in advance what I will have to face. On the Internet, I met only a method that offers to make and restore a dump. Are there other, perhaps faster, options?

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

M

Melkij, 2019-03-26
@melkij

pg_dump + pg_restore is just the fastest, if by "fast" you mean the total time spent on the task and the minimum problems, and not the minimum orphaned write window.
In other cases, you need some kind of logical replication to transfer the database. Trigger or something based on logical decoding (for example, logical replication in pg10+). A rake with any data schema changes in the kit
Edge case: if one or more databases need to be evicted to a new cluster. Then you make a streaming replica, when you catch up, you restart to the master and delete unnecessary bases from it.