Postgresql копирование таблицы в файл

Обновлено: 07.07.2024

Я пытаюсь скопировать большую таблицу (

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: это здорово, но, к сожалению, очень ручной.

Ну, тебе придется делать вещи вручную, к сожалению. Но все это можно сделать из чего-то вроде 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

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

Вариант 1. Запуск от пользователя root; одна база.

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB $ -name "*-1[^5].*" -o -name "*-[023]?.*" $ -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

find $pathB $ -name "*-1[^5].*" -o -name "*-[023]?.*" $ -ctime +61 -delete

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Зададим в качестве владельца файла, пользователя postgres:

chown postgres:postgres /scripts/postgresql_dump.sh

crontab -e -u postgres

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su - postgres -c "/scripts/postgresql_dump.sh"

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t <table> или --table=<table>:

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы (структуры)

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump --schema-only users > /tmp/users.schema.dump

Также, внутри каждой базы могут быть свои схемы с данными. Если нам нужно сделать дамп именно той схемы, которая внутри базы, используем ключ -n:

pg_dump --schema-only users -n production > /tmp/users.schema_production.dump

* в данном примере мы создадим дамп структуры базы данных users только для схемы production.

Или полный дамп с данными для схемы внутри базы данных:

pg_dump users -n production > /tmp/users.production.dump

Только данные

pg_dump --data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin - подключаемся к серверу - кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп - выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия - ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

Другие форматы позволяют делать частичное восстановление, работать в несколько потоков и сжимать данные.

Приветствую всех.
При автоматизации небольших магазинов для хранения данных часто используют PostgreSQL. И часто возникает потребность экспортировать эти данные в Excel. В этой статье я расскажу вам как я решал эту задачу. Естественно, матерые специалисты вряд ли откроют для себя что-то новое. Однако, материал будет интересен тем кто «плавает» в этой теме.

Итак, естественно, самый просто и банальный способ экспортировать данные результатов запросов в csv-файлы, а затем открыть их в Excel. Это выглядит вот так:

во-первых, вставка данных из PostgreSQL происходит именно на сервере;
во-вторых, можно конечно заморочиться написать batch-скрипт, который будет удаленно вызывать этот запрос на сервере, затем этот файл скопировать на компьютер пользователя и инициировать открытие в Excel.

1. Идем по ссылке и в зависимости от разрядности компьютера скачиваем установщик ODBC драйвера. Установка его проста и не требует особых знаний.

2. Чтобы пользователи могли со своих компьютеров цепляться к БД не забудьте в файле pg_hba.conf установить параметры для IP-адресов, с которых можно производить подключения:

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

3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ(All files):

DATABASE – указывается наименование БД к которой будет производиться подключение;
SERVER – адрес сервера;
PASSWORD – пароль на подключение к БД.

Обратите внимание, что в большом тексте указываются параметры подключения к БД и ваша БД. Также можно еще сконфигурировать множество параметров подключения

В последней строке пишется сам запрос. Далее сохраняем файл. Если на компьютере установлен Microsoft Excel, тогда файл сразу же приобретет пиктограмму:

При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:

И получаем результат запроса из БД:

Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:

Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла:

Затем немного покодил (вот часть кода):

Результат получился тот же — данные из Excel, и пользователю удобно. Да, кстати, в строке:

если речь идет о 64-битном процессоре и драйвере ODBC, установленном для 64 бит, то надо писать:

Ну, и самое главное, несмотря на всю простоту способа, у него есть конечно недостатки: запрос можно писать только в одну строку, т.е. записать строку вот в таком виде не получиться. Нужно только в одну:

— Не сможет обрабатывать на изменение данных типа:
или

Ну и может выводить только результат запроса в виде списка, т.е. красивый документ сделать не получиться. На этом все. Надеюсь данный способ кому-нибудь пригодиться. Буду рад получить ваши рекомендации по усовершенствованию моего метода или альтернативного решения данной проблемы.

База данных

Многие пользователи просят дублировать таблицу без ее повторного создания и добавления данных в PostgreSQL. Здесь можно использовать команды дублирования. Давайте посмотрим на это, открыв графический интерфейс pgAdmin из меню «Пуск» на рабочем столе Windows 10. Дважды укажите пароль вашего сервера по запросу. После этого вы получите графический пользовательский интерфейс pgAdmin для PostgreSQL. В базах данных вы можете исследовать многие вещи. Вы найдете базу данных Postgres, которая уже была определена и создана PostgreSQL в процессе установки и настройки. Итак, вам не нужно создавать новую базу данных.

Пример 1

Давайте рассмотрим наш первый пример для дублирования таблицы, уже определенной в Postgres. Изучив базу данных Postgres, вы найдете вариант Таблицы. Создайте новую таблицу «test» с записью в ней нескольких столбцов. Вы можете найти эту таблицу под опциями таблицы после ее изучения, как показано на изображении ниже.

Нажмите на значок инструмента запросов, чтобы открыть его. Когда он откроется, напишите в него запрос SELECT, чтобы получить «тестовые» записи только что созданной таблицы в соответствии с приведенной ниже командой. Нажмите на значок «Выполнить», чтобы выполнить эту команду. В выходных данных показаны три различных «тестовых» столбца таблицы с их записями, например, ID, Fname и Lname.

Когда вы исследуете список таблиц, он показывает вам две таблицы, например, dup_test и test.

Когда мы проверяем только что созданную таблицу «dup_test» с помощью запроса SELECT в области инструментов запроса, мы обнаружили, что она содержит те же данные и структуру, что и таблица «test». Итак, запись и структура таблицы test полностью продублированы в таблице dup_test.

Пример 2

Пользователь также может создать дублирующую таблицу в PostgreSQL с помощью другой команды. Это дублирование будет выполнено без дублирования данных таблицы. Следовательно, мы будем использовать ключевое слово «no data» после оператора select в соответствии с приведенным ниже запросом. Запрос создавал новую таблицу с именем «duplicate» с помощью оператора CREATE TABLE и копировал структуру таблицы «test» с помощью оператора SELECT. Оператор «без данных» будет использоваться для предотвращения копирования данных из таблицы «test» в таблицу «duplicate». После выполнения запрос был успешным, как показано ниже, и таблица была успешно продублирована.

Вы можете найти эту таблицу в разделе «Таблицы» PostgreSQL, как показано ниже.

Проверив записи новой дублированной таблицы с именем «duplicate» с помощью запроса SELECT, как показано ниже, мы обнаружили, что структура таблицы такая же, как у таблицы «test». В этой таблице нет записей из-за использования в запросе оператора «без данных». Следовательно, запрос был успешным.

Пример 3

Вы можете увидеть вновь созданную таблицу «новая» в списке таблиц в базе данных Postgres.

После извлечения содержимого таблицы «новая» инструментом запроса с помощью команды SELECT он показывает те же данные, что и таблица «test» вместе со структурой, например, имена столбцов.

Пример 4

Приведем еще один простой пример, иллюстрирующий концепцию дублирования. На этот раз мы создали таблицу «new» в базе данных Postgres графического пользовательского интерфейса pgAdmin. Эта таблица содержит 10 записей в четырех столбцах, например ID, Имя, Город и Возраст. Давайте посмотрим на записи таблицы «новая» с помощью инструмента запроса. Мы попробовали следующую команду в области запроса, чтобы получить «новый» порядок таблицы по столбцу идентификатора. Выходные данные этой команды показывают 10 записей для некоторых пользователей.

После копирования и создания таблицы давайте посмотрим на вновь созданную дублированную таблицу, например, «duplicate». Итак, мы получили содержимое таблицы «дубликат» при использовании запроса SELECT в области запроса, упорядоченной по столбцу «ID». Мы видели, что структура «дубликата» таблицы совпадает с «новой» таблицей. Эта таблица не копировала записи таблицы «новая», использующая предложение «WITH NO DATA».

Заключение

Мы обсудили различные команды PostgreSQL для дублирования таблицы. Мы видели, как дублировать структуру таблицы с данными и без них. Все эти команды одинаково эффективны для использования в оболочке командной строки PostgreSQL.

Читайте также: