Как восстановить данные из бекапа в postgresql

2025-05-27 2025-05-29 982 слов 5 минут

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

Содержание

1 Цель

Иногда возникает потребность сделать бекап postgresql базы, используя подручные средста и накатить этот бекап на другую базу, например, для тестов.
Будем использовать утилиты pg_dump и pg_restore для достижения цели.

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

Заодно вспомним как работать с docker, sql.

2 Создание БД в облаке

В качестве удаленной бд используем базу Neon на платформе Vercel.
Neon - это облачная база данных, совместимая с PostgreSQL, ориентированная на бессерверную архитектуру.

Регистрируемся на vercel.com, создаем БД Neon, сохраняем строку подключения в переменную PG_DSN:

export PG_DSN="postgresql://user:password@host/database?sslmode=require"

3 Подключение к удаленной БД

У нас есть удаленная БД с данными, у которой известна строка подключения PG_DSN.

Подключимся к удаленной БД из докера с помощью psql и проверим какие данные есть в БД.

❯ docker run --rm -it bitnami/postgresql psql "$PG_DSN"
postgresql 19:27:44.27 INFO  ==>
postgresql 19:27:44.30 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 19:27:44.35 INFO  ==>

psql (17.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

neondb=> \d
 public | test        | table    | neondb_owner
 public | test_id_seq | sequence | neondb_owner

neondb=> select * from test LIMIT 5;
 id |  name
----+--------
  1 | test 1
  2 | test 2
  3 | test 3
  4 | test 4
  5 | test 5
(5 rows)

Видим, что есть таблица test с данными.

4 Создание дампа

Аналогично запускаем утилиту pg_dump, указываем строку подключения к БД и путь к выходному файлу дампа.
Важно, что дамп будет сохранен в докер контейнере, поэтому нужно примонтировать в докер каталог файловой системы, чтобы дамп сохранился на хостовой системе.

❯ docker run --rm -it --volume ~/data:/mnt/data bitnami/postgresql pg_dump -Fc -f /mnt/data/pg.dump --verbose "$PG_DSN"
postgresql 19:31:09.45 INFO  ==>
postgresql 19:31:09.47 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 19:31:09.53 INFO  ==>

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading dependency data
...
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "neon_auth.users_sync"
pg_dump: dumping contents of table "public.test"

Дамп создан, проверим формат файла:

❯ ls -l pg.dump
-rw-r--r-- 1 root  root  9981 May 27 22:31 pg.dump

❯ file pg.dump
pg.dump: PostgreSQL custom database dump - v1.16-0

5 Создание локальной БД

Для простоты поднимем локальную БД postgresql в докере.

❯ docker run -e POSTGRES_USER=labuser -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=database -p 5432:5432 bitnami/postgresql
postgresql 20:07:23.57 INFO  ==>
postgresql 20:07:23.59 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 20:07:23.65 INFO  ==>
postgresql 20:07:23.82 INFO  ==> ** Starting PostgreSQL setup **
postgresql 20:07:23.91 INFO  ==> Validating settings in POSTGRESQL_* env vars..
postgresql 20:07:23.98 INFO  ==> Loading custom pre-init scripts...
postgresql 20:07:24.03 INFO  ==> Initializing PostgreSQL database...
postgresql 20:07:24.21 INFO  ==> pg_hba.conf file not detected. Generating it...
postgresql 20:07:24.23 INFO  ==> Generating local authentication configuration
postgresql 20:07:27.03 INFO  ==> Starting PostgreSQL in background...
postgresql 20:07:28.39 INFO  ==> Creating user labuser
postgresql 20:07:28.49 INFO  ==> Granting access to "labuser" to the database "database"
postgresql 20:07:28.66 INFO  ==> Setting ownership for the 'public' schema database "database" to "labuser"
postgresql 20:07:28.81 INFO  ==> Configuring replication parameters
postgresql 20:07:29.14 INFO  ==> Configuring synchronous_replication
postgresql 20:07:29.16 INFO  ==> Configuring fsync
postgresql 20:07:29.40 INFO  ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped
postgresql 20:07:29.87 INFO  ==> Loading custom scripts...
postgresql 20:07:29.92 INFO  ==> Enabling remote connections
postgresql 20:07:30.02 INFO  ==> ** PostgreSQL setup finished! **

postgresql 20:07:30.11 INFO  ==> ** Starting PostgreSQL **
2025-05-28 20:07:30.198 GMT [1] LOG:  pgaudit extension initialized
2025-05-28 20:07:30.209 GMT [1] LOG:  starting PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-05-28 20:07:30.213 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-05-28 20:07:30.213 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2025-05-28 20:07:30.217 GMT [1] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-28 20:07:30.225 GMT [143] LOG:  database system was shut down at 2025-05-28 20:07:29 GMT
2025-05-28 20:07:30.244 GMT [1] LOG:  database system is ready to accept connections

Попробуем подключиться к локальной БД через psql и убедимся, что в локальной БД нет таблицы test

❯ export PG_LOCAL_DSN="postgres://labuser:secret@host.docker.internal:5432/testdb"

❯ docker run --rm -it bitnami/postgresql psql "$PG_LOCAL_DSN"
postgresql 19:34:50.30 INFO  ==>
postgresql 19:34:50.32 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 19:34:50.38 INFO  ==>

psql (17.5)
Type "help" for help.

testdb=> select * from test LIMIT 5;
ERROR:  relation "test" does not exist
LINE 1: select * from test LIMIT 5;
                      ^

Отлично! Локальня БД запустилась. Клиент смог к ней подключиться.

6 Восстановление данных из дампа

Запускаем pg_restore, указываем строку подключения и путь к дампу.

❯ docker run --rm -it --volume ~/data:/mnt/data bitnami/postgresql pg_restore -d "$PG_LOCAL_DSN" --verbose --clean ./mnt/data/pg.dump
postgresql 19:36:24.72 INFO  ==>
postgresql 19:36:24.74 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 19:36:24.80 INFO  ==>

pg_restore: connecting to database for restore
...
pg_restore: creating SEQUENCE "public.test_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.test_id_seq"
pg_restore: creating DEFAULT "public.test id"
pg_restore: processing data for table "public.test"
pg_restore: executing SEQUENCE SET test_id_seq
pg_restore: creating CONSTRAINT "public.test test_pkey"

7 Проверка данных из дампа

Снова подключаемся к локальной БД, выполняем такие же запросы и видим, что в БД появились данные.

❯ docker run --rm -it bitnami/postgresql psql "$PG_LOCAL_DSN"
postgresql 19:34:50.30 INFO  ==>
postgresql 19:34:50.32 INFO  ==> Welcome to the Bitnami postgresql container
postgresql 19:34:50.38 INFO  ==>

psql (17.5)
Type "help" for help.

database=> select * from test LIMIT 5;
 id |  name
----+--------
  1 | test 1
  2 | test 2
  3 | test 3
  4 | test 4
  5 | test 5
(5 rows)

Супер. Удалось создать бекап и накатить его на новую базу данных.