Question - How do I migrate a database from a standalone Postgresql server to a Repmgr cluster?
Closed this issue · 4 comments
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)
root@psvzabbix:/home/psv# su - postgres
postgres@psvzabbix:~$ psql -l
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа
-----------+----------+-----------+------------+----------+-----------------------
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
zabbix | zabbix | UTF8 | C | C |
(4 строки)
- 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)
root@zabservtstnode1:/home/psv# sudo -u postgres psql -l
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа
-----------+----------+-----------+------------+----------+-----------------------
postgres | postgres | SQL_ASCII | C | C |
repmgr | repmgr | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
zabbix | zabbix | UTF8 | C | C |
(5 строк)
root@zabservtstpve:/home/psv# sudo -u postgres psql -l
Список баз данных
Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа
-----------+----------+-----------+------------+----------+-----------------------
postgres | postgres | SQL_ASCII | C | C |
repmgr | repmgr | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
zabbix | zabbix | UTF8 | C | C |
(5 строк)
- 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?
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
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
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/ .
Thanks for the answer, Ian Barwick.
Regards
Sergey