База данных и таблица

=> CREATE DATABASE backup_overview;
CREATE DATABASE
=> \c backup_overview
You are now connected to database "backup_overview" as user "postgres".
=> CREATE TABLE t(n integer);
CREATE TABLE
=> INSERT INTO t VALUES (1), (2), (3);
INSERT 0 3

Логическая резервная копия

postgres$ pg_dump -f /home/postgres/backup_overview.dump -d backup_overview --create

Удаление и восстановление

=> \c postgres
You are now connected to database "postgres" as user "postgres".
=> DROP DATABASE backup_overview;
DROP DATABASE
postgres$ psql -f /home/postgres/backup_overview.dump
SET
SET
SET
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "backup_overview" as user "postgres".
SET
SET
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
=> \c backup_overview
You are now connected to database "backup_overview" as user "postgres".
=> SELECT * FROM t;
 n 
---
 1
 2
 3
(3 rows)

Физическая автономная резервная копия

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

Изменение таблицы

postgres$ psql -p 5432 -d backup_overview
=> DELETE FROM t;
DELETE 3

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

Изменение порта:

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
postgres$ psql -p 5433 -d backup_overview
=> SELECT * FROM t;
 n 
---
 1
 2
 3
(3 rows)