The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

Оптимизация и администрирование PostgreSQL

   Корень / Программисту и web-разработчику / SQL и базы данных / PostgreSQL специфика / Оптимизация и администрирование PostgreSQL

----* Отмена перехода на зимнее время в PostgreSQL   [комментарии]
  В PostgreSQL используется своя внутренняя таблица временных зон (postgresql-x.x.x/src/timezone), поэтому обновление системной базы zoneinfo не повлияет на перевод часов в PostgreSQL.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Установка PostgreSQL под Windows вручную   Автор: Аноним  [комментарии]
  Задача: установить PostgreSQL в Windows без использования инсталлятора.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Опыт обслуживания базы 1С в PostgreSQL (доп. ссылка 1)   Автор: sashacd  [комментарии]
  Знакомство с СУБД PostgreSQL было определено выходом версии платформы "1С:Предприятие 8.1", в которой была реализована поддержка СУБД PostgreSQL. Но все встречи с PostgreSQL проходили на резервном сервере (с ОС Linux), где методом тестового использования решался вопрос об использовании PostgreSQL в качестве СУБД для рабочей базы 1С. В это время на основном сервере (с ОС Linux) база 1С работала в файл-серверном режиме.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Отмена запущенных запросов в PostgreSQL (доп. ссылка 1)   Автор: Konstantin A Mironov  [комментарии]
 
Для отмены запущенных длительных запросов в PostgreSQL можно воспользоваться
системными SP. Например, отменить множественные INSERT или множество
продолжительных запросов:

   SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'INSERT%';

Если запрос инициирован из интерфейса pgsql, то завершени работы pgsql не
поможет - запрос все равно продолжит свое выполнение, необходимо именно
вызывать pg_cancel_backend.
 
----* Восстановление PostgreSQL после повреждения файлов XLOG (доп. ссылка 1)   Автор: Konstantin A Mironov  [комментарии]
  Бывают случаи, когда файлы журнала транзакций (pg_xlog) могут быть повреждены или случайно удалены. В таком случае PostgreSQL не сможет работать и просто не запустится с подобной ошибкой:
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Ускорение загрузки дампа PostgreSQL на многоядерных системах (доп. ссылка 1)   [комментарии]
  В бета версии PostgreSQL 8.4 в утилите pg_restore появилась поддержка возможности загрузки дампа в несколько параллельных потоков. Например, загрузка дампа базы размером 300 Гб на 8-ядерном сервере занимала стандартным образом 12 часов, при распараллеливании процесса загрузки на 8 потоков, время загрузи сократилось до 3 часов. Полная перезагрузка дампа базы может понадобиться например при миграции с PostgreSQL 8.2 на 8.3 или при переходе с 32- на 64-разрядную сборку системы.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* PostgreSQL: безопасность на уровне строк (row level security)   Автор: FLUF  [комментарии]
  Некоторые базы данных, такие как MSSQL и MySQL5, имеют специальные механизмы для разграничения доступа пользователей к различным ресурсам БД вплоть до ячеек таблиц. PostgreSQL не нуждается в таких излишествах. На практике обычно требуется разграничить доступ на уровне строк. Для того, что бы сделать это, есть простой способ, основанный на использовании представлений, правил и функции current_user() или эквивалентного ей ключевого слова user.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Как в PostgreSQL ограничить число одновременных сессий для базы и пользователя (доп. ссылка 1)   [комментарии]
 
Начиная с PostgreSQL 8.1 появилась возможность ограничения числа соединений к серверу 
для отдельного пользователя и базы:

   ALTER ROLE имя_пользователя CONNECTION LIMIT число;
   ALTER DATABASE имя_базы CONNECTION LIMIT число; 

Можно указать лимит при создании базы или пользователя:
   CREATE USER имя_пользователя CONNECTION LIMIT число ENCRYPTED PASSWORD 'пароль';
   CREATE DATABASE имя_базы OWNER имя_пользователя CONNECTION LIMIT число; 
 
----* Как посмотреть размер баз и таблиц в PostgreSQL   [комментарии]
  Для сопоставления OID номеров и имен баз и таблиц в contrib есть утилита oid2name.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Как вытащить тело PostgreSQL функции из системного каталога (доп. ссылка 1)   Автор: Олег Бартунов  [комментарии]
 
