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

Обновлено: 21.11.2024

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

  • Перенос базы данных из одной учетной записи веб-хостинга или провайдера в другую.
  • Импорт сторонней базы данных.
  • Резервное копирование базы данных.

Экспорт базы данных PostgreSQL

Вы можете экспортировать базу данных PostgreSQL в файл с помощью программы командной строки pg_dump или использовать phpPgAdmin.

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

  1. Откройте командную строку на компьютере, где хранится база данных. Например, если база данных находится в другой учетной записи веб-хостинга или у другого поставщика веб-хостинга, войдите в учетную запись, используя SSH. Если у вас есть физический доступ к компьютеру, вы можете открыть окно DOS или терминала для доступа к командной строке.
  2. Введите следующую команду и нажмите Enter. Замените username на имя пользователя вашей учетной записи A2 Hosting и замените dbname на имя базы данных, которую вы хотите экспортировать:

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

Файл dbexport.pgsql теперь содержит все данные для базы данных dbname. Если файл dbexport.pgsql находится на удаленном компьютере, загрузите его на локальный компьютер.

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

Эти ошибки возникают из-за того, что некоторые шаблоны баз данных сервера включают PostGIS с ограниченными правами доступа. Чтобы экспортировать базу данных PostgreSQL без этих данных, введите следующую команду вместо команды, указанной на шаге 2:

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

    Войдите в cPanel.

Страница администрирования phpPgAdmin появится в новом окне.

Создайте новую базу данных PostgreSQL и назначьте пользователя

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

    Войдите в cPanel.

В разделе "Создать новую базу данных" в текстовом поле "Имя базы данных" введите имя базы данных.

Импорт базы данных PostgreSQL

После того как вы создали новую базу данных в cPanel, вы можете импортировать содержимое базы данных с помощью программы командной строки psql или с помощью phpPgAdmin.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Я переношу свое серверное приложение из существующей системы в другую систему. К сожалению, существующая система также является сервером базы данных. В нем хранятся данные. Можно ли скопировать эти данные в другую систему? У меня есть копия всех схем таблиц.
Я понятия не имею, как поступить. Я использую PostgreSQL.

3 ответа 3

Вы можете создать дамп базы данных с помощью pg_dump, а затем восстановить ее на новом сервере с помощью psql. Вот пара команд из приведенной выше ссылки:

Создайте резервную копию:

pg_dump mydb > db.sql

Скопируйте db.sql на новый сервер (конкретная команда зависит от ОС)

Перейти на новый сервер

createdb mydb -E UTF8 (кодировку UTF8 указывать не обязательно, но я всегда это делаю)

psql -d mydb -f db.sql

Пока я отвечал, JohnP только что ответил прекрасным ответом, но предположил, что pg_hba.conf был отредактирован для разрешения удаленных подключений, а postgres.conf отредактирован для прослушивания в сети.

@Ashwin, куда бы вы ни указали. в моем примере это место, где была запущена команда.

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

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

ОБНОВЛЕНИЕ: Если вы перемещаете данные между разными версиями сервера, используйте pg_dump из последней версии, скорее всего pg_dump на новом сервере.

В прошлый раз, когда мне приходилось выполнять подобную задачу, я использовал бесплатный инструмент ETL под названием «GeoKettle»

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

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

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

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

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

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

Я хочу скопировать рабочую базу данных 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 на localhost: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 на тот же сервер или с одного сервера на другой.

Копировать базу данных 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 на сервер базы данных или с сервера базы данных на другой.

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