Как перенести базу данных postgresql на другой компьютер

Перенос базы данных PostgreSQL с помощью дампа и ее восстановление

Базу данных PostgreSQL можно извлечь в файл дампа с помощью pg_dump. Затем необходимо использовать pg_restore для восстановления базы данных PostgreSQL из файла архива, созданного pg_dump .

Предварительные требования

Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:

• Сервер базы данных Azure для PostgreSQL с правилами брандмауэра, разрешающими доступ к этом серверу.
• Установленные программы командной строки pg_dump и pg_restore.

Создание файла дампа, содержащего необходимые для загрузки данные

Чтобы создать резервную копию базы данных PostgreSQL локально или на виртуальной машине, выполните следующую команду:

pg_dump -Fc -v --host= --username= --dbname= -f .dump 

Например, если имеется локальный сервер с базой данных testdb, запустите на нем следующее:

pg_dump -Fc -v --host=localhost --username=masterlogin --dbname=testdb -f testdb.dump 

Восстановление данных в целевую базу данных

После создания целевой базы данных можно воспользоваться командой pg_restore с параметром —dbname , чтобы восстановить данные в целевую базу данных из файла дампа.

pg_restore -v --no-owner --host= --port= --username= --dbname= .dump 

При включении параметра —no-owner все объекты, созданные во время восстановления, будут присвоены пользователю, отмеченному —username . Дополнительные сведения см. в документации по PostgreSQL.

На серверах базы данных Azure для PostgreSQL соединения TLS и SSL включены по умолчанию. Если сервер PostgreSQL требует соединения TLS или SSL, но не содержит их, задайте переменную среды PGSSLMODE=require чтобы утилита pg_restore могла подключаться с помощью TLS. Без протокола TLS может появиться ошибка «FATAL: SSL connection is required. Please specify SSL options and retry.» (Критическая ошибка: необходимо соединение SSL. Настройте SSL и повторите попытку). В командной строке Windows выполните команду SET PGSSLMODE=require перед выполнением команды pg_restore . В Linux или Bash выполните команду export PGSSLMODE=require перед выполнением команды pg_restore .

В этом примере необходимо восстановить данные из файла дампа testdb.dump в базу данных mypgsqldb на целевом сервере mydemoserver.postgres.database.azure.com.

Ниже приведен пример использования этого pg_restore для одиночного сервера:

pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb testdb.dump 

Ниже приведен пример использования этого pg_restore для гибкого сервера:

pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin --dbname=mypgsqldb testdb.dump 

Оптимизация процесса миграции

Один из способов миграции существующей базы данных PostgreSQL в службу баз данных Azure для PostgreSQL — это резервное копирование базы данных в источнике и ее восстановление в Azure. Чтобы свести к минимуму время, необходимое для завершения миграции, можно использовать следующие параметры с командами резервного копирования и восстановления.

Подробные сведения о синтаксисе см. в статьях о pg_dump и pg_restore.

Для резервного копирования

Создайте резервную копию с использованием параметра -Fc , чтобы можно было выполнять восстановление параллельно. Это позволит ускорить процесс. Пример:

pg_dump -h my-source-server-name -U source-server-username -Fc -d source-databasename -f Z:\Data\Backups\my-database-backup.dump 

Для восстановления

• Переместите файл резервной копии на виртуальную машину Azure в том же регионе, в котором находится сервер базы данных Azure для PostgreSQL, на который выполняется миграция. Выполните pg_restore из этой виртуальной машины, чтобы снизить задержку в сети. Создание виртуальной машины с ускоренной сетью.
• Откройте файл дампа, чтобы убедиться в том, что инструкции создания индекса находятся после вставки данных. Если это не так, переместите инструкции создания индекса после вставленных данных. Это должно быть сделано по умолчанию, но рекомендуется дополнительно проверить и подтвердить.
• Восстановление с помощью переключателя -j N (где N представляет число) для параллелизации восстановления. Указанное вами число — это количество ядер на целевом сервере. Вы также можете попробовать установить вдвое большее количество ядер целевого сервера, чтобы оценить нагрузку. Ниже приведен пример использования этого pg_restore для одиночного сервера:

 pg_restore -h my-target-server.postgres.database.azure.com -U azure-postgres-username@my-target-server -j 4 -d my-target-databasename Z:\Data\Backups\my-database-backup.dump 

