Создадим базу данных и таблицу.
α=> CREATE DATABASE db1;
CREATE DATABASE
α=> \c db1
You are now connected to database "db1" as user "student".
α=> CREATE TABLE t( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, s text );
CREATE TABLE
α=> INSERT INTO t(s) VALUES ('Привет, мир!'), (''), (NULL);
INSERT 0 3
α=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | (3 rows)
Вот что показывает команда COPY (выдаем на консоль, а не в файл):
α=> COPY t TO stdout;
1 Привет, мир! 2 3 \N
Видно, как различаются в выводе пустые строки и неопределенные значения.
Формат вывода настраивается достаточно гибко. Можно изменить разделитель, представление неопределенных значений и т. п. Например:
α=> COPY t TO stdout WITH (NULL '<NULL>', DELIMITER ',');
1,Привет\, мир! 2, 3,<NULL>
Обратите внимание, что символ-разделитель внутри строки был экранирован (символ для экранирования тоже настраивается).
Вместо таблицы можно указать произвольный запрос.
α=> COPY (SELECT * FROM t WHERE s IS NOT NULL) TO stdout;
1 Привет, мир! 2
Таким образом можно сохранить результат запроса, данные представления и т. п.
Команда поддерживает вывод в формате CSV, который поддерживается множеством программ.
α=> COPY t TO stdout WITH (FORMAT CSV);
1,"Привет, мир!" 2,"" 3,
Аналогично работает и ввод данных из файла или с консоли:
α=> TRUNCATE TABLE t;
TRUNCATE TABLE
α=> COPY t FROM stdin; 1 Привет, мир! 2 3 \N \.
COPY 3
При вводе с консоли требуется маркер конца файла - обратная косая черта с точкой. В обычном файле он не нужен.
Все параметры при вводе должны совпадать с теми, что были указаны при выводе.
Вот что загрузилось в таблицу (для наглядности настроим в psql вывод неопределенных значений):
α=> \pset null '\\N'
Null display is "\N".
α=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | \N (3 rows)
При запуске без дополнительных параметров утилита pg_dump выдает команды SQL, создающие все объекты в базе данных:
student$ pg_dump -d db1
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.4 (Ubuntu 10.4-2.pgdg16.04+1)
-- Dumped by pg_dump version 10.4 (Ubuntu 10.4-2.pgdg16.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: t; Type: TABLE; Schema: public; Owner: student
--
CREATE TABLE public.t (
id integer NOT NULL,
s text
);
ALTER TABLE public.t OWNER TO student;
--
-- Name: t_id_seq; Type: SEQUENCE; Schema: public; Owner: student
--
ALTER TABLE public.t ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
SEQUENCE NAME public.t_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--
COPY public.t (id, s) FROM stdin;
1 Привет, мир!
2
3 \N
\.
--
-- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student
--
SELECT pg_catalog.setval('public.t_id_seq', 3, true);
--
-- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: student
--
ALTER TABLE ONLY public.t
ADD CONSTRAINT t_pkey PRIMARY KEY (id);
--
-- PostgreSQL database dump complete
--
Видно, что pg_dump создал таблицу t и заполнил ее с помощью уже рассмотренной нами команды COPY. Ключ --column-inserts позволяет использовать команды INSERT, но загрузка будет работать существенно дольше.
Рассмотрим некоторые полезные ключи.
Могут пригодиться при восстановлении копии на системе с другим набором ролей:
Полезны для выгрузки и загрузки данных частями:
Удобны, если восстанавливать копию на системе, в которой уже есть данные (и наоборот, на чистой системе):
Важный момент: в выгрузку попадают и изменения, сделанные в шаблонной БД template1. Поэтому восстанавливать резервную копию лучше на базе данных, созданной из template0. При использовании ключа --create это учитывается автоматически:
student$ pg_dump --create -d db1 | grep 'CREATE DATABASE'
CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
Существуют ключи для выбора объектов, которые должны попасть в резервную копию:
И наоборот, включить в копию все, кроме указанного:
Например, восстановим таблицу t в другой базе данных на другом сервере.
student$ psql -p 5433
β=> CREATE DATABASE db2;
CREATE DATABASE
student$ pg_dump --table=t -d db1 | psql -p 5433 -d db2
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 3
setval
--------
3
(1 row)
ALTER TABLE
β=> \c db2
You are now connected to database "db2" as user "student".
β=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | (3 rows)
Серьезное ограничение обычного формата (plain) состоит в том, что выбирать объекты нужно в момент выгрузки. Формат custom позволяет сначала сделать полную копию, а выбирать объекты уже при загрузке.
student$ pg_dump --format=custom -d db1 -f /home/student/db1.custom
Для восстановления объектов из такой копии предназначена утилита pg_restore. Повторим восстановление таблицы t:
β=> DROP TABLE t;
DROP TABLE
student$ pg_restore --table=t -p 5433 -d db2 /home/student/db1.custom
Формат резервной копии указывать не обязательно - утилита распознает его сама.
Утилита pg_restore понимает те же ключи для фильтрации объектов, что и pg_dump, и даже больше:
β=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | (3 rows)
Еще один пример: восстановим целиком исходную базу данных db1 на другом сервере.
student$ pg_restore --create -p 5433 -d postgres /home/student/db1.custom
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2878; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
WARNING: errors ignored on restore: 1
Ошибка "must be owner of extension plpgsql" возникает из-за того, что владельцем расширения является postgres. В данном случае эта ошибка не приводит к проблеме (разумеется, все сообщения надо анализировать).
Здесь мы указали БД postgres, но могли указать любую - утилита сама создаст нужную БД и тут же переключится в нее.
Проверим:
β=> \c db1
You are now connected to database "db1" as user "student".
β=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | (3 rows)
Резервную копию в обычном (plain) формате при необходимости можно изменить в текстовом редакторе. Резервная копия формата custom хранится в двоичном виде, но и для нее доступны более широкие возможности фильтрации объектов, чем рассмотренные ключи. Утилита pg_restore может сформировать список объектов - оглавление резервной копии:
student$ pg_restore --list /home/student/db1.custom
; ; Archive created at 2018-06-13 19:58:37 MSK ; dbname: db1 ; TOC Entries: 13 ; Compression: -1 ; Dump Version: 1.13-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 10.4 (Ubuntu 10.4-2.pgdg16.04+1) ; Dumped by pg_dump version: 10.4 (Ubuntu 10.4-2.pgdg16.04+1) ; ; ; Selected TOC Entries: ; 2876; 1262 16386 DATABASE - db1 student 3; 2615 2200 SCHEMA - public postgres 2877; 0 0 COMMENT - SCHEMA public postgres 1; 3079 12998 EXTENSION - plpgsql 2878; 0 0 COMMENT - EXTENSION plpgsql 197; 1259 16389 TABLE public t student 196; 1259 16387 SEQUENCE public t_id_seq student 2870; 0 16389 TABLE DATA public t student 2879; 0 0 SEQUENCE SET public t_id_seq student 2747; 2606 16396 CONSTRAINT public t t_pkey student
Такой список можно записать в файл, отредактировать и использовать его для восстановления с помощью ключа --use-list.
Формат directory интересен тем, что позволяет выгружать данные в несколько параллельных потоков.
student$ pg_dump --format=directory --jobs=2 -d db1 -f /home/student/db1.directory
При этом гарантируется согласованность данных: все параллельные потоки будут использовать один и тот же снимок данных.
Заглянем внутрь каталога:
student$ ls -l /home/student/db1.directory
total 8 -rw-r--r-- 1 student student 60 июн 13 19:58 2870.dat.gz -rw-r--r-- 1 student student 2666 июн 13 19:58 toc.dat
В нем находится файл оглавления и по одному файлу на каждый выгружаемый объект (у нас он всего один):
student$ zcat /home/student/db1.directory/2870.dat.gz
1 Привет, мир! 2 3 \N \.
Восстановление из резервной копии:
β=> \q
student$ pg_restore --clean --create --jobs=2 -p 5433 -d postgres /home/student/db1.custom
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2878; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
WARNING: errors ignored on restore: 1
Здесь мы предварительно отключились от базы данных db1 и добавили ключ --clean, который генерирует команду удаления БД.
Утилита pg_dump годится для выгрузки одной базы данных, но никогда не выгружает общие объекты кластера БД, такие, как роли и табличные пространства. Чтобы сделать полную копию кластера, нужна утилита pg_dumpall.
student$ pg_dumpall --clean -U postgres -f /home/student/alpha.sql
Утилиты pg_dumpall, pg_dump и pg_restore не требуют каких-то отдельных привилегий, но у выполняющей их роли должны быть привилегии на чтение (создание) всех затронутых объектов. Утилитой pg_dump может, например, пользоваться владелец базы данных. Но поскольку для копирования кластера надо иметь доступ ко всем БД, мы выполняем pg_dumpall под суперпользовательской ролью.
В копию кластера дополнительно попадают такие команды, как:
student$ grep 'ROLE' alpha.sql
DROP ROLE postgres; DROP ROLE student; CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS; CREATE ROLE student; ALTER ROLE student WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD 'md550d9482e20934ce6df0bf28941f885bc';
Восстановление выполняется с помощью psql - никакой другой формат не поддерживается.
student$ psql -p 5433 -U postgres -f alpha.sql
SET
SET
SET
DROP DATABASE
DROP DATABASE
psql:alpha.sql:24: ERROR: current user cannot be dropped
psql:alpha.sql:25: ERROR: role "student" cannot be dropped because some objects depend on it
DETAIL: owner of database db2
1 object in database db2
psql:alpha.sql:32: ERROR: role "postgres" already exists
ALTER ROLE
psql:alpha.sql:34: ERROR: role "student" already exists
ALTER ROLE
CREATE DATABASE
CREATE DATABASE
REVOKE
GRANT
You are now connected to database "db1" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 3
setval
--------
3
(1 row)
ALTER TABLE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
You are now connected to database "student" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
CREATE EXTENSION
COMMENT
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
COMMENT
CREATE EXTENSION
COMMENT
В процессе восстановления могут возникать ошибки из-за существующих объектов - в данном случае это нормально и не мешает процессу.
student$ psql -p 5433
β=> SELECT datname FROM pg_database;
datname ----------- postgres template0 db2 db1 student template1 (6 rows)
β=> \c db1
You are now connected to database "db1" as user "student".
β=> SELECT * FROM t;
id | s ----+-------------- 1 | Привет, мир! 2 | 3 | (3 rows)
Если на таблицах определены политики защиты строк, то есть опасность выгрузить неполные данные и даже не узнать об этом. Чтобы этого не произошло, перед выполнением команды COPY можно установить параметр row_security в значение off - в этом случае применение политики приведет к явной ошибке.
Простой пример. Настроим политику так, чтобы не выводились пустые строки, и включим ее для владельца таблицы:
α=> CREATE POLICY t_s_not_null ON t USING (s IS NOT NULL);
CREATE POLICY
α=> ALTER TABLE t ENABLE ROW LEVEL SECURITY;
ALTER TABLE
α=> ALTER TABLE t FORCE ROW LEVEL SECURITY;
ALTER TABLE
Теперь запрос покажет только две строки:
α=> COPY t TO stdout;
1 Привет, мир! 2
Но с параметром, установленным в off, будет зафиксирована ошибка:
α=> SET row_security = off;
SET
α=> COPY t TO stdout;
ERROR: query would be affected by row-level security policy for table "t" HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
Утилиты pg_dump и pg_dumpall автоматически используют этот параметр, так что дополнительные действия предпринимать не нужно:
student$ pg_dump -d db1 > /dev/null
pg_dump: [archiver (db)] query failed: ERROR: query would be affected by row-level security policy for table "t" HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY. pg_dump: [archiver (db)] query was: COPY public.t (id, s) TO stdout;
Конец демонстрации.