Перенос базы данных Postgresql

Обновлено: 21.11.2024

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

Копировать базу данных PostgreSQL на том же сервере:

Если возникает ситуация, когда необходимо скопировать базу данных PostgreSQL на сервер баз данных для целей тестирования. PostgreSQL упрощает это с помощью оператора CREATE DATABASE следующим образом:

Этот оператор копирует исходную_базу данных в целевую_базу данных. Например, чтобы скопировать описанную здесь демонстрационную базу данных dvdrental, которую можно скачать отсюда, в базу данных dvdrental_test, используйте следующий оператор:

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

Скопировать базу данных PostgreSQL с одного сервера на другой:

Существует множество способов копирования базы данных между различными серверами баз данных PostgreSQL. Соединение между серверами становится медленнее по мере увеличения базы данных. Один из способов сделать это — создать дамп базы данных и восстановить тот же дамп на другом сервере. Для этого необходимо выполнить следующие команды:

  • Шаг 1. Создайте файл дампа исходной базы данных.
  • Шаг 2. Скопируйте файл дампа на удаленный сервер.
  • Шаг 3. Создайте новую базу данных на удаленном сервере, где вы хотите восстановить дамп базы данных:
  • Шаг 4. Восстановите файл дампа на удаленном сервере:

Пример:
Здесь мы скопируем базу данных dvdrental с локального сервера на удаленный сервер. Сначала мы создадим дамп базы данных dvdrental в файл дампа, например, dvdrental.sql:

Затем мы скопируем файл дампа на удаленный сервер и создадим базу данных dvdrental на удаленном сервере:

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

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

Например, если кто-то хочет скопировать базу данных dvdrental с локального хоста на удаленный сервер, вы делаете это следующим образом:

Я хочу скопировать рабочую базу данных PostgreSQL на сервер разработки. Как это сделать быстрее и проще всего?

12 ответов 12

Вам не нужно создавать промежуточный файл. Вы можете сделать

используя psql или pg_dump для подключения к удаленному хосту.

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

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

но это решение также требует наличия сеанса на обоих концах.

Нет необходимости в промежуточных файлах — вы можете использовать сжатый туннель SSH или просто канал: pg_dump | bzip2 | ssh "bunzip2 | pg_restore"

Как я могу работать со сжатием, если я извлекаю данные из рабочей среды в разработку? Я настроил SSH-соединение от разработки к производству. Так что это будет ssh remoteuser@remotehost "pg_dump -C dbname | bzip2" | распаковка2 | psql имя_базы_данных ?

Вот что я сделал: (1) pg_dump -C -h удаленный хост -U удаленный пользователь x | psql -h localhost -U localuser (2) dropdb y (3) psql -U postgres -c 'ИЗМЕНИТЬ БАЗУ ДАННЫХ "x" ПЕРЕИМЕНОВАТЬ В "y"'

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

Затем скопируйте резервную копию на сервер разработки и восстановите с помощью:

Кто-то сказал мне, что это может быть проблематично — проблемы с разрешениями приводят к тому, что либо дамп, либо восстановление умирают при срабатывании триггера?

@rmbarnes: Если есть проблемы - их надо исправлять. Без подробного знания того, что сделал этот «Кто-то», никто не может ни подтвердить, ни опровергнуть это утверждение.

Используйте флаг --no-owner с pg_dump. Это пропускает проблему, и первое редактирование этого сообщения использовало это, но затем я подумал, что вам может потребоваться более точное соответствие исходной базе данных.

Для меня описанный выше подход работал следующим образом: pg_dump -C -h host -U имя_пользователя db_name > /any_directory/dump_schema_and_data_file. А для восстановления из файла: psql -h host -U имя_пользователя db_name

Это уберегло меня от МНОГО раздражения. Я использовал Google диск для перемещения файла между машинами. Поскольку у меня уже была база данных на новой машине (но пустая), я получил МНОГО ошибок дублирования ключей. Однако это среда разработки, и они ничему не повредили.

Используйте pg_dump, а затем psql или pg_restore — в зависимости от того, какие параметры pg_dump выбраны —Fp или —Fc.

Пример использования:

Если вы хотите выполнить миграцию между версиями (например, вы обновили postgres и используете 9.1 на localhost:5432 и 9.3 работает на локальном хосте: 5434), вы можете запустить:

