Установка и настройка PostgreSQL

PostgreSQL - это объектно-реляционная система управления базами данных, базирующаяся на СУБД Postgres, которая разработана в Калифорнийском университете в Беркли. ОРСУБД PostgreSQL поддерживает большую часть возможностей стандарта SQL и большинство современных технологий:
  • Сложные запросы
  • Внешние ключи
  • Триггеры
  • Представления
  • Транзакции
  • Управление конкурентным доступом с помощью многоверсионности
Так же PostgreSQL позволяет пользователю добавлять свои:
  • Типы данных
  • Функции
  • Операторы
  • Агрегатные функции
  • Методы индексирования
  • Языки программирования
Установку и настройку PostgreSQL я буду производить на FreeBSD 7.4. Для установки я выбрал версию сервера - 8.3, ибо ее возможностей мне вполне хватает. Идем в порты и устанавливаем:
  1. # cd /usr/ports/databases/postgresql83-server
  2. # make install clean
Описание доступных опций (опции, которые выбрал я - подчеркнуты):
  • NLS - использовать многоязычные сообщения
  • PAM - поддержка PAM
  • LDAP - поддержка аутентификации через LDAP
  • MIT_KRB5 - поддержка MIT kerberos
  • HEIMDAL_KRB5 - поддержка Heimdal kerberos
  • OPTIMIZED_CFLAGS - использовать ключи оптимизации для компилятора при компиляции
  • XML - поддержка типа данных XML
  • TZDATA - использовать внутреннюю базу данных временных зон
  • DEBUG - включить отладочные символы
  • ICU - использовать ICU для работы с текстом в юникоде
  • INTDATE - использовать 64-х битный тип данных data/time
После завершения установки нужно инициализировать кластер базы данных и только потом запускать сервис. Добавим сервис в автозагрузку и укажем дополнительные параметры инициализации в /etc/rc.conf:
  1. postgresql_enable="YES"
  2. postgresql_data="/usr/local/pgsql/data"
  3. postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
  4. postgresql_class="postgresql"
Так же я создал новый класс пользователей с кодировкой по умолчанию UTF8 специально для сервиса postgresql и задал его пользователю pgsql (не забываем ребилдидь базу данных классов пользователей командой cap_mkdb).
  1. # cat /etc/login.conf
  2. ---[вырезано]---
  3. postgresql|PostgreSQL User Account:\
  4.         :charset=UTF-8:\
  5.         :lang=ru_RU.UTF-8:\
  6.         :tc=default:
  7. ---[вырезано]---
Теперь можно инициализировать кластер:
  1. # service postgresql initdb
Теперь переходим к настройке сервиса. По умолчанию все конфигурационные файлы хранятся в корне кластера, в моей конфигурации это /usr/local/pgsql/data. Управление параметрами сервиса осуществляется тремя конфигурационными файлами:
  • postgresql.conf - основной конфигурационный файл
  • pg_hba.conf - в этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к серверу, а также методы идентификации клиентов
  • pg_ident.conf - этот файл используется при установке способа идентификации клиентов через ident сервер