Создаем view:

   CREATE OR REPLACE VIEW funcsource as
      SELECT '--\012create or replace function ' ||
         n.nspname::text || '.'::text || p.proname::text  ||
         '('::text || oidvectortypes(p.proargtypes) || ')\012'::text ||
         'returns ' || t.typname || ' as \'\012' ||
         p.prosrc || '  \'\012' || 
         'language \'' || l.lanname || ' \';\012' as func_source,
         proname as function, nspname as schema, t.typname as rettype,
         oidvectortypes(p.proargtypes) as args, l.lanname as language
      FROM pg_proc p, pg_type t, pg_namespace n, pg_language l
      WHERE p.prorettype = t.oid AND p.pronamespace = n.oid AND p.prolang = l.oid
     AND l.lanname <> 'c' AND l.lanname <> 'internal'  ;

Сохраняем исходные коды функций в файл:
    psql -Atc "select func_source from funcsource;" > functions.out
 
----* Как восстановить template0 в PostgreSQL (доп. ссылка 1)   Автор: Олег Бартунов  [обсудить]
 
Восстановление из резервной template1:
   postgres template1
   create database template0 with template template1
   update pg_database set datallowconn = false where datname='template0';
   vacuum full;
   vacuum freeze; 
 
----* Как в PostgreSQL временно отключить тригеры для ускорения закачки БД (доп. ссылка 1)   Автор: Олег Бартунов  [обсудить]
 
Выключить:
   UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname"='tablename';

Включить:
   UPDATE pg_class SET reltriggers = (
      SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid)
      WHERE relname = 'table name';            
 
----* Как включить помещение в лог всех запросов к PostgreSQL (доп. ссылка 1)   Автор: Brainbug  [комментарии]
 
В файле конфигурации PostgreSQL:
   log_duration = true
   log_pid = true
   log_statement = true
   log_timestamp = true 
 
----* Как увеличить скорость вставки большого числа INSERT в PostgreSQL   [комментарии]
 
Для увеличения скорости помещения большого числа INSERT запросов (например,
дамп в формате pg_dump -D):

    SET autocommit TO 'off';
    строки с INSERT
    COMMIT;
    SET autocommit TO 'on';

Или заключить набор INSERT'ов в одну транзакцию:
    BEGIN;
    INSERT....
    COMMIT;

Вместо INSERT лучше использовать "COPY таблица (список полей) FROM stdin;"
 
----* Как посмотреть статистику работы PostgreSQL и MySQL   [обсудить]
 
Чем сейчас занимается SQL сервер:
   MySQL: mysqladmin processlist
   PostgreSQL: select * from pg_stat_activity;
               select * from pg_stat_database;
Общая статистика по работе сервера:
   MySQL: mysqladmin extended-status; mysqladmin status
   PostgreSQL: select * from pg_stats;
 
----* Что можно сделать когда VACUUM в PostgreSQL выполняется почти часами и часто падает с deadlock.   [комментарии]
 
Иногда, особенно для интенсивно обновляемых таблиц, начинают сыпаться deadlock'и и VACUUM 
выполняется нереально долгое время. Часто проблема из-за поврежденного индекса. Решение:
  REINDEX TABLE таблица;
  REINDEX INDEX индекс;
 
----* Что нужно делать если после отключения по питанию, PostgreSQL не запускается   [комментарии]
 
- Скопируйте содержимое директории pgsql/data
- Если postmaster не загрузился - попробуйте удалить файлы 
  rm -f /tmp/.s.PGSQL.* /usr/local/pgsql/data/postmaster.pid
и попытаться запустить PostgreSQL.
- Если postmaster загрузился, но ни один запрос, включая vacuum, не выполняется,
попробуйте использовать утилиту contrib/pg_resetxlog.
- После всего выполните полный бэкап базы (pg_dumpall) и vacuumdb --all --full --analyze
 
----* Как лучше бэкапить данные баз в PostgreSQL   [обсудить]
 
Полный бэкап всех баз:
  pg_dumpall [-s] [-D] > backup_file
Выборочный бэкап:
  pg_dump [-s] [-D] [-t table] db > backup_file        
-s - записывается только информация о структуре базы, без данных.
-D - формируется бэкап данных в виде INSERT команд.
-t table - бэкап выборочных таблиц.
 
----* Как избавиться от deadlock в PostgreSQL ?   [обсудить]
 
Скорее всего вы используйте индексы типа "Hash". 
Они не очень подходят для баз с множеством параллельных запросов,
замените Hash на BTree.
 
