Резервное копирование и восстановление PostgreSQL

Введение

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

Вы начнете с создания образца базы данных и заполнения ее данными. Затем вы будете использовать утилиту pg_dump для создания полной резервной копии базы данных. После этого вы научитесь восстанавливать базу данных из файла резервной копии с помощью инструмента командной строки psql. Наконец, вы отработаете резервное копирование и восстановление одной конкретной таблицы из вашей базы данных.

Создание и заполнение образца базы данных

На этом этапе вы создадите базу данных PostgreSQL с именем salesdb и заполните ее образцом данных. Эта база данных послужит основой для операций резервного копирования и восстановления на последующих этапах.

Шаг 1: Создание базы данных

Сначала из терминала создайте базу данных salesdb, используя утилиту командной строки createdb. Это удобная обертка для SQL-команды CREATE DATABASE.

createdb salesdb

Шаг 2: Подключение к базе данных

Затем подключитесь к только что созданной базе данных salesdb с помощью интерактивного терминала psql.

psql -d salesdb

Теперь вы увидите приглашение psql, которое выглядит как salesdb=#, что означает, что вы подключены к базе данных salesdb.

Шаг 3: Создание таблиц и вставка данных

В оболочке psql выполните следующие SQL-команды для создания двух таблиц: customers и products.

Создайте таблицу customers:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Создайте таблицу products:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2)
);

Теперь вставьте некоторые образцы данных в обе таблицы.

Вставьте данные в customers:

INSERT INTO customers (name) VALUES ('Alice'), ('Bob');

Вставьте данные в products:

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00), ('Mouse', 25.00);

Шаг 4: Проверка данных

Чтобы убедиться, что данные были вставлены правильно, выполните запросы SELECT для обеих таблиц.

SELECT * FROM customers;

Вывод должен быть следующим:

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Теперь проверьте таблицу products:

SELECT * FROM products;

Вывод должен быть следующим:

 id |  name  |  price
----+--------+---------
  1 | Laptop | 1200.00
  2 | Mouse  |   25.00
(2 rows)

Наконец, выйдите из оболочки psql, чтобы вернуться в обычный терминал.

\q

Вы успешно настроили базу данных для следующих шагов.

Резервное копирование всей базы данных

На этом этапе вы будете использовать утилиту pg_dump для создания полной резервной копии базы данных salesdb. pg_dump генерирует файл, содержащий SQL-команды, которые могут быть использованы для восстановления базы данных.

Понимание pg_dump

pg_dump — это стандартная утилита PostgreSQL для резервного копирования одной базы данных. Она создает согласованный снимок базы данных, даже если она активно используется. По умолчанию она генерирует файл скрипта SQL в простом текстовом формате.

Шаг 1: Выполнение резервного копирования

Из терминала выполните следующую команду, чтобы выгрузить базу данных salesdb в файл с именем salesdb.sql в вашем каталоге проекта.

pg_dump salesdb > ~/project/salesdb.sql

Разберем команду:

pg_dump salesdb: Это указывает, что вы хотите создать резервную копию базы данных salesdb.
> ~/project/salesdb.sql: Это перенаправляет стандартный вывод команды в файл с именем salesdb.sql в каталоге ~/project.

Шаг 2: Проверка файла резервной копии

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

ls -l ~/project

В списке файлов вы должны увидеть salesdb.sql.

Затем просмотрите содержимое файла резервной копии, чтобы понять, что создал pg_dump. Используйте команду head для просмотра первых нескольких строк.

head ~/project/salesdb.sql

Вывод покажет начало SQL-скрипта, включая комментарии о версии pg_dump и команды для настройки среды базы данных. Это подтверждает, что ваш файл резервной копии является действительным SQL-скриптом.

Восстановление базы данных из резервной копии

На этом этапе вы узнаете, как восстановить базу данных из резервной копии в виде SQL-файла. Вы создадите новую базу данных, а затем используете утилиту psql для выполнения SQL-команд из вашего файла резервной копии.

Понимание восстановления базы данных

Для SQL-файлов в простом текстовом формате, созданных pg_dump, утилита psql является стандартным инструментом для восстановления. Она считывает SQL-команды из файла и выполняет их против указанной базы данных, эффективно воссоздавая таблицы и данные.

