PostgreSQL в РЭСах

=============================================================

1. Устанавливаем PostgreSQL на linux из rpm

rpm -ivh postgre*

=============================================================

2. Ставим Postgres на автозапуск


Обязательно нужно следить за тем, чтобы postgres стартовал после
oracle, или если oracle нет, то обязательно нужно выставить
правильные параметры ядра!!!

ln -s /etc/init.d/postgresql /etc/init.d/rc5.d/K11postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc5.d/S15postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc3.d/K11postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc3.d/S15postgresql

=============================================================

3. Меняем пароль postgres в БД

linux01:~ # su - postgres
postgres@linux01:~> psql
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# alter role postgres password 'postgres_postgres';
ALTER ROLE
postgres=# \q
postgres@linux01:~> exit
logout
linux01:~ #

=============================================================

4. Меняем содержимое конфигов Postgres

/var/lib/pgsql/data/pg_hba.conf

local all all ident sameuser
host all all 127.0.0.1/32 ident sameuser
host all all 0.0.0.0/0 password

/var/lib/pgsql/data/postgresql.conf

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
max_connections = 100
superuser_reserved_connections = 5
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
max_fsm_pages = 153600
max_stack_depth = 2MB
shared_buffers = 500MB
temp_buffers = 30MB
work_mem = 5MB
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
fsync = off
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
constraint_exclusion = on
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_line_prefix = '%s %h %u '
log_min_duration_statement = 60000
log_min_error_statement = warning
redirect_stderr = on
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = off
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
datestyle = 'iso, dmy'
lc_messages = 'ru_RU.UTF-8'
lc_monetary = 'ru_RU.UTF-8'
lc_numeric = 'ru_RU.UTF-8'
lc_time = 'ru_RU.UTF-8'
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

=============================================================

5. Создаем все что нужно для РЭСа в БД

linux01:~ # mkdir /u02/pgdata/kvres -p
linux01:~ # chown postgres:postgres /u02/pgdata/kvres

Ну и под postgres в любой psql консоли (psql, pgAdmin, EMS, Navicat...)

CREATE ROLE kvres LOGIN ENCRYPTED PASSWORD 'md519ad6b2da7f7bf9f2994481e06047f1b'
NOINHERIT
VALID UNTIL 'infinity';

CREATE TABLESPACE kvres OWNER kvres LOCATION '/u02/pgdata/kvres';

CREATE DATABASE kvres
WITH ENCODING='UTF8'
OWNER=kvres
TABLESPACE=kvres;

-- И в базе kvres делаем схему
CREATE SCHEMA kvres AUTHORIZATION kvres;

=============================================================

6. Настраиваем vacuum & analyze

linux01:~ # mkdir /u02/scheduler/analyze/pgsql/ -p
linux01:~ # touch /u02/scheduler/analyze/pgsql/pgsql.sh
linux01:~ # touch /u02/scheduler/analyze/pgsql/analyze.sql
linux01:~ # touch /u02/scheduler/analyze/pgsql/vacuum.sql
linux01:~ # chown postgres:postgres /u02/scheduler/analyze/pgsql/ -R
linux01:~ # chmod ug+x /u02/scheduler/analyze/pgsql/pgsql.sh


Содержимое /u02/scheduler/analyze/pgsql/pgsql.sh:

#!/bin/bash
su - postgres -c "cd /u02/scheduler/analyze/pgsql; psql -d kvres -f vacuum.sql -o vacuum_execute -t; psql -d nkres -f vacuum_execute -o vacuum_execute.log"
su - postgres -c "cd /u02/scheduler/analyze/pgsql; psql -d kvres -f analyze.sql -o analyze_execute -t; psql -d nkres -f analyze_execute -o analyze_execute.log"

/u02/scheduler/analyze/pgsql/analyze.sql

select 'analyze verbose ' || table_schema || '.' || table_name || ';'
from information_schema.tables
where lower(table_schema) = 'kvres'
and lower(table_type) = 'base table'
order by table_schema, table_name;

/u02/scheduler/analyze/pgsql/vacuum.sql

select 'vacuum full verbose ' || table_schema || '.' || table_name || ';'
from information_schema.tables
where lower(table_schema) = 'kvres'
and lower(table_type) = 'base table'
order by table_schema, table_name;

=============================================================

7. Настраиваем резервное копирование

linux01:~ # mkdir /u02/scheduler/backup/pgsql/ -p
linux01:~ # touch /u02/scheduler/backup/pgsql/pgsql.sh
linux01:~ # chmod ug+x /u02/scheduler/backup/pgsql/pgsql.sh

Содержимое /u02/scheduler/backup/pgsql/pgsql.sh:

#!/bin/bash
export CURDT=$(date +%Y-%m-%d.%H.%M.%S)
mkdir data
mkdir mountpoint
chown postgres:postgres data
chown postgres:postgres mountpoint

su - postgres -c "cd /u02/scheduler/backup/pgsql/data; pg_dump -C -F c -b -f pgsql.kvres "kvres""
tar -cvzf data/pgsql.kvres.$CURDT.tar.gz data/pgsql.kvres
rm data/pgsql.kvres
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
#mount 10.77.44.3:/files/backup/pgsql mountpoint -w
/usr/bin/smbmount \\\\10.77.42.113\\Backup mountpoint -o username=linux,password=linux,rw
cp -fv data/pgsql.kvres.$CURDT.tar.gz mountpoint\pgsql
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint
umount mountpoint

=============================================================

8. Добавляем в планировщик.
То есть в tasks.sh добавляем:

###############################
# Backup PostgreSQL databases #
###############################
cd /u02/scheduler/backup/pgsql
./pgsql.sh
#############################
# VACUUL+ANALYZE postgresql #
#############################
cd /u02/scheduler/analyze/pgsql
./pgsql.sh

=============================================================

9. Делаем пользователя только на чтение

-- Под пользователем postgres
CREATE ROLE "read_ber" NOINHERIT LOGIN PASSWORD 'read_ber';
-- Под пользователем kvres
GRANT USAGE
ON SCHEMA "kvres" TO "read_ber";