Перенос postgresql на другой сервер windows

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

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

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

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

Наша задача – создать копию существующей БД, а затем перенести её на новый VPS-сервер и там восстановить данные. Всё это легко делается через клиентский компьютер в веб-приложении pgAdmin 4:

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

Программа предложит вам указать имя для дампа и путь, по которому сохранится бэкап-файл. В поле «Формат» оставляем «Специальный», или «Custom», если интерфейс приложения у вас на английском. Рассматриваемый формат предполагает сжатие, и он рекомендуется для резервирования больших и средних баз данных, так будет проще осуществить перенос. Помимо него, существуют еще три формата:

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

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

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

Полученный файл необходимо перенести на другой VPS или VDS. Здесь всё просто:

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

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

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

Графическая оболочка pgAdmin 4 позволяет также осуществить экспорт базы данных в виде системных SQL инструкций. Делать нужно практически всё то же самое, только при выборе формата указываем «Простой», и дополнительно активируем пару опций во вкладке «Параметры выгрузки»:

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

Затем аналогичным образом переносим резервную копию на новый сервер и импортируем её. Стандартные функции для восстановления здесь не подойдут, вместо этого от нас потребуется выполнить SQL-скрипт, содержащийся в файле дампа. Для этого делаем следующее:

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

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

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

Here is an example using pg_basebackup

I chose to go this route because it backs up the entire database cluster (users, databases, etc.).

I’m posting this as a solution on here because it details every step I had to take, feel free to add recommendations or improvements after reading other answers on here and doing some more research.

For Postgres 12 and Ubuntu 18.04 I had to do these actions:

On the server that is currently running the database:

Update pg_hba.conf, for me located at /etc/postgresql/12/main/pg_hba.conf

Add the following line (substitute 192.168.0.100 with the IP address of the server you want to copy the database to).

host  replication  postgres  192.168.0.100/32  trust

Update postgresql.conf, for me located at /etc/postgresql/12/main/postgresql.conf. Add the following line:

listen_addresses = '*'

Restart postgres:

sudo service postgresql restart

On the host you want to copy the database cluster to:

sudo service postgresql stop

sudo su root

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

exit

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

sudo service postgresql start

Big picture — stop the service, delete everything in the data directory (mine is in /var/lib/postgreql/12). The permissions on this directory are drwx------ with user and group postgres. I could only do this as root, not even with sudo -u postgres. I’m unsure why. Ensure you are doing this on the new server you want to copy the database to! You are deleting the entire database cluster.

Make sure to change the IP address from 192.168.0.101 to the IP address you are copying the database from. Copy the data from the original server with pg_basebackup. Start the service.

Update pg_hba.conf and postgresql.conf to match the original server configuration — before you made any changes adding the replication line and the listen_addresses line (in my care I had to add the ability to log-in locally via md5 to pg_hba.conf).

Note there are considerations for max_wal_senders and wal_level that can be found in the documentation. I did not have to do anything with this.

In this article, we will discuss the process of copying a PostgreSQL database on the same server or from a server to another. 

PostgreSQL copy database within the same server: 

If a situation arises where one needs to copy a PostgreSQL database within a database server for testing purposes. PostgreSQL makes it simple to do so using the CREATE DATABASE statement as follows: 

Syntax:
CREATE DATABASE target_database 
WITH TEMPLATE source_database;

This statement copies the source_database to the target_database. For instance, to copy the dvdrental sample database which is described here and can be downloaded from here, to the dvdrental_test database, you use the following statement: 

CREATE DATABASE dvdrental_test 
WITH TEMPLATE dvdrental;

It may take a while to complete copying depending upon the size of the original database. 

PostgreSQL copy database from a server to another: 

There are many ways to copy a database between various PostgreSQL database servers. The connection between servers grows slower as the database gets larger. One way of doing so is to create a database dump and restore the same dump to another server. To do so the following commands need to be followed: 

• Step 1: Create a Dump file of the source database.

pg_dump -U postgres -d source_database -f source_database.sql

• Step 2: Copy the dump file to the remote server. 
• Step 3: Create a new database in the remote server where you want to restore the database dump:

