> For the complete documentation index, see [llms.txt](https://utm-1.gitbook.io/utm-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://utm-1.gitbook.io/utm-docs/dokumentaciya/utm-it/resheniya/subd/postgres/instansy/utm-postgres18-na-utm-db4-9.md). # utm-postgres18 на utm-db4-9 ## Общая информация Инстанс Postgres на utm-db4-9 Postgres работает в контейнере Docker.
ИмяЗначение
Сервер Имя utm-db4-9
Сервер IP 192.168.4.9
Порт5432
Порт мониторинга9187
Порт pgAdmin8082
Версия Postgres18
БД
  • postgres
  • db_utm
Пользователи
  • postgres
  • maxim
  • andrew
Репозиторий на GitHubdb-projects
Папка с проектом в репозиторииutm-postgres18
Имя Docker контейнераutm-postgres18
Каталог на сервере с смонтированной папкой данных/var/lib/docker/volumes/docker_utm-postgres18-data
## Предварительная подготовка ### Репозиторий в GitHub Необходимо создать приватный репозиторий db-projects на GitHub. В аккаунте GitHub должен быть прописан публичный SSH ключ для доступа к GitHub. Склонировать репозиторий db-projects **на рабочую станцию где ведется разработка.** ### Linux cервер развертывания
Настройка Linux сервера развертывания Postgres На Linux сервере развертывания должно быть: * [Установлен Docker](https://app.gitbook.com/o/ydyc10nkiaS0aa9uq3Ai/s/UHLel8CyIki7deC8qpxM/~/changes/235/instrukcii/dokumentaciya/virtualnye-servera/utm-server4/system-servers/utm-db4-9/ustanovka-docker) ```bash docker --version # Docker version 28.4.0, build d8eb465 ``` * Установлен Git ```bash git --version # git version 2.43.0 ``` * Группы доступа ```bash # Группа доступа к проектам GitHub sudo addgroup utm-github # Добавить пользователей в группу sudo usermod -aG utm-github maxim sudo usermod -aG utm-github andrew ``` * Папки ```bash # Создать папку sudo mkdir -p /srv/github/utenkov-maxim # Права на доступ к папке sudo chmod -R 0775 /srv/github/utenkov-maxim # Владелец папки sudo chown -R root:utm-github /srv/github/utenkov-maxim # Добавить линк в домашней папке к папке /srv/github/utenkov-maxim ln -s /srv/github $HOME/github ``` * Настройка доступа к GitHub по SSH Скопировать в папку $HOME/.ssh пару SSH ключей доступа к GitHub ```bash # Права на доступ к папке sudo chmod 600 ~/.ssh/id_ed25519_github_utenkov-maxim # Настроить config для SSH sudo nano ~/.ssh/config ``` Добавить следующее содержимое: ```ssh-config Host github.com HostName github.com User git IdentityFile ~/.ssh/id_ed25519_github_utenkov-maxim IdentitiesOnly yes ```
## Подготовка файлов проекта Предварительно должен быть склонирован репозиторий db-projects из GitHub. В папке `db-projects` создать папку проекта `utm-postgres18/docker` и создать следующие файлы: **compose.yaml**
Содержимое файла compose.yaml ```yaml volumes: utm-postgres18-data: utm-pgadmin4-data: services: utm-postgres18: image: postgres:18 container_name: utm-postgres18 restart: always command: postgres -c config_file=/postgresql.conf environment: PGDATA: /var/lib/postgresql/18/docker POSTGRES_USER: ${POSTGRES_USER} POSTGRES_PASSWORD: ${POSTGRES_PASSWORD} POSTGRES_DB: ${POSTGRES_DB} DB_USER1: ${DB_USER1} DB_USER1_PASSWORD: ${DB_USER1_PASSWORD} DB_USER2: ${DB_USER2} DB_USER2_PASSWORD: ${DB_USER2_PASSWORD} DB_NAME: ${DB_NAME} volumes: - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d - ./postgresql.conf:/postgresql.conf - utm-postgres18-data:/var/lib/postgresql/18 ports: - '5432:5432' healthcheck: test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"] interval: 10s timeout: 5s retries: 5 start_period: 10s logging: options: max-size: '50m' pgadmin: image: dpage/pgadmin4:9.10 container_name: utm-pgadmin4 restart: always depends_on: - utm-postgres18 ports: - 8082:80 user: root environment: # Required by pgAdmin PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL} PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD} PGADMIN_LISTEN_ADDRESS: 0.0.0.0 # determines whether pgAdmin runs in server mode (multi-user) or desktop mode (single-user). We’re setting it to false, so we won’t be prompted for login credentials PGADMIN_CONFIG_SERVER_MODE: 'True' # controls whether a master password is required to access saved server definitions and other sensitive information PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: 'True' PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION: 'True' PGADMIN_CONFIG_CONSOLE_LOG_LEVEL: 10 volumes: # This setup will ensure that PGAdmin data from inside container is synced to host machine, enabling persistence across container restarts. - utm-pgadmin4-data:/var/lib/pgadmin' # This setup to make PGAdmin automatically detect and connect to PostgreSQL when it starts (following the config being set in servers.json) - ./pgadmin/servers.json:/pgadmin4/servers.json:ro ```
**postgresql.conf**
Содержимое файла postgresql.conf ```apache # ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, run "pg_ctl reload", or execute # "SELECT pg_reload_conf()". Some parameters, which are marked below, # require a server shutdown and restart to take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # TB = terabytes h = hours # d = days #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '' # write an extra PID file # (change requires restart) #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - TCP settings - # see "man 7 tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds; # 0 selects the system default # - Authentication - #authentication_timeout = 1min # 1s-600s #password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off # GSSAPI using Kerberos #krb_server_keyfile = '' #krb_caseins_users = off # - SSL - #ssl = off #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_min_protocol_version = 'TLSv1' #ssl_max_protocol_version = '' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - shared_buffers = 128MB # min 128kB # (change requires restart) #huge_pages = try # on, off, or try # (change requires restart) #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. #work_mem = 4MB # min 64kB #maintenance_work_mem = 64MB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB #shared_memory_type = mmap # the default is the first option # supported by the operating system: # mmap # sysv # windows # (change requires restart) dynamic_shared_memory_type = posix # the default is the first option # supported by the operating system: # posix # sysv # windows # mmap # (change requires restart) # - Disk - #temp_file_limit = -1 # limits per-process temp file space # in kB, or -1 for no limit # - Kernel Resources - #max_files_per_process = 1000 # min 25 # (change requires restart) # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables) #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 1-10000 credits # - Background Writer - #bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables #bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round #bgwriter_flush_after = 512kB # measured in pages, 0 disables # - Asynchronous Behavior - #effective_io_concurrency = 1 # 1-1000; 0 disables prefetching #max_worker_processes = 8 # (change requires restart) #max_parallel_maintenance_workers = 2 # taken from max_parallel_workers #max_parallel_workers_per_gather = 2 # taken from max_parallel_workers #parallel_leader_participation = on #max_parallel_workers = 8 # maximum number of max_worker_processes that # can be used in parallel operations #old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate # (change requires restart) #backend_flush_after = 0 # measured in pages, 0 disables #------------------------------------------------------------------------------ # WRITE-AHEAD LOG #------------------------------------------------------------------------------ # - Settings - #wal_level = replica # minimal, replica, or logical # (change requires restart) #fsync = on # flush data to disk for crash safety # (turning this off can cause # unrecoverable data corruption) #synchronous_commit = on # synchronization level; # off, local, remote_write, remote_apply, or on #wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync (default on Linux) # fsync # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes #wal_compression = off # enable compression of full-page writes #wal_log_hints = off # also do full page writes of non-critical updates # (change requires restart) #wal_init_zero = on # zero-fill new WAL files #wal_recycle = on # recycle WAL files #wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds #wal_writer_flush_after = 1MB # measured in pages, 0 disables #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_timeout = 5min # range 30s-1d max_wal_size = 1GB min_wal_size = 80MB #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_flush_after = 256kB # measured in pages, 0 disables #checkpoint_warning = 30s # 0 disables # - Archiving - #archive_mode = off # enables archiving; off, on, or always # (change requires restart) #archive_command = '' # command to use to archive a logfile segment # placeholders: %p = path of file to archive # %f = file name only # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables # - Archive Recovery - # These are only used in recovery mode. #restore_command = '' # command to use to restore an archived logfile segment # placeholders: %p = path of file to restore # %f = file name only # e.g. 'cp /mnt/server/archivedir/%f %p' # (change requires restart) #archive_cleanup_command = '' # command to execute at every restartpoint #recovery_end_command = '' # command to execute at completion of recovery # - Recovery Target - # Set these only when performing a targeted recovery. #recovery_target = '' # 'immediate' to end recovery as soon as a # consistent state is reached # (change requires restart) #recovery_target_name = '' # the named restore point to which recovery will proceed # (change requires restart) #recovery_target_time = '' # the time stamp up to which recovery will proceed # (change requires restart) #recovery_target_xid = '' # the transaction ID up to which recovery will proceed # (change requires restart) #recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed # (change requires restart) #recovery_target_inclusive = on # Specifies whether to stop: # just after the specified recovery target (on) # just before the recovery target (off) # (change requires restart) #recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID # (change requires restart) #recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown' # (change requires restart) #------------------------------------------------------------------------------ # REPLICATION #------------------------------------------------------------------------------ # - Sending Servers - # Set these on the master and on any standby that will send replication data. #max_wal_senders = 10 # max number of walsender processes # (change requires restart) #wal_keep_segments = 0 # in logfile segments; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables #max_replication_slots = 10 # max number of replication slots # (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit # (change requires restart) # - Master Server - # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep # method to choose sync standbys, number of sync standbys, # and comma-separated list of application_name # from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed # - Standby Servers - # These settings are ignored on a master server. #primary_conninfo = '' # connection string to sending server # (change requires restart) #primary_slot_name = '' # replication slot on sending server # (change requires restart) #promote_trigger_file = '' # file name whose presence ends recovery #hot_standby = on # "off" disallows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; # -1 allows indefinite delay #max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay #wal_receiver_status_interval = 10s # send replies at least this often # 0 disables #hot_standby_feedback = off # send info from standby to prevent # query conflicts #wal_receiver_timeout = 60s # time that receiver waits for # communication from master # in milliseconds; 0 disables #wal_retrieve_retry_interval = 5s # time to wait before retrying to # retrieve WAL after a failed attempt #recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery # - Subscribers - # These settings are ignored on a publisher. #max_logical_replication_workers = 4 # taken from max_worker_processes # (change requires restart) #max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers #------------------------------------------------------------------------------ # QUERY TUNING #------------------------------------------------------------------------------ # - Planner Method Configuration - #enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_indexonlyscan = on #enable_material = on #enable_mergejoin = on #enable_nestloop = on #enable_parallel_append = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on #enable_partitionwise_join = off #enable_partitionwise_aggregate = off #enable_parallel_hash = on #enable_partition_pruning = on # - Planner Cost Constants - #seq_page_cost = 1.0 # measured on an arbitrary scale #random_page_cost = 4.0 # same scale as above #cpu_tuple_cost = 0.01 # same scale as above #cpu_index_tuple_cost = 0.005 # same scale as above #cpu_operator_cost = 0.0025 # same scale as above #parallel_tuple_cost = 0.1 # same scale as above #parallel_setup_cost = 1000.0 # same scale as above #jit_above_cost = 100000 # perform JIT compilation if available # and query more expensive than this; # -1 disables #jit_inline_above_cost = 500000 # inline small functions if query is # more expensive than this; -1 disables #jit_optimize_above_cost = 500000 # use expensive JIT optimizations if # query is more expensive than this; # -1 disables #min_parallel_table_scan_size = 8MB #min_parallel_index_scan_size = 512kB #effective_cache_size = 4GB # - Genetic Query Optimizer - #geqo = on #geqo_threshold = 12 #geqo_effort = 5 # range 1-10 #geqo_pool_size = 0 # selects default based on effort #geqo_generations = 0 # selects default based on effort #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_seed = 0.0 # range 0.0-1.0 # - Other Planner Options - #default_statistics_target = 100 # range 1-10000 #constraint_exclusion = partition # on, off, or partition #cursor_tuple_fraction = 0.1 # range 0.0-1.0 #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses #force_parallel_mode = off #jit = on # allow JIT compilation #plan_cache_mode = auto # auto, force_generic_plan or # force_custom_plan #------------------------------------------------------------------------------ # REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - #log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: #logging_collector = off # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: #log_directory = 'log' # directory where log files are written, # can be absolute or relative to PGDATA #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, # can include strftime() escapes #log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation #log_truncate_on_rotation = off # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. #log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. #log_rotation_size = 10MB # Automatic rotation of logfiles will # happen after that much log output. # 0 disables. # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' #syslog_sequence_numbers = on #syslog_split_messages = on # This is only relevant when logging to eventlog (win32): # (change requires restart) #event_source = 'PostgreSQL' # - When to Log - #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #log_min_error_statement = error # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off) #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their durations, > 0 logs only # statements running at least this number # of milliseconds #log_transaction_sample_rate = 0.0 # Fraction of transactions whose statements # are logged regardless of their duration. 1.0 logs all # statements from all transactions, 0.0 never logs. # - What to Log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off #log_line_prefix = '%m [%p] ' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds # %n = timestamp with milliseconds (as a Unix epoch) # %i = command tag # %e = SQL state # %c = session ID # %l = session line number # %s = session start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<%u%%%d> ' #log_lock_waits = off # log lock waits >= deadlock_timeout #log_statement = 'none' # none, ddl, mod, all #log_replication_commands = off #log_temp_files = -1 # log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files log_timezone = 'Etc/UTC' #------------------------------------------------------------------------------ # PROCESS TITLE #------------------------------------------------------------------------------ #cluster_name = '' # added to process titles if nonempty # (change requires restart) #update_process_title = on #------------------------------------------------------------------------------ # STATISTICS #------------------------------------------------------------------------------ # - Query and Index Statistics Collector - #track_activities = on #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) #stats_temp_directory = 'pg_stat_tmp' # - Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off #------------------------------------------------------------------------------ # AUTOVACUUM #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit #------------------------------------------------------------------------------ # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------------ # - Statement Behavior - #client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #search_path = '"$user", public' # schema names #row_security = on #default_tablespace = '' # a tablespace name, '' uses the default #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #default_table_access_method = 'heap' #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off #session_replication_role = 'origin' #statement_timeout = 0 # in milliseconds, 0 is disabled #lock_timeout = 0 # in milliseconds, 0 is disabled #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000 #vacuum_cleanup_index_scale_factor = 0.1 # fraction of total number of tuples # before index cleanup, 0 always performs # index cleanup #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' #gin_fuzzy_search_limit = 0 #gin_pending_list_limit = 4MB # - Locale and Formatting - datestyle = 'iso, mdy' #intervalstyle = 'postgres' timezone = 'Etc/UTC' #timezone_abbreviations = 'Default' # Select the set of available time zone # abbreviations. Currently, there are # Default # Australia (historical usage) # India # You can create your own file in # share/timezonesets/. #extra_float_digits = 1 # min -15, max 3; any value >0 actually # selects precise output mode #client_encoding = sql_ascii # actually, defaults to database # encoding # These settings are initialized by initdb, but they can be changed. lc_messages = 'en_US.utf8' # locale for system error message # strings lc_monetary = 'en_US.utf8' # locale for monetary formatting lc_numeric = 'en_US.utf8' # locale for number formatting lc_time = 'en_US.utf8' # locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.english' # - Shared Library Preloading - #shared_preload_libraries = '' # (change requires restart) #local_preload_libraries = '' #session_preload_libraries = '' #jit_provider = 'llvmjit' # JIT library to use # - Other Defaults - #dynamic_library_path = '$libdir' #------------------------------------------------------------------------------ # LOCK MANAGEMENT #------------------------------------------------------------------------------ #deadlock_timeout = 1s #max_locks_per_transaction = 64 # min 10 # (change requires restart) #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) #max_pred_locks_per_relation = -2 # negative values mean # (max_pred_locks_per_transaction # / -max_pred_locks_per_relation) - 1 #max_pred_locks_per_page = 2 # min 0 #------------------------------------------------------------------------------ # VERSION AND PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - #array_nulls = on #backslash_quote = safe_encoding # on, off, or safe_encoding #escape_string_warning = on #lo_compat_privileges = off #operator_precedence_warning = off #quote_all_identifiers = off #standard_conforming_strings = on #synchronize_seqscans = on # - Other Platforms and Clients - #transform_null_equals = off #------------------------------------------------------------------------------ # ERROR HANDLING #------------------------------------------------------------------------------ #exit_on_error = off # terminate session on any error? #restart_after_crash = on # reinitialize after backend crash? #data_sync_retry = off # retry or panic on failure to fsync # data? # (change requires restart) #------------------------------------------------------------------------------ # CONFIG FILE INCLUDES #------------------------------------------------------------------------------ # These options allow settings to be loaded from files other than the # default postgresql.conf. Note that these are directives, not variable # assignments, so they can usefully be given more than once. #include_dir = '...' # include files ending in '.conf' from # a directory, e.g., 'conf.d' #include_if_exists = '...' # include file only if it exists #include = '...' # include file #------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here ```
**.env**
Содержимое файла .env ```ssh-config # Основной пользователь и БД POSTGRES_USER=postgres POSTGRES_PASSWORD=<пароль для пользователя postgres> POSTGRES_DB=postgres # Дополнительнаые пользователи DB_USER1=maxim DB_USER1_PASSWORD=<пароль для пользователя maxim> DB_USER2=andrew DB_USER2_PASSWORD=<пароль для пользователя andrew> # Дополнительная БД DB_NAME=db_utm # Пользователь для PGADMIN PGADMIN_DEFAULT_EMAIL=utenkov@list.ru PGADMIN_DEFAULT_PASSWORD=<пароль для пользователя utenkov@list.ru> ```
В папке `utm-postgres18/docker` создать подпапку `docker-entrypoint-initdb.d` для инициализации сервера и создать следующие файлы: **init-db.sh**
Содержимое файла init-db.sh ```sh #!/bin/bash set -e psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL CREATE USER "$DB_USER1" PASSWORD '$DB_USER1_PASSWORD'; CREATE USER "$DB_USER2" PASSWORD '$DB_USER2_PASSWORD'; CREATE DATABASE "$DB_NAME"; GRANT ALL PRIVILEGES ON DATABASE "$DB_NAME" TO "$DB_USER1"; GRANT ALL PRIVILEGES ON DATABASE "$DB_NAME" TO "$DB_USER2"; EOSQL ```
В папке `db-projects` создать папку проекта `utm-postgres18/docker-postgres-exporter` и создать следующие файлы: **compose.yaml**
Содержимое файла compose.yaml ```yaml services: postgres-exporter: image: quay.io/prometheuscommunity/postgres-exporter restart: always environment: DATA_SOURCE_URI: "192.168.4.9:5432/postgres?sslmode=disable" DATA_SOURCE_USER: postgres_exporter DATA_SOURCE_PASS: <пароль> container_name: postgres-exporter-pg18 ports: - "9187:9187" ```
Сделать коммит в GitHub со всеми изменениями в репозитории db-projects. ## Запуск Docker контейнера с Postgres и pgAdmin4
Выполнение запуска Docker контейнера с Postgres Подключение репозитория db-projects: ```bash # Перейти в общую папку cd ~/github/utenkov-maxim # Выполнить клонирование репозитория git clone git@github.com:utenkov-maxim/db-projects.git # Перейти в папку репозитория db-projects cd db-projects/utm-postgres18/docker ``` Обновление репозитория: ```bash # Перейти в папку репозитория db-projects cd ~/github/utenkov-maxim/db-projects # Получить свежие данные из репозитория git pull # Перейти в папку проекта cd ~/github/utenkov-maxim/db-projects/utm-postgres18/docker ``` Запустить контейнер ```bash # Запустить docker compose up -d ``` Проверки: ```bash # Посмотреть запущенные контейнеры docker ps # Посмотреть каталог с volume с данными БД который примонтирован к контейнеру sudo su cd /var/lib/docker/volumes/docker_utm-postgres18-data ``` Дополнительные команды: ```bash # Остановить контейнер docker stop utm-postgres18 # Список всех контейнеров, включая остановленные docker ps -a # Удалить контейнер docker rm utm-postgres18 # Список volumes docker volume ls # Список volumes примонтированных к контейнеру docker inspect utm-postgres18 | grep -i "Mounts" # Перезапуск контейнера docker restart utm-postgres18 ```
pgAdmin4: ## Дополнительная настройка Postgres
Действия после первого запуска Docker контейнера с Postgres После первоначального запуска в Docker контейнера utm-postgres18 выполняется первоначальная инициализация сервера Postgres. При этом выполняются все скрипты из папки `docker-entrypoint-initdb.d`. Создаются пользователи: * postgres * maxim * andrew База данных: * db\_utm Пользователи создаются с паролями, указанными в файле compose.yaml Поэтому после инициализации необходимо сменить пароли для этих пользователей. Для этого необходимо подключиться через DBeaver к серверу и выполнить следующие команды: ```plsql ALTER USER postgres WITH PASSWORD 'new_password'; ALTER USER maxim WITH PASSWORD 'new_password'; ALTER USER andrew WITH PASSWORD 'new_password'; ```
## Создание пользователя postgres\_exporter на сервере Postgres
Действия после первого запуска Docker контейнера с Postgres Для запуска мониторинга необходимо на сервере Postgres создать пользователя postgres\_exporter ```plsql CREATE USER postgres_exporter WITH PASSWORD '****'; ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog; -- Права доступа для пользователя мониторинга: GRANT CONNECT ON DATABASE <ваша бд> TO postgres_exporter; GRANT pg_monitor TO postgres_exporter; ``` Также можно дополнительно создать объекты, [описание тут](https://github.com/prometheus-community/postgres_exporter/?tab=readme-ov-file#running-as-non-superuser)
## Запуск Docker контейнера с postgres-exporter
Выполнение запуска Docker контейнера с postgres-exporter Перейти в папку проекта docker-postgres-exporter: ```bash cd ~/github/utenkov-maxim/db-projects/utm-postgres18/docker-postgres-exporter ``` Запустить контейнер ```bash # Запустить docker compose up -d ``` Дополнительные команды: ```bash # Посмотреть запущенные контейнеры docker ps # Остановить контейнер docker stop postgres-exporter-pg18 # Список всех контейнеров, включая остановленные docker ps -a # Удалить контейнер docker rm postgres-exporter-pg18 # Перезапуск контейнера docker restart postgres-exporter-pg18 ``` Проверка метрик ```bash curl "http://localhost:9187/metrics" ``` Через браузер:
## Настройка Prometheus и Grafana Настройка Prometheus и Grafana [описана тут](/utm-docs/dokumentaciya/utm-it/resheniya/monitoring/prometheus/monitoring-postgres.md) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://utm-1.gitbook.io/utm-docs/dokumentaciya/utm-it/resheniya/subd/postgres/instansy/utm-postgres18-na-utm-db4-9.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.