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

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

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

cloud

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

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

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

Картинка с сайта: timeweb.cloud

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

Картинка с сайта: timeweb.cloud

1. Далее необходимо решить, будет ли сервер доступен из внешний сети или же только из приватной (частной) сети. Для выхода сервера в Интернет, нужно заказать плавающий IP.
2. По желанию можно оформить дополнительные услуги, включая резервные копии и защиту от DDoS-атак (последняя доступна в Санкт-Петербурге и Москве).
3. Также заранее можно загрузить SSH-ключ, чтобы не входить на север при помощи пароля.
4. Можно задать необходимое имя для сервера которое будет отображаться в панели управления, а также выбрать проект.
5. Для создания сервера необходимо нажать на кнопку «Заказать»:

Картинка с сайта: timeweb.cloud

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

Картинка с сайта: timeweb.cloud

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

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

systemctl restart postgresql

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

sudo -i -u postgres psql

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

ALTER USER postgres WITH PASSWORD 'nsH7z*0kl>&7?7';

Где postgres — это имя пользователя, а 'nsH7z*0kl>&7?7' — пароль для учетной записи 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, то можно сначала сохранить базу данных в файл или архив, далее скопировать файл с резервной копией базы данных при помощи утилиты 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:

Картинка с сайта: timeweb.cloud

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

Картинка с сайта: timeweb.cloud

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

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

Картинка с сайта: timeweb.cloud

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

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

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

Картинка с сайта: timeweb.cloud

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

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

Картинка с сайта: timeweb.cloud

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

Картинка с сайта: timeweb.cloud

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

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

Картинка с сайта: timeweb.cloud

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

Картинка с сайта: timeweb.cloud

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

CREATE DATABASE e_commerce;

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

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

Картинка с сайта: timeweb.cloud

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

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

Картинка с сайта: timeweb.cloud

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

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

Картинка с сайта: timeweb.cloud

Заключение

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

Migrating the data cluster from C:\ to E:\

I’m new to working with large data in Windows, and ran into some unexpected difficulty when the PostgreSQL database ran out of space on my server’s C drive. It might also be a good idea to isolate your postgres database from a location other than the C:\Program Files\ folder for permissions/security/data integrity reasons (I had a reference for this but I lost it). Here’s how to move your PostgreSQL data directory

Basing myself on this dba.stackex answer and this wiki post

1. Stop the PostgreSQL service

Go to Start > Services. Scroll down to postgresql-x64-9.5 and right-click Stop

2. Copy data

I copied the data with File Explorer from C:\Program Files\PostgreSQL\9.5\data\ to E:\pg_db\data

3. Modify postgresql.conf (probably not necessary if you move everything to the new location)

I edited the following lines

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = 'E:\pg_db\data'		# use data in another directory
					# (change requires restart)
hba_file = 'C:\Program Files\PostgreSQL\9.5\data\pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = 'C:\Program Files\PostgreSQL\9.5\data\pg_ident.conf'	# ident configuration file
					# (change requires restart)

4. Modify the command that starts PostgreSQL

If you Right Click to Properties on the PostgreSQL services in Services, you’ll notice that the startup command is

"C:\Program Files\PostgreSQL\9.5\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.5" -D "C:\Program Files\PostgreSQL\9.5\data" -w

The -D "C:\Program Files\PostgreSQL\9.5\data" flag is indicating that the data directory is C:\Program Files\PostgreSQL\9.5\data

DO NOT EDIT THE REGISTRY

In any case, I didn’t have permission on the server I was using and it seems to be a Bad Idea(tm) see here

Direct registry modification should be avoided (because you can’t be sure what else Windows is changing when it modifies the path to exe, f.e.) unless you are absolutely sure what you do

Start the cmd prompt as an administrator by hitting the Windows button and typing cmd then right-clicking “Run as Administrator”

You can see the current configuration for PostgreSQL by typing sc qc postgresql-x64-9.5 1000 and hitting Enter.

Modify the configuration to the following with the sc command

sc config postgresql-x64-9.5 binPath= "\"C:\Program Files\PostgreSQL\9.5\bin\pg_ctl.exe\" runservice -N \"postgresql-x64-9.5\" -D \"E:\pg_db\data\" -w"

5. Change permissions for the new data directory

For the new data-dictionary folder: Right-click on it and click Properties. Under the Security Tab click “Edit...” and then “Add...”. Type “Network Service” and then click “Check Names”, make sure it has Modify and Full Control permissions and then click OK.
Equally important PostgreSQL needs to be able to “see” the data-directory (see my ServerFault.StackEx question), i.e. it needs to have read access to the parent directories above it. So Right-click on the pg_db folder and under the Security Permissions add Network Services again, but this time it only needs Read & Execute as well as List folder contents permissions.

6. Restart the Service

Go back to the Services window (if it was still open, refresh it) and Start the PostgreSQL service. You should be able to connect to it again in PGAdmin

postgresql