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

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

В данной статье мы рассмотрим различные способы переноса базы данных в PostgreSQL с одного сервера на другой на операционной системе Ubuntu 22.04.

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

Для переноса базы данных нам потребуется:

• Текущий сервер или виртуальная машина на операционной системе Ubuntu 22.04 с предустановленой СУБД PostgreSQL. База данных, которая будет перенесена, должна уже присутствовать в PostgreSQL. 

• Сервер или виртуальная машина с предустановленной ОС Ubuntu 22.04, например, запущенный в Timeweb Cloud. На сервере должна быть заранее установлена СУБД PostgreSQL. В данной статье будет использоваться 15 версия PostgreSQL.

• Опционально: заранее установленная официальная клиентская программа с графическим интерфейсом для подключения и управления базами данных PostgreSQL — pgAdmin. Установить pgAdmin можно на любую ОС, включая Windows, Linux и macOS. Установить программу можно на ваш домашний или рабочий компьютер.

Регистрация в Timeweb Cloud

Чтобы арендовать облачный или выделенный сервер в Timeweb Cloud, необходимо пройти регистрацию. 

Переходим по ссылке для регистрации нового пользователя. Мы выберем регистрацию на физическое лицо и заполним следующие данные:

• ФИО,

• адрес электронной почты.

Номер телефона можно указать по желанию. Также регистрацию можно пройти, воспользовавшись одним из сторонних сервисов: Google-аккаунт, ВКонтакте и GitHub.

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

Создание облачного сервера

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

1. Переходим на страницу авторизации и входим в аккаунт при помощи логина или адреса электронной почты и пароля или при помощи ВКонтакте, GitHub, Google.
2. После успешной авторизации отобразится панель управления текущего проекта. Переходим в раздел «Облачные серверы» и нажимаем «Создать» или «Добавить».
3. Выбираем операционную систему, которая будет установлена на сервер. В нашем случае нам необходима Ubuntu версии 22.04.
4. Выбираем регион, в котором будет находиться наш сервер. Выбирать рекомендуется тот регион, который ближе всего находится к вам физически. У каждого доступного региона справа вверху отображается ping, т.е. время, необходимое для передачи данных с вашего компьютера на сервер. Чем меньше указанное время, тем быстрее будет осуществляться передача данных.

5. Далее выбираем необходимую конфигурацию для сервера. Так как в данной статье упор делается именно на перенос базы данных, то для конфигурации сервера выберем двухъядерный процессор и 40 ГБ жесткого диска. В реальности вам необходимо выбирать именно ту конфигурацию, которая будет удовлетворять вашим потребностям для базы данных. Выбираем соответствующий тариф:

6. Далее необходимо решить, будет ли сервер доступен из внешний сети или же только из приватной (частной) сети. Если не уверены в настройках, оставьте эти параметры без изменений. Будьте внимательны, если заказываете сервер в Москве — по умолчанию для них не выдается публичный IP-адрес. Для выхода сервера в Интернет, нужно заказать плавающий IP.
7. По желанию можно оформить дополнительные услуги, включая резервные копии и защиту от DDoS-атак (последняя доступна только в Санкт-Петербурге).
8. Также заранее можно загрузить SSH-ключ, чтобы не входить на север при помощи пароля.
9. Можно задать необходимое имя для сервера которое будет отображаться в панели управления, а также выбрать проект.
10. Для создания сервера необходимо нажать на кнопку «Заказать»:

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

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

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

• Присутствует сетевое соединение между серверами. Для проверки сетевого соединения можно воспользоваться такими утилитами как ping и telnet (при использовании утилиты telnet проверить необходимо 22 и 5432 порты при условии, что они не были изменены);

• При использование firewall (например UFW, Iptables или другого ПО) убедитесь, что открыт порт 22 (для демона SSH) или другой порт, если порт по умолчанию для SSH был изменен. Также убедитесь, что открыт порт 5432 (стандартный порт, который слушает PostgreSQL) или другой порт, если порт для PostgreSQL по умолчанию был изменен;

• На двух серверах достаточно свободного места на жестких дисках.

• На серверах, где установлена PostgreSQL, необходимо убедиться, что PostgreSQL может принимать удаленные соединения. Для этого:

1. Открываем на редактирование конфигурационный файл postgresql.conf, который находится по пути /etc/postgresql/15/main/postgresql.conf. Если у вас используется иная версия, замените 15 на свою версию:

nano /etc/postgresql/15/main/postgresql.conf

