Перенос базы postgresql с windows на linux

I have Postgres DB on machine with Windows OS, also I have virtual machine with Linux (Ubuntu). I need to move data from Windows to Linux. Thanks in advance!

I have Postgres DB on machine with Windows OS, also I have virtual machine with Linux (Ubuntu). I need to move data from Windows to Linux.
Thanks in advance!

asked Oct 11, 2017 at 14:36

Viktor Predybaylo's user avatar

2

you can use below query to restore a database from one server to another 

pg_dump -C -h SourceServer -U SourceUser  SourceDB | psql -h TargetHost -U TargetUser  TargetDB

Password can be used from pgpass.conf

answered Oct 11, 2017 at 19:03

YogeshR's user avatar

YogeshRYogeshR

1,4362 gold badges20 silver badges38 bronze badges

I had to transfer a DB from my local machine ( Windows 11) to an Linux (Ubuntu Bionic) server, so I did the following:

  1. Dumped my DB into a backup file with extension .sql by using the following command

  • Open the terminal

  • Go to the folder where postgresql is installed in my case it was

    C:Program FilesPostgreSQL14bin

  • In the terminal write

    cd C:Program FilesPostgreSQL14bin

  • Type the following command, change the <db_name> and <folder_name> as required and press enter.

    pg_dump.exe -U postgres -d <db_name> -f D:<folder_name><db_name>.sql

  • Enter your password and it will create the backup in the mentioned folder.

  1. Connect to the Linux Server and copy the <db_name>.sql file

    scp  D:<folder_name><db_name>.sql  username@ip_address:/<destination_folder>

  1. Install postgresql on the server

    sudo apt-get install postgresql postgresql-contrib

  • Check the status

    service postgresql status

  • Create DB

    sudo su postgres
psql -U postgres -c “create database <db_name>”

  • Import the data from the dump file to this new DB.

    psql db_name < /path/db_name.sql

answered Dec 28, 2021 at 7:03

coderina's user avatar

coderinacoderina

1,44511 silver badges19 bronze badges

Источник

Just for the record, you should ask in PostgreSQL mailing list.

My personal idea is the binary format is fixed, so no problem.

Anyway, just to try, I:

a) copy the pgdata directory, from PG9.5 from Windows Server 2012 to Linux;

b) compile 9.5 on Linux;

c) change the specific Windows options in postgresql.conf;

d) run it…

And it starts, but it complains about locale (I have to investigate further to figure out):

LOG: database system was interrupted; last known up at 2020-02-27
03:57:27 CET

LOG: database system was not properly shut down; automatic recovery
in progress

LOG: redo starts at 34/DFD49EF8

LOG: invalid record length at 34/DFE33560

LOG: redo done at 34/DFE33538

LOG: last completed transaction was at log time 2020-02-27
04:00:52.844739+01

LOG: MultiXact member wraparound protections are now enabled

LOG: database system is ready to accept connections

LOG: autovacuum launcher started

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «postgres»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «postgres»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «template1»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «template0»

FATAL: role «gelma» does not exist

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE
«Italian_Italy.1252», which is not recognized by setlocale().

HINT: Recreate the database with another locale or install the
missing locale.

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE
«Italian_Italy.1252», which is not recognized by setlocale().

Anyway the server is up and running.
Also using pg_dump I don’t think the locale is automagically converted.

So, finding the original super user and how to change collate/locale should be the only things left.

Источник

Just for the record, you should ask in PostgreSQL mailing list.

My personal idea is the binary format is fixed, so no problem.

Anyway, just to try, I:

a) copy the pgdata directory, from PG9.5 from Windows Server 2012 to Linux;

b) compile 9.5 on Linux;

c) change the specific Windows options in postgresql.conf;

d) run it…

And it starts, but it complains about locale (I have to investigate further to figure out):

LOG: database system was interrupted; last known up at 2020-02-27
03:57:27 CET

LOG: database system was not properly shut down; automatic recovery
in progress

LOG: redo starts at 34/DFD49EF8

LOG: invalid record length at 34/DFE33560

LOG: redo done at 34/DFE33538

LOG: last completed transaction was at log time 2020-02-27
04:00:52.844739+01

LOG: MultiXact member wraparound protections are now enabled

LOG: database system is ready to accept connections

LOG: autovacuum launcher started

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «postgres»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «postgres»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «template1»

FATAL: no pg_hba.conf entry for host «[local]», user «gelma»,
database «template0»

FATAL: role «gelma» does not exist

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE
«Italian_Italy.1252», which is not recognized by setlocale().

HINT: Recreate the database with another locale or install the
missing locale.

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE
«Italian_Italy.1252», which is not recognized by setlocale().

Anyway the server is up and running.
Also using pg_dump I don’t think the locale is automagically converted.

So, finding the original super user and how to change collate/locale should be the only things left.

Источник

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

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

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

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

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

C:UsersUser-NDesktopБД  

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

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

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

 "C:Program FilesPostgreSQL10binpsql" -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:UsersUser-NDesktopCSVticket_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:UsersUser-NDesktopCSVairports.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:UsersUser-NDesktopCSVflights_SVO.csv' CSV HEADER DELIMITER ',';

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

