# Команда COPY

Создадим базу данных и таблицу.

    α=> 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

При запуске без дополнительных параметров утилита 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, но загрузка будет работать существенно дольше.

Рассмотрим некоторые полезные ключи.

Могут пригодиться при восстановлении копии на системе с другим набором ролей:

- -O, --no-owner - не генерировать команды для установки владельца объектов;
- -x, --no-acl - не генерировать команды для установки привилегий.

Полезны для выгрузки и загрузки данных частями:

- -s, --schema-only - выгрузить только определения объектов без данных;
- -a, --data-only - выгрузить только данные, без создания объектов.

Удобны, если восстанавливать копию на системе, в которой уже есть данные (и наоборот, на чистой системе):

- -c, --clean - генерировать команды DROP для создаваемых объектов;
- -C, --create - генерировать команды создания БД и подключения к ней.

Важный момент: в выгрузку попадают и изменения, сделанные в шаблонной БД 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';

Существуют ключи для выбора объектов, которые должны попасть в резервную копию:

- -n, --schema - шаблон для имен схем;
- -t, --table - шаблон для имен таблиц.

И наоборот, включить в копию все, кроме указанного:

- -N, --exclude-schema - шаблон для имен схем;
- -T, --exclude-table - шаблон для имен таблиц.

Например, восстановим таблицу 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)

# Утилита pg_dump - формат custom

Серьезное ограничение обычного формата (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, и даже больше:

- -I, --index - загрузить определенные индексы;
- -P, --function - загрузить определенные функции;
- -T, --trigger - загрузить определенные триггеры.

    β=> 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.

# Утилита pg_dump - формат directory

Формат 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_dumpall

Утилита 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;

Конец демонстрации.