CREATE DATABASE target_database;

• Step 4: Restore the dump file on the remote server: 

psql -U postgres -d target_database -f source_database.sql

Example: 
Here we will copy the dvdrental database from the local server to the remote server. First, we will dump the dvdrental database into a dump file e.g., dvdrental.sql: 

pg_dump -U postgres -O dvdrental dvdrental.sql

Then we will copy the dump file to a remote server and we will create the dvdrental database on the remote server: 

CREATE DATABASE dvdrental;

Now, we will restore the dump file that we just created into the remote server: 

psql -U postgres -d dvdrental -f dvdrental.sql

For high-speed connections between servers or for smaller databases, you can also use the following command: 

pg_dump -C -h local -U localuser source_database | psql -h remote -U remoteuser target_database

For instance, if one desires to copy the dvdrental database from the localhost to the remote server, you do it as follows: 

pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental

In this article, we will discuss the process of copying a PostgreSQL database on the same server or from a server to another. 

PostgreSQL copy database within the same server: 

If a situation arises where one needs to copy a PostgreSQL database within a database server for testing purposes. PostgreSQL makes it simple to do so using the CREATE DATABASE statement as follows: 

Syntax:
CREATE DATABASE target_database 
WITH TEMPLATE source_database;

This statement copies the source_database to the target_database. For instance, to copy the dvdrental sample database which is described here and can be downloaded from here, to the dvdrental_test database, you use the following statement: 

CREATE DATABASE dvdrental_test 
WITH TEMPLATE dvdrental;

It may take a while to complete copying depending upon the size of the original database. 

PostgreSQL copy database from a server to another: 

There are many ways to copy a database between various PostgreSQL database servers. The connection between servers grows slower as the database gets larger. One way of doing so is to create a database dump and restore the same dump to another server. To do so the following commands need to be followed: 

• Step 1: Create a Dump file of the source database.

pg_dump -U postgres -d source_database -f source_database.sql

• Step 2: Copy the dump file to the remote server. 
• Step 3: Create a new database in the remote server where you want to restore the database dump:

CREATE DATABASE target_database;

• Step 4: Restore the dump file on the remote server: 

psql -U postgres -d target_database -f source_database.sql

Example: 
Here we will copy the dvdrental database from the local server to the remote server. First, we will dump the dvdrental database into a dump file e.g., dvdrental.sql: 

pg_dump -U postgres -O dvdrental dvdrental.sql

Then we will copy the dump file to a remote server and we will create the dvdrental database on the remote server: 

CREATE DATABASE dvdrental;

Now, we will restore the dump file that we just created into the remote server: 

psql -U postgres -d dvdrental -f dvdrental.sql

For high-speed connections between servers or for smaller databases, you can also use the following command: 

pg_dump -C -h local -U localuser source_database | psql -h remote -U remoteuser target_database

For instance, if one desires to copy the dvdrental database from the localhost to the remote server, you do it as follows: 

pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental

Не простая операция,  если вы не имели опыта настройки / работы с postgresql до сих пор. Расскажу поэтапно как выгрузить дамп базы, и как затем этот дамп загрузить в нужном месте :).

Фокусы, которые тут не работают

Базы mySQL я иногда экспортирую не с помощью скрипта, а копирую файлы данных. В каталоге, где они хранятся, название подкаталогов — это имена баз, а каждая таблица состоит из 2-3 файлов, с именем как у самой таблицы. Копирование сработает, если основные версии серверов совпадает.

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

В примере папка с базами: -D /var/lib/postgresql/9.3/main

Названия таблиц и баз вы здесь не увидите в явном виде. Копирование файлов будет бесполезно.

Самый главный админ

Первое, что надо усвоить — операции с postgresql удобно выполнять с правами postgresql же суперадмина. На юзера root PSQL чихал и не признает его авторитета. По умолчанию, супер-юзер имеет имя postgres.

Переходим под эту учетку.

Посмотреть список баз данных

Чтобы узнать какие базы у вас есть в наличии можно использовать команду:

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

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