----* Почему в PosgreSQL 7.2 наблюдается разростание таблиц ? Vacuum analyze запускается каждый день.   [комментарии]
 
В PostgreSQL 7.2 полностью изменился механизм работы VACUUM. Теперь для
реальной перестройки содержимого таблицы
и реального освобождения пустых блоков необходимо запускать VACUUM FULL
ANALYZE. Иначе при интенсивном апдейте,
небольшая таблица может превратится в огромного монстра. Во время работы VACUUM
FULL - текущая таблица блокируется.
 
----* Как использовать индексы для оптимизации запросов по полю типа timestamp   [обсудить]
 
EXPLAIN SELECT num FROM news WHERE enter_date < (now() - '30 days'::interval);
По умолчанию используется Seq Scan, несмотря на наличие индекса по
 enter_date, для использования индекса нужно использовать:
     EXPLAIN SELECT num FROM news WHERE enter_date < (SELECT now() - '30 days'::interval);
или 
     set enable_seqscan to off;
 
----* Как в PostgreSQL посмотреть список активных баз и пользователей ?   [обсудить]
 
Список пользователей:
   select * from pg_shadow;

Список баз данных:
   select * from pg_database;

Из командной строки:

   psql -A -q -t -c "select datname from pg_database" template1

В современных версиях PostgreSQL можно просто набрать:
   psql -l
 
----* Решение проблемы сборки PostgreSQL c --enable-multibyte   Автор: Yuri A. Kabaenkov  [обсудить]
 
Если у вас уже установлен postgresql и вы решили добавить поддержку --enable-multibyte, 
то при компиляции возможно возникнет ошибка и компиляция прекратится. Ошибка
связана с функцией pg_encoding_to_char в libpq.
(Компилятор лезет за библиотекой в старый libpq)
Решение:
1. make clean
2. удалить старые библиотеки постгреса или удалить путь к библиотекам из ldconfig.
 
----* Как установить из исходных текстов PostgreSQL   [комментарии]
 
./configure --enable-locale --enable-multibyte=KOI8 --with-perl --enable-unicode-conversion
         
gmake; gmake install
adduser postgres
su - postgres
export LANG=ru_RU.KOI8-R # Убедитесь в том что установлена правильная русская локаль.
/usr/local/pgsql/bin/initdb -E KOI8 -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
 
----* Как оптимизировать работу UPDATE и INSERT операций в PostgreSQL   [обсудить]
 
Можно запретить сброс буферов на диск после каждой транзакции: "-o -F"
data/postgresql.conf:
fsync = off
sort_mem = 2048
effective_cache_size=2000
 
----* Как увеличить производительность PostgreSQL   [обсудить]
 
1. Запустите postmaster c опциями "-o '-F'" (асинхронная запись, возможна потеря данных при сбое)
2. Почаще выполняйте команду vacuum analyze (pgsql/bin/vacuum --analyze).
 
----* Как увеличить максимально возможный размер блока в PostgreSQL   [обсудить]
 
По умолчанию элемент таблицы не может превышать 8Кб.
в src/include/config.h измените значение BLCKSZ 
PS. В PostgreSQL 7.1.x ограничение на размер снято.
 
----* Как увеличить количество shared memory для работы высоконагруженного PostgreSQL   [комментарии]
  В linux: echo 134217728 >/proc/sys/kernel/shmall echo 134217728 >/proc/sys/kernel/shmmax В конфиге ядра FreeBSD: options SYSVSHM
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 
----* Репликация PostgreSQL при помощи londiste из пакета skytools   Автор: Pavel Sorokin  [комментарии]
  Сначала необходимо всё правильно поставить. SkyTools есть в репозиториях постгреса для CentOS, но проблема заключается в том, что в этом пакете не включены дополнительные модули, а именно Pgq_LowLevel. Поэтому сделал следующее: Скачал последнюю версию skytools с сайта https://developer.skype.com/SkypeGarage/DbProjects/SkyTools , сконфигурировал, скомпилировал, и взял оттуда лишь модули для питона Pgq_LowLevel, затем поставил пакет(благо версии пакета в репозитории и модулей совпали) и скопировал модули pgq_lowlevel.so, pgq_triggers.so, logtriga.so в /usr/lib/pgsql/.
...
[Слишком большой объем текста. Скрыт. Для просмотра см. продолжение
]
 

 Версия для печати





Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру