# Базы данных и объекты

    α=> CREATE DATABASE db1;

    CREATE DATABASE

    α=> \c db1

    You are now connected to database "db1" as user "student".

    α=> CREATE TABLE t1(n integer);

    CREATE TABLE

    α=> INSERT INTO t1 VALUES (1), (2), (3);

    INSERT 0 3

    α=> CREATE VIEW v1 AS SELECT * FROM t1;

    CREATE VIEW

    α=> CREATE DATABASE db2;

    CREATE DATABASE

    α=> \c db2

    You are now connected to database "db2" as user "student".

    α=> CREATE TABLE t2(n integer);

    CREATE TABLE

    α=> INSERT INTO t2 VALUES (1), (2), (3);

    INSERT 0 3

    α=> CREATE VIEW v2 AS SELECT * FROM t2;

    CREATE VIEW

# Копия глобальных объектов

    student$ pg_dumpall --clean --globals-only -U postgres -f /home/student/alpha_globals.sql

# Копии баз данных

Здесь мы ограничимся теми базами данных, которые создали сами.

    student$ pg_dump --jobs=2 --format=directory -d db1 -f /home/student/db1.directory

    student$ pg_dump --jobs=2 --format=directory -d db2 -f /home/student/db2.directory

# Восстановление кластера

Сначала восстанавливаем глобальные объекты:

    student$ psql -p 5433 -U postgres -f alpha_globals.sql

    SET
    SET
    SET
    psql:alpha_globals.sql:16: ERROR:  current user cannot be dropped
    psql:alpha_globals.sql:17: ERROR:  role "student" cannot be dropped because some objects depend on it
    DETAIL:  owner of database student
    psql:alpha_globals.sql:24: ERROR:  role "postgres" already exists
    ALTER ROLE
    psql:alpha_globals.sql:26: ERROR:  role "student" already exists
    ALTER ROLE
    psql:alpha_globals.sql:34: NOTICE:  role "student" is already a member of role "pg_read_all_stats"
    GRANT ROLE

Затем восстанавливаем базы данных:

    student$ pg_restore -p 5433 -d postgres --create --jobs=2 /home/student/db1.directory

    pg_restore: [archiver (db)] Error while PROCESSING TOC:
    pg_restore: [archiver (db)] Error from TOC entry 2877; 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

    student$ pg_restore -p 5433 -d postgres --create --jobs=2 /home/student/db2.directory

    pg_restore: [archiver (db)] Error while PROCESSING TOC:
    pg_restore: [archiver (db)] Error from TOC entry 2877; 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

Проверим:

    student$ psql -p 5433

    β=> \c db1

    You are now connected to database "db1" as user "student".

    β=> \d

            List of relations
     Schema | Name | Type  |  Owner
    --------+------+-------+---------
     public | t1   | table | student
     public | v1   | view  | student
    (2 rows)

    β=> \c db2

    You are now connected to database "db2" as user "student".

    β=> \d

            List of relations
     Schema | Name | Type  |  Owner
    --------+------+-------+---------
     public | t2   | table | student
     public | v2   | view  | student
    (2 rows)

# Ломаем COPY

Например, можно установить отображение неопределенных значений, совпадающее с какими-либо даными:

    α=> CREATE TABLE anticopy(s text);

    CREATE TABLE

    α=> INSERT INTO anticopy(s) VALUES ('N'), (NULL);

    INSERT 0 2

    α=> COPY anticopy TO stdout WITH (NULL 'N');

    N
    N

Вывод двух разных значений теперь неотличим друг от друга.

Осторожнее с изменением формата по умолчанию!