Pgpass postgresql linux где должен находиться

Обновлено: 03.07.2024

В короткой заметке описан cценарий Point In Time Recovery с использованием средств из стандартной поставки PostgreSQL версии 11.

Для создания базовой резервной копии используется штатная утилита — pg_basebackeup.
Для потоковой архивации файлов WAL используется штатная утилита — pg_receivewal

Общий сценарий довольно подробно описан в документации Непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery, PITR), однако довольно общими фразами. Поэтому при попытке реализовать на практике возникли некоторые, хотя и вполне преодолимые шероховатости.

Посмотрел по поиску на Хабре, вроде не нашел статей о PITR штатными средствами. Так, что может быть кому то пригодится, в качестве шаблона-рыбы. Или студентам, как лабораторка ;-)

Конфигурация для тестирования — самая простая, один сервер RedHat Linux, один сервис PostgreSQL(pgdata=/postgres/pgdata, waldir=/postgres/waldir), pg_receivewal запускается от имени postgres, заполнение тестовыми данными не уточняется и оставлено на личноe усмотрение.

Порядок действий по пунктам

1. Создать папки хранения и добавить скрипты

Cкрипты в папке /postgres/scripts

2. Запустить непрерывное архивирование WAL-файлов

Настроить файл .pgpass, для запуска pg_receivewal без пароля:

Настроить сервис для запуска pg_receivewal.Пользователь root.

Запускаем сервис.Пользователь root

Проверяем.Пользователь root.

В случае успешного старта получаем ответ

3. Заполнение тестовыми данными

Любым способом, на усмотрение читателя.

4. Создание базовой резервной копии

Все стандартно и тривиально:

Получаем ответ о успешном выполнении:

5. Имитируем катастрофу БД

Остановить сервер PostgreSQL любым способом.

Удалить папки файлов данных и WAL:

6. Восстановление БД

Создать папки файлов данных и WAL:

Восстановить файлы данных из базовой резервной копии:

В папке /postgres/wal_arc имеется неполный файл WAL(с расширением partial), нужно его переименовать, на случай если точка восстановления окажется в незаполненном файле WAL. Переименовать файл — вручную или скриптом rename.sh:

Для восстановления на заданную точку во времени необходимо подготовить файл recovery.conf:

recovery_target_time задается требуемая временная точка восстановления в формате timestamp.

Запустить сервис PostgreSQL любым доступным способом.

Файл recovery.conf переименован в recovery.done?

Если файл переименован-восстановление прошло успешно.

Дополнительно полезно убедиться, что лог файл содержит следующие строки:

В случае успешного восстановления перезапускаем потоковую архивацию файлов WAL:

Рассмотрим процесс подключения к базам данных, методы подключения и аутентификации в PostgreSQL, а также сопоставление пользователей ОС и ролей БД.

Процесс подключения

Процесс подключение можно разделить на три этапа:

Настройки по умолчанию используется метод аутентификации trast, который позволяет подключаться без аутентификации любым локальным пользователям.

Метод аутентификации trast (alex@deb:

$ psql -U postgres)

Основные настройки

Его местоположение можно изменить задав параметр hba_file в конфигурационном файле postgresql.conf:

При изменении этого файла конфигурацию сервера нужно перечитать, выполнив:

Если вы подключены к СУБД, то узнать местоположение файла можно таким способом:

Файл pg_hba.conf состоит из строк, а строки состоят из следующих полей:

тип подключения;
имя БД;
имя пользователя;
адрес узла;
метод аутентификации;
необязательные дополнительные параметры в виде имя=значение. Эти параметры нужны некоторым методам аутентификации.

Эти строки обрабатываются сверху вниз и применяется первая найденная строка. Таким образом если тип подключения, имя БД, имя пользователя и адрес сервера совпали, то применяется определённый метод аутентификации.

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

Вот пример файла pg_hba.conf, который создаётся при сборке из исходников:

Первая строчка это тип подключения local, в котором используется локальный unix сокет, и не задействована сеть. При таком подключении все пользователи (all) могут подключаться методом trust. О методах поговорим позже.

Третья и четвёртая строки относятся к tcp подключениям (host). При таком подключении все пользователи могут подключаться только из локального хоста (127.0.0.1/32 или ::1/128) используя метод trust.

Последние три строки относятся к репликации. Репликация возможна по сокету (local) и по сети (host) но только с локального хоста (127.0.0.1/32 или ::1/128). Здесь тоже используется метод trust.

Если вы подключены к СУБД, то сможете посмотреть содержимое файла pg_hba.conf с помощью представления pg_hba_file_rules:

Если в строке допущена ошибка, то это представление в поле error покажет ошибку.

Параметры подключения

Теперь рассмотрим параметры подключений!

Типы подключений:

Имя базы данных:

Адрес узла:

Имя роли:

Тип аутентификации:

Пароль в СУБД

