Создадим базу данных и таблицу в ней.
=> 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 в простом формате (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 мар 31 15:26 backup_label drwx------ 7 postgres postgres 4096 мар 31 15:26 base drwx------ 2 postgres postgres 4096 мар 31 15:26 global drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_commit_ts drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_dynshmem -rw------- 1 postgres postgres 4513 мар 31 15:26 pg_hba.conf -rw------- 1 postgres postgres 1636 мар 31 15:26 pg_ident.conf drwx------ 4 postgres postgres 4096 мар 31 15:26 pg_logical drwx------ 4 postgres postgres 4096 мар 31 15:26 pg_multixact drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_notify drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_replslot drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_serial drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_snapshots drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_stat drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_stat_tmp drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_subtrans drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_tblspc drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_twophase -rw------- 1 postgres postgres 3 мар 31 15:26 PG_VERSION drwx------ 3 postgres postgres 4096 мар 31 15:26 pg_wal drwx------ 2 postgres postgres 4096 мар 31 15:26 pg_xact -rw------- 1 postgres postgres 84 мар 31 15:26 postgresql.auto.conf -rw------- 1 postgres postgres 22766 мар 31 15:26 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)
Конец демонстрации.