Как сделать одну базу данных из другой по шаблону?

Question

[Анастасия]

Есть БД-1, она доступна по SSH туннелю.
Нужно создать БД-2 по шаблону:
Берем из БД-1 из таблиц items и actions некоторые колонки и создаем из этого таблицу things в БД-2 с этими колонками.
Т.е. берем какие-то данные из БД-1 и делаем из этого БД-2.
Как правильно это сделать? Что использовать?

Comments

[Виктор]

написать скрипт или сделать дампы БД1 в csv, отредактировать и залить в БД2

[Анастасия]

Виктор, а если БД весит сотни ГБ?

[Ипатьев]

Значит вариант "сделать дамп" становится менее желательным.

[Виктор]

Анастасия, написать скрипт. Использовать очередь, класть в очередь id из БД1, таким образом можно сделать порционный перенос и очередь будет гарантировать консистентность данных

[Ипатьев]

для mysqldump можно указать запрос, которым будут выбираться данные для дампа
насколько я вижу, для pg_dump нельзя, но гуглятся варианты

использовать psql понятное дело

[Ипатьев]

кстати, а какая БД-то? Нельзя ли определиться?

[Анастасия]

Ипатьев, БД-1 mySQL, БД-2 postgresql

[Ипатьев]

OMFG, а в вопросе это явно написать не судьба?

[Ипатьев]

И заодно написать, нужна ли будет после этого синхронизация. Поскольку тут уже писателей набежало решать проблему, которая не ставилась.

[Ипатьев]

Алексей Уколов вы бы хоть почитали комментарии. По-хорошему этот мусор надо целиком удалить, а не теги править. Потому что в таком виде к проблемам автора вопрос вообще уже никакого отношения не имеет, а половина ответов становятся бессмысленными, поскольку написаны про другую БД.

Answer 1

[Лариса .•º]

1. Создание клона БД из template0/template1, имеет ряд ограничений ( подключенные сеансы, данные БД): Template Databases
2. Есть способ подписки на wal, используя к примеру pg_sync, но для этого на мастере должна быть включена репликация
3. Размер БД <->доступный размер диска
4. Нагрузка в момент копирования
5. Как мне кажется, самый безопасный вариант, разделить задачу на две:
- сделать бэкап на другой сервер dump bd for large database
- и уже на новой БД, update данных: где, так же, присутствуют свои практики, как быстро - это сделать
- переиндексация данных ( возможно )

Comments

[Ипатьев]

Прекрасный поток сознания, ChatGPT тихо плачет в уголке от зависти

Answer 2

[Daemon23RUS]

Хоть бы указали какая база, предположим MySQL тогда в локальной базе делаем MySQL Federated и работаем далее запросами так если бы это было на одном компе

CREATE TABLE federated_table (... структура...) ENGINE=FEDERATED DEFAULT CHARSET=latin1 
      CONNECTION='mysql://root@localhost:9306/federated/test_table';

INSERT INTO table1 (Column1, Column2)
SELECT column1, column2 FROM federated_table;

Ессно надо через SSH пробросить порт удаленной базы и так чтобы не пересекался с локальной и все это указать в строке подключения. И при создании федеративных таблиц структура должна совпадать, ее надо запросить с удаленного сервера
P.S. В комментариях выяснилось что база с основными данными MySQL а создать надо PostgreSQL
Похожий механизм есть и там, называется Foreign Data Wrapper (FDW). Не на много сложнее, чем описано мной выше (установить расширение, создать сервер, сопоставить пользователя и импортировать схемы). И дальше работать в локальной базе с запросами, а данные потянутся с удаленного сервера.

Comments

[Ипатьев]

Так в вопросе написано, что структура НЕ должна совпадать.
Вы его вообще читали?

[Daemon23RUS]

Ипатьев,

Так в вопросе написано, что структура НЕ должна совпадать.
Вы его вообще читали?

Так а что ТС мешает в SELECT сделать нужную выборку, думали ?

[Ипатьев]

В вопросе написано "создать БД", а не "сделать выборку"
Впрочем я вижу, что задавать наводящие вопросы здесь бессмысленно.

[Daemon23RUS]

Ипатьев, У ТС основная проблема, в том, что БД с данными удаленная, решив эту проблему, создание базы( с какой структурой ?) и заполнение ее данными выборочными колонками из 2х таблиц - мелочи. Еще вангую, что базы окажутся разными.

[Ипатьев]

Daemon23RUS, ванговать не надо это уже выяснилось в комментариях
К вопросу о том, следует ли кидаться строчить ответ, не выяснив все детали вопроса.

[Анастасия]

Daemon23RUS, по деталям
БД-1 - MySQL - доступна по SSH(т.е. подключаемся к серверу сначала по ssh, а затем к бд оттуда),
БД-2- PostgreSQL,

Выборку тоже нужно сделать и ИНСЕРТИТЬ в новую дб.

Вот пример:
Берем из БД-1 из таблиц items и actions некоторые колонки(допустим 4 колонки) и создаем из этого таблицу things в БД-2 с этими колонками + инсертим данные из этих колонок в новую таблицу

[Daemon23RUS]

Ипатьев,

К вопросу о том, следует ли кидаться строчить ответ, не выяснив все детали вопроса.

Стесняюсь спросить, технологии MySQL Federated и Foreign Data Wrapper (FDW) не решают стоящей перед ТС задачи ?

[Ипатьев]

Стесняюсь ответить что это даже не из пушки по воробьям, а атомной бомбой по комарам.
причем без малейшего объяснения - ЗАЧЕМ.

[Daemon23RUS]

Анастасия, Вам в PostgreSQL надо сделать так, чтобы выполнялся select в удаленной БД, я примерно набросаю, догуглите детали:

CREATE EXTENSION mysql_fdw (под админом)

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'mysql-database.example.com', port '3306', database 'mysql_db')

CREATE USER MAPPING FOR pgml_user SERVER mysql_server OPTIONS (username 'mysql_user', password 'mysql_user_password')

IMPORT FOREIGN SCHEMA mysql_schema FROM SERVER mysql_server INTO public

после этого Вы сможете делать select из БД подключенной по SSH и инсертить в локально.
Это избавит от необходимости дампить исходную базу, и имеет смысл если база огромна а выборка не так велика.

[Daemon23RUS]

Ипатьев, Мне нравится Ваша аналогия, улыбнуло. Про комаров согласен, задача у ТС простейшая. А вот про ядерную бомбу не соглашусь, обычный источник данных внутри БД, который тянет их из внешней. Не так часто используется, поэтому мало описан и кажется чем-то сверхъестественным. Если не пробовали, попробуйте, Вам тоже понравится.