Копирование базы данных PostgreSQL на другой сервер

Question

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

Answer 1

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

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

или же

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

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

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

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

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

или же

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

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

Answer 2

pg_dump the_db_name > the_backup.sql

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

psql the_new_dev_db < the_backup.sql

Answer 3

Используйте pg_dump, а затем psql или pg_restore - в зависимости от того, выберете ли вы опции -Fp или -Fc для pg_dump.

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

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

Answer 4

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

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

Проверьте документы по миграции.

Answer 5

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

Answer 6

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

PGPASSWORD={{export_db_password}} pg_dump --create -h {{export_db_host}} -U {{export_db_user}} {{export_db_name}} | PGPASSWORD={{import_db_password}} psql -h {{import_db_host}} -U {{import_db_user}} {{import_db_name}}

Answer 7

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

 pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 eg. pg_dump -U postgres mydbname -f mydbnamedump.sql

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

eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/

На удаленном компьютере выполните следующую команду в папке ~/some/ для восстановления БД.

 psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

 eg. psql -U postgres -d mynewdb -f mydbnamedump.sql

Answer 8

---

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

---

Импортируйте свою базу данных обратно: psql db_name < backup.sql

Answer 9

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

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

sudo su - postgres
pg_dump -c --inserts old_db_name > dump.sql

Мне пришлось использовать пользователя Postgres Linux для создания дампа. Также мне пришлось использовать -c, чтобы форсировать создание базы данных на новом сервере. --inserts говорит ему использовать синтаксис INSERT(), который в противном случае не работал бы для меня:(

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

sudo su - postgres
psql new_database_name < dump.sql

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

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

Answer 10

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

Ссылка взята из этого блога:

Linux Bash Shell Script для переноса данных между серверами PostgreSQL:

#!/bin/bash
psql \
    -X \
    -U user_name \
    -h host_name1 \
    -d database_name \
    -c "\\copy tbl_Students to stdout" \
| \
psql \
    -X \
    -U user_name \
    -h host_name2 \
    -d database_name \
    -c "\\copy tbl_Students from stdin"

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

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

Answer 11

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

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

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

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

---

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

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

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

host  replication  postgres  192.168.0.100/32  trust

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

listen_addresses = '*'

Перезапустите postgres:

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

---

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

служба sudo postgresql stop

sudo su root

rm -rf / var / lib / postgresql / 12 / основной /*

Выход

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

sudo service postgresql start

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

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

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

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

Answer 12

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

• Подключитесь к исходному и целевому серверам
• Щелкните правой кнопкой мыши исходную базу данных> резервная копия
• Щелкните правой кнопкой мыши целевой сервер> создать> базу данных. Используйте те же свойства, что и исходная база данных (вы можете увидеть свойства исходной базы данных, щелкнув правой кнопкой мыши> свойства)
• Щелкните правой кнопкой мыши созданную базу данных> восстановить.

Answer 13

Это самое простое решение:

pg_dump --dbname=postgresql://[user]:[password]@[host]:[port]/[database] | psql --dbname=postgresql://[user]:[password]@[host]:[port]/[database]

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

Вторая часть,psql, загружается на этот адрес.

Пример:

pg_dump --dbname=postgresql://postgres:[e-mail]:5432/originaldatabasename | psql --dbname=postgresql://postgres:[e-mail]:5432/newdatabasename

Я обнаружил, что в предыдущих примерах не указано, как создать строку --dbname, или в документации. Ссылка: Каков формат строки/URL-адреса подключения PostgreSQL?