Шаг 1: Создание новой базы данных для восстановления

Чтобы избежать перезаписи вашей исходной базы данных, вы восстановите резервную копию в новую, пустую базу данных. Создайте базу данных с именем salesdb_restored.

createdb salesdb_restored

Шаг 2: Восстановление резервной копии

Теперь используйте psql для восстановления резервной копии salesdb.sql в базу данных salesdb_restored.

psql -d salesdb_restored -f ~/project/salesdb.sql

Разберем команду:

psql: Интерактивный терминал PostgreSQL, который также может выполнять файлы скриптов.
-d salesdb_restored: Указывает целевую базу данных для восстановления.
-f ~/project/salesdb.sql: Указывает входной файл, содержащий SQL-команды для выполнения.

Вы увидите вывод выполняемых SQL-команд, таких как CREATE TABLE и INSERT.

Шаг 3: Проверка восстановленной базы данных

Подключитесь к базе данных salesdb_restored, чтобы убедиться, что таблицы и данные были успешно восстановлены.

psql -d salesdb_restored

В оболочке psql сначала выведите список таблиц:

\dt

Вы должны увидеть таблицы customers и products.

              List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | labex
 public | products  | table | labex
(2 rows)

Затем выполните запрос к таблице customers, чтобы убедиться в наличии данных.

SELECT * FROM customers;

Вывод должен соответствовать исходным данным.

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Выйдите из оболочки psql.

\q

Вы успешно восстановили базу данных из резервной копии.

Резервное копирование и восстановление отдельной таблицы

Иногда может потребоваться создать резервную копию или восстановить только определенную таблицу, а не всю базу данных. Утилита pg_dump поддерживает это с помощью опции -t.

Шаг 1: Резервное копирование отдельной таблицы

Давайте создадим резервную копию только таблицы customers из исходной базы данных salesdb.

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

pg_dump -d salesdb -t customers > ~/project/customers_table.sql

-d salesdb: Указывает исходную базу данных.
-t customers: Указывает целевую таблицу для дампа.
> ~/project/customers_table.sql: Перенаправляет вывод в новый файл резервной копии.

Шаг 2: Имитация потери данных

Чтобы продемонстрировать процесс восстановления, сначала удалим таблицу customers из базы данных salesdb_restored, чтобы имитировать случайное удаление.

psql -d salesdb_restored -c "DROP TABLE customers;"

Команда выведет DROP TABLE, подтверждая удаление.

Шаг 3: Восстановление отдельной таблицы

Теперь восстановите таблицу customers из вашего файла резервной копии, специфичного для таблицы, в базу данных salesdb_restored.

psql -d salesdb_restored -f ~/project/customers_table.sql

Шаг 4: Проверка восстановления таблицы

Наконец, подключитесь к базе данных salesdb_restored и убедитесь, что таблица customers восстановлена, а таблица products осталась нетронутой.

psql -d salesdb_restored

В psql выведите список таблиц:

\dt

Вы должны снова увидеть в списке таблицы customers и products.

              List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | products  | table | labex
 public | customers | table | labex
(2 rows)

Выполните запрос к восстановленной таблице customers, чтобы подтвердить целостность ее данных.

SELECT * FROM customers;

Вывод должен показать исходные данные клиентов.

 id | name
----+-------
  1 | Alice
  2 | Bob
(2 rows)

Выйдите из оболочки psql.

\q

Вы успешно создали резервную копию и восстановили отдельную таблицу.

Резюме

В этой лабораторной работе вы изучили основные процедуры резервного копирования и восстановления баз данных PostgreSQL. Эти навыки необходимы для защиты данных и аварийного восстановления.

Вы научились:

Создавать базу данных и заполнять ее таблицами и данными.
Использовать pg_dump для экспорта всей базы данных в SQL-файл в простом текстовом формате.
Использовать psql для восстановления базы данных из SQL-файла резервной копии в новую базу данных.
Создавать резервную копию отдельной таблицы из базы данных с помощью опции -t в pg_dump.
Восстанавливать отдельную таблицу в базу данных.

Освоение этих команд придаст вам уверенности в эффективном управлении и защите ваших данных PostgreSQL.