Ниже приведен пример использования этого pg_restore для гибкого сервера:

 pg_restore -h my-target-server.postgres.database.azure.com -U azure-postgres-username -j 4 -d my-target-databasename Z:\Data\Backups\my-database-backup.dump 

• Отключите отслеживание производительности запросов. Эти статистические данные не требуются во время миграции. Это можно сделать, установив для параметров pg_stat_statements.track , pg_qs.query_capture_mode и pgms_wait_sampling.query_capture_mode значение NONE .
• Используйте SKU с высоким объемом ресурсов вычисления и памяти, например модель 32 vCore Memory Optimized (32 виртуальных ядра с оптимизацией для операций в памяти), чтобы ускорить миграцию. Вы можете легко вернуться к предпочитаемому SKU после завершения восстановления. Чем выше номер SKU, тем большего параллелизма можно достичь, увеличив значение соответствующего параметра -j в команде pg_restore .
• Увеличьте число операций ввода-вывода в секунду на целевом сервере — это может улучшить производительность восстановления. Вы можете подготовить больше операций ввода-вывода в секунду, увеличив объем хранилища на сервере. Этот параметр необратим, но стоит принять во внимание, будет ли большее количество операций ввода-вывода в секунду полезным для вашей рабочей нагрузки в будущем.

Не забудьте проверить и протестировать эти команды в тестовой среде, прежде чем использовать их в рабочей среде.

Дальнейшие действия

• Узнать больше о миграции базы данных PostgreSQL с помощью метода экспорта и импорта можно в статье Миграция базы данных PostgreSQL с помощью метода экспорта и импорта.
• Дополнительные сведения о переносе баз данных в службу «База данных Azure для PostgreSQL» см. в этой статье.

Как перенести базу данных postgresql на другой компьютер

Идея, стоящая за этим методом, заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере пересоздадут базу данных в том же самом состоянии, в котором она была на момент выгрузки. PostgreSQL предоставляет для этой цели вспомогательную программу pg_dump . Простейшее применение этой программы выглядит так:

pg_dump имя_базы > файл_дампа 

Как видите, pg_dump записывает результаты своей работы в устройство стандартного вывода. Далее будет рассмотрено, чем это может быть полезно. В то время как вышеупомянутая команда создаёт текстовый файл, pg_dump может создать файлы и в других форматах, которые допускают параллельную обработку и более гибкое управление восстановлением объектов.

Программа pg_dump является для PostgreSQL обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что pg_dump не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. (Если у вас нет достаточных прав для резервного копирования всей базы данных, вы, тем не менее, можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема или -t таблица .)

Указать, к какому серверу должна подключаться программа pg_dump , можно с помощью аргументов командной строки -h сервер и -p порт . По умолчанию в качестве сервера выбирается localhost или значение, указанное в переменной окружения PGHOST . Подобным образом, по умолчанию используется порт, заданный в переменной окружения PGPORT , а если она не задана, то порт, указанный по умолчанию при компиляции. (Для удобства при компиляции сервера обычно устанавливается то же значение по умолчанию.)

Как и любое другое клиентское приложение PostgreSQL , pg_dump по умолчанию будет подключаться к базе данных с именем пользователя, совпадающим с именем текущего пользователя операционной системы. Чтобы переопределить имя, либо добавьте параметр -U , либо установите переменную окружения PGUSER . Помните, что pg_dump подключается к серверу через обычные механизмы проверки подлинности клиента (которые описываются в Главе 19).

Важное преимущество pg_dump в сравнении с другими методами резервного копирования, описанными далее, состоит в том, что вывод pg_dump обычно можно загрузить в более новые версии PostgreSQL , в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только метод с применением pg_dump будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.

Дампы, создаваемые pg_dump , являются внутренне согласованными, то есть, дамп представляет собой снимок базы данных на момент начала запуска pg_dump . pg_dump не блокирует другие операции с базой данных во время своей работы. (Исключение составляют операции, которым нужна исключительная блокировка, как например, большинство форм команды ALTER TABLE .)

24.1.1. Восстановление дампа

Текстовые файлы, созданные pg_dump , предназначаются для последующего чтения программой psql . Общий вид команды для восстановления дампа:

psql имя_базы < файл_дампа 

где файл_дампа — это файл, содержащий вывод команды pg_dump . База данных, заданная параметром имя_базы , не будет создана данной командой, так что вы должны создать её сами из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 имя_базы ). Программа psql принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно pg_dump . За дополнительными сведениями обратитесь к справке по psql . Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой pg_restore .

