Схема БД или таблицы в PostgreSQL
Всё время забываю, как в PostgreSQL сделать дамп схемы БД, без данных. Команда на удивление проста:
pg_dump -s -d %DATABASE_NAME% -U %USER_NAME% > %FILE_NAME%
| Параметр | Назначение |
|---|---|
-s |
Ключ указывает на то, что нужно сделать копию только схемы БД |
-d |
Ключ, задающий имя БД, с которой нужно работать. |
-U |
Ключ, указывающий пользователя, от имени которого будет делаться копия схемы. Пользователь должен иметь доступ хотя бы на чтение данной БД. |
%FILE_NAME% |
Файл, в который следует сохранить вывод. В противном случае вся схема будет выведена на экран. |
А если вместо -s указать -t и потом имя таблицы, то будет снята схема только с неё, например:
pg_dump -d project -U xphoenix -t auth_users > auth_users_shema.sql
Настройка Redmine на Debian 7
Установка и настройка Redmine в Debian 7 (nginx + postgresql + thin)
В данной статье рассматривается установка и настройка Redmine версии 1.x в Debian 7 с помощью apt. Если вам нужна инструкция по настройке Redmine версии 2.x, то эта статья не для вас.
Краткое содержание:
- Введение
- Список используемых пакетов.
- Порядок установки и настройки пакетов.
- Предварительная настройка PostgreSQL
- Установка пакетов
- Создание базы данных
- Увязка Redmine с nginx через thin.
Введение
Redmine - система для управления проектами. Если говорить простым языком, эта система служит для того, чтобы можно было вести учёт обращений, заявок, багрепортов и известных проблем о каждом продукте компании. Помимо этого в системе есть встроенная Wiki, база знаний, диаграммы Ганта, разделение по проектам и ролям и т.д. Если вы работаете в фирме, которая занимается разработкой ПО, то этот или аналогичный продукт вам жизненно необходим. С помощью Redmine можно оперативно отслеживать ход работы, исправления обнаруженных ошибок и т.д. В общем, на выходе получается целый комбайн.
Список используемых пакетов
Для работы Redmine требуется установка Ruby On Rails. К нашему удовольствию разработчики Debian'а включили все необходимые пакеты в список зависимостей, значительно упростив установку. Исключение сделано лишь для двух пакетов - сервер, который будет запускать Redmine, и пакет для соединения с БД.
Ниже приведён минимальный список пакетов, которые нам потребуются:
thin- сервер для запуска продуктов, написанных на Rubyredmine- сама системаruby-pg- драйвер для подключения программ, написанных на Ruby On Rails, к СУБД PostgreSQL.
Установка Ruby будет выполнена автоматически
Предварительная настройка базы в PostgreSQL
Запустим интерпретатор psql от имени суперпользователя postgres:
sudo su -c psql postgres
Создадим нового пользователя и базу данных, к которой он будет иметь полный доступ, после чего закроем интерпретатор:
CREATE ROLE redmine WITH PASSWORD 'redmine' LOGIN;
CREATE DATABASE redmine WITH OWNER redmine;
\q
Естественно, вы можете дать пользователю и базе любое нужное имя. Кроме того, на одном сервере можно развернуть несколько экземпляров Redmine - для этого нужно будет создать несколько баз (и, возможно, пользователей) и настроить несколько конфигураций для thin, но я сейчас этого делать не буду.
Установка пакетов и настройка параметров Redmine
Установка пакетов
Для установки всех необходимых пакетов нужно от имени root выполнить команду:
apt-get install redmine ruby-pg thin -y
Во время установки менеджер пакетов предложит настроить базу данных. Отвечаем отрицательно. Базу данных лучше настраивать через правку конфигурационных файлов, т.к. установщик Redmine предлагает дать ему административный доступ к PostgreSQL.
Настройка доступа к БД
Перейдём в каталог /etc/redmine/default
cd /etc/redmine/default
Здесь находится всего два файла - database.yml и session.yml. Нам нужен только первый, отвечающий за настройки доступа к БД.
Содержимое database.yml:
production:
adapter: postgresql
database: redmine
host: localhost
port:
username: redmine
password: redmine
encoding: utf8
Все настройки, полагаю, понятны без комментариев. Отмечу только, что первая строка, production:, указывает на то, что данная конфигурация должна использоваться при запуске сервера в рабочем режиме. Также доступны режимы development и test.
Настройка сервера thin
Теперь нужно настроить thin. В Debian 7 при установке thin ставятся сразу две его версии, 1.8 и 1.9.1, мы будем использовать последнюю. Переходим в каталог настроек и создаём файл для запуска Redmine:
cd /etc/thin1.9.1/
touch redmine.yml
chmod 644 redmine.yml
Открываем созданный файл в любимом текстовом редакторе и вносим туда следующие данные:
---
chdir: /usr/share/redmine
environment: production
timeout: 30
log: /var/log/thin/redmine.log
pid: /var/run/redmine/redmine.pid
max_conns: 1024
max_persistent_conns: 512
require: []
wait: 30
socket: /var/run/redmine/redmine.sock
daemonize: true
user: www-data
group: www-data
servers: 1
prefix: /redmine
socket указывает путь и имя сокетов, создаваемых thin при запуске Redmine.
Параметр servers указывает, сколько серверов следует запустить. Другими словами, он определяет количество сокетов, которые будут созданы в указанном каталоге. К имени сокета, указанному выше, будет добавлен порядковый номер.
Servers: 1
ls /var/run/redmine
redmine.sock.0
Servers: 2
ls /var/run/redmine
redmine.sock.0
redmine.sock.1
prefix указывает на то, по какому адресу на сервере будут обрабатываться запросы к Redmine. Поясню на примере. Допустим, адрес нашего сайта - prefix следует удалить из файла конфигурации полностью!
Настройка nginx.
В каталоге /etc/nginx/ следует создать файл proxy_params.conf. На моих серверах он выглядит примерно так:
proxy_redirect off;
proxy_set_header Host $http_host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Url-Scheme $scheme;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
client_max_body_size 20m;
client_body_buffer_size 1m;
proxy_buffering off;
proxy_send_timeout 180;
proxy_read_timeout 180;
proxy_connect_timeout 180;
proxy_buffer_size 4k;
proxy_buffers 32 32k;
proxy_busy_buffers_size 64k;
proxy_temp_file_write_size 1m;
add_header X-Frame-Options "SAMEORIGIN";
При необходимости измените настройки, исходя из параметров вашего сервера. Указанные в примере данные хорошо себя показали на не очень мощной машине с 2 GB RAM.
В каталоге /etc/nginx/conf.d/ создадим файл redmine.conf со следующим содержимым:
upstream redmine {
server unix:/var/run/redmine/redmine.0.sock;
}
server {
listen 80;
server_name sharkon.ru;
access_log off;
error_log /var/log/redmine/nginx_error.log;
include /etc/nginx/proxy_params.conf;
proxy_redirect off;
location / {
root /var/www/sharkon.ru;
}
location /redmine {
root /usr/share/redmine/public;
error_page 404 404.html;
error_page 500 502 503 504 500.html;
try_files $uri/index.html $uri.html $uri @redmine_server;
}
location @redmine_server {
proxy_pass http://redmine;
}
}
Здесь указано, что следует использовать для соединения с Redmine сокет, расположенный по пути /var/run/redmine/. Имя сокета формируется из строки, указанной ранее в параметрах thin плюс порядковый номер созданного сокета, начиная с нуля. Далее идёт указание имени нашего сервера и пути, по которому расположен Redmine. Все запросы к нему автоматически будут переадресовываться к созданному ранее сокету.
Миграции
Т.к. мы настраивали доступ к БД вручную, создание её структуры мы будем производить вручную. Это не сложно:
cd /usr/share/redmine
rake db:migrate RAILS_ENV=production
Права на сокет
Так как сокет для redmine будет создаваться пользователем www-data, следует сделать так, чтобы после перезагрузки системы Redmine мог создать его заново. Для этого мы сменим владельца папки с сокетами и pid-файлом.
cd /var/run
mkdir redmine
chown www-data:www-data redmine/
Кодировка CSV-файлов
По-умолчанию Redmine генерирует все отчёты в кодировке UTF-8. Пакет Microsoft Office использует кодировку Win1251, поэтому CVS-отчёт, открытый с помощью Excel, будет показывать кракозябры. Следует всего лишь в одном месте изменить настройки:
cd /usr/share/redmine/config/locale
nano ru.yml
Здесь следует найти строку:
general_csv_encoding: UTF-8
и заменить её на
general_csv_encoding: CP1251
При необходимости здесь же можно изменить и другие параметры локализации Redmine, например, перевод некоторых слов и кодировку файлов pdf.
Запуск
Все подготовительные операции выполнены, можно запустить сам Redmine и перезапустить nginx.
service thin start
service nginx restart
Redmine будет доступен по адресу
Оптимизация PostgreSQL
В этой статье я хочу поделиться своими знаниями в области оптимизации PostgreSQL и ссылками на весьма авторитетные источники. Очень подробное руководство с полным описанием всех параметров настройки производительности (и не только) СУБД PostreSQL написал Алексей Васильев.
Вот его страничка.
А вот его замечательная книга.
Книга распространяется абсолютно бесплатно, кроме того, описанные в ней приёмы актуальны для последних версий PostgeSQL. От себя хочу лишь добавить небольшую выдержку из этого фундаментального труда.
Для оптимизации PostgreSQL я использую утилиту pgtune.
apt-get install pgtune
После её установки надо скопировать конфигурационный файл postresql.conf и запустить эту утилиту с некоторыми параметрами:
cd /etc/postgresql/9.3/main cp postgresql.conf postgresql.conf.bak pgtune -i postgresql.conf.bak -o postgresql.conf
Кратко по параметрам:
-i postgresql.conf.bak
Задаёт имя файла, настройки из которого нужно взять за основу
-o postgresql.conf
Имя файла, в который нужно вывести оптимизированные настройки.
Помимо этих двух параметров утилита pgtune имеет ещё кучу дополнительных опций. Самой полезной лично для меня оказалась директива
--typeОна позволяет указать, для каких целей планируется использовать СУБД, что влияет на параметры оптимизации. Для веб-сайтов, например, можно написать вот так:
pgtune -i postgresql.conf.bak -o postgresql.conf --type web
Другие ключи можно посмотреть в описании утилиты по команде
man pgtune
Установка и настройка PostgreSQL в CentOS
По-умолчанию в CentOS 6.4 идет Postgresql версии 8.4. Нам не надо такую старую, поэтому первым делом нужно добавить официальный репозиторий и запретить установку старых пакетов. Все операции выполняются под root'ом.
Идем сюда и смотрим ссылку на последнюю стабильную версию для CentOS. На момент написания статьи это была версия 9.2, ссылка на пакет была такой
Скачиваем его командой wget:
wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm
Если система выдает сообщение, что wget - неизвестная команда, его нужно установить:
yum install wget -y
После успешной установки повторяем запрос. Итак, Файл репозитория скачался, пора его импортировать:
rpm -i pgdg-centos92-9.2-6.noarch.rpm
Теперь необходимо запретить системе устанавливать старые пакеты из стандартного репозитория. Открываем любым редактором файл /etc/yum.repos.d/CentOS-Base.repo и добавляем в секции [base] и [updates] строку
exclude=postgresql*
Должно получиться примерно так (комментарии удалены):
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
[extras]
name=CentOS-$releasever - Extras
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
#baseurl=http://mirror.centos.org/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
[centosplus]
name=CentOS-$releasever - Plus
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
#baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
[contrib]
name=CentOS-$releasever - Contrib
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib
#baseurl=http://mirror.centos.org/centos/$releasever/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Теперь нужно обновить локальную копию списка пакетов:
yum update
А вот теперь можно установить сервер:
yum install postgresql92-server -y
Теперь сервер нужно настроить. Инициализируем его:
service postgresql-9.2 initdb
и добавим в автозагрузку:
chkconfig postgresql-9.2 on
Все настройки сервера хранятся в каталоге /var/lib/pgsql/9.2/data/. Отредактируем файл pg_hba.conf
vim /var/lib/pgsql/9.2/data/pg_hba.conf
В этом файле несколько строк, которые отвечают за параметры доступа к серверу. Следует обратить внимание на строку
local all all ident
Не вдаваясь в детали скажу, что эта строка разрешает подключение локальных пользователей к серверу без ввода логина и пароля, если пользователь авторизован в системе. Пока не будем ничего менять, вернемся сюда позже. Сейчас нужно войти в систему под именем пользователя postgres:
su postgres
Запускаем оболочку psql
bash-4.1$ psql
Нас пускают без ввода пароля, выдавая окно приветствия:
psql (9.2.4)
Введите "help", чтобы получить справку.
postgres=#
Зададим пользователю postgres пароль, чтобы иметь возможность более простым способом авторизоваться в СУБД. Ввод точки с запятой в конце команды обязателен.
ALTER ROLE postgres WITH PASSWORD 'postgres';
Система ответит:
ALTER ROLE
Можно покинуть интерпретатор, для этого вводим в консоли команду
postgres=# \q
Выходим из-под пользователя postgres:
bash-4.1$ exit
Теперь внесем изменения в вышеуказанный файл pg_hba, приведя его примерно к такому виду:
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.2/32 md5
Первая строка разрешает подключение к СУБД для всех локальных пользователей, но при этом требует с них ввод пароля. Вторая делает практически то же самое, но мы эту строку оставим для того, чтобы подключаться к СУБД могли даже те пользователи, у кого нет системной учетной записи, а есть только роль в СУБД. Например, Вы добавили несколько баз данных и указали для них владельцев, но при этом не хотите, чтобы они имели возможность входить в систему. При этом эти люди не будут ограничены в работе со своей базой данных.
Последняя строка разрешает подключаться к любой базе любому пользователю с адреса 192.168.1.2, требуя взамен логин и пароль. Чтобы эта настройка работала, необходимо в файле postgresql.conf раскомментировать и изменить строку
#listen_addresses = 'localhost'
на
listen_addresses = '*'
Это заставит СУБД слушать все сетевые интерфейсы в поисках входящих подключений. Удобно (и очень опасно), если Вы хотите управлять СУБД через pgAdmin удаленно. Остальные настройки можно оставить по-умолчанию.
Перезапускаем службу:
service postgresql-9.2 restart
Если всё нормально, видим:
Останавливается служба postgresql-9.2: [ OK ]
Запускается служба postgresql-9.2: [ OK ]
Пробуем подключиться от имени пользователя postgres:
psql -U postgres
Поздравляю! Можете приступать к изучению консольных команд PostgreSQL. В самом простом случае Вам нужно будет создать пользователя СУБД и добавить для него базу данных. Запустите интерпретатор psql:
psql -U postgres
Введите команды:
CREATE ROLE abcd WITH PASSWORD 'dcba' LOGIN;
CREATE DATABASE aabbccdd WITH OWNER abcd;
Вы только что создали пользователя abcd с паролем dcba, разрешили ему вход в СУБД и добавили базу данных, указав, что ее владельцем является этот новый пользователь, т. е. он имеет все необходимые привилегии для работы с этой базой. Если захотите подключиться к БД aabbccdd от имени пользователя abcd, введите в консоли команду psql с вот этими ключами:
psql -U abcd -d aabbccdd
Если не указать ключ -d и имя БД следом за ним, PostgreSQL попытается подключиться к БД с тем же названием, что и имя текущего пользователя, не найдет ее и выдаст сообщение об ошибке.