Получение дампа базы postgresql

Для создания дампа базы служит команда pg_dump. Мы направим её вывод в файл через gzip.

Здесь:

myDBname — это название базы данных, а myDB-filedump.gz — название файла, куда будет сохранен дамп.

Расшифровка ключей:

-h [host] : явное указание хоста, значение по умолчанию localhost или значение из переменной окружения PGHOST.

-О : пропускает команды для установки владельца таблиц, видов и т.д.

-F {c|t|p} — формат вывода данных custom, tar, plane text.

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

-C, —create — добавляет команду для создания БД, я покажу как создать её вручную.

-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

Есть и более мощная команда, которая выгружает сразу все базы:

Справку по ключам можно посмотреть так:

Создание новой базы из консоли PostpreSQL

Получив дамп базы, развернем его на новом месте. Если вы не «зашили» в дамп создание базы, то вам нужно будет предварительно её создать (если она не была создана ранее).

Переходим в консоль postgresql:

В консоли введите SQL команду на создание базы данных. Как видите, я авторизован как супер-юзер PSQL. Для выхода из консоли можно нажать CTRL+D.

Не забывайте про «точку с запятой» в конце команды — без неё команда не будет выполнена. В случае успеха, вы увидите в консоли отклик сервера — «CREATE DATABASE».

Заливка дампа базы postgresql

Для начала распакуем файл из обертки gz:

Теперь все готово для импорта данных.

Так база данных myNewBase будет заполнена из файла myDB-filedump.

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

Базы данных MySQL можно перенести простым копированием файлов базы. В каталоге, где они хранятся, название подкаталогов — это имена баз, а каждая таблица состоит из 2-3 файлов, с именем как у самой таблицы. Прямое копирование сработает, если основные версии серверов совпадают.

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

ps auxw | grep postgres | grep -- -D

Названия таблиц и баз в явном виде отсутствует. Копирование файлов будет бесполезно.

Главный админ

Операции с postgresql удобно выполнять с правами postgresql суперадмина. Права системного пользователя root PSQL не признает. По умолчанию, суперюзер имеет имя postgres.

Переходим под эту учетку

su postgres

Посмотреть список баз данных

Чтобы узнать какие базы есть используется команда

psql -l

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

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

Получение дампа базы postgresql

Для создания дампа базы служит команда pg_dump. Для сжатия полученного дампа направляем вывод в файл через gzip.

pg_dump -h localhost -O -F t -c -U postgres myDBname | gzip -c > myDB-filedump.gz

Здесь:
myDBname — это название базы данных, а myDB-filedump.gz — название файла, куда будет сохранен дамп.

Расшифровка ключей:
-h [host] : явное указание хоста, значение по умолчанию localhost или значение из переменной окружения PGHOST.
-О : пропускает команды для установки владельца таблиц, видов и т.д.
-F {c|t|p} — формат вывода данных custom, tar, plane text.
-U [username] — пользователь, чьими правами доступа нужно воспользоваться при запросе данных. По умолчанию берется текущий пользователь. При необходимости программа запросит пароль.
-C, —сreate — добавляет команду для создания БД, я покажу как создать её вручную.
-c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

Есть и более мощная команда, которая выгружает сразу все базы:

pg_dumpall | gzip -c > allDB-filedump.gz

Справку по ключам можно посмотреть так:

pg_dump --help
pg_dumpall --help

Создание новой базы из консоли PostpreSQL

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

Переходим в консоль postgresql:

psql

В консоли вводим SQL команду на создание базы данных. Для выхода из консоли можно нажать CTRL+D или использовать команду q.

В конце команд консоли обязательно должна присутствовать «точка с запятой»  — без неё команда не будет выполнена. В случае успешного выполнения, в консоли будет отклик сервера — «CREATE DATABASE».

Заливка дампа базы postgresql

Распаковываем файл дампа из архива gz:

gunzip myDB-filedump.gz

Импортируем дамп в базу.

psql -d myNewBase -f myDB-filedump

База данных myNewBase будет заполнена из файла myDB-filedump.