Источник
title description author ms.author ms.service ms.subservice ms.topic ms.date ms.openlocfilehash ms.sourcegitcommit ms.translationtype ms.contentlocale ms.lasthandoff ms.locfileid

Дамп и восстановление — база данных Azure для PostgreSQL — один сервер

Описывает, как извлечь базу данных PostgreSQL в файл дампа и выполнить восстановление из файла, созданного pg_dump в базе данных Azure для PostgreSQL-Single Server.

sr-msft

srranga

postgresql

migration-guide

how-to

09/22/2020

16166183b56b371fe8338894f83dbacf2e659c53

772eb9c6684dd4864e0ba507945a83e48b8c16f0

MT

ru-RU

03/20/2021

103563561

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

[!INCLUDEapplies-to-postgres-single-flexible-server]

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

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

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

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

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

Создание файла дампа, содержащего загружаемые данные, с помощью pg_dump

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

pg_dump -Fc -v --host=<host> --username=<name> --dbname=<database name> -f <database>.dump

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

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

Восстановите данные в целевую базу данных Azure для PostgreSQL с помощью pg_restore

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

pg_restore -v --no-owner --host=<server name> --port=<port> --username=<user-name> --dbname=<target database name> <database>.dump

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

[!NOTE]
Если серверу PostgreSQL требуются подключения TLS/SSL (по умолчанию в базе данных Azure для серверов PostgreSQL), задайте переменную среды, PGSSLMODE=require чтобы pg_restore средство подключается к TLS. Без TLS ошибка может быть прочитана FATAL: SSL connection is required. Please specify SSL options and retry.

В командной строке 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. Чтобы свести к минимуму время, необходимое для завершения миграции, можно использовать следующие параметры с командами резервного копирования и восстановления.

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

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

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

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

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

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

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

  • Для параллелизации восстановления необходимо выполнить восстановление с помощью коммутаторов-FC и-j # . # число ядер на целевом сервере. Также можно попробовать # установить в два раза больше, чем количество ядер целевого сервера, чтобы увидеть влияние. Пример:

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

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

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

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

  • Можно также изменить файл дампа, добавив команду set synchronous_commit = off; в начале и команду set synchronous_commit = on; в конце. Если не включить ее в конце, прежде чем приложения изменят данные, это может привести к последующей потере данных.

  • Перед восстановлением рассмотрите возможность выполнения следующих действий на целевом сервере Базы данных Azure для PostgreSQL.

    • Отключите отслеживание производительности запросов, так как эти статистические данные не нужны во время миграции. Вы можете сделать это, задав для pg_stat_statements.track, pg_qs.query_capture_mode и pgms_wait_sampling.query_capture_mode значение NONE.

    • Используйте номер SKU с высоким объемом ресурсов вычисления и памяти, например номер с оптимизацией для операций в памяти с 32 виртуальными ядрами, чтобы ускорить миграцию. Вы можете легко вернуться к предпочитаемому номеру SKU после завершения восстановления. Чем выше номер SKU, тем большего параллелизма можно достичь, увеличив значение соответствующего параметра -j в команде pg_restore.

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

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

Следующие шаги

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

Добрый день!

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

Версия PostgreSQL на Windows машине и на linux: 9.4

База данных на Windows имеет следующие параметры:

  • Кодировка: UTF-8
  • Сопоставление: Russian_Russian.1251

  • Тип символа: Russian_Russian.1251

    postgres=# l

Список баз данных

Имя | Владелец | Кодировка | LC_COLLATE | LC_CTYPE | Права доступа<br>
-----------+----------+-----------+---------------------+---------------------+-----------------------<br>
postgres | postgres | UTF8 | Russian_Russia.1251 | Russian_Russia.1251 |<br>
project | postgres | UTF8 | Russian_Russia.1251 | Russian_Russia.1251 |<br>
template0 | postgres | UTF8 | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres +<br>
| | | | | postgres=CTc/postgres<br>
template1 | postgres | UTF8 | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres +<br>
| | | | | postgres=CTc/postgres<br>
database | postgres | UTF8 | Russian_Russia.1251 | Russian_Russia.1251 |

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

postgres=# CREATE DATABASE database WITH OWNER = postgres ENCODING = 'UTF-8' LC_COLLATE = 'ru_RU.cp1251' LC_CTYPE = 'ru_RU.cp1251' CONNECTION LIMIT = -1;

ОШИБКА: кодировка «UTF8» не соответствует локали «ru_RU.cp1251»
ПОДРОБНОСТИ: Для выбранного параметра LC_CTYPE требуется кодировка «WIN1251».

То есть либо всё должно быть в UTF-8, либо в WIN1251.

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

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

И как быть в такой ситуации? Как перенести базу данных из Windows в Linux с такими параметрами?

Помогите, пожалуйста.

Источник

Понравилась статья? Поделить с друзьями:
  • Перенос базы mysql на другой сервер windows
  • Перенос баз postgresql на другой диск windows
  • Перенос профиля пользователя windows 10 transwiz
  • Перенос активации windows 10 на другой компьютер
  • Перенос профиля пользователя windows 10 profwiz