Резервные копии и восстановление баз PostgreSQL

Базовая команда на выгрузку базы:
pg_dump — выгрузить базу данных Postgres в формате скрипта в файл или архив.
Синтаксис: pg_dump [параметр-подключения…] [параметр…] [имя_бд]

Создаем простой скрипт для выгрузки базы (dump):

#!/bin/bash

# Текущая дата 
cur_date=$(date --iso)

PGPASSWORD=YourPassword
# команда на выгрузку баз 
pg_dump -U postgres mybase | gzip -c > /backup/mybase_$cur_date.sql.gz

Переменная PGPASSWORD передает пароль пользователя postgres в окружение и при выполнении команды не запрашивает пароль. Однако такой метод является не очень безопасным. Рекомендуется использовать password file (.pgpass).

Файл .pgpass размещается в корне домашней директории пользователя от имени которого будет доступ к pg_dump. Привилегии на доступ к файлу должны запрещать любой доступ для всех и для группы (chmod 0600 ~/.pgpass). Если привилегии менее строгие, то файл ~/.pgpass будет игнорироваться. Если установлена переменная PGPASSWORD, то файл ~/.pgpass не читается.

Формат файла .pgpass следующий:

хост:порт:база:пользователь:пароль

Создаем файл .pgpass:

touch ~/.pgpass
chmod 0600 ~/.pgpass

В файл записываем данные пользователя:

*:*:*:postgres:Postgres_Password

Перезапускаем postgres:

service postgresql restart

Теперь можно изменить скрипт:

#!/bin/bash

# Текущая дата 
cur_date=$(date --iso)

# команда на выгрузку баз 
pg_dump -U postgres mybase | gzip -c > /backup/mybase_$cur_date.sql.gz

Теперь открыто писать пароль в скрипте не нужно.

Подробнее:
https://postgrespro.ru/docs/postgrespro/10/app-pgdump
https://www.postgresql.org/docs/current/libpq-pgpass.html

Восстановление

Разархивируем нужный архив:

gunzip mybase_xx.yy.zz.gz

Восстановим базу mybase с дампа:

psql -U postgres -d mybase -f mybase.sql

Информация (базовые команды для PostgreSQL)

su postgres
psql

\c db_name – подключиться к базе с именем db_name
\du – список пользователей
\l – список баз данных
\dt - список таблиц в базе 
\d "table" - описание таблицы
\q – выход

# удалить базу 
postgres=# DROP DATABASE mybase;

# создать базу:
postgres=# CREATE DATABASE mybase;

# удалить / создать таблицу в базе 
postgres=# \c mybase
mybase-# DROP TABLE TABLE_NAME;
mybase-# CREATE TABLE TABLE_NAME;

# Создать пользователя с полными правами для базы mybase
postgres=# CREATE USER username WITH PASSWORD 'user_password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE username to mybase;

!!! После изменений нужно перезапустить сервис
systemctl restart postgresql

Возможно потребуется изменения в pg_hba.conf, в этом конфигурационном файле содержится информация о методах аутентификации.