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

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

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

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