Находим параметр listen_addresses. По умолчанию он закомментирован и принимает только локальные соединения, т.е. только с localhost. Указываем, с каких адресов разрешены удаленные подключения. Можно как указать конкретные адреса, с которых будет производиться подключение, так и разрешить доступ со всех адресов с помощью символа *. В рамках данной инструкции и в тестовых целях можно разрешить доступ со всех адресов:

listen_addresses = '*'

При работе в production-окружениях необходимо указывать только конкретные адреса, с которых будет доступно подключение к PostgreSQL.

Сохраняем изменения и выходим из файла.

2. Далее открываем на редактирование файл pg_hba.conf, который располагается по пути /etc/postgresql/15/main/pg_hba.conf:

nano /etc/postgresql/15/main/pg_hba.conf

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

Находим строку, в которой содержатся настройки подключения для IPv4 (под комментарием # IPv4 local connections) и приводим ее к следующему виду:

Вместо 0.0.0.0/0 (разрешен доступ со всех адресов) можно указать конкретные адреса. Сохраняем изменения и выходим из файла.

3. Перезапускаем сервер PostgreSQL:

systemctl restart postgresql

• Заранее задайте пароль для пользователя БД на двух серверах сразу (в данном примере будет использоваться пользователь postgres, который присутствует по умолчанию; под этим пользователем будут выполняться все команды в psql). Для этого переключитесь на пользователя postgres в терминале при помощи команды:

sudo -i -u postgres psql

И в консоли psql выполните запрос:

ALTER USER postgres WITH PASSWORD 'Postgres35$#';

Где postgres — это имя пользователя, а Postgres35$# — пароль для учетной записи postgres.

Перенос базы данных при помощи pg_dump и psql

Как правило, перенос базы данных осуществляется в три шага:

• Снятие резервной копии базы данных.

• Перенос созданной копии на нужный сервер (иногда данный этап соединяют с этапом восстановления из резервной копии).

• Восстановление из резервной копии на новом сервере.

Все три шага можно выполнить стандартными инструментами PostgreSQL, которые поставляются в комплекте с СУБД: pg_dump и psql.

pg_dump — утилита, предназначенная для создания резервных копий баз данных. Поддерживает множество различных параметров. Помимо выгрузки всей структуры БД, поддерживает выгрузку только определенных объектов базы данных (например, можно выгрузить только определенные таблицы или столбцы). Базу данных можно выгрузить как одним файлом, так и в виде архива одного из поддерживаемых форматов.

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

В качестве примера будет использоваться база данных с именем e-commerce, в которой хранится информация о покупках в интернет магазине. Размер тестовой базы данных составляет около 7,7 МБ. Создадим структуру базы данных. Все SQL запросы перечисленные ниже необходимо выполнять строго последовательно.

CREATE DATABASE e_commerce;

\c e_commerce

CREATE TABLE categories (
  category_id SERIAL NOT NULL PRIMARY KEY,
  category_name VARCHAR(255),
  description VARCHAR(255)
);

INSERT INTO categories (category_name, description)
VALUES
  ('Confections', 'Desserts, candies, and sweet breads'),
  ('Dairy Products', 'Cheeses'),
  ('Grains/Cereals', 'Breads, crackers, pasta, and cereal'),
  ('Meat/Poultry', 'Prepared meats'),
  ('Seafood', 'Seaweed and fish');

CREATE TABLE customers (
  customer_id SERIAL NOT NULL PRIMARY KEY,
  customer_name VARCHAR(255),
  contact_name VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR(255),
  postal_code VARCHAR(255),
  country VARCHAR(255)
);

INSERT INTO customers (customer_name, contact_name, address, city, postal_code, country)
VALUES
  ('Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
  ('Ana Trujillo Emparedados y helados', 'Ana Trujillo', 'Avda. de la Constitucion 2222', 'Mexico D.F.', '05021', 'Mexico'),
  ('Antonio Moreno Taquera', 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '05023', 'Mexico'),
  ('Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
  ('Berglunds snabbkoep', 'Christina Berglund', 'Berguvsvegen 8', 'Lulea', 'S-958 22', 'Sweden');

CREATE TABLE products (
  product_id SERIAL NOT NULL PRIMARY KEY,
  product_name VARCHAR(255),
  category_id INT,
  unit VARCHAR(255),
  price DECIMAL(10, 2)
);

INSERT INTO products (product_id, product_name, category_id, unit, price)
VALUES
  (1, 'Chais', 1, '10 boxes x 20 bags', 18),
  (2, 'Chang', 1, '24 - 12 oz bottles', 19),
  (3, 'Aniseed Syrup', 2, '12 - 550 ml bottles', 10),
  (4, 'Chef Antons Cajun Seasoning', 2, '48 - 6 oz jars', 22),
  (5, 'Chef Antons Gumbo Mix', 2, '36 boxes', 21.35);

CREATE TABLE orders (
  order_id SERIAL NOT NULL PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

INSERT INTO orders (order_id, customer_id, order_date)
VALUES
  (10248, 90, '2021-07-04'),
  (10249, 81, '2021-07-05'),
  (10250, 34, '2021-07-08'),
  (10251, 84, '2021-07-08'),
  (10252, 76, '2021-07-09');

В качестве первого варианта, рассмотрим перенос базы данных с сервера с IP-адресом 80.90.188.61 на сервер с IP-адресом 81.200.149.98. Для это воспользуемся следующей командой:

pg_dump -h localhost -U postgres e_commerce | psql -h 81.200.149.98 -U postgres e_commerce

Где:

• pg_dump — вызов утилиты pg_dump для создания резервной копии базы данных;
• -h localhost — адрес сервера (IP-адрес или доменное имя), на котором находится база данных. В данном примере адрес сервера БД совпадает с адресом самого сервера. Если база данных находится на другом сервере, то при наличии сетевого доступа можно указать адрес удаленной БД;
• -U postgres — имя пользователя, из-под которого производится подключение к БД;
• e_commerce — имя базы данных, которая будет перенесена;
• psql — вызов утилиты psql. В данном случае утилита psql выполняет сразу 2 действия: подключается к удаленному серверу PostgreSQL и загружает ранее указанную базу данных;
• -h 81.200.149.98 — адрес сервера (IP-адрес или доменное имя), на который будет произведен перенос базы данных;
• -U postgres - имя пользователя, из-под которого производится подключение к БД;
• e_commerce — имя базы данных, в которую будет скопирована вся структура переносимой базы данных. Данная БД должна быть создана заранее. Для этого на сервер, на который будет перенесена база данных, необходимо войти под необходимым пользователем, например postgres, запустить утилиту psql и ввести запрос: 

CREATE DATABASE e_commerce;

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

Перенос базы данных при отсутствии удаленного доступа к серверу PostgreSQL

Если у вас отсутствует удаленный доступ к серверу PostgreSQL, то можно сначала сохранить базу данных в файл или архив, далее скопировать файл с резервной копией базы данных при помощи утилиты scp и загрузить её на хост с сервером PostgreSQL. Для этого воспользуемся командой:

pg_dump -h localhost -U postgres e_commerce > e_commerce.sql && scp e_commerce.sql db-admin@81.200.149.98:/var/lib/postgresql

При выполнении команды во время копирования файлов с помощью утилиты scp будет запрошен пароль пользователя postgres в системе, а не пароль пользователя postgres, заданный внутри СУБД.

Где:

• pg_dump — вызов утилиты pg_dump для создания резервной копии базы данных;
• -h localhost — адрес сервера (IP-адрес или доменное имя), на котором находится база данных. В данном примере адрес сервера БД совпадает с адресом самого сервера. Если база данных находится на другом сервере, то при наличии сетевого доступа можно указать адрес удаленной БД;
• -U postgres — имя пользователя, из-под которого производится подключение к БД;
• e_commerce — имя базы данных, которая будет перенесена;
• e_commerce.sql — имя файла в формате .sql, в который будет сохранена структура базы данных и все ее данные;
• scp — утилита для защищенного копирования файлов между хостами. Для передачи данных и обеспечения защиты использует протокол SSH;
• db-admin@81.200.149.98:/var/lib/postgresql — указывает имя пользователя_на_удаленном_сервере@адрес_удаленного_сервера:полный_путь, куда будет сохранен файл с резервной копией.

После ввода команды необходимо сперва ввести пароль от учетной записи пользователя БД (в данном примере это пользователь postgres) далее ввести пароль пользователя удаленного сервера (в данном примере это пользователь db-admin).

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

CREATE DATABASE e_commerce;

Далее выходим из оболочки psql и в терминале выполняем команду:

psql e_commerce < e_commerce.sql

Начнется процесс импорта данных в созданную базу данных.

Создание сжатого архива резервной копии базы данных

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

pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip db-admin@81.200.149.98:/var/lib/postgresql

При выполнении команды во время копирования файлов с помощью утилиты scp будет запрошен пароль пользователя postgres в системе, а не пароль пользователя postgres, заданный внутри СУБД.

Импорт архива происходит точно так же, как и с файлом с расширением .sql (дополнительно разархивировать архив не надо):

psql e_commerce < e_commerce.tar.gzip

Создание временной метки (Timestamp) в имени архива

Если вам необходимо знать точную дату и время, когда была создана резервная копия базы данных, вы можете включить её в имя создаваемого файла. Для этого используется команда date и формат даты. В примере ниже будет использован формат даты «день-месяц-год»:

pg_dump -h localhost -U postgres e_commerce > e_commerce_$(date +%d-%m-%y).sql

Перенос базы данных при помощи программы pgAdmin

Помимо использования консольных утилит pg_dump и psql, перенести базу данных можно при помощи графического интерфейса, а именно используя программу pgAdmin. Единственное ограничение, существующее в pgAdmin, заключается в том, что при попытке восстановления файла резервной копии базы данных программа не поддерживает формат обычного текстового формата (plain text). В этом случае при сохранении файла резервной копии необходимо сохранять её в виде tar-архива или директории (будет показано далее). 

1. Запускаем pgAdmin и подключаемся к серверу PostgreSQL, на котором располагается необходимая база данных. Для этого нажимаем правой кнопкой по меню Object Explorer, которое находится слева. Далее в контекстном меню выбираем Register, потом Server:

2. В появившемся окне задаем любое удобное имя для нового соединения в поле Name, например, my_db:

3. Переходим в раздел Connections и заполняем следующие поля:

• • Host name/address — IP-адрес или доменное имя хоста, на котором находится сервер PostgreSQL. Если pgAdmin установлен на одном хосте с PostgreSQL, то в качестве адреса можно указать localhost или 127.0.0.1. Если же СУБД установлена на удаленном хосте, то прописываем адрес нужного хоста. Также необходимо убедиться, что до удаленного сервера с PostgreSQL проходит сетевое соединение и разрешен удаленный доступ;
  • Port — порт, который слушает PostgreSQL. Порт по умолчанию — 5432. Если же стандартный порт был изменен, то указываем необходимый;
  • Maintenance database — имя базы данных, к которой будет произведено подключение и для которой будет создаваться резервная копия;
  • Username — имя пользователя, из-под которого будет произведено подключение к базе данных;
  • Password — пароль от учетной записи пользователя.

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

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

4. Для того чтобы сделать резервную копию, щелкаем правой кнопкой мыши по имени базы данных и выбираем пункт Backup:
   

5. В появившемся окне заполняем следующие поля:

• • Filename — имя для файла резервной копии БД. При нажатии на значок папки справа можно выбрать путь, куда будет сохранен файл резервной копии;
  • Format — формат файла, в котором будет сохранена резервная копия. Необходимо выбрать одно значение из выпадающего списка;
  • Encoding — кодировка для хранения текста базы данных. Рекомендуется выбрать универсальный вариант кодировки — UTF8.

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

После того как необходимые данные выбраны, нажимаем на кнопку Backup.

Начнется процесс резервного копирования базы данных. При нажатии на кнопку View Processes:

Можно наблюдать за процессом резервного копирования:

6. Для того чтобы восстановить базу данных из резервной копии, заранее создаем пустую базу данных в оболочке psql:

CREATE DATABASE e_commerce;

Далее в pgAdmin подключаемся к другому серверу PostgreSQL, указав в качестве подключаемой БД e_commerce.

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

7. В открывшемся меню заполняем следующие поля:

• • Format — из выпадающего списка выбираем формат файла, в котором сохранена резервная копия. Как уже было упомянуто ранее, pgAdmin не поддерживает восстановление из формата plain text (сюда относятся любые файлы, расширение которых не .tar и которые сохранены не в виде директорий). Если попытаться загрузить файл обычного формата (например, с расширением .sql), то возникнет ошибка: pg_restore: error: input file appears to be a text format dump. Please use psql.
  • Filename — указываем полный путь до файла с резервной копией. Путь можно указать как вручную, так и воспользовавшись проводником (иконка директории, располагающаяся справа).

После этого нажимаем на кнопку Restore.

Начнется процесс восстановления из резервной копии. После того как процесс будет полностью завершен, появится всплывающее окно с надписью Process completed:

Заключение

СУБД PostgreSQL предоставляет несколько способов организации переноса базы данных с одного хоста на другой. Также благодаря обширному функционалу встроенных инструментов открываются расширенные возможности гибкого резервного копирования ваших баз данных.