Скопировать таблицу из одной базы данных в другую в Postgres
Я пытаюсь скопировать всю таблицу из одной базы данных в другую в Postgres. Какие-либо предложения?
Извлеките таблицу и направьте ее прямо в целевую базу данных:
Примечание. Если в другой базе данных уже настроена таблица, используйте этот -a флаг только для импорта данных, в противном случае вы можете увидеть странные ошибки, такие как «Недостаточно памяти»:
Вы также можете использовать функцию резервного копирования в pgAdmin II. Просто следуйте этим шагам:
- В pgAdmin щелкните правой кнопкой мыши таблицу, которую хотите переместить, выберите «Резервное копирование».
- Выберите каталог для выходного файла и установите «Формат» в «обычный»
- Перейдите на вкладку «Параметры дампа # 1», установите флажок «Только данные» или «Только схема» (в зависимости от того, что вы делаете)
- В разделе «Запросы» нажмите «Использовать вставки столбцов» и «Команды вставки пользователя».
- Нажмите кнопку «Резервное копирование». Это выводит в файл .backup
- Откройте этот новый файл с помощью блокнота. Вы увидите сценарии вставки, необходимые для таблицы / данных. Скопируйте и вставьте их в новую страницу базы данных sql в pgAdmin. Запустить как pgScript — Query-> Выполнить как pgScript F6
Хорошо работает и может делать несколько таблиц одновременно.
Использование dblink было бы удобнее!
Использование psql на Linux-хосте, который имеет подключение к обоим серверам
Затем вы бы сделали что-то вроде:
Используйте pg_dump для выгрузки данных таблицы, а затем восстановите их с помощью psql.
Если у вас есть оба удаленных сервера, вы можете выполнить следующие действия:
Он скопирует упомянутую таблицу исходной базы данных в ту же именованную таблицу целевой базы данных, если у вас уже есть существующая схема.
Вы можете сделать следующее:
Вот что сработало для меня. Первый дамп в файл:
затем загрузите выгруженный файл:
Чтобы переместить таблицу из базы данных A в базу данных B при локальной настройке, используйте следующую команду:
Я попробовал некоторые решения здесь, и они были действительно полезны. По моему опыту лучшим решением является использование командной строки psql , но иногда я не чувствую необходимости использовать командную строку psql. Итак, вот еще одно решение для pgAdminIII
Проблема этого метода заключается в том, что должны быть записаны имена полей и типы таблиц, которые вы хотите скопировать.
pg_dump не работает всегда.
Учитывая, что у вас есть одна и та же таблица ddl в обоих dbs, вы можете взломать ее из stdout и stdin следующим образом:
То же, что и ответы user5542464 и Piyush S. Wanare, но разделены на два этапа:
в противном случае канал запрашивает два пароля одновременно.
Вы должны использовать DbLink для копирования данных одной таблицы в другую таблицу в другой базе данных. Вы должны установить и настроить расширение DbLink для выполнения кросс-запроса к базе данных.
Я уже создал подробный пост на эту тему. Пожалуйста, посетите эту ссылку
Если обе БД (от и до) защищены паролем, в этом сценарии терминал не будет запрашивать пароль для обеих БД, запрос пароля появится только один раз. Итак, чтобы это исправить, передайте пароль вместе с командами.
Я использовал DataGrip (по Intellij Idea). и было очень легко копировать данные из одной таблицы (из другой базы данных в другую).
Во-первых, убедитесь, что вы подключены к обоим источникам данных в Data Grip.
Выберите исходную таблицу и нажмите клавишу F5 или (щелкните правой кнопкой мыши -> выберите Копировать таблицу в.)
Это покажет вам список всех таблиц (вы также можете искать, используя имя таблицы во всплывающем окне). Просто выберите цель и нажмите ОК.
DataGrip позаботится обо всем остальном за вас.
Если вы запустите pgAdmin (Backup:, pg_dump Restore 🙂 pg_restore из Windows, он попытается вывести файл по умолчанию, c:\Windows\System32 и именно поэтому вы получите ошибку «Разрешение / доступ запрещен», а не потому, что пользователь postgres недостаточно повышен. Запустите pgAdmin от имени администратора или просто выберите место для вывода, отличное от системных папок Windows.
В качестве альтернативы вы также можете представить свои удаленные таблицы в качестве локальных таблиц, используя расширение для сторонних данных. Затем вы можете вставить в свои таблицы, выбрав из таблиц в удаленной базе данных. Единственным недостатком является то, что это не очень быстро.
Полное копирование таблицы postgres с помощью SQL
ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: Этот вопрос аналогичен вопросу о переполнении стека здесь, но ни один из этих ответов не работает для моей проблемы, как я объясню позже.
Я пытаюсь скопировать большую таблицу (
40M строк, 100+ столбцов) в postgres, где много столбцов проиндексировано. В настоящее время я использую этот бит SQL:
У этого метода есть две проблемы:
- Он добавляет индексы перед загрузкой данных, поэтому это займет гораздо больше времени, чем создание таблицы без индексов и последующее индексирование после копирования всех данных.
- Это не копирует должным образом столбцы в стиле SERIAL. Вместо того, чтобы устанавливать новый «счетчик» в новой таблице, он устанавливает значение по умолчанию для столбца в новой таблице равным счетчику прошлой таблицы, то есть он не будет увеличиваться по мере добавления строк.
Размер таблицы делает индексацию проблемой в реальном времени. Это также делает невозможным выгрузку в файл для повторной загрузки. У меня также нет преимущества командной строки. Мне нужно сделать это в SQL.
Я бы хотел либо прямо сделать точную копию с помощью какой-нибудь чудо-команды, либо, если это невозможно, скопировать таблицу со всеми ограничениями, но без индексов, и убедиться, что они являются ограничениями « по духу » (также известные как новый счетчик для столбца SERIAL). Затем скопируйте все данные с помощью SELECT * , а затем скопируйте все индексы.
Полное копирование таблицы postgres с помощью SQL
отказ от ответственности: этот вопрос похож на вопрос переполнения стека здесь, но ни один из этих ответов подойдет для моей проблемы, как я объясню позже.
Я пытаюсь скопировать большую таблицу (
40M строк, 100 + столбцов) в postgres, где многие столбцы индексируются. В настоящее время я использую этот бит SQL:
этот метод имеет две проблемы:
- он добавляет индексы перед глотанием данных, поэтому это займет много времени дольше, чем создание таблицы без индексов, а затем индексирование после копирования всех данных.
- это не копирует столбцы стиля «SERIAL» должным образом. Вместо настройки нового «счетчика» в новой таблице он устанавливает значение по умолчанию столбца в новой таблице в счетчик прошлой таблицы, что означает, что он не будет увеличиваться по мере добавления строк.
размер таблицы делает индексирование проблемой в реальном времени. Это также делает невозможным сброс в файл, чтобы затем повторно глотать. У меня также нет преимущества командной строки. Мне нужно сделать это в SQL.
то, что я хотел бы сделать, это либо прямо сделать точную копию с какой-то чудо-командой, либо, если это невозможно, скопировать таблицу со всеми противопоказаниями, но без индексов, и убедиться, что они являются ограничениями «по духу» (он же новый счетчик для последовательного столбца). Затем скопируйте все данные с помощью SELECT * а затем скопируйте все индексы.
источник
переполнение стека вопрос о копировании базы данных: это не то, что я прошу по трем причинам
- он использует опцию командной строки pg_dump -t x2 | sed ‘s/x2/x3/g’ | psql и в этой ситуации у меня нет доступа к командной строке
- он создает индексы pre данных глотать, который является медленным
- он не обновляет последовательные столбцы правильно в качестве доказательства default nextval(‘x1_id_seq’::regclass)
метод сброса значения последовательности для таблицы postgres: это здорово, но, к сожалению, очень ручной.
6 ответов
Ну, тебе придется делать вещи вручную, к сожалению. Но все это можно сделать из чего-то вроде psql. Первая команда достаточно проста:
Это создаст newtable с данными oldtable, но не индексами. Затем вы должны создать индексы и последовательности и т. д. самостоятельно. Вы можете получить список всех индексов в таблице с помощью команды:
затем запустите psql-E для доступа к вашей БД и используйте \d, чтобы посмотреть на старую таблицу. Затем вы можете изменить эти два запроса, чтобы получить информацию о последовательностях:
замените этот 74359 выше на oid, который вы получаете от предыдущего запроса.
на create table as функция в PostgreSQL теперь может быть ответом, который искал OP.
Это создаст идентичную таблицу с данными.
добавлять with no data будет копировать схему без данных.
Это создаст таблицу со всеми данными, но без индексов и триггеров так далее.
create table my_table_copy (like my_table including all)
синтаксис create table like будет включать все триггеры, индексы, ограничения и т. д. Но не включать данные.
ближайшая команда «чудо» — это что-то вроде
в частности, это заботится о создании индексов после загрузки табличных данных.
но это не сбрасывает последовательности; вам придется написать это самостоятельно.
предупреждение:
все ответы, которые используют pg_dump и любое регулярное выражение для замены имени исходной таблицы, действительно опасны. Что делать, если ваши данные содержат подстроку, которую вы пытаетесь заменить? Вы в конечном итоге измените свои данные!
Я предлагаю двухпроходное решение:
- исключить строки данных из дампа, используя некоторые данные конкретного regexp
- выполнить поиск и замену на оставшиеся линии
вот пример, написанный на Ruby:
в приведенном выше я пытаюсь скопировать таблицу «members»в » members_copy_20130320″. Мое регулярное выражение для данных — /^\d+\t.*(?:t/f)$/
вышеуказанный тип решения работает для меня. Будьте бдительны.
edit:
OK, вот еще один способ синтаксиса псевдо-оболочки для людей, которые не любят регулярное выражение:
- pg_dump-s-t mytable mydb > mytable_schema.в SQL
- поиск и замена имени таблицы в mytable_schema.в SQL > mytable_copy_schema.в SQL
psql-f mytable_copy_schema.в SQL базы данных mydb
pg_dump-a-t mytable mydb > mytable_data.в SQL
по-видимому, вы хотите «перестроить» таблицу. Если вы хотите только перестроить таблицу, а не копировать ее, вместо этого следует использовать кластер.
вы можете выбрать индекс, попробуйте выбрать тот, который соответствует вашим запросам. Вы всегда можете использовать первичный ключ, если другой индекс не подходит.
Если ваша таблица слишком велика для кэширования, кластер может быть медленным.
создать таблицу newTableName (например, oldTableName, включая индексы); вставить в newTableName выберите * из oldTableName