Backup restore postgresql базы данных с pg_dump
pg_dump - это утилита позволяющая делать бэкап базы данных из postgresql. Она сохраняет в файл набор SQL команд которые полностью воссоздают структуру исходной базы данных. Для последующего восстановления из подобного файла достаточно psql которая почти всегда идет в комплекте с самим postgresql.
С помощью pg_dump, вы можете сделать бэкап базы данных в текущий момент времени, и в дальнейшнейм развернуть его без каких либо проблем. Для тех, кто раньше работал с mysql, pg_dump может показаться весьма знакомым. С mysql в поставке идет аналогичная утилита под названием mysqldump.
В этой небольшой статье мы рассмотрим несколько полезных примеров использования pg_dump.
Резервное копирование базы данных
Бэкап одной базы данных
В примере ниже, мы сделаем резервную копию базы данных под названием thebosharu, принадлежащей пользователю bosha и сохраним её в файл thebosharu.sql:
pg_dump -U bosha thebosharu -f thebosharu.sql
Если вы работаете с базой данных не под тем же пользователем, под которым работаете в системе, то pg_dump спросит пароль к базе данных и после его успешного ввода создаст указанный файл содержащий SQL команды для создания необходимой структуры и копирования данных.
Вот часть дампа моей базы данных, чтобы вы понимали что в нем находится:
--
-- Name: auth_user; Type: TABLE; Schema: public; Owner: bosha; Tablespace:
--
CREATE TABLE auth_user (
id integer NOT NULL,
username character varying(250) NOT NULL,
password character varying(250),
email character varying(250),
first_name character varying(250),
last_name character varying(250),
middle_name character varying(250),
authenticated boolean,
active boolean,
is_admin boolean
);
ALTER TABLE auth_user OWNER TO bosha;
--
-- Name: auth_user_id_seq; Type: SEQUENCE; Schema: public; Owner: bosha
--
CREATE SEQUENCE auth_user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE auth_user_id_seq OWNER TO bosha;
--
-- Name: auth_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: bosha
--
ALTER SEQUENCE auth_user_id_seq OWNED BY auth_user.id;
--
-- Data for Name: auth_user; Type: TABLE DATA; Schema: public; Owner: bosha
--
COPY auth_user (id, username, password, email, first_name, last_name, middle_name, authenticated, active, is_admin) FROM stdin;
1 test pbkdf2:sha1:$sfadfadafda$sfadfadf10019sdfadfad0101010dsfadf0202022 \N f f f
\.
Так же можно передать ключ --format=t, чтобы pg_dump запаковал базу данных в tar:
pg_dump -U bosha thebosharu --format=t -f thebosharu.sql.tar
Бэкап всех баз данных
Для того, чтобы сделать резервную копию всех баз данных, нужно использовать другую утилиту - pg_dumpall.
Права доступа могут быть настроены у всех по разному, поэтому бэкап всех баз данных лучше делать из под пользователя postgres.
Зайдем под ним:
su - postgres
И выполним:
pg_dumpall > all_databases.sql
Бэкап всех баз данных будет содержаться в файле all_databases.sql.
Крайне желательно убедится, что все нужные базы данных были копированы. Для этого все из под того же пользователя postgres посмотрим список всех баз данных:
psql -l
В моем случае было три базы данных:
postgres:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+----------+----------+-------------+-------------+---------------------------
thebosharu | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | thebosharu=CTc/postgres
testdb1 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | testdb1=CTc/postgres
testdb2 | postgres | UTF8 | ru_RU.UTF-8 | ru_RU.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | testdb2=CTc/postgres
(3 rows)
Теперь удостоверимся, что все перечисленные базы попали в дамп:
bash:~$ grep "^[\]connect" all.sql
\connect thebosharu
\connect testdb1
\connect testdb2
Бэкап определенной таблицы
pg_dump --table auth_user -U bosha thebosharu -f auth_user_table.sql
Для бэкапа определенной таблицы используется параметр --table и следом за ним названием таблицы. Если в базе данных указанная таблица есть в разных схемах, то её можно указать используя параметр --schema.
Восстановление из бэкапа
Восстановление одной базы данных
psql -U bosha -d thebosharu -f thebosharu.sql
В процессе восстановления из резервной копии, могут возникнуть ошибки и предупреждения. Их можно игнорировать:
psql:mydb.sql:49: ERROR: must be owner of schema public
psql:mydb.sql:89: ERROR: must be member of role "bosha"
psql:mydb.sql:159: WARNING: no privileges could be revoked
psql:mydb.sql:261: WARNING: no privileges could be revoked
psql:mydb.sql:381: WARNING: no privileges were granted
psql:mydb.sql:452: WARNING: no privileges were granted
Восстановление из бэкапа всех баз данных
su - postgres
psql -f all_databases.sql
Восстановление одной таблицы
psql -f auth_user_table.sql thebosharu