Question - How do I migrate a database from a standalone Postgresql server to a Repmgr cluster?

Question

Question - How do I migrate a database from a standalone Postgresql server to a Repmgr cluster?

Closed this issue 3 years ago · 4 comments

SergeyPotap commented 3 years ago

Good day.

I ask the developers to advise me. The question is this:

I have a standalone server with an up-to-date Zabbix database under Postgresql:

root@psvzabbix:/home/psv# hostname -f
psvzabbix.none

root@psvzabbix:/home/psv# psql -V
psql (PostgreSQL) 12.4 (Debian 12.4-1.pgdg100+1)

I have two Postgresql servers that are clustered under repmgr:

root@zabservtstnode1:/home/psv# hostname -f
zabservtstnode1.none

root@zabservtstpve:/home/psv# hostname -f
zabservtstpve.none

root@zabwitness:/home/psv# sudo -u postgres repmgr -f /etc/postgresql/12/main/repmgr.conf service status
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabwitness fallback_application_name=repmgr options=-csearch_path="
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabservtstnode1 fallback_application_name=repmgr options=-csearch_path="
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabservtstpve fallback_application_name=repmgr options=-csearch_path="
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabservtstnode1 fallback_application_name=repmgr options=-csearch_path="
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabwitness fallback_application_name=repmgr options=-csearch_path="
DEBUG: connecting to: "user=repmgr connect_timeout=3 dbname=repmgr host=zabservtstnode1 fallback_application_name=repmgr options=-csearch_path="
ID | Name | Role | Status | Upstream | repmgrd | PID | Paused? | Upstream last seen
----+-----------------+---------+-----------+-----------------+---------+-------+---------+--------------------
1 | zabservtstnode1 | primary | * running | | running | 427 | no | n/a
2 | zabservtstpve | standby | running | zabservtstnode1 | running | 1842 | no | 2 second(s) ago
5 | zabwitness | witness | * running | zabservtstnode1 | running | 20028 | no | 1 second(s) ago

root@zabservtstnode1:/home/psv# psql -V
psql (PostgreSQL) 13.1 (Debian 13.1-1.pgdg100+1)

root@zabservtstpve:/home/psv# psql -V
psql (PostgreSQL) 13.1 (Debian 13.1-1.pgdg100+1)

I need to transfer the database from the standalone server psvzabbix.none to the cluster: zabservtstnode1.none, zabservtstpve.none

I did this migration using the pg_dump routine 4 or 5 months ago.
But when restoring the dump to the cluster, I got a bunch of errors.
As a result, I had to completely demolish the database on the cluster and create it again and fill in the dump.

But for me this is unacceptable.
Please help me - can you tell me how to transfer the database without errors?

Answer 1 · 2021-05-13T01:53:36.000Z

Hi

This question is about transferring a database from one primary PostgreSQL instance to another. This kind of operation does not fall under the functionality provided by repmgr (which is a utility for supporting streaming replication within a PostgreSQL cluster).

Your options are:

resolve the issues encountered when using pg_dump
set up logical replication

Regards

Ian Barwick

Answer 2 · 2021-05-13T02:08:46.000Z

Good afternoon, Ian Barwick.
Thanks for the answer. Could you tell me what keys to the pg_dump procedure it is desirable to use? And can you also suggest whether you need to perform any actions in the cluster to correctly restore the saved database from the dump? For example, turn off the Standby node.
I would be very grateful for a hint.

Regards
Sergey

Answer 3 · 2021-05-13T02:17:05.000Z

Sorry, that's way outside the scope for repmgr support. It's not relevant whether repmgr is present or not for this kind of thing. I suggest you try the pgsql-general mailing list, or https://dba.stackexchange.com/ .

Answer 4 · 2021-05-13T02:21:27.000Z

Thanks for the answer, Ian Barwick.

Regards
Sergey