Приведу пример моего конфигурационного файла postgresql.conf, далее в статье я опишу подробно некоторые опции.
  1. # -----------------------------
  2. # Конфигурационный файл PostgreSQL
  3. # -----------------------------
  4. #
  5. # Формат файла такой:
  6. #
  7. #   name = value
  8. #
  9. # Знак "=" не обязателен, можно просто использовать пробел. Комментарии
  10. # начинается со знака "#" и может находиться в любой части строки. Полный
  11. # список возможных параметров можно найти в документации PostgreSQL.
  12. #
  13. # Прокомментированные настройки в этом файле инициализированы значениями 
  14. # по умолчанию. Не забывайте перезагружать сервер после изменения параметров.
  15. #
  16. # Этот файл читается при запуске сервера и при получении им сигнала
  17. # SIGHUP. После редактирования конфигурационного файла вы должны
  18. # послать сигнал SIGHUP серверу, чтобы он перечитал его и изменения
  19. # вступили в силу (так же, вы можете использовать команду pg_ctl reload).
  20. # Некоторые параметры, представленные ниже, требуют перезапуска сервера.
  21. #
  22. # Любой параметр также можно задать с помощью аргументов командной строки,
  23. # например "postgres -c log_connections=on". Некоторые параметры можно
  24. # задать во время сессии с помощью SQL команды "SET".
  25. #
  26. # Memory units:  kB = kilobytes MB = megabytes GB = gigabytes
  27. # Time units:    ms = milliseconds s = seconds min = minutes h = hours d = days
  28.  
  29.  
  30. #------------------------------------------------------------------------------
  31. # Директории и файлы
  32. #------------------------------------------------------------------------------
  33.  
  34. # Значения по умолчанию для этих опций передаются с помощью
  35. # параметра командной строки -D или переменной окружения PGDATA,
  36. # которая представлена здесь как ConfigDir.
  37.  
  38. #data_directory = 'ConfigDir'		# путь до кластера БД
  39. 					# (требуется перезагрузка)
  40. #hba_file = 'ConfigDir/pg_hba.conf'	# host-based authentication file
  41. 					# (требуется перезагрузка)
  42. #ident_file = 'ConfigDir/pg_ident.conf'	# ident configuration file
  43. 					# (требуется перезагрузка)
  44.  
  45. # Если этот параметр не задан, то PID файл создаваться не будет.
  46. #external_pid_file = '(none)'		# Записывать PID файл
  47. 					# (требуется перезагрузка)
  48.  
  49.  
  50. #------------------------------------------------------------------------------
  51. # Подключения и аутентификация
  52. #------------------------------------------------------------------------------
  53.  
  54. # - Параметры подключения -
  55.  
  56. listen_addresses = '*'			# на каком IP адресе(ах) принимать подключения;
  57. 					# список разделенный запятыми;
  58. 					# по умолчанию 'localhost', '*' = all
  59. 					# (требуется перезагрузка)
  60. port = 5432				# (требуется перезагрузка)
  61. max_connections = 30			# (требуется перезагрузка)
  62. # Внимание: Увеличение значения параметра max_connections потребует
  63. # приблизительно 400 байт в разделяемой памяти на подключение, плюс
  64. # блокировка пространства (смотрите max_locks_per_transaction). Так же
  65. # вы должны увеличить параметр shared_buffers, чтобы принимать больше
  66. # подключений. 
  67. superuser_reserved_connections = 1	# (требуется перезагрузка)
  68. #unix_socket_directory = ''		# (требуется перезагрузка)
  69. #unix_socket_group = ''			# (требуется перезагрузка)
  70. #unix_socket_permissions = 0777		# 0 вначале означает 8-ую систему счисления
  71. 					# (требуется перезагрузка)
  72. #bonjour_name = ''			# По умолчанию - имя компьютера
  73. 					# (требуется перезагрузка)
  74.  
  75. # - Безопасность и аутентификация -
  76.  
  77. authentication_timeout = 1min		# 1s-600s
  78. ssl = on				# (требуется перезагрузка)
  79. #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
  80. 					# (требуется перезагрузка)
  81. #ssl_renegotiation_limit = 512MB	# amount of data between renegotiations
  82. password_encryption = on
  83. #db_user_namespace = off
  84.  
  85. # Kerberos and GSSAPI
  86. #krb_server_keyfile = ''		# (требуется перезагрузка)
  87. #krb_srvname = 'postgres'		# (требуется перезагрузка, только Kerberos)
  88. #krb_server_hostname = ''		# пустая строка, значит любая запись keytab
  89. 					# (требуется перезагрузка, только Kerberos)
  90. #krb_caseins_users = off		# (требуется перезагрузка)
  91. #krb_realm = ''           		# (требуется перезагрузка)
  92.  
  93. # - TCP Keepalives -
  94. # смотрите "man 7 tcp"
  95.  
  96. #tcp_keepalives_idle = 0		# TCP_KEEPIDLE, in seconds;
  97. 					# 0 - использовать системное значение
  98. #tcp_keepalives_interval = 0		# TCP_KEEPINTVL, in seconds;
  99. 					# 0 - использовать системное значение
  100. #tcp_keepalives_count = 0		# TCP_KEEPCNT;
  101. 					# 0 - использовать системное значение
  102.  
  103.  
  104. #------------------------------------------------------------------------------
  105. # Использование ресурсов (кроме WAL)
  106. #------------------------------------------------------------------------------
  107.  
  108. # - Память -
  109.  
  110. shared_buffers = 128MB			# минимум 128kB или max_connections*16kB
  111. 					# (требуется перезагрузка)
  112. temp_buffers = 8MB			# минимум 800kB
  113. max_prepared_transactions = 0		# может быть 0 или больше
  114. 					# (требуется перезагрузка)
  115. # Внимание: Увеличение значения параметра max_prepared_transactions потребует
  116. # приблизительно 600 байт в разделяемой памяти на подключение, плюс
  117. # блокировка пространства (смотрите max_locks_per_transaction).
  118. work_mem = 2MB				# минимум 64kB
  119. maintenance_work_mem = 16MB		# минимум 1MB
  120. max_stack_depth = 32MB			# минимум 100kB
  121.  
  122. # - Карта свободного пространства -
  123.  
  124. max_fsm_pages = 179200			# минимум max_fsm_relations*16, 6 байт на каждую
  125. 					# (требуется перезагрузка)
  126. #max_fsm_relations = 1000		# минимум 100, ~70 байт на каждый
  127. 					# (требуется перезагрузка)
  128.  
  129. # - Использование ядерных ресурсов -
  130.  
  131. #max_files_per_process = 1000		# минимум 25
  132. 					# (требуется перезагрузка)
  133. #shared_preload_libraries = ''		# (требуется перезагрузка)
  134.  
  135. # - Cost-Based Vacuum Delay -
  136.  
  137. #vacuum_cost_delay = 0			# 0-1000 milliseconds
  138. #vacuum_cost_page_hit = 1		# 0-10000 credits
  139. #vacuum_cost_page_miss = 10		# 0-10000 credits
  140. #vacuum_cost_page_dirty = 20		# 0-10000 credits
  141. #vacuum_cost_limit = 200		# 1-10000 credits
  142.  
  143. # - Background Writer -
  144.  
  145. #bgwriter_delay = 200ms			# 10-10000ms between rounds
  146. #bgwriter_lru_maxpages = 100		# 0-1000 max buffers written/round
  147. #bgwriter_lru_multiplier = 2.0		# 0-10.0 multipler on buffers scanned/round
  148.  
  149.  
  150. #------------------------------------------------------------------------------
  151. # Ведение лога
  152. #------------------------------------------------------------------------------
  153.  
  154. # - Установки -
  155.  
  156. fsync = on				# Вкл/выкл синхронизацию
  157. synchronous_commit = on		# Вызов fsync при коммите
  158. wal_sync_method = fsync		# the default is the first option 
  159. 					# supported by the operating system:
  160. 					#   open_datasync
  161. 					#   fdatasync
  162. 					#   fsync
  163. 					#   fsync_writethrough
  164. 					#   open_sync
  165. full_page_writes = on			# recover from partial page writes
  166. wal_buffers = 128kB			# минимум 32kB
  167. 					# (требуется перезагрузка)
  168. #wal_writer_delay = 200ms		# 1-10000 milliseconds
  169.  
  170. #commit_delay = 0			# диапазон 0-100000, в микросекундах
  171. #commit_siblings = 5			# диапазон 1-1000
  172.  
  173. # - Контрольные точки -
  174.  
  175. #checkpoint_segments = 3		# сегменты в лог файле, минимум 1, каждый 16MB
  176. #checkpoint_timeout = 5min		# диапазон 30s-1h
  177. #checkpoint_completion_target = 0.5	# checkpoint target duration, 0.0 - 1.0
  178. #checkpoint_warning = 30s		# 0 is off
  179.  
  180. # - Архивирование -
  181.  
  182. archive_mode = off		# разрешить архивирование
  183. 				# (требуется перезагрузка)
  184. #archive_command = ''		# команда, используемая для архивирования сегментов
  185. #archive_timeout = 0		# принудительно переключать сегмент в лог файле
  186. 				# по истечению времени; 0 - выключено
  187.  
  188.  
  189. #------------------------------------------------------------------------------
  190. # Тюнинг планировщика запросов
  191. #------------------------------------------------------------------------------
  192.  
  193. # - Конфигурация методов планирования  -
  194.  
  195. enable_bitmapscan = on
  196. enable_hashagg = on
  197. enable_hashjoin = on
  198. enable_indexscan = on
  199. enable_mergejoin = on
  200. enable_nestloop = on
  201. enable_seqscan = on
  202. enable_sort = on
  203. enable_tidscan = on
  204.  
  205. # - Planner Cost Constants -
  206.  
  207. #seq_page_cost = 1.0			# measured on an arbitrary scale
  208. #random_page_cost = 4.0			# same scale as above
  209. #cpu_tuple_cost = 0.01			# same scale as above
  210. #cpu_index_tuple_cost = 0.005		# same scale as above
  211. #cpu_operator_cost = 0.0025		# same scale as above
  212. #effective_cache_size = 128MB
  213.  
  214. # - Оптимизатор запросов -
  215.  
  216. geqo = on
  217. #geqo_threshold = 12
  218. #geqo_effort = 5			# диапазон 1-10
  219. #geqo_pool_size = 0			# selects default based on effort
  220. #geqo_generations = 0			# selects default based on effort
  221. #geqo_selection_bias = 2.0		# диапазон 1.5-2.0
  222.  
  223. # - Другие параметры планировщика -
  224.  
  225. #default_statistics_target = 10		# диапазон 1-1000
  226. #constraint_exclusion = off
  227. #from_collapse_limit = 8
  228. #join_collapse_limit = 8		# 1 disables collapsing of explicit 
  229. 					# JOIN clauses
  230.  
  231.  
  232. #------------------------------------------------------------------------------
  233. # Лог ошибок
  234. #------------------------------------------------------------------------------
  235.  
  236. # - Куда слать логи -
  237.  
  238. #log_destination = 'syslog'
  239. log_destination = 'stderr'		# Возможные значение:
  240. 					# stderr, csvlog, syslog and eventlog,
  241. 					# зависят от платформы.  csvlog
  242. 					# требует, чтобы logging_collector был включен.
  243.  
  244. # This is used when logging to stderr:
  245. logging_collector = on			# Разрешить сбор сообщений с stderr или csvlog
  246. 					# в лог файлы. Требуется для
  247. 					# csvlogs.
  248. 					# (требуется перезагрузка)
  249.  
  250. # Эти параметры используются только, если параметр logging_collector включен:
  251. log_directory = 'pg_log'			# директория - куда писать логи,
  252. 					# может будь абсолютной или релятивной относительно PGDATA
  253. log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# шаблон имени лог файла,
  254. 					# можно использовать последовательности strftime()
  255. #log_truncate_on_rotation = off		# Если включено, то существующий файл будет
  256. 					# очищен, а запись будет начата сначала файла.
  257. 					# Файл будет очищаться только по срабатыванию
  258. 					# таймера, то есть при перезапуске сервера или
  259. 					# при достижении определенного размера,
  260. 					# файл очищаться не будет. По умолчанию
  261. 					# выключено, что означает дописывание
  262. 					# в конец существующих файлов во всех случаях.
  263. #log_rotation_age = 1d			# Автоматическая ротация логов будет производиться
  264. 					# по истечению заданного времени. 0 - выключено.
  265. #log_rotation_size = 10MB			# Автоматическая ротация логов будет производиться
  266. 					# при достижении размера файла заданных размеров.
  267. 					# 0 - выключено.
  268.  
  269. # Эти параметры имеют значение, если логи шлются в syslog:
  270. #syslog_facility = 'LOCAL0'
  271. #syslog_ident = 'postgres'
  272.  
  273.  
  274. # - Уровень информативности -
  275.  
  276. #client_min_messages = notice		# значения представлены в порядке
  277. 					#  понижения информативности:
  278. 					#   debug5
  279. 					#   debug4
  280. 					#   debug3
  281. 					#   debug2
  282. 					#   debug1
  283. 					#   log
  284. 					#   notice
  285. 					#   warning
  286. 					#   error
  287.  
  288. #log_min_messages = notice		# values in order of decreasing detail:
  289. 					#   debug5
  290. 					#   debug4
  291. 					#   debug3
  292. 					#   debug2
  293. 					#   debug1
  294. 					#   info
  295. 					#   notice
  296. 					#   warning
  297. 					#   error
  298. 					#   log
  299. 					#   fatal
  300. 					#   panic
  301.  
  302. #log_error_verbosity = default		# terse, default, or verbose messages
  303.  
  304. #log_min_error_statement = error	# values in order of decreasing detail:
  305. 				 	#   debug5
  306. 					#   debug4
  307. 					#   debug3
  308. 					#   debug2
  309. 					#   debug1
  310. 				 	#   info
  311. 					#   notice
  312. 					#   warning
  313. 					#   error
  314. 					#   log
  315. 					#   fatal
  316. 					#   panic (effectively off)
  317.  
  318. #log_min_duration_statement = -1		# -1 is disabled, 0 logs all statements
  319. 					# and their durations, > 0 logs only
  320. 					# statements running at least this time.
  321.  
  322. silent_mode = on
  323. #silent_mode = off				# не используйте без syslog или
  324. 					# logging_collector
  325. 					# (требуется перезагрузка)
  326.  
  327. # - Что писать в лог -
  328.  
  329. #debug_print_parse = off
  330. #debug_print_rewritten = off
  331. #debug_print_plan = off
  332. #debug_pretty_print = off
  333. #log_checkpoints = off
  334. #log_connections = off
  335. #log_disconnections = off
  336. #log_duration = off
  337. #log_hostname = off
  338. #log_line_prefix = ''				# специальные значения:
  339. 					#   %u = имя пользователя
  340. 					#   %d = имя БД
  341. 					#   %r = удаленный хост и порт
  342. 					#   %h = удаленный хост
  343. 					#   %p = идентификатор процесса
  344. 					#   %t = время без миллисекунд
  345. 					#   %m = время с миллисекундами
  346. 					#   %i = command tag
  347. 					#   %c = идентификатор сессии
  348. 					#   %l = session line number
  349. 					#   %s = время старта сессии
  350. 					#   %v = идентификатор виртуальной транзакции
  351. 					#   %x = идентификатор транзакции (0, если нет)
  352. 					#   %q = stop here in non-session
  353. 					#        processes
  354. 					#   %% = '%'
  355. 					# e.g. '<%u%%%d> '
  356. #log_lock_waits = off			# log lock waits >= deadlock_timeout
  357. #log_statement = 'none'			# none, ddl, mod, all
  358. #log_temp_files = -1			# log temporary files equal or larger
  359. 					# than specified size;
  360. 					# -1 disables, 0 logs all temp files
  361. #log_timezone = unknown			# actually, defaults to TZ environment
  362. 					# setting
  363.  
  364.  
  365. #------------------------------------------------------------------------------
  366. # Сбор статистики
  367. #------------------------------------------------------------------------------
  368.  
  369. # - Query/Index Statistics Collector -
  370.  
  371. track_activities = on
  372. track_counts = on
  373. #update_process_title = on
  374.  
  375. # На FreeBSD включение этого параметра приведет к падению производительности,
  376. # так что оставьте его выключенным, если вам важна производительность.
  377. update_process_title = off
  378.  
  379. # - Statistics Monitoring -
  380.  
  381. #log_parser_stats = off
  382. #log_planner_stats = off
  383. #log_executor_stats = off
  384. #log_statement_stats = off
  385.  
  386.  
  387. #------------------------------------------------------------------------------
  388. # Параметры AUTOVACUUM
  389. #------------------------------------------------------------------------------
  390.  
  391. autovacuum = on				# Разрешить запуск процесса autovacuum?  'on' 
  392. 					# требует включенного параметра track_counts.
  393. #log_autovacuum_min_duration = -1		# -1 disables, 0 logs all actions and
  394. 					# their durations, > 0 logs only
  395. 					# actions running at least that time.
  396. autovacuum_max_workers = 1		# максимальное кол-во autovacuum процессов
  397. #autovacuum_naptime = 1min		# период запуска autovacuum процессов
  398. #autovacuum_vacuum_threshold = 50		# минимальное кол-во обновлений строк,
  399. 					# перед vacuum
  400. #autovacuum_analyze_threshold = 50		# минимальное кол-во обновление строк,
  401. 					# перед analyze
  402. #autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
  403. #autovacuum_analyze_scale_factor = 0.1	# fraction of table size before analyze
  404. #autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
  405. 					# (требуется перезапуск)
  406. #autovacuum_vacuum_cost_delay = 20	# default vacuum cost delay for
  407. 					# autovacuum, -1 means use
  408. 					# vacuum_cost_delay
  409. #autovacuum_vacuum_cost_limit = -1		# default vacuum cost limit for
  410. 					# autovacuum, -1 means use
  411. 					# vacuum_cost_limit
  412.  
  413.  
  414. #------------------------------------------------------------------------------
  415. # Настройки по умолчанию для клиентов
  416. #------------------------------------------------------------------------------
  417.  
  418. # - Statement Behavior -
  419.  
  420. #search_path = '"$user",public'		# имена схем
  421. #default_tablespace = ''			# a tablespace name, '' uses the default
  422. #temp_tablespaces = ''			# a list of tablespace names, '' uses
  423. 					# only default tablespace
  424. #check_function_bodies = on
  425. #default_transaction_isolation = 'read committed'
  426. #default_transaction_read_only = off
  427. #session_replication_role = 'origin'
  428. #statement_timeout = 0			# 0 - выключено
  429. #vacuum_freeze_min_age = 100000000
  430. #xmlbinary = 'base64'
  431. #xmloption = 'content'
  432.  
  433. # - Locale and Formatting -
  434.  
  435. datestyle = 'iso, dmy'
  436. #timezone = unknown			# actually, defaults to TZ environment
  437. 					# setting
  438. #timezone_abbreviations = 'Default'     		# Select the set of available time zone
  439. 					# abbreviations.  Currently, there are
  440. 					#   Default
  441. 					#   Australia
  442. 					#   India
  443. 					# You can create your own file in
  444. 					# share/timezonesets/.
  445. #extra_float_digits = 0			# min -15, max 2
  446. #client_encoding = sql_ascii			# actually, defaults to database
  447. 					# encoding
  448.  
  449. # Эти параметры установлены командой initdb, но их можно изменять.
  450. lc_messages = 'en_US.UTF-8'		# локаль для системных сообщений
  451. lc_monetary = 'ru_RU.KOI8-R'		# локаль для денежного формата
  452. lc_numeric = 'ru_RU.KOI8-R'			# локаль для числового формата
  453. lc_time = 'ru_RU.KOI8-R'			# локаль для формата времени
  454.  
  455. # Настройки по умолчанию для полнотекстового поиска
  456. default_text_search_config = 'pg_catalog.russian'
  457.  
  458. # - Other Defaults -
  459.  
  460. #explain_pretty_print = on
  461. #dynamic_library_path = '$libdir'
  462. #local_preload_libraries = ''
  463.  
  464.  
  465. #------------------------------------------------------------------------------
  466. # Управление блокировками
  467. #------------------------------------------------------------------------------
  468.  
  469. #deadlock_timeout = 1s
  470. #max_locks_per_transaction = 64		# min 10
  471. 					# (требуется перезагрузка)
  472. # Внимание: Для каждой блокировки таблицы используется приблизительно 270 байт
  473. # в разделяемой памяти, и max_locks_per_transaction * (max_connections + max_prepared_transactions).
  474.  
  475.  
  476. #------------------------------------------------------------------------------
  477. # Совместимость версий
  478. #------------------------------------------------------------------------------
  479.  
  480. # - Previous PostgreSQL Versions -
  481.  
  482. #add_missing_from = off
  483. #array_nulls = on
  484. #backslash_quote = safe_encoding	# on, off, or safe_encoding
  485. #default_with_oids = off
  486. #escape_string_warning = on
  487. #regex_flavor = advanced		# advanced, extended, or basic
  488. #sql_inheritance = on
  489. #standard_conforming_strings = off
  490. #synchronize_seqscans = on
  491.  
  492. # - Other Platforms and Clients -
  493.  
  494. #transform_null_equals = off
  495.  
  496.  
  497. #------------------------------------------------------------------------------
  498. # CUSTOMIZED OPTIONS
  499. #------------------------------------------------------------------------------
  500.  
  501. #custom_variable_classes = ''		# list of custom variable class names
