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

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

Как перенести одну из нескольких баз в другое место (к примеру на другой диск E:) не затрагивая месторасположение остальных БД ?

detach базы, копирование (перенос) файлов, attach

Ну или в плохом случае — полный бекап и потом рестор в новую базу.
Но первый лучше.

(1) а постгрес умеет работать с базами на разных дисках?

А Можно поподробней я не очень силен в postgree *((
как поменять место хранения всех баз я представляю, а вот как отжелить одну и перенести в другое место не особо

1) Выясняем в какой папке лежит нужная тебе БД. они имеют цифровые номера
2) Останавливаем постгес
3) переносим содержимое этой папки на нужный тебе диск
4) монтируем этт диск в исходную папку (в виндовсе это сделать можно тоже)
5) убеждаемся что с правами доступа к файлам ОК
6) запускаем пострегс

Как перенести базу данных 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 на другой сервер с помощью pgAdmin 4

В этой статье расскажем, как правильно осуществить перенос базы данных PostgreSQL с одного сервера на другой. Рассмотрим несколько вариантов создания дампа и его дальнейшего импорта на новый VPS/VDS.

О чем вы узнаете в этой статье?

• Создание резервной копии БД PostgreSQL в pgAdmin 4
• Импорт дампа БД PostgreSQL в pgAdmin 4
• Экспортирование и импортирование базы данных в простом формате SQL

• написание SQL-запросов;
• разработка процедур;
• администрирование БД PostgreSQL.

Прелесть pgAdmin 4 редакции в том, что она работает в режиме веб-приложения, так что с программой можно работать удаленно буквально из любой точки мира. Это программное обеспечение совместимо со всеми актуальными версиями операционных систем Linux, Windows и macOS, что лишь подчеркивает его универсальность. Но не будем сильно отклоняться от темы и перейдем к рассмотрению процесса переноса базы данных PostgreSQL на другой сервер.

Создание резервной копии БД PostgreSQL в pgAdmin 4

1. В обозревателе выберите целевую базу данных.
2. Кликните на неё ПКМ и выберите пункт «Резервная копия».

1. Tar (tar) – в этом случае база данных не сжимается.
2. Простой (plain). На выходе получаем текстовый SQL-скрипт, содержащий инструкции. Этот формат хорош тем, что позволяет на ходу редактировать дамп базы данных через любой удобный текстовый редактор. Если после создания дампа планируете что-то изменять в нем до импорта на новый сервер, то этот вариант оптимален.
3. Каталог (directory). Создается каталог, где все таблицы и объемные объекты резервируются в виде отдельных файлов. Directory-формат применяет алгоритмы сжатия и позволяет выгружать данные в несколько потоков параллельно – удобно для больших БД.

Как мы уже отметили выше, в большинстве случаев следует оставлять формат, установленный по умолчанию – это custom. В результате сжатия вы получите файл в расширении .backup, и система выдаст сообщение об успешном завершении.

Импорт дампа БД PostgreSQL в pgAdmin 4

1. На новом сервере заходим в pgAdmin и создаем пустую базу данных. Для этого кликните правой кнопкой по вкладке «Базы данных» и выберите пункт «Создать».
2. Теперь по созданной базе данных во всё той же вкладке «Базы данных» кликните правой кнопкой и выберите пункт «Восстановить», или «Restore».
3. Далее укажите формат дампа базы данных и путь к файлу с расширением .backup, который мы создали несколькими минутами ранее.

После подтверждения начнется процесс импорта дампа БД PostgreSQL в pgAdmin 4, он может длиться от доли секунды до нескольких минут, всё зависит от производительности серверного железа и размера файла.

Экспортирование и импортирование базы данных в простом формате SQL

• Использовать команды INSERT.
• INSERT с указанием столбцов.

1. Через контекстное меню целевой БД заходим в запросник Query Tool.
2. Нажимаем на пункт «Открыть файл», и в появившемся окошке выбираем дамп базы данных в простом формате SQL, созданный ранее.
3. Жмем «Выполнить».

Если всё сделали правильно, процесс восстановления займет пару мгновений, и вы сможете приступить к дальнейшей работе. Импорт дампа БД в формате SQL подойдет в тех ситуациях, когда нужно перенести базу данных с одной ОС на другую – например, с Windows на Linux, с macOS на Debian и т.д.

Напоследок добавим, что при необходимости переноса объемной базы данных, размер которой исчисляется несколькими десятками или даже сотнями гигабайт, разумнее прибегнуть к использованию консольных утилит pg_dump или pg_dumpall, в обход графического интерфейса pgAdmin 4. На этом мы заканчиваем наш материал, спасибо за внимание!