Перед восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с изначальными владельцами и/или правами. (Иногда это желаемый результат, но обычно нет).

По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP , это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:

psql --set ON_ERROR_STOP=on имя_базы < файл_дампа

В любом случае вы получите только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или —single-transaction . Выбирая этот режим, учтите, что даже незначительная ошибка может привести к откату восстановления, которое могло продолжаться несколько часов. Однако, это всё же может быть предпочтительней, чем вручную вычищать сложную базу данных после частично восстановленного дампа.

Благодаря способности pg_dump и psql писать и читать каналы ввода/вывода, можно скопировать базу данных непосредственно с одного сервера на другой, например:

pg_dump -h host1 имя_базы | psql -h host2 имя_базы

Важно

Дампы, которые выдаёт pg_dump , содержат определения относительно template0 . Это означает, что любые языки, процедуры и т. п., добавленные в базу через template1 , pg_dump также выгрузит в дамп. Как следствие, если при восстановлении вы используете модифицированный template1 , вы должны создать пустую базу данных из template0 , как показано в примере выше.

После восстановления резервной копии имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 23.1.3 и Подразделу 23.1.6. Другие советы по эффективной загрузке больших объёмов данных в PostgreSQL вы можете найти в Разделе 14.4.

24.1.2. Использование pg_dumpall

Программа pg_dump выгружает только одну базу данных в один момент времени и не включает в дамп информацию о ролях и табличных пространствах (так как это информация уровня кластера, а не самой базы данных). Для удобства создания дампа всего содержимого кластера баз данных предоставляется программа pg_dumpall , которая делает резервную копию всех баз данных кластера, а также сохраняет данные уровня кластера, такие как роли и определения табличных пространств. Простое использование этой команды:

pg_dumpall > файл_дампа 

Полученную копию можно восстановить с помощью psql :

psql -f файл_дампа postgres

(В принципе, здесь в качестве начальной базы данных можно указать имя любой существующей базы, но если вы загружаете дамп в пустой кластер, обычно нужно использовать postgres ). Восстанавливать дамп, который выдала pg_dumpall , всегда необходимо с правами суперпользователя, так как они требуются для восстановления информации о ролях и табличных пространствах. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.

pg_dumpall выдаёт команды, которые заново создают роли, табличные пространства и пустые базы данных, а затем вызывает для каждой базы pg_dump . Таким образом, хотя каждая база данных будет внутренне согласованной, состояние разных баз не будет синхронным.

Только глобальные данные кластера можно выгрузить, передав pg_dumpall ключ —globals-only . Это необходимо, чтобы полностью скопировать кластер, когда pg_dump выполняется для отдельных баз данных.

24.1.3. Управление большими базами данных

Некоторые операционные системы накладывают ограничение на максимальный размер файла, что приводит к проблемам при создании больших файлов с помощью pg_dump . К счастью, pg_dump может писать в стандартный вывод, так что вы можете использовать стандартные инструменты Unix для того, чтобы избежать потенциальных проблем. Вот несколько возможных методов:

Используйте сжатые дампы. Вы можете использовать предпочитаемую программу сжатия, например gzip :

pg_dump имя_базы | gzip > имя_файла.gz

Затем загрузить сжатый дамп можно командой:

gunzip -c имя_файла.gz | psql имя_базы

cat имя_файла.gz | gunzip | psql имя_базы

Используйте split . Команда split может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 1 мегабайту:

pg_dump имя_базы | split -b 1m - имя_файла

Восстановить их можно так:

cat имя_файла* | psql имя_базы

Используйте специальный формат дампа pg_dump . Если при сборке PostgreSQL была подключена библиотека zlib , дамп в специальном формате будет записываться в файл в сжатом виде. В таком формате размер файла дампа будет близок к размеру, полученному с применением gzip , но он лучше тем, что позволяет восстанавливать таблицы выборочно. Следующая команда выгружает базу данных в специальном формате:

pg_dump -Fc имя_базы > имя_файла

Дамп в специальном формате не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore , например:

pg_restore -d имя_базы имя_файла

За подробностями обратитесь к справке по командам pg_dump и pg_restore .

Для очень больших баз данных может понадобиться сочетать split с одним из двух других методов.

Используйте возможность параллельной выгрузки в pg_dump . Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в pg_dump . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j . Параллельная выгрузка поддерживается только для формата архива в каталоге.