Вот описание некоторых важных опций:
  • max_connections - максимальное количество одновременных подключений к серверу БД. Используйте этот параметр, чтобы не допустить запуска большого количества процессов сервера. Иначе есть вероятность, что серер БД исчерпает весь объем ОЗУ и будет активно использоваться подкачка, что в свою очередь повлечет за собой падение производительности.
  • shared_buffers - объем разделяемой памяти, используемый сервером БД. По умолчанию - 32 Мб, но может быть меньше, если настройки вашего ядра не позволяют выделить такой объем памяти, определяется при инициализации кластера БД утилитой initdb. Большие значения этого параметра положительно сказываются на производительности сервера БД. Если у вас выделенный компьютер с ОЗУ 1 Гб и больше под сервер БД, то хорошим значением этой переменной будет 25% от объема памяти. При большой нагрузке даже большие значения этого параметра могут быть эффективными, но так как PostgreSQL полагается на кэш ОС, то выделение более 40% от объема памяти вряд ли имеет смысл. Для больших значений этого параметра требуется так же увеличить значение параметра checkpoint_segments. На системах с ОЗУ меньше 1 Гб правильнее будет использовать меньший объем памяти (чем 25%), чтобы не исчерпать весь объем оперативной памяти. На винде большие значения этого параметра могут оказаться эффективными. Вы можете добиться большей производительности сохраняя это значение маленьким и больше используя средства кэширования ОС. Хорошим диапазоном значений для Windows является 64-512 Мб.
  • temp_buffers - максимальный размер временных буферов для каждой сессии. Эта память используется только локально в сессии для временных таблиц. По умолчанию - 8 МБ. Значение может быть изменено во время сессии, но только до первого использования этой памяти.
  • max_prepared_transactions - максимальное количество "prepared" транзакций (смотрите описание SQL команды PREPARE TRANSACTION в документации). Чтобы отключить эту фишку, поставьте значение в 0.
  • work_mem - определяет объем памяти, который будет использоваться внутренними операциями сортировки и хэш-таблицами прежде, чем переключиться на временные дисковые файлы. Учтите, что для сложных запросов несколько внутренних операций сортировки и работа с хэш-таблицами могут работать параллельно (одновременно). Кроме того, несколько сессий могут делать такие операции одновременно. В итоге необходимый объем памяти для этих операций может в несколько раз превышать значение параметра work_mem. Учтите это при выборе значения для этого параметра. Под внутренними операциями сортировки подразумевается - ORDER BY, DISTINCT и слияния.
  • maintenance_work_mem - максимальный объем памяти, используемый для внутренних операций, таких как VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию - 16 Мб. Эти команды выполняются только во во время сессии, так что можно выбирать большие значения для этого параметра, чем для параметра work_mem. Большие значения могут положительно сказаться на производительности vacuuming и скорости восстановления БД из дампа. Только учтите, что процесс autovacuum запускается autovacuum_max_workers раз, поэтому может потребоваться больше свободной памяти.
  • max_stack_depth - максимальная глубина стека. Хорошим значением этого параметра является максимально разрешенная глубина стека в системе.
  • max_fsm_pages - с помощью этого параметра можно управлять картой свободного пространства. Когда что-то удаляется из таблицы, то место занимаемое этим что-то не освобождается на диске, вместо этого занимаемое место просто помечается как "свободно" в карте свободного пространства. Потом это место используется для новых записей. Если на вашем сервере очень много удаляется/добавляется данных из/в таблицы, то большие значения этого параметра могут положительно сказаться на производительности.
  • fsync - если этот параметр включен, то PostgreSQL будет дожидаться физической записи данных на диск, используя системную функцию fsync() или аналогичную ей (смотрите wal_sync_method). Это означает, что кластер БД будет возможно восстановить после системного или аппаратного сбоя. Однако, включение данного параметра очень сильно снижает производительность: когда транзакция завершена, то PostgreSQL будет ждать пока операционная система физически запишет данные на диск. Когда данный параметр выключен, тогда операционная система может оставлять данные в буфере, то есть она сама будет решать в какой момент сбросить его на диск. Такой подход сильно увеличивает производительность. Однако, если произойдет сбой системы (или аппаратный), то данные нескольких последний транзакций могут быть частично или полностью утеряны. В худшем случае, кластер вообще не удастся восстановить. Стоит отметить, что сбой самого сервера БД не вызовет таких проблем, к этому может привести только системный или аппаратный сбой.
  • synchronous_commit - данный параметр определяет, что результат об успешном завершении транзакции (который отправляется клиенту) будет отправлен только тогда, когда физически запишутся данные WAL на диск. По умолчанию - включен. Если выключить, то может быть некоторая разница во времени между положительным результатом завершения транзакции, отправленным клиенту, и физической записи данных этой транзакции на диск (то есть клиент может получить сообщение о том, что транзакция выполнена раньше, чем она физически запишется на диск на сервере). (максимальная разница может составлять 3 * wal_writer_delay). Выключение данного параметра не приведет к необратимому повреждению кластера (как в случае с fsync): системный сбой мог бы привести только к потери нескольких последних транзакций, но кластер остался бы не поврежденным. Так что данный параметр может оказаться хорошей альтернативой для повышения производительности, чем параметр fsync.
  • full_page_writes - если этот параметр включен, то PostgreSQL записывает все содержимое каждой страницы диска в WAL при первом изменении этой страницы после контрольной точки. Это необходимо, потому что запись страницы может быть осуществлена лишь частично, наример, из-за системного сбоя. Что приведет к тому, что на диске окажется страница, в которой новые данные смешаны со старыми. Изменений на уровне строк данных, которые обычно хранятся в WAL, может быть не достаточно для восстановления страницы после системного сбоя. Сохранение содержимого всей страницы гарантирует корректное восстановление, ценой увеличения записываемых данных в WAL. Отключение этого параметра повышает производительность, но может привести к повреждению базы данных в случае системного сбоя или отключения питания. Риск такой же, ка и при отключении fsync, но немного меньше. Отключение данного параметра может быть более безопасно, если у вас железо (например, контроллер с батареей резервного питания) или файловая система (например, ReiserFS 4) уменьшают риск частичной записи страницы на низком уровне.
  • wal_buffers - объем из разделяемой памяти (shared_buffers), используемый для хранения данных WAL. По умолчанию 64Кб. Объем памяти должен быть достаточным, чтобы вместить данные WAL, сгенерированные одной типичной транзакцией.
  • wal_writer_delay - время между раундами записи WAL на диск. В каждом раунде происходит сброс WAL на диск. После чего происходит пауза, на указанное здесь время, и повтор операции. По умолчанию 200 миллисекунд. Во многих системах хорошим значением является значение кратное 10. Установка значения не кратным 10 может приносить такой же эффект как и значение большее, но кратное 10.
  • commit_delay - задержка в микросекундах между записью транзакции в буфер WAL и сбросом его на диск. Ненулевое значение означает, что несколько транзакций может быть обработано одним вызовом fsync(). То есть во время задержки могут поступить еще транзакции, которые будут помещены в буфер WAL, который по окончании задержки будет сброшен на диск. Это полезно при большом количестве транзакций. Но если их не так уж и много, то лучше не использовать данную возможность, чтобы эта задержка не была сделана впустую. Если при успешном завершении транзакции есть еще активные транзакции, количество которых не менее commit_siblings, то будет сделана данная задержка. По умолчанию 0, то есть без задержек.
  • commit_siblings - минимальное количество активных транзакций, чтобы вызвать задержку commit_delay. По умолчанию 5.
