copy

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

=> CREATE DATABASE backup_overview;
CREATE DATABASE
=> \c backup_overview
You are now connected to database "backup_overview" as user "postgres".
=> CREATE TABLE t(id numeric, s text);
CREATE TABLE
=> INSERT INTO t VALUES (1, 'Привет!'), (2, ''), (3, NULL);
INSERT 0 3

=> SELECT * FROM t;
 id |    s    
----+---------
  1 | Привет!
  2 | 
  3 | 
(3 rows)


Вот как выглядит таблица в выводе команды COPY:

=> COPY t TO stdout;
1	Привет!
2	
3	\N

Обратим внимание на то, что пустая строка и NULL - разные значения, хотя, выполняя запрос, этого и не заметно.


Аналогично можно вводить данные:

=> TRUNCATE TABLE t;
TRUNCATE TABLE
=> COPY t FROM STDIN;
1	Hi there!
2	
3	\N
\.
COPY 3

Проверим:

=> SELECT * FROM t;
 id |     s     
----+-----------
  1 | Hi there!
  2 | 
  3 | 
(3 rows)

=> COPY t TO stdout;
1	Hi there!
2	
3	\N

pg_dump

Посмотрим на результат работы утилиты pg_dump в простом формате (plain). Обратите внимание на то, в каком виде сохранены данные из таблицы.

Если в шаблон template1 вносились какие-либо изменения, они также попадут в резервную копию. Поэтому при восстановлении базы данных имеет смысл предварительно создать ее из шаблона template0 (указанный ключ --create добавляет нужные команды автоматически).

postgres$ pg_dump -d backup_overview --create
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.0
-- Dumped by pg_dump version 10.0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: backup_overview; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE backup_overview WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE backup_overview OWNER TO postgres;

\connect backup_overview

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
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 search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: t; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE t (
    id numeric,
    s text
);


ALTER TABLE t OWNER TO postgres;

--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY t (id, s) FROM stdin;
1	Hi there!
2	
3	\N
\.


--
-- PostgreSQL database dump complete
--

В качестве примера использования скопируем таблицу в другую базу.

=> CREATE DATABASE backup_overview2;
CREATE DATABASE
postgres$ pg_dump -d backup_overview --table=t | psql -d backup_overview2
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3

postgres$ psql -d backup_overview2
=> SELECT * FROM t;
 id |     s     
----+-----------
  1 | Hi there!
  2 | 
  3 | 
(3 rows)

=> \q

Автономная резервная копия

В PostgreSQL 10 значения параметров по умолчанию позволяют использовать репликацию:

=> SELECT name, setting
FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders');
      name       | setting 
-----------------+---------
 max_wal_senders | 10
 wal_level       | replica
(2 rows)

В более ранних версиях аналогичные значения пришлось бы установить самостоятельно.


Разрешение на локальное подключение по протоколу репликации в pg_hba.conf также прописано по умолчанию, начиная с версии 10:

=> SELECT type, database, user_name, address, auth_method 
FROM pg_hba_file_rules() 
WHERE database = '{replication}';
 type  |   database    | user_name |  address  | auth_method 
-------+---------------+-----------+-----------+-------------
 local | {replication} | {all}     |           | trust
 host  | {replication} | {all}     | 127.0.0.1 | trust
 host  | {replication} | {all}     | ::1       | trust
(3 rows)


Выполним команду pg_basebackup. Используем формат по умолчанию (plain), в качестве каталога для сохранения сразу используем PGDATA резервного сервера.

postgres$ rm -rf /usr/local/pgsql2/data/*
postgres$ pg_basebackup --pgdata=/usr/local/pgsql2/data

Резервный сервер уже предварительно собран и установлен.


Проверим содержимое каталога:

postgres$ ls -l /usr/local/pgsql2/data
total 116
-rw------- 1 postgres postgres   206 дек 24 17:30 backup_label
drwx------ 7 postgres postgres  4096 дек 24 17:30 base
drwx------ 2 postgres postgres  4096 дек 24 17:30 global
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_commit_ts
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_dynshmem
-rw------- 1 postgres postgres  4513 дек 24 17:30 pg_hba.conf
-rw------- 1 postgres postgres  1636 дек 24 17:30 pg_ident.conf
drwx------ 4 postgres postgres  4096 дек 24 17:30 pg_logical
drwx------ 4 postgres postgres  4096 дек 24 17:30 pg_multixact
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_notify
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_replslot
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_serial
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_snapshots
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_stat
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_stat_tmp
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_subtrans
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_tblspc
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_twophase
-rw------- 1 postgres postgres     3 дек 24 17:30 PG_VERSION
drwx------ 3 postgres postgres  4096 дек 24 17:30 pg_wal
drwx------ 2 postgres postgres  4096 дек 24 17:30 pg_xact
-rw------- 1 postgres postgres    84 дек 24 17:30 postgresql.auto.conf
-rw------- 1 postgres postgres 22766 дек 24 17:30 postgresql.conf

Восстановление из автономной резервной копии

Мы собираемся запустить сервер вместе с основным, поэтому в настройках необходимо поменять порт. Сделаем это в файле postgresql.auto.conf:

postgres$ echo 'port = 5433' >> /usr/local/pgsql2/data/postgresql.auto.conf

И можно запускать сервер.

postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data start
waiting for server to start.... done
server started

В приведенной команде мы явно указываем расположение каталога с данными кластера. Для удобства в виртуальной машине есть команды "1" и "2", которые переключают окружение на первый и на второй сервер соответственно.


Теперь оба сервера работают одновременно и независимо. Основной:

postgres$ psql -p 5432 -d backup_overview
=> INSERT INTO t VALUES (4, 'Основной сервер');
INSERT 0 1
=> SELECT * FROM t;
 id |        s        
----+-----------------
  1 | Hi there!
  2 | 
  3 | 
  4 | Основной сервер
(4 rows)


Сервер, восстановленный из резервной копии:

postgres$ psql -p 5433 -d backup_overview
=> INSERT INTO t VALUES (4, 'Резервная копия');
INSERT 0 1
=> SELECT * FROM t;
 id |        s        
----+-----------------
  1 | Hi there!
  2 | 
  3 | 
  4 | Резервная копия
(4 rows)


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