Меня несколько раз спрашивают (myuser91/postgres)-пароль, есть ли способ, чтобы мне нужно было вводить пароль только один раз?

Сейчас pg_basebackup кажется лучшим способом сделать это, особенно для больших баз данных.

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

pg_basebackup работает с серверами той же или более старой основной версии, вплоть до 9.1. Однако режим потоковой передачи WAL ( -X stream ) работает только с версией сервера 9.3 и выше, а режим формата tar ( --format=tar ) текущей версии работает только с версией сервера 9.5 или выше.

Для этого вам нужно на исходном сервере:

    чтобы иметь возможность подключаться с целевого сервера. Убедитесь, что порт 5432 открыт.
  1. Как минимум 1 доступное соединение для репликации: max_wal_senders = 1 ( -X fetch ), 2 для потока -X (по умолчанию в случае PostgreSQL 12) или больше. или выше, чтобы можно было установить max_wal_senders > 0 .
  2. репликация узла postgres DST_IP/32 доверяет pg_hba.conf . Это предоставляет доступ к кластеру pg любому пользователю с компьютера DST_IP. Возможно, вы захотите прибегнуть к более безопасному варианту.

Изменения 1, 2, 3 требуют перезагрузки сервера, изменение 4 требует перезагрузки.

На целевом сервере:

Вероятность того, что вы будете использовать разные версии базы данных для разработки и производства, невелика. В прошлый раз у меня был неприятный разговор с одной из моих коллег по команде, когда она пыталась сообщить о проблеме, что какой-то код не работает с PG 9.6, тогда как в то время мы использовали 9.5 в производстве. Базовое резервное копирование выполняется намного быстрее. Тогда при необходимости можно использовать pg_upgrade.

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

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

Если вы используете общедоступный IP-адрес postgres для подключения, вам необходимо добавить параметры -p, чтобы указать порт хоста, например: -h <> -p >

Дамп вашей базы данных: pg_dump имя_базы_данных_имя > backup.sql

Импортируйте базу данных обратно: psql db_name

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

Теперь скопируйте этот файл дампа на удаленную машину, на которую вы хотите скопировать БД.

На удаленной машине выполните следующую команду в папке ~/some/folder, чтобы восстановить БД.

как сделать резервную копию, если вы не можете подключиться к psql? есть ли способ файловой системы для достижения этого? пример: если ваш сервер config/exe заразился вирусом?

Я много боролся, и в конце концов метод, который позволил мне заставить его работать с Rails 4, был следующим:

на вашем старом сервере