pg_dump -j число -F d -f выходной_каталог имя_базы

Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j . Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j .

Пред.	Наверх	След.
Глава 24. Резервное копирование и восстановление	Начало	24.2. Резервное копирование на уровне файлов

Импорт и экспорт данных в PostgreSQL, гайд для начинающих

В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.

В своё время, столкнувшись с импортом и экспортом данных, обнаружилось, что какой-то более-менее структурированной инфы мало: этот момент обходят на всяких там курсах по аналитике, подразумевая, что это очень простые моменты, которым не следует уделять внимание.

В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.

1. Импорт базы данных в формате в PostgreSQL

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

Имя файла: demo-big-20170815

Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой cd C:\Users\User-N\Desktop\БД :

Далее выполняем команду для загрузки БД из sql-файла:

«C:\Program Files\PostgreSQL\10\bin\psql» -U postgres -f demo-big-20170815.sql

Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f — название файла БД.

Отметим, что в зависимости от размера базы данных загрузка может занимать до нескольких десятков минут. Конец загрузки будет отмечен следующим видом:

Заходим в pgAdmin и наблюдаем там импортированную БД:

2. Импорт данных из csv-файла

Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.

В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.

Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.

Ну и вводим команды на импорт данных из файла:

\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW.csv’ DELIMITER ‘,’ CSV HEADER;

Где tickets – название созданной в БД таблицы, из – путь, где хранится .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER , указывающий на заголовки «колонок».

Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.

Заходим в pgAdmin и удостоверяемся, что данные были загружены.

3. Экспорт данных в .csv-файл

Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.

Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:

\COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;

Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.

4. Экспорт данных выборки в .csv-файл

Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = ‘SVO’. Данный запрос можно вставить сразу в команду psql:

\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = ‘SVO’) TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv’ CSV HEADER DELIMITER ‘,’;

Вот такой небольшой гайд получился.

• Импорт экспорт данных в PostgreSQL
• импорт и экспорт в csv
• psql команда copy

Перенос базы PostgreSQL с сервера на сервер

Не простая операция, если вы не имели опыта настройки / работы с postgresql до сих пор. Расскажу поэтапно как выгрузить дамп базы, и как затем этот дамп загрузить в нужном месте :).

Фокусы, которые тут не работают

Базы mySQL я иногда экспортирую не с помощью скрипта, а копирую файлы данных. В каталоге, где они хранятся, название подкаталогов — это имена баз, а каждая таблица состоит из 2-3 файлов, с именем как у самой таблицы. Копирование сработает, если основные версии серверов совпадает.

В случае postgresql подобный фокус не работает. Вы можете подсмотреть папку с базами данных в конфигурации (вам поможет вот такая команда)

ps auxw | grep postgres | grep — — D
postgres 23395 0.0 0.7 247736 16164 ? S 17 : 33 0 : 00
/ usr / lib / postgresql / 9.3 / bin / postgres — D / var / lib / postgresql / 9.3 / main — c
config_file =/ etc / postgresql / 9.3 / main / postgresql .conf

В примере папка с базами: -D /var/lib/postgresql/9.3/main

Названия таблиц и баз вы здесь не увидите в явном виде. Копирование файлов будет бесполезно.

Самый главный админ

Первое, что надо усвоить — операции с postgresql удобно выполнять с правами postgresql же суперадмина. На юзера root PSQL чихал и не признает его авторитета. По умолчанию, супер-юзер имеет имя postgres.

Переходим под эту учетку.

su — postgres

Посмотреть список баз данных

Чтобы узнать какие базы у вас есть в наличии можно использовать команду:

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

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

Получение дампа базы postgresql

Для создания дампа базы служит команда pg_dump. Мы направим её вывод в файл через gzip.

pg_dump — h localhost — O — F t — c — U postgres myDBname | gzip — c > myDB — filedump . gz

myDBname — это название базы данных, а myDB-filedump.gz — название файла, куда будет сохранен дамп.

-h [host] : явное указание хоста, значение по умолчанию localhost или значение из переменной окружения PGHOST.

-О : пропускает команды для установки владельца таблиц, видов и т.д.

-F — формат вывода данных custom, tar, plane text.

-U [username] — пользователь, чьими правами доступа нужно воспользоваться при запросе данных. По умолчанию берется текущий пользователь. При необходимости программа запросит пароль.

-C, —create — добавляет команду для создания БД, я покажу как создать её вручную.

-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

Есть и более мощная команда, которая выгружает сразу все базы: