Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Резервное копирование PostgreSQL (большие обьемы)

Господа, подскажите советом!

Есть PostgerSQL, который настроен в режиме Master-Slave (pg_basebackup) + master и slave копируются скриптом pg_dump на сервер бекапа через ssh.
Сервер бекапа всего 2.4 Терабайта, а размер БД уже 180 Гигабайт.
Сжатие gzip (самый быстрый по тестам) занимает 10 часов! и недалек час когда время следующего бекапа наступит раньше момента окончания архивирования предыдущего.

Как оптимизировать этот процесс? (бекапить Postgres в сжатом виде за меньшее кол-во времени)
Ответ: Всем спасибо.
Решил вопрос.

Вот таким образом в скрипте бекапа:
pbzip2 -k -v -p4

Архивирует за 75 минут, сжимает 180г до 55 (кстати меньше чем gzip)
Вопрос: Кластер PostgreSQL и возможности PostgreSQL

Хочется оценить возможности использования PostgreSQL в одном из проектов, и хочется прояснить несколько вопросов о возможностях PostgreSQL.

Можно ли как-то настроить кластер PostgreSQL с разделяемым хранилищем? Аналогично как в SQL Server c MS Cluster?
То что я прочитал, не требует разделяемого хранилища, но при этом нагружает базу данных, что выглядит плохо.

Еще вопрос. Как PostgreSQL работает с большими базами и большими объемами памяти? Например, база данных размером 500 Гб, активная часть данных 200 Гб, 256 Гб ОЗУ, 32 ядра процессора. Как PostgreSQL будет работать при этих параметрах при 300+ открытых соединениях, при этом в каждый момент времени 20-30 соединений будут параллельно выполнять какие-то запросы?

Вопрос про надежность. Реально ли получить 24x6 надежность? При том что в течение этих 6 дней даже глубокой ночью имеется минимум несколько сотен активных пользователей, которые будут недовольны даже если база будет недоступна несколько секунд.

И вопрос про оптимизацию. И в MS SQL, и в Оракл имеется много возможностей по оптимизации запросов, при помощи различных хинтов. Как с этим обстоит дело в PostgreSQL?
Ответ:
andsm
Причина почему в Оракл возникает Snapshot Too Old понятно, это можно регулировать.
А как с этим в PostgreSQL?

Не так страшен вакуум, как его малюют. Обычно, autovacuum вполне справляется, при двух условиях:
1. схему базы данных и код, который с ней работает, писали люди, который понимают как работает PG, какие его слабые и сильные стороны. А то был у меня проект, где базу дизайнили люди, пришедшие из мира NoSQL, а весь опыт их работы с реляционными базами ограничивался микроскопическими базами на 4-5 таблиц в MySQL. В этом случае никакой autovacuum не спасет.
2. Админ постгреса должен понимать, что он тюнит в настройках autovacuum и зачем именно так делает.
Вопрос: Восстановление на определенный момент времени

Добрый день!

У меня стоит задача - восстановиться на определенный момент времени на тот же сервер. Вроде ничего сложного - создать новую инстанцию, подсунуть init файл, контрольники, сделать в rman'е set new name и спокойно восстанавливаться until time с бэкапа. Но у этого бэкапа есть одна небольшая особенность, с которой я раньше не встречался. В рмане настроен EXCLUDE FOR TABLESPACE , соответственно одно из таблспейсов не бэкапится. И я в таком случае не очень понимаю, как восстановиться на определенное время:
1. Файлы отресторятся
2. Начнется recovery, которое увидит, что одного таблсейса нету. Соответсвенно recovery остановится.

Я пробовал сделать дубликат базы: если делать until time, то при recovery ругается на этот отсутвущий таблспейс. Если попробовать добавить skip этот tablespace, то ругается при проверке self-contained.

Я не очень понимаю, как накатывать логи при восстановлении, если файла нет. С другой стороны, наверно человек настраивающий бэкап наверняка знал, что делает. Кто-то может подсказать? Заранее спасибо
Ответ:
SKIP READONLY
...
SKIP [FOREVER]
   TABLESPACE
   tablespace_name



SKIP [FOREVER]
   TABLESPACE
   tablespace_name
Вопрос: Сформулировать запрос по остаткам на заданный момент времени

Есть таблица остатков на складе, сохраняющая значения веса и момент времени, когда происходила отгрузка или погрузка части груза в партию.
Поле cargo_card_id - означает партию груза, remains_time - момент времени, когда вес в партии менялся.
CREATE TABLE `cargo_remains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cargo_card_id` int(11) unsigned NOT NULL,
  `weight_netto_remains` decimal(11,4) DEFAULT NULL,
  `weight_brutto_remains` decimal(11,4) DEFAULT NULL,
  `cargo_places_remains` int(11) DEFAULT NULL,
  `remains_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cargo_card_id` (`cargo_card_id`),
  CONSTRAINT `cargo_remains_fk1` FOREIGN KEY (`cargo_card_id`) REFERENCES `cargo_card` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=0;

Необходимо составить запрос, выдающий остатки в каждой партии на заданную дату.
Например, если в партии cargo_card_id=3 груз забирался две недели назад, неделю назад и сегодня, то остаток на вчерашнюю дату должен показать значение, соответствующее записи недельной давности, и не показывать запись двухнедельной давности.
Для одной партии запрос будет выглядеть так:
SELECT *
FROM cargo_remains
WHERE remains_time < :remains_time
AND cargo_card_id = :cargo_card_id
ORDER BY remains_time DESC
LIMIT 1

Но как сделать запрос, чтобы он выдавал аналогичный ответ по всем партиям?
Ответ: svnvlad,

- FAQ: Выборка первой/последней записи в группах.

Первым делом фильтруешь данные по дате, далее - выбираешь из групп последние (по дате) записи.
Вопрос: Вывод разности между значениями поля на два заданных момента времени

Добрый день!
Есть таблица с полями
[Tagname] - текст, уникальное
[Value] - числовой
[DateTime] - соответственно DateTime

Хотелось бы получить отчет вида
[Tagname],[Value1],[Value2],[Value2 - Value1]
где Value1 и Value2 это значения двум моментам времени [DateTime] (начало и конец запрашиваемого периода)

пока ничего умнее чем создать 2 временные таблицы и объединить их по полю [Tagname] с вычислением разности в голову не приходит



Кликните здесь для просмотра всего текста
Код T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT [TagName], [Value]
INTO #tempTable1
FROM AnalogHistory
WHERE
[TagName] LIKE '%\CountHrA'
ND
[DateTime] = '30/7/2015 18:10:00'
 
 
SELECT [TagName], [Value]
INTO #tempTable2
FROM AnalogHistory
WHERE
[TagName] LIKE '%\CountHrA'
AND
[DateTime] = '30/7/2015 20:30:00'
 
 
SELECT #tempTable1.[TagName], ISNULL(#tempTable1.[Value], 0), ISNULL(#tempTable2.[Value], 0),
                              ISNULL(#tempTable2.[Value],0) - ISNULL(#tempTable1.[Value], 0) AS Diff
FROM #tempTable1
JOIN #tempTable2 ON #tempTable1.[TagName] = #tempTable2.[TagName]
GO
 
DROP TABLE #tempTable1;
DROP TABLE #tempTable2;


Как это выполнить с оптимальным использованием ресурсов сервера?
Есть ли смысл обернуть это в хранимую процедуру?

MS SQL 2005 SE
Ответ: что-то типа
Код T-SQL
1
2
3
4
5
6
7
select 
  val1=stuff(min(concat(date,val)),1,10,''), 
  val2=stuff(max(concat(date,val)),1,10,''),
  delta=stuff(min(concat(date,val)),1,10,'')
       -stuff(max(concat(date,val)),1,10,'')
from ...
where date between dt1 and dt2
преобразованиями заниматься лень)
Вопрос: Определить количество открытых сессий в определенный момент времени

Подскажите, как подсчитать количество сессий открытых в определенный момент времени.
Аудит на базе включен.
Ответ: Вот такой запрос получился.
Возвращает в период с :bd по :ed с интервалом в :intr количество открытых сессий
+
WITH s AS
 (SELECT spare1 OS_USERNAME
        ,userid USERNAME
        ,userhost USERHOST
        ,terminal TERMINAL
        ,CAST( /* TIMESTAMP */(from_tz(ntimestamp#, '00:00') at LOCAL) AS DATE) TIMESTAMP
        ,obj$creator OWNER
        ,obj$name OBJECT_NAME
        ,aud.action# ACTION
        ,act.name ACTION_NAME
        ,new$owner NEW_OWNER
        ,new$name NEW_NAME
        ,decode(aud.action#
               ,108 /* grant  sys_priv */
               ,NULL
               ,109 /* revoke sys_priv */
               ,NULL
               ,114 /* grant  role */
               ,NULL
               ,115 /* revoke role */
               ,NULL
               ,auth$privileges) OBJ_PRIVILEGE
        ,decode(aud.action#
               ,108 /* grant  sys_priv */
               ,spm.name
               ,109 /* revoke sys_priv */
               ,spm.name
               ,NULL) SYS_PRIVILEGE
        ,decode(aud.action#
               ,108 /* grant  sys_priv */
               ,substr(auth$privileges, 1, 1)
               ,109 /* revoke sys_priv */
               ,substr(auth$privileges, 1, 1)
               ,114 /* grant  role */
               ,substr(auth$privileges, 1, 1)
               ,115 /* revoke role */
               ,substr(auth$privileges, 1, 1)
               ,NULL) ADMIN_OPTION
        ,auth$grantee GRANTEE
        ,decode(aud.action#
               ,104 /* audit   */
               ,aom.name
               ,105 /* noaudit */
               ,aom.name
               ,NULL) AUDIT_OPTION
        ,ses$actions SES_ACTIONS
        ,logoff$time LOGOFF_TIME
        ,logoff$lread LOGOFF_LREAD
        ,logoff$pread LOGOFF_PREAD
        ,logoff$lwrite LOGOFF_LWRITE
        ,decode(aud.action#
               ,104 /* audit   */
               ,NULL
               ,105 /* noaudit */
               ,NULL
               ,108 /* grant  sys_priv */
               ,NULL
               ,109 /* revoke sys_priv */
               ,NULL
               ,114 /* grant  role */
               ,NULL
               ,115 /* revoke role */
               ,NULL
               ,aud.logoff$dead) LOGOFF_DLOCK
        ,comment$text COMMENT_TEXT
        ,sessionid SESSIONID
        ,entryid ENTRYID
        ,STATEMENT STATEMENTID
        ,returncode RETURNCODE
        ,spx.name PRIVILEGE
        ,clientid CLIENT_ID
        ,auditid ECONTEXT_ID
        ,sessioncpu SESSION_CPU
        ,from_tz(ntimestamp#, '00:00') at LOCAL EXTENDED_TIMESTAMP
        ,proxy$sid PROXY_SESSIONID
        ,user$guid GLOBAL_UID
        ,instance# INSTANCE_NUMBER
        ,process# OS_PROCESS
        ,xid TRANSACTIONID
        ,scn SCN
        ,to_nchar(substr(sqlbind, 1, 2000)) SQL_BIND
        ,to_nchar(substr(sqltext, 1, 2000)) SQL_TEXT
        ,dbid DBID
    FROM sys.aud$_arch         aud
        ,system_privilege_map  spm
        ,system_privilege_map  spx
        ,STMT_AUDIT_OPTION_MAP aom
        ,audit_actions         act
   WHERE aud.action# = act.action(+)
     AND - aud.logoff$dead = spm.privilege(+)
     AND aud.logoff$dead = aom.option#(+)
     AND - aud.priv$used = spx.privilege(+)),
tim AS
 (SELECT :bd + 1 / 1440 * (LEVEL - 1) dt
    FROM dual
  CONNECT BY :bd + :intr / 1440 * (LEVEL - 1) < :ed)
SELECT tim.dt, COUNT(*)
  FROM tim, s
 WHERE tim.dt BETWEEN TIMESTAMP AND nvl(LOGOFF_TIME, SYSDATE)
 GROUP BY tim.dt;
Вопрос: Скорость восстановления данных со временем заметно замедляется

Всем доброе время суток. Пробую смигрировать БД с 32 на 64 битную платформу, есть при этом бэкап БД (весит 233 Гб).
С помощью следующего скрипта делаю рестор:
D:/postgresql/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "asd_production" --no-password  --jobs 4 --verbose "D:\asd_production 2016-01-29 19-00-41.backup"

при этом на начальном этапе скорость космическая (диск SSD), за час восстановилось порядка 75 Гб, и после этого включаются резкие тормоза, за следующий час скорость восстановления - 3 Гб!! Это катастрофа, куда смотреть, что можно подправить? Конфиг прилагаю.

К сообщению приложен файл (postgresql.conf - 19Kb)
Ответ:
Sheriffua
Если с потоковостью бороться не имеет смысла, то какие основные параметры влияют на время обработки данных при ресторе?

Рекомендации по загрузке данных для 9.3 .

На время работы pg_restore установите:
archive_mode = off
max_wal_senders = 0
wal_level = minimal

Это позволит не писать в WAL работу COPY.
Вопрос: Проблемы с rollforward на заданное время

Всем доброго времени суток!
Столкнулся со следующей проблемой при восстановлении ONLINE-бекапа c INCLUDE LOGS и журналов с TSM.
BACKUP и ARCHIVE LOG делается на основном сервере DB2 (9.7.9), а восстановление на дополнительный.
Если делаю rollforward to end of logs - все отрабатывает нормально, а когда пробую восстановиться на некоторое время получаю следующее:

>RESTORE DATABASE NAMEDB use TSM options '-fromnode=XXX_db' TAKEN AT 20160323081950 INTO NAMEDB LOGTARGET 'C:\Backup\NODE0000\NAMEDB\C0000000' WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
DB20000I Команда RESTORE DATABASE выполнена успешно.

> db2adutl extract logs since s0000520.log db NAMEDB nodename 'XXX_db' without prompting
Retrieving LOG ARCHIVE information.
LOG ARCHIVE image:
Log file: S0000520.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2016-03-23-05.18.58
Writing to file:
.\NODE0000\NAMEDB\C0000000\S0000520.LOG
........

LOG ARCHIVE image:
Log file: S0000524.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2016-03-23-12.39.16
Writing to file:
.\NODE0000\NAMEDB\C0000000\S0000524.LOG
LOG ARCHIVE image:
Log file: S0000525.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2016-03-23-17.55.47
Writing to file:
.\NODE0000\NAMEDB\C0000000\S0000525.LOG
LOG ARCHIVE image:
Log file: S0000526.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2016-03-24-10.38.47
Writing to file:
.\NODE0000\NAMEDB\C0000000\S0000526.LOG
LOG ARCHIVE image:
Log file: S0000527.LOG, Chain Num: 0, DB Partition Number: 0, Taken at: 2016-03-24-10.42.59
Writing to file:
.\NODE0000\NAMEDB\C0000000\S0000527.LOG

> rollforward db NAMEDB query status
Состояние повтора
Алиас входной базы данных = NAMEDB
Число узлов с возвращенным состоянием = 1
Номер узла = 0
Статус повтора = DB отложена
Следующий файл журнала на чтение = S0000520.LOG
Обработано файлов журналов = -
Последняя принятая транзакция = 2016-03-23-02.19.53.000000 UTC

> ROLLFORWARD DATABASE NAMEDB TO 2016-03-23-15.39.04 USING LOCAL TIME OVERFLOW LOG PATH ('C:\Backup\NODE0000\NAMEDB\C0000000') NORETRIEVE

Состояние повтора
Алиас входной базы данных = NAMEDB
Число узлов с возвращенным состоянием = 1
Номер узла = 0
Статус повтора = DB работает
Следующий файл журнала на чтение = S0000525.LOG
Обработано файлов журналов = S0000520.LOG - S0000524.LOG
Последняя принятая транзакция = 2016-03-23-15.39.04.000000 Local
DB20000I Команда ROLLFORWARD выполнена успешно.

> rollforward db NAMEDB stop
SQL4970N Восстановление с повтором транзакций для базы данных "NAMEDB" нельзя
довести до заданной конечной точки (конца журналов или момента времени) на
разделах базы данных "0". Восстановление с повтором транзакций остановлено на
файле журнала "S0000525.LOG".

Задействованы ли в данном режиме параметры logretain и userexit. Они оба OFF
Ответ: Mark, спасибо. Все получилось. Указываю время и восстанавливается до последней принятой транзакции.
Вопрос: Postgresql 9.5 не запускается автоматически после сбоя питания

Добрый день!
Помогите, пожалуйста, разобраться со следующей проблемой:
На десктопе под Debian 8.2 установлена тестовая БД PostgreSQL (9.5.3). Настройки БД в postgresql.conf - минимальные - listen_adress, port и т.п. Остально по умолчанию. Файл прилагаю.
Достаточно часто отключают электроэнергию, упса нет.

После появления питания СУБД сама не запускается - лог привожу ниже

автор
2016-08-04 19:13:30 MSK [733-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:30 MSK [731-1] СООБЩЕНИЕ: работа системы БД была прервана; последний момент работы: 2016-08-04 16:15:49 MSK
2016-08-04 19:13:30 MSK [732-1] [н/д]@[н/д] СООБЩЕНИЕ: неполный стартовый пакет
2016-08-04 19:13:31 MSK [773-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:31 MSK [777-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:32 MSK [780-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:32 MSK [812-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:33 MSK [835-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:33 MSK [838-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:34 MSK [848-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:34 MSK [854-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:35 MSK [866-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:35 MSK [871-1] postgres@postgres ВАЖНО: система баз данных запускается
2016-08-04 19:13:35 MSK [671-1] СООБЩЕНИЕ: получен запрос на "вежливое" выключение
2016-08-04 19:13:45 MSK [731-2] СООБЩЕНИЕ: система БД была остановлена нештатно; производится автоматическое восстановление
2016-08-04 19:13:45 MSK [731-3] СООБЩЕНИЕ: неверная длина записи по смещению 1/9F46638
2016-08-04 19:13:45 MSK [731-4] СООБЩЕНИЕ: данные REDO не требуются
2016-08-04 19:13:46 MSK [731-5] СООБЩЕНИЕ: Защита от наложения мультитранзакций сейчас включена
2016-08-04 19:13:46 MSK [1006-1] СООБЩЕНИЕ: выключение
2016-08-04 19:13:46 MSK [1006-2] СООБЩЕНИЕ: система БД выключена


После этого либо запуском вручную либо после перезагрузки компа система стартует нормально.
Как можно обеспечить, чтобы после внештатного выключения в случае успешного восстановления система запускалась автоматически?

К сообщению приложен файл (postgresql.conf - 21Kb)
Ответ: asdasd1,

я подозреваю что настройки systemd виноваты. после какого-то таймаута он выключает базу штатно:

2016-08-04 19:13:35 MSK [671-1] СООБЩЕНИЕ: получен запрос на "вежливое" выключение



посмотрите, какие настройки в /usr/lib/systemd/system/postgresql-9.5.service файле.
Вопрос: Организация резервирования ИБ 1С на postgresql 9.2 CentOS 6.5

Заранее прошу прощения, если неправильно или не корректно излагаю суть своего вопроса, но перерыл кучу информации и зашел в тупик, прошу помощи людей которые с этим столкнулись!

Моя задача следующая: В одном городе А находится сервер 1С на СУБД postgresql 9.2, работают пользователи, делаются архивы - все нормально, но появилась необходимость иметь актуальную данную базу на моем рабочем месте, то есть в другом городе.
Имеется сервер здесь в городе Б, установил на него CentOS 6.5, postgresql 9.2, оба 64 битные, все как по мануалу. Кроме железа полностью идентичные. Актуальная копия нужна хотя бы на 1 день отстающая от эталонной базы.
В городе А на сервере в postgresql.conf изменил:
wal_level = archive
archive_mode = on
archive_command = 'cp -i %p /var/lib/postgresql/backups/archive/%f < /dev/null'

Выполняю базовый бэкап:
pg_basebackup -h osds1c -D /var/lib/postgresql/backups/base

Создается бэкап, создаются wal архивы по 16 мб
Копирую весь бэкап и первые wal файлы на сервер города Б
Очищаю каталог data, меняю содержимым что скопировал с эталона, создаю папку wals для хранения архивов, настраиваю права, создаю recovery.conf с содержимым:
standby_mode = 'on'
restore_command = 'cp /var/lib/postgresql/wals/%f %p'

Запускаю postgresql, стартует без вопросов, в логе все пучком, recovery.conf переименовался в recovery.done. Захожу в базу 1С, полностью идентична. Теперь самое интересное, в городе А захожу в 1С, делаю изменения, создался wal файл, я его копирую к себе, ложу в папку wals, recovery.done переименовываю в recovery.conf, чтобы запустить режим восстановления и подтянуть изменения от этого wal файла и постгрес стартует, но в логах вижу следующее: файл приложил, изменения соответственно не принялись.
Помогите разобраться, что делаю не так?

К сообщению приложен файл. Размер - 65Kb
Ответ: qwwq,

Для начала бы сделать хотябы read only, потоковая репликация или тригерная тоже не спасут я так понял, потому что необходимо чтобы мастер постоянно слушал слейв и обратно, выделенного канала постоянного нет между городами. План был с помощью скриптов копировать на слейв вал логи и скриптами их загружать с помощью восстановления. Поэтому я и не делал акцент на read only