Для создания дампа мне пришлось использовать пользователя postgres linux. также мне пришлось использовать -c, чтобы принудительно создать базу данных на новом сервере. --inserts указывает использовать синтаксис INSERT(), который в противном случае не работал бы для меня :(

затем на новом сервере просто:

для передачи файла dump.sql между серверами я просто использовал "кошку" для печати содержимого, а затем "нано" для воссоздания его путем копирования содержимого.

Кроме того, РОЛЬ, которую я использовал в двух базах данных, была разной, поэтому мне пришлось найти и заменить все имена владельцев в дампе.

Позвольте мне поделиться сценарием оболочки Linux для копирования данных вашей таблицы с одного сервера на другой сервер PostgreSQL.

Сценарий оболочки Linux Bash для переноса данных между серверами PostgreSQL:

Я просто переношу данные; создайте пустую таблицу на целевом/втором сервере базы данных.

Это служебный скрипт. Кроме того, вы можете изменить скрипт для универсального использования, например, добавив параметры для host_name, database_name, table_name и другие

Вот пример использования pg_basebackup

Я выбрал этот путь, поскольку он обеспечивает резервное копирование всего кластера базы данных (пользователей, баз данных и т. д.).

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

Для Postgres 12 и Ubuntu 18.04 мне пришлось выполнить следующие действия:

На сервере, на котором в данный момент запущена база данных:

Обновите pg_hba.conf , для меня он находится в /etc/postgresql/12/main/pg_hba.conf

Добавьте следующую строку (замените 192.168.0.100 на IP-адрес сервера, на который вы хотите скопировать базу данных).

Обновление postgresql.conf , для меня расположенное в /etc/postgresql/12/main/postgresql.conf . Добавьте следующую строку:

перезагрузка службы sudo postgresql

На хосте, на который вы хотите скопировать кластер базы данных:

остановка службы sudo postgresql

sudo su root

rm -rf /var/lib/postgresql/12/main/*

выход

sudo -u postgres pg_basebackup -h 192.168.0.101 -U postgres -D /var/lib/postgresql/12/main/

запуск службы sudo postgresql

Общая картина — остановите службу, удалите все в каталоге данных (мой находится в /var/lib/postgreql/12). Права доступа к этому каталогу: drwx------ с пользователем и группой postgres. Я мог сделать это только как root, даже не с помощью sudo -u postgres. Я не уверен, почему. Убедитесь, что вы делаете это на новом сервере, на который хотите скопировать базу данных! Вы удаляете весь кластер базы данных.

Обязательно измените IP-адрес с 192.168.0.101 на IP-адрес, с которого вы копируете базу данных. Скопируйте данные с исходного сервера с помощью pg_basebackup. Запустите службу.

Обновите pg_hba.conf и postgresql.conf, чтобы они соответствовали исходной конфигурации сервера — прежде чем вносить какие-либо изменения, добавляйте строку репликации и строку listen_addresses (в моем случае мне пришлось добавить возможность локального входа через md5 в pg_hba .conf).

Обратите внимание, что для max_wal_senders и wal_level есть рекомендации, которые можно найти в документации. Мне не нужно было ничего с этим делать.

PostgreSQL — это объектно-реляционная база данных с открытым исходным кодом, созданная с упором на расширяемость, целостность данных и скорость. Поддержка параллелизма делает его полностью совместимым с ACID, а также поддерживает динамическую загрузку и операции на основе каталога, что позволяет пользователям настраивать типы данных, функции и многое другое.

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

В настоящее время мы не поддерживаем перенос баз данных из кластеров внутри DigitalOcean в другие кластеры внутри DigitalOcean.

Стратегии логической репликации и миграции

Функция онлайн-миграции использует логическую репликацию для переноса данных из одной базы данных в другую. Логическая репликация непрерывно выполняет репликацию построчно, включая любые изменения, записываемые в базу данных во время миграции, пока репликация не будет остановлена.

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

Непрерывная миграция или дамп

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

Как проверить, есть ли у вас права суперпользователя

Чтобы проверить, есть ли у вас права суперпользователя, используйте команду \du из терминала PostgreSQL ( psql ):

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

Если в имени вашей роли нет атрибута суперпользователя, обратитесь к системному администратору и попросите предоставить его вам.

Мы рекомендуем непрерывную миграцию; однако, если у вас нет прав суперпользователя, вы можете перенести базу данных с помощью дампа, если вместо этого у вас есть следующие разрешения:

  • Подключиться
  • Выбрать во всех таблицах базы данных
  • Выбрать все последовательности в базе данных

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

Дамп — это снимок на определенный момент времени. Это означает, что любые данные, записанные в исходную базу данных после создания дампа, не будут перенесены в целевую базу данных. Если вы выберете этот метод, убедитесь, что ваша база данных находится в автономном режиме или в режиме обслуживания.

Предпосылки

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

Иметь права суперпользователя

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

Сделать базу данных общедоступной

Для переноса базы данных имя хоста или IP-адрес исходной базы данных должны быть доступны из общедоступного Интернета. Информация о общедоступном подключении к базам данных DigitalOcean находится в разделе «Сведения о подключении» базы данных на панели управления. Дополнительные сведения о других поставщиках см. в их документации.

Учетные данные исходной базы данных

Перед переносом существующей базы данных вам потребуется следующая информация об исходной базе данных:

  • Имя хоста или строка подключения. Общедоступное имя хоста, строка подключения или IP-адрес, используемые для подключения к базе данных.
  • Порт: порт, используемый для подключения к базе данных. Кластеры DigitalOcean по умолчанию подключаются к порту 25061.
  • Имя пользователя: имя пользователя, используемое для подключения к базе данных. Имя пользователя требует разрешения суперпользователя для доступа к данным, которые вы хотите перенести.
  • Пароль: пароль, используемый для подключения к базе данных.

Подробнее о том, как найти эту информацию, см. в документации поставщика базы данных.

Обновить или отключить брандмауэры

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

Чтобы сделать это в целевой базе данных DigitalOcean, удалите все доверенные источники из кластера базы данных. Удаление всех доверенных источников оставляет базу данных открытой для всех входящих подключений. Чтобы обеспечить безопасность ваших баз данных после переноса, не забудьте добавить в базу данных доверенные источники.

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

Подготовка исходной базы данных к миграции

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

  • Разрешение удаленных подключений.
  • Изменение локального подключения IPv4 на 0.0.0.0/0 .
  • Включение логической репликации.
  • Установка максимального количества слотов репликации, равного или превышающего количество баз данных на сервере PostgreSQL.
  • Перезапуск сервера PostgreSQL.

Разрешить удаленные подключения

Во-первых, убедитесь, что ваша база данных разрешает все удаленные подключения. Это определяется переменной listen_addresses вашей базы данных, которая разрешает все удаленные подключения, если ее значение установлено на *. Чтобы проверить его текущее значение, запустите следующий запрос в терминале PostgreSQL ( psql ):

Если включено, командная строка возвращает:

Если выходные данные отличаются, разрешите удаленные подключения в базе данных, выполнив следующий запрос:

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

Откройте файл pg_hba.conf в любом текстовом редакторе, например nano :

В разделе "Локальные подключения IPv4" найдите и замените IP-адрес на 0.0.0.0/0, что разрешает все адреса IPv4:

Полное описание синтаксиса файла конфигурации см. в официальной документации.

Включить логическую репликацию

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

Чтобы убедиться, что логическая репликация включена, выполните следующий запрос в терминале PostgreSQL ( psql ):

Если включено, командная строка возвращает:

Если выходные данные отличаются, включите логическую репликацию в своей базе данных, установив для wal_level значение logical :

Изменить максимальное количество слотов репликации

После включения логической репликации вам необходимо убедиться, что значение max_replication_slots вашей базы данных больше или равно количеству баз данных на вашем сервере PostgreSQL. Чтобы проверить текущее значение, выполните следующий запрос в терминале PostgreSQL ( psql ):

Командная строка возвращает:

Если меньше, чем количество баз данных на вашем сервере PostgreSQL, измените его, выполнив следующий запрос, где use_your_number – это количество баз данных на вашем сервере:

Перезагрузите сервер

Чтобы изменения в этом разделе вступили в силу, перезапустите сервер PostgreSQL:

Миграция базы данных PostgreSQL

Чтобы перенести базу данных PostgreSQL из панели управления DigitalOcean, нажмите «Базы данных», а затем выберите базу данных, на которую вы хотите выполнить миграцию, из списка баз данных.

На странице обзора базы данных нажмите кнопку "Действия" и выберите "Настроить миграцию".

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

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

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

Копировать базу данных PostgreSQL на том же сервере

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

PostgreSQL позволяет легко сделать это с помощью оператора CREATE DATABASE следующим образом:

Этот оператор копирует исходную базу данных в целевую базу данных. Например, чтобы скопировать образец базы данных dvdrental в базу данных dvdrental_test, используйте следующую инструкцию:

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

Если к базе данных dvdrental есть активные соединения, вы получите следующую ошибку:

Следующий запрос возвращает активные соединения:

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

После этого вы можете снова выполнить оператор CREATE TABLE WITH TEMPLATE, чтобы скопировать базу данных dvdrental в базу данных dvdrental_test.

Скопировать базу данных PostgreSQL с одного сервера на другой

Существует несколько способов копирования базы данных между серверами баз данных PostgreSQL.

Если размер исходной базы данных велик, а соединение между серверами баз данных медленное, вы можете сбросить исходную базу данных в файл, скопировать файл на удаленный сервер и восстановить его:

Сначала сохраните исходную базу данных в файл.

Во-вторых, скопируйте файл дампа на удаленный сервер.

В-третьих, создайте новую базу данных на удаленном сервере:

Наконец, восстановите файл дампа на удаленном сервере:

Копирование примера базы данных dvdrental

Следующие шаги иллюстрируют, как скопировать базу данных dvdrental с локального сервера на удаленный сервер.

Сначала поместите базу данных dvdrental в файл дампа, например, dvdrental.sql :

Во-вторых, скопируйте файл дампа на удаленный сервер.

В-третьих, создайте базу данных dvdrental на удаленном сервере:

В-четвертых, восстановите файл дампа dvdrental.sql на удаленном сервере:

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

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

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

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