Введение в администрирование PostgreSQL

Cтатья поможет c установкой, базовыми командами, автоматическим созданием бэкапов.

Установка #

Установите пакеты postgresql и postgresql-client:

sudo aptitude install postgresql postgresql-client libpq-dev
sudo pacman -S postgresql postgresql-client

Настройка #

Задайте пароль суперпользователя:

sudo -u postgres psql postgres
\password postgres

Выйти из консоли можно командой \q.

На рабочей машине можно дать все права и системному пользователю. Cоздать администратора можно так:

sudo createuser --superuser --username postgres --interactive

Базовые операции: #

Создание базы и пользователя:

sudo -u postgres psql
CREATE DATABASE myapp_production;
CREATE USER myapp WITH PASSWORD 'p@ssw0rd';
GRANT ALL PRIVILEGES ON DATABASE myapp_production TO myapp;

Резервное копирование #

Скрипт для автоматизации бэкапов:

sudo mkdir /root/scripts/
sudo vim /root/scripts/pg_backup.sh
#!/bin/bash

# backups dir
backup_path="/storage/backup/postgresql"

# excludes from backup
db_exclude='template1 template0'

# list of all dbs
databases="$(sudo -u postgres psql -At -c 'select datname from pg_database postgres')"

# clean list from excludes
for exclude in $db_exclude; do
  databases=$(echo $databases | sed "s/\b$exclude\b//g")
done

# backup
sudo mkdir -p $backup_path
sudo chown postgres:postgres $backup_path

for database in $databases; do
  echo $database...

  sudo -u postgres mkdir -p $backup_path/$database
  sudo -u postgres pg_dump --format=custom \
                           --compress=9    \
                           --clean         \
                           --no-privileges \
                           --no-owner \
                           --no-tablespaces \
                           --file=$backup_path/$database/$database.$(date +%Y-%m-%d-%H-%M).sql \
                           $database
done

# rm old backups
find $backup_path -mtime +31 -delete
sudo chmod 700 /root/scripts/pg_backup.sh
sudo crontab -e

Четыре часа утра — оптимальное время для бэкапа:

00 4  * * * /root/scripts/pg_backup.sh

Восстановление базы из бэкапа:

pg_restore -Fc --clean --host localhost --dbname dobroserver_blog_development /tmp/dobroserver_blog_production.2015-06-18-13-40.sql

А так можно залить данные из одной базы в другую, не создавая файл:

pg_dump -U dobroserver -h localhost -Fc -c dobroserver_production | pg_restore -c -U dobroserver -h localhost -d dobroserver_staging

Добавить комментарий