Ну вот с основным конфигурационным файлом покончено, теперь можно переходить к настройке параметров доступа к серверу в файле pg_hba.conf. Только сначала очень желательно задать пароль для главного пользователя в кластере БД - pgsql. Для этого запустите сервис и подключитесь к серверу под главным пользователем, и задайте пароль для него, например так:
  1. ALTER ROLE "pgsql" ENCRYPTED PASSWORD '***';
Вот так файл pg_hba.conf выглядит у меня:
  1. # Конфигурационный файл аутентификации клиентов PostgreSQL
  2. # ===================================================
  3. #
  4. # Смотрите главу "Client Authentication" в документации
  5. # PostgreSQL, в которой представлено полное описание этого
  6. # файла. Далее изложены краткие инструкции.
  7. #
  8. # С помощью этого файла можно управлять: каким хостам можно 
  9. # подключаться к серверу, методами аутентификации клиентов,
  10. # каким пользователям разрешено подключаться к серверу,
  11. # к каким БД разрешено подключаться. Формат файла такой:
  12. #
  13. # local      DATABASE  USER  METHOD  [OPTION]
  14. # host       DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
  15. # hostssl    DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
  16. # hostnossl  DATABASE  USER  CIDR-ADDRESS  METHOD  [OPTION]
  17. #
  18. # (Слова в верхнем регистре должны быть заменены актуальными значениями.)
  19. #
  20. # В первой колонке задается тип подключения:
  21. #   "local" - доменные сокеты Unix
  22. #   "host" - TCP/IP сокет с шифрованием и без него
  23. #   "hostssl" - TCP/IP сокет с шифрованием
  24. #   "hostnossl" - TCP/IP сокет без шифрования
  25. #
  26. # DATABASE может быть - "all", имя базы данных или список БД, 
  27. # разделенных запятыми.
  28. #
  29. # USER может быть - "all", имя пользователя, имя группы (начинающееся
  30. # со знака"+") или список, разделенный запятыми.
  31. #
  32. # В колонках DATABASE и USER вы можете указать файл, в котором
  33. # содержаться необходимые данные, для этого просто укажите имя 
  34. # файла, поставив вначале знак "@".
  35. #
  36. # В колонке CIDR-ADDRESS указываются хосты, которым разрешено
  37. # подключаться. 
  38. #
  39. # В колонке METHOD можно указывать такие значения "trust", "reject", "md5",
  40. # "crypt", "password", "gss", "sspi", "krb5", "ident", "pam" или "ldap".
  41. # Учтите, что при методе "password" пароли на сервер отправляются в
  42. # открытом виде, а при методе "md5" - в зашифрованном.
  43. #
  44. # OPTION -  ident или имя PAM сервиса, зависит от колонки METHOD.
  45. #
  46. # Имена БД и пользователей, содержащие пробелы, запятые, кавычки и 
  47. # другие специальные символы должны заключаться в каычки. Если
  48. # заключить в кавычки специальные слова "all", "sameuser" или "samerole",
  49. # то они теряют свое специальное назначение и просто используются, как
  50. # обычный текст.
  51. #
  52. # Этот файл читается при запуске сервера и при получении им сигнала
  53. # SIGHUP. После редактирования конфигурационного файла вы должны
  54. # послать сигнал SIGHUP серверу, чтобы он перечитал его и изменения
  55. # вступили в силу (так же, вы можете использовать команду pg_ctl reload).
  56.  
  57. # Поместите сюда актуальные данные
  58. # ----------------------------------
  59. #
  60. # Если вам нужно управлять не только локальными соединениями,
  61. # то добавляетйте больше записей "host". Так же вам необходимо,
  62. # чтобы сервер принимал подключения не только с локального
  63. # интерфейса, это можно сделать с помощью параметра listen_addresses
  64. # в основном конфигурационном файле или с помощью аргументов
  65. # командной строки -i или -h.
  66. #
  67. # Предупреждение: для локальных подключений ключевое слово "trust"
  68. # будет значить, что любой локальный пользователь сможет подключится
  69. # к серверу PostgreSQL под любым пользователем, даже под
  70. # суперпользователем. 
  71.  
  72.  
  73. # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
  74.  
  75. # "local" is for Unix domain socket connections only
  76. local   all         all                                md5
  77. # IPv4 local connections:
  78. host    all         all           0.0.0.0/0            md5
  79. # IPv6 local connections:
  80. #host    all         all         ::1/128               trust
В принципе, о настройке сервера все. Более подробно обо всех параметрах смотрите описание в документации. Теперь когда сервер настроен, можно его запускать или перезапустите, если он был запущен, чтобы изменения вступили в силу. Убедиться, что сервер запущен можно попробовав подключиться к нему или посмотреть - слушает ли сервис на нужных портах:
  1. # sockstat -4 -l | grep postgres
  2. pgsql    postgres   70634 3  tcp4   192.168.7.253:5432    *:*
Если сервер не запускается, то читайте логи и проверяйте конфиги. Так же хочу сказать пару слов про различные утилиты, облегчающие работу с сервером - полный список всех тулз можно посмотреть на официальном сайте вот здесь. Отмечу только два приложения: кроссплатформенная утилита с хорошим и удобным интерфесом для работы с сервером, называется pgAdmin (в портах /usr/ports/databases/pgadmin3) и веб-морда - phpPgAdmin (в портах /usr/ports/databases/phppgadmin).

Добавить комментарий

Filtered text

CAPTCHA
Этот вопрос предназначен для предотвращения автоматизированной обработки форм.
Fill in the blank.