Пароль хранится в СУБД в зашифрованном виде при использовании методов аутентификации md5 и scram-sha-256.

Задать пароль роли при её создании можно так:

Создать пароль для уже существующей роли можно так:

Пользователю с пустым паролем будет отказано в доступе при аутентификации по паролю.

Пароли в зашифрованном виде хранятся в системном каталоге, в таблице pg_authid.

При аутентификации пароль можно вводить вручную, но не всегда это удобно. Еще можно установить переменную $PGPASSWORD на клиенте, в неё нужно задать пароль, тогда утилита psql будет использовать пароль из этой переменной. Но это не очень удобно и не безопасно.

Также можно создать файл

/.pgpass. Там можно прописать разные пароли к разным серверам следующим образом:

1. Установка Linux. Можно долго спорить и выбирать дистрибутив. Это скорей вопрос личных предпочтений и «кто на чем привык работать». Я взял Debian из-за его высокой стабильности и устойчивости. К тому же в сети огромное количество статей по любому вопросу работы Debian. Не стану описывать процесс установки. Есть масса мануалов на тему. (Вот очень хороший: Свои пять копеек:

1.2 Я не долго думал над разбивкой диска на разделы. Всю систему поставил в один раздел. (Только СУБД Postgres разнес по разным дискам. Об этом далее)

2. Установка postgres.

2.1 Ставим следующие пакеты: aptitude –R libicu38 libxslt1.1 libxml2 libreadline5

2.3 Ставим Обязательно патчи из каталога /extra (скачанные по ftp по ссылке выше)

2.4 Etersoft указывает, что для работы Postgres нужно установить параметр SHMMAX(shared memory) ядра Linux равным 128 Мб. Даем команду:

echo “kernel.shmmax = 134217728” >> /etc/sysctl.conf

2.5 Меняем права на каталог СУБД:

chown –R postgres:postgres /var/lib/pgsql

и перезагружаем сервер

2.6 Меняем пароль пользователю postgres:

Теперь меняем пользователя:

Меняем пароль внутреннему пользователю СУБД:

alter user postgres with password “PASSWORD”;

Выходим из psql:

3. Настройка postgres.

3.1 Для настройки системы нам понадобятся 2 файла.

/var/lib/pgsql/data/postgresql.conf – отвечает за все основный настройки postgres

/var/lib/pgsql/data/pg_hba.conf - файл настроек доступа к СУБД

3.2 Мой файл /var/lib/pgsql/data/postgresql.conf (Intel Core i7, 8Gb RAM, 40 одновременно работающих пользователей):

max_connections = 150 (максимально большое количество одновременных соединений)

shared_buffers = 75MB (это не память для Postgres, а «размер разделяемой между процессами PostgreSQL памяти, которая нужна для выполнения активных операций»)

work_mem = 64MB («определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др.» Исчисляется по хитрой формуле: (ОЗУ – память_для_всех_запущенных_приложений - shared_buffers) / максимальное_число_одновременных_запросов * число_операций_в_запросе. Сам не высчитывал, а взял из какой-то статьи.)

effective_cache_size = 4096MB (Самый большой объект в БД, который может быть размещен в кэше. Высчитывается как:

2/3 ОЗУ - память_для_всех_запущенных_приложений. Я просто поставил 50% ОЗУ)

autovacuum = on (Периодическая дефрагментация БД)

fsync on (Если не используете RAID с аварийным питанием. Данные пишутся сразу на диск)

maintenance_work_mem = 256MB (Параметр определяет объём памяти, выделяемый для таких операций, как VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Устанавливается в половину объема памяти самой большой таблицы. Рекомендуют не заморачиваться и ставить 32-256Мб)

backslash_quote = safe_encoding
escape_string_warning = off
standard_conforming_strings = off

З.Ы. «если вы комментируете какой-либо параметр в postgresql.conf, это совсем не значит, что он принимает первоначальное значение по умолчанию. PostgreSQL будет помнить значение его последней настройки»

Идем в самый низ файла, находим строки:

TYPE DATABASE USER CIDR-ADDRESS METHOD

host all all 127.0.0.1/32 md5

host all all 192.168.0.0/24 md5

Вместо 192.168.0.0/24 ставим свою сеть и ее разрядность.

3.4 Отредактировали конфиги – перегружаем СУБД:

4. Подключаем базу 1С. (Сервер 1С Предприятие у меня установлен на отдельную машину с Windows. В данной статье не рассматривается его установка на Linux) Есть разные способы. Если мы хотим создать новую базу 1С, то это необходимо делать из оснастки «Администрирование серверов 1С Предприятие» (поставить галочку «Создать, в случае отсутствия»). Этот способ необходим для создания «пустой» базы (например, для последующей загрузки *.dt – архива). Но этот способ не подойдет, если базу 1С будем восстанавливать из архива postgres (*.backup) Вы можете возразить, что архив средствами postgres нам и не нужен. Ниже я покажу, как можно очень удобно делать бэкап на лету средствами postgres, не выгоняя пользователей и без ущерба производительности. Так вот, чтобы восстановить архив 1С *.backup, сделанный средствами СУБД делаем следующее.

Есть очень удобное графическое средство для администрирования Postgresql из-под Windows – утилита pgadmin (5. Хранение базы данных.

5.1 PostgreSQL как и почти любая СУБД критична к дисковой подсистеме.

Наиболее дешевым способом является создание массива типа RAID 0. Т.е. это два диска, объединенных в один массив, при котором скорость доступа к данным увеличивается в два раза (недостаток типа RAID 0 – это низкая надежность. При выходе из строя одного диска, данные теряются. Повысить надежность можно через создание частых бэкапов. См. ниже.). Можно использовать различные аппаратные RAID-контроллеры, но опять же, наиболее дешевым способом организации является программный метод. «Зачастую использование программного RAID более предпочтительно, особенно если в сервере установлен дешевый контроллер». Для построения RAID-массива я использовал утилиту mdadm. Отличная статья по теме: 5.2 Для повышения быстродействия СУБД я разместил систему postgres, логи и сами базы на разные диски. «Выделение для лога транзакций собственных дисковых ресурсов (массива или просто отдельного диска) дает как минимум 12% выигрыш для нагруженных систем.» Т.е. postgres работает на диске где и операционная система Linux, для логов подключаем еще один отдельный жесткий диск, а базы крутятся на тоже отдельном RAID-массиве. Делается это путем создания ссылок.

Тоже самое с папками /var/lib/pgsql/data/pg_clog и /var/lib/pgsql/data/pg_log

Так же переносим каталог с базами:

6.1 pg_dump. Чтобы утилита работала через скрипт без запроса пароля, нужно в каталоге пользователя от имени которого он запускаются положить файл .pgpass: *:*:*:postgres:password

и изменить права на файл (для обеспечения уровня безопасности, требуемого postgres-ом нужно чтобы файл был только на выполнение):

Как я могу назвать psql в Так что не запрашивает пароль?

вот что у меня есть:

однако я не смог найти аргумент, который передает пароль, и поэтому psql всегда запрашивает его.

чтобы ответить на ваш вопрос, есть несколько способов предоставить пароль для аутентификации на основе пароля. Очевидный способ - через подсказку пароля. Вместо этого вы можете указать пароль в файле pgpass или через PGPASSWORD переменные среды. Смотри:

нет возможности указать пароль в качестве аргумента командной строки, поскольку эта информация часто доступна всем пользователям и поэтому небезопасна. Однако в средах Linux / Unix вы можете предоставить переменную среды для одной команды, например это:

вы можете добавить эту командную строку в начале вашего скрипта:

Если вы собираетесь иметь несколько хостов / подключений к базе данных,

обратите внимание, что если у вас есть набор переменных export PGPASSWORD postinfo clearfix">

Если у вас возникли проблемы с windows, как я (я использую Windows 7 64-бит) и set PGPASSWORD=[Password] не работает.

тогда, как сказал Kavaklioglu в одном из комментариев,

вам нужно будет сохранить это в верхней части файла или перед любым использованием, чтобы его установить перед вызовом.

конечно, работает на windows:)

учитывая проблемы безопасности при использовании переменной среды PGPASSWORD, я думаю, что лучшее общее решение заключается в следующем:

напишите свой собственный временный файл pgpass с паролем, который вы хотите использовать.
используйте переменную среды PGPASSFILE, чтобы сообщить psql использовать этот файл.
удалите временный файл pgpass

здесь есть несколько моментов. Шаг 1, чтобы избежать mucking с

/ пользователя.pgpass файл, который может существовать. Вы также должны убедиться, что файл имеет разрешения 0600 или меньше.

некоторые предложили использовать bash, чтобы сократить это следующим образом:

дом на ответ mightybyte для тех, кто не комфортно с * nix Shell scripting, вот рабочий скрипт:

двойной знак доллара ( $$ ) в /tmp/pgpasswd$$ в строке 2 добавляется идентификатор процесса к имени файла, так что этот сценарий может быть запущен более одного раза, даже одновременно, без побочных эффектов.

обратите внимание на использование chmod команда в строке 4-так же, как "не равнина файл" ошибка, что mightybyte описано, есть также "разрешения" ошибка, если это не будет сделано.

в строке 6 вам не придется использовать -h myserver, the -p myport или -U ipetrov вместо флаг, если вы используете по умолчанию (localhost в : 5432) и только один пользователь базы данных. Для нескольких пользователей (но соединение по умолчанию) измените эту строку к

не забудьте сделать скрипт исполняемый файл С

chmod +x runpsql (или как вы называете файл сценария)

Это можно сделать просто с помощью PGPASSWORD. Я использую psql 9.5.10. В вашем случае решение будет

Читайте также: