In addition to the postgresql.conf file already mentioned, PostgreSQL uses two other manually-edited configuration files, which control client authentication (their use is discussed in Chapter 21). By default, all three configuration files are stored in the database cluster’s data directory. The parameters described in this section allow the configuration files to be placed elsewhere. (Doing so can ease administration. In particular it is often easier to ensure that the configuration files are properly backed-up when they are kept separate.)
data_directory(string)-
Specifies the directory to use for data storage. This parameter can only be set at server start.
config_file(string)-
Specifies the main server configuration file (customarily called
postgresql.conf). This parameter can only be set on thepostgrescommand line. hba_file(string)-
Specifies the configuration file for host-based authentication (customarily called
pg_hba.conf). This parameter can only be set at server start. ident_file(string)-
Specifies the configuration file for user name mapping (customarily called
pg_ident.conf). This parameter can only be set at server start. See also Section 21.2. external_pid_file(string)-
Specifies the name of an additional process-ID (PID) file that the server should create for use by server administration programs. This parameter can only be set at server start.
In a default installation, none of the above parameters are set explicitly. Instead, the data directory is specified by the -D command-line option or the PGDATA environment variable, and the configuration files are all found within the data directory.
If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set in postgresql.conf (or on the command line) to show where the data directory is actually located. Notice that data_directory overrides -D and PGDATA for the location of the data directory, but not for the location of the configuration files.
If you wish, you can specify the configuration file names and locations individually using the parameters config_file, hba_file and/or ident_file. config_file can only be specified on the postgres command line, but the others can be set within the main configuration file. If all three parameters plus data_directory are explicitly set, then it is not necessary to specify -D or PGDATA.
When setting any of these parameters, a relative path will be interpreted with respect to the directory in which postgres is started.
Where are my postgres *.conf files?
Query the database with the sql query:
SHOW config_file;
The reason you may have trouble finding postgresql.conf is because it is owned by postgres, not root.
Here is where mine is on Fedora 17:
[root@defiant /]# ll /var/lib/pgsql/data/postgresql.conf
Notice it is owned by postgres:
-rw------- 1 postgres postgres 19332 Oct 14 09:38
/var/lib/pgsql/data/postgresql.conf
It has permission 600 which explains why you have a hard time finding it with a file search. The location of postgresql.conf will be different depending on what operating system you are using.
Here is the contents of mine:
# -----------------------------
# 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, or use "pg_ctl reload". 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
# 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 = '(none)' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432 # (change requires restart)
# Note: In RHEL/Fedora installations, you can't set the port number here;
# adjust it in the service file instead.
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'
# 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)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# 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
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 24MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#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 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level; on, off, or local
#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_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Master Server -
# These settings are ignored on a standby server
#max_wal_senders = 0 # max number of walsender processes
# (change requires restart)
#wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
#replication_timeout = 60s # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
# - Standby Servers -
# These settings are ignored on a master server
#hot_standby = off # "on" allows 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
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = 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
#effective_cache_size = 128MB
# - 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
#------------------------------------------------------------------------------
# ERROR 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 = on # 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 = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.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 = on # 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 = 0 # 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'
#silent_mode = off # Run server silently.
# DO NOT USE without syslog or
# logging_collector
# (change requires restart)
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#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
# - 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 = '' # 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
# %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_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#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_vacuum_cost_delay = 20ms # 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 -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#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
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#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?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
Сервер баз данных PostgreSQL имеет очень много параметров с помощью которых его можно настроить под любые нужды. В этой статье мы не будет рассматривать все эти параметры. Здесь мы посмотрим на различные cпособы конфигурирования PostgreSQL.
Если же вы хотите посмотреть список параметров настройки PostgreSQL, то ищите его в справочнике на официальном сайте: на английском и на русском языках.
Конфигурационный файл postgresql.conf
Главный конфигурационный файл для кластера PostgreSQL – это postgresql.conf, в разных системах он может находится в разных местах. Так как мы собирали сервер из исходников и не меняли путь хранения этого файла, то по умолчанию он будет находится в каталоге PGDATA:
postgres@s-pg13:~$ echo $PGDATA /usr/local/pgsql/data postgres@s-pg13:~$ ls -l $PGDATA/postgresql.conf -rw------- 1 postgres postgres 28023 июн 21 15:15 /usr/local/pgsql/data/postgresql.conf
Этот конфигурационный файл читается один раз при запуске сервера. Если параметр указан несколько раз, то применяется последний.
Самый точный способ узнать расположение этого файла, посмотреть из терминала psql:
postgres@s-pg13:~$ psql
Timing is on.
psql (13.3)
Type "help" for help.
postgres@postgres=# SHOW config_file;
config_file
---------------------------------------
/usr/local/pgsql/data/postgresql.conf
(1 row)
Time: 0,391 ms
Если вы измените параметры в этом файле, его нужно перечитать. Первый способ – из командной оболочки операционной системы:
postgres@postgres=# q postgres@s-pg13:~$ pg_ctl reload server signaled
Второй способ – из терминала psql:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) Time: 0,555 ms
Но есть некоторые параметры, для изменения которых потребуется перезапуск сервера.
Конфигурация сервера используя ALTER SYSTEM
Для настройки сервера также существует другой файл – postgresql.auto.conf. Он были придуман для настройки сервера из консоли psql. Читается этот файл после postgresql.conf, поэтому параметры из него имеют приоритет. Этот файл всегда находится в каталоге с данными (PGDATA).
Для создания параметров в файле postgresql.auto.conf нужно выполнить подобный запрос:
ALTER SYSTEM SET <параметр> TO <значение>;
Чтобы удалить параметр используем другой запрос:
ALTER SYSTEM RESET <параметр>;
А чтобы удалить все параметры из postgresql.auto.conf выполним:
ALTER SYSTEM RESET ALL;
Чтобы применить изменения нужно перечитать конфигурационные файлы как было описано выше.
Информация о текущих настройках сервера
В PostgreSQL есть 2 представления через которые можно посмотреть текущие настройки сервера:
- pg_file_settings – какие параметры записаны в файлах postgresql.conf и postgresql.auto.conf;
- pg_settings – текущие параметры, с которыми работает сервер.
Например посмотрим значение параметра config_file из представления pg_settings, который покажет конфигурационный файл текущего кластера:
postgres@postgres=# SELECT setting FROM pg_settings WHERE name = 'config_file';
setting
---------------------------------------
/usr/local/pgsql/data/postgresql.conf
(1 row)
Time: 1,844 ms
Внесём изменения в параметр work_mem в postgresql.conf и postgresql.auto.conf. Затем посмотрим на все не закомментированные параметры в этих файлах:
postgres@postgres=# ! echo 'work_mem = 8MB' >> $PGDATA/postgresql.conf
postgres@postgres=# ALTER SYSTEM SET work_mem TO '10MB';
ALTER SYSTEM
Time: 0,728 ms
postgres@postgres=# SELECT sourceline, name, setting, applied FROM pg_file_settings;
sourceline | name | setting | applied
------------+----------------------------+--------------------+---------
63 | port | 5433 | f
64 | max_connections | 100 | t
121 | shared_buffers | 128MB | t
142 | dynamic_shared_memory_type | posix | t
228 | max_wal_size | 1GB | t
229 | min_wal_size | 80MB | t
563 | log_timezone | Europe/Moscow | t
678 | datestyle | iso, dmy | t
680 | timezone | Europe/Moscow | t
694 | lc_messages | ru_RU.UTF-8 | t
696 | lc_monetary | ru_RU.UTF-8 | t
697 | lc_numeric | ru_RU.UTF-8 | t
698 | lc_time | ru_RU.UTF-8 | t
701 | default_text_search_config | pg_catalog.russian | t
780 | work_mem | 8MB | f
3 | work_mem | 10MB | t
(16 rows)
Time: 0,650 ms
Как можно заметить в примере выше, у меня 2 одинаковых параметра work_mem. Колонка applied показывает, может ли быть применён параметр. Первый work_mem не может быть применен, так как второй его перезапишет. При этом реальное значение с которым работает сервер отличается, так как сервер не перечитал конфигурацию.
Теперь посмотрим на реальное, текущее значение этого параметра:
postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'gx -[ RECORD 1 ]---+--------- name | work_mem setting | 4096 unit | kB boot_val | 4096 reset_val | 4096 source | default sourcefile | sourceline | pending_restart | f context | user Time: 0,854 ms
В примере выше мы использовали расширенный режим (в конце запроса gx), поэтому табличка перевёрнута. Разберём колонки:
- name – имя параметра;
- setting – текущее значение;
- unit – единица измерения;
- boot_val – значение по умолчанию (жёстко задано в коде postgresql);
- reset_val – если перечитаем конфигурацию, то применится это значение;
- source – источник, это значение по умолчанию;
- sourcefile – если бы источником был конфигурационный файл, то тут был бы указан этот файл;
- sourceline – номер строки в этом файле;
- pending_restart – параметр изменили в конфигурационном файле и требуется перезапуск сервера. У нас требуется всего лишь перечитать конфигурацию;
- context – действия, необходимые для применения параметра, может быть таким:
- internal – изменить нельзя, задано при установке;
- postmaster – требуется перезапуск сервера;
- sighup – требуется перечитать файлы конфигурации;
- superuser – суперпользователь может изменить для своего сеанса;
- user – любой пользователь может изменить для своего сеанса на лету.
Перечитаем конфигурацию сервера:
postgres@postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) Time: 3,178 ms postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'gx -[ RECORD 1 ]---+------------------------------------------- name | work_mem setting | 10240 unit | kB boot_val | 4096 reset_val | 10240 source | configuration file sourcefile | /usr/local/pgsql/data/postgresql.auto.conf sourceline | 3 pending_restart | f context | user Time: 1,210 ms
Как видим, параметр изменился. Он был взят из postgresql.auto.conf и теперь равняется 10 MB.
Установка параметров на лету
Для своего сеанса можно изменить параметры с context=user. Для этого используется конструкция:
SET <параметр> TO '<значение>';
Например сделаем это для work_mem:
postgres@postgres=# SET work_mem TO '64MB'; SET Time: 0,119 ms postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'gx -[ RECORD 1 ]---+--------- name | work_mem setting | 65536 unit | kB boot_val | 4096 reset_val | 10240 source | session sourcefile | sourceline | pending_restart | f context | user Time: 0,651 ms
Как видим, теперь источником является текущая сессия, а параметр равен 64 MB, но если мы перечитаем конфигурацию параметр снова станет равным 10 MB.
Чтобы вернуть все на место нужно просто перезайти в psql. Или выполнить команду RESET <параметр>:
postgres@postgres=# RESET work_mem; RESET Time: 0,211 ms postgres@postgres=# SELECT name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context FROM pg_settings WHERE name = 'work_mem'gx -[ RECORD 1 ]---+------------------------------------------- name | work_mem setting | 10240 unit | kB boot_val | 4096 reset_val | 10240 source | configuration file sourcefile | /usr/local/pgsql/data/postgresql.auto.conf sourceline | 3 pending_restart | f context | user Time: 0,632 ms
Тоже самое может проделывать приложение для одной транзакции, и если транзакция откатывается, то и значение параметра откатывается вместе с ней:
postgres@postgres=# BEGIN; BEGIN Time: 0,070 ms postgres@postgres=# SET work_mem TO '64MB'; SET Time: 0,085 ms postgres@postgres=# SHOW work_mem; work_mem ---------- 64MB (1 row) Time: 0,102 ms postgres@postgres=# ROLLBACK; ROLLBACK Time: 0,108 ms postgres@postgres=# SHOW work_mem; work_mem ---------- 10MB (1 row) Time: 0,120 ms
Как вы могли заметить посмотреть текущее значение параметра ещё можно так:
SHOW <параметр>;
Какие параметры требуют перезапуск сервера?
Чтобы это выяснить нужно посмотреть все параметры у которых context = postmaster:
postgres@postgres=# SELECT name, setting, unit FROM pg_settings WHERE context = 'postmaster';
name | setting | unit
-------------------------------------+---------------------------------------+------
archive_mode | off |
autovacuum_freeze_max_age | 200000000 |
autovacuum_max_workers | 3 |
autovacuum_multixact_freeze_max_age | 400000000 |
bonjour | off |
bonjour_name | |
cluster_name | |
config_file | /usr/local/pgsql/data/postgresql.conf |
data_directory | /usr/local/pgsql/data |
data_sync_retry | off |
dynamic_shared_memory_type | posix |
event_source | PostgreSQL |
external_pid_file | |
hba_file | /usr/local/pgsql/data/pg_hba.conf |
hot_standby | on |
huge_pages | try |
ident_file | /usr/local/pgsql/data/pg_ident.conf |
ignore_invalid_pages | off |
jit_provider | llvmjit |
listen_addresses | localhost |
logging_collector | off |
max_connections | 100 |
max_files_per_process | 1000 |
max_locks_per_transaction | 64 |
max_logical_replication_workers | 4 |
max_pred_locks_per_transaction | 64 |
max_prepared_transactions | 0 |
max_replication_slots | 10 |
max_wal_senders | 10 |
max_worker_processes | 8 |
old_snapshot_threshold | -1 | min
port | 5432 |
recovery_target | |
recovery_target_action | pause |
recovery_target_inclusive | on |
recovery_target_lsn | |
recovery_target_name | |
recovery_target_time | |
recovery_target_timeline | latest |
recovery_target_xid | |
restore_command | |
shared_buffers | 16384 | 8kB
shared_memory_type | mmap |
shared_preload_libraries | |
superuser_reserved_connections | 3 |
track_activity_query_size | 1024 | B
track_commit_timestamp | off |
unix_socket_directories | /tmp |
unix_socket_group | |
unix_socket_permissions | 0777 |
wal_buffers | 512 | 8kB
wal_level | replica |
wal_log_hints | off |
(53 rows)
Time: 0,666 ms
Сводка
Имя статьи
PostgreSQL. Конфигурирование
Описание
Сервер баз данных PostgreSQL имеет очень много параметров с помощью которых его можно настроить под любые нужды. В этой статье мы не будет рассматривать все эти параметры. Здесь мы посмотрим на различные способы настройки этого сервера.
Where are the files postgresql.conf and pg_hba.conf on a Linux server running PostgreSQL 8.4 installed from Ubuntu repos?
Wesley
32.6k9 gold badges80 silver badges116 bronze badges
asked Jun 19, 2010 at 21:46
3
Looking for “pg_hba.conf ubuntu” on Google gives you
https://help.ubuntu.com/community/PostgreSQL
which shows the location of the files.
The documentation states the following:
Client authentication is controlled by
a configuration file, which
traditionally is named pg_hba.conf and
is stored in the database cluster’s
data directory. (HBA stands for
host-based authentication.) A default
pg_hba.conf file is installed when the
data directory is initialized by
initdb. It is possible to place the
authentication configuration file
elsewhere, however; see the hba_file
configuration parameter.
Note it says stored in the database cluster’s data directory and that it’s possible to place it elsewhere, via a configuration parameter. Official documentation cannot point you to a specific folder because the actual location depends on both how the OS maker and the machine’s administrator have set PostgreSQL up. Remember PostgreSQL supports a lot of different operating systems (and Linux distributions.)
As Neutrino shows, if you can access your server via psql, you can tell it to show you the file location.
Also, two tips:
- locate will help you find files you know the name of but not the location
- Debian based distributions place under /usr/share/doc documentation on how they set up different packages by default, I’m sure you’ll find under /usr/share/doc/postgresql-8.4 (or maybe just postgresql) info about the configuration files. Very useful to read in case they have modified some standard behavior.
answered Jun 19, 2010 at 21:49
Vinko Vrsalovic♦Vinko Vrsalovic
1,5232 gold badges15 silver badges20 bronze badges
3
Open a command prompt.
> psql -U postgres
=# show hba_file;
=# show config_file
When they change the names of the config settings, or you want to see something else.
=# show all;
answered Oct 6, 2017 at 10:17
NeutrinoNeutrino
3993 silver badges3 bronze badges
0
Possibly
/etc/postgresql/9.*/main
answered Jun 16, 2015 at 6:56
kmonsoorkmonsoor
1531 silver badge5 bronze badges
answered Jun 19, 2010 at 23:27
As Vinko said, the location is distribution-dependent. To add to his answer:
Your package management sofware will tell you where are the files installed by each package, (for example: dpkg -L postgresql).
You can also look inside the service startup script ( typically /etc/init.d/postgresql)
answered Jun 19, 2010 at 22:26
leonbloyleonbloy
2,09817 silver badges23 bronze badges
To see the configuration files from the terminal:
psql -U postgres -c 'SHOW hba_file'
psql -U postgres -c 'SHOW config_file'
To check the data directory:
echo $PGDATA
or
psql -U postgres -c 'SHOW data_directory'
answered Sep 30, 2020 at 15:45
ZstackZstack
1212 bronze badges
Состояние перевода: На этой странице представлен перевод статьи PostgreSQL. Дата последней синхронизации: 21 ноября 2022. Вы можете помочь синхронизировать перевод, если в английской версии произошли изменения.
PostgreSQL — это поддерживаемая сообществом система управления базами данных с открытым исходным кодом.
Установка
Важно: Смотрите раздел #Обновление PostgreSQL для выполнения обязательных шагов перед установкой обновлений пакета PostgreSQL.
Установите пакет postgresql. Он также создаст системного пользователя postgres.
Для переключения в пользователя postgres можно использовать программу для повышения привилегий.
Примечание: Команды, которые нужно запускать от имени пользователя postgres, в данной статье обозначены префиксом [postgres]$.
Для переключения в пользователя postgres можно использовать одну из следующих команд:
- Если у вас есть sudo и ваш пользователь прописан в sudoers:
$ sudo -iu postgres
- Или через su:
$ su # su -l postgres
Смотрите также документацию sudo(8) или su(1).
Начальная настройка
В первую очередь необходимо инициализировать кластер баз данных:
[postgres]$ initdb -D /var/lib/postgres/data
Где опция -D указывает на стандартное расположение данных кластера (если вы хотите использовать другой каталог, смотрите раздел #Изменение стандартного каталога данных). initdb принимает дополнительные аргументы:
- По умолчанию локаль и кодировка для кластера баз данных наследуются из вашего текущего окружения (используется значение $LANG). Если вас это не устраивает, вы можете прописать нужные параметры вручную с помощью опций
--locale=локаль(где локаль должна быть одной из доступных системных локалей) и--encoding=кодировкадля выбора кодировки (должна соответствовать выбранной локали). (После настройки базы данных вы сможете посмотреть используемые значения командой[postgres]$ psql -l.) - Если каталог с данными расположен на файловой системе без контроля целостности данных, вы можете включить встроенный в PostgreSQL подсчёт контрольных сумм для повышения гарантий целостности — для этого добавьте аргумент
--data-checksums. Дополнительная информация описана в разделе #Включение подсчёта контрольных сумм. (После настройки базы данных вы сможете посмотреть. включена ли эта функция, командой[postgres]$ psql -c "SHOW data_checksums".) - Другие доступные опции можно посмотреть в
initdb --helpили официальной документации.
Пример для русской локали:
[postgres]$ initdb --locale=ru_RU.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
После инициализации на экране появится много строчек, некоторых из которых оканчиваются на ... ок:
Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres".
От его имени также будет запускаться процесс сервера.
Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8".
Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8".
Выбрана конфигурация текстового поиска по умолчанию "russian".
Контроль целостности страниц данных отключён.
исправление прав для существующего каталога /var/lib/postgres/data... ок
создание подкаталогов... ок
выбирается реализация динамической разделяемой памяти... posix
выбирается значение max_connections по умолчанию... 100
выбирается значение shared_buffers по умолчанию... 128MB
выбирается часовой пояс по умолчанию... Europe/Moscow
создание конфигурационных файлов... ок
выполняется подготовительный скрипт... ок
выполняется заключительная инициализация... ок
сохранение данных на диске... ок
initdb: предупреждение: включение метода аутентификации "trust" для локальных подключений
Другой метод можно выбрать, отредактировав pg_hba.conf или используя ключи -A,
--auth-local или --auth-host при следующем выполнении initdb.
Готово. Теперь вы можете запустить сервер баз данных:
pg_ctl -D /var/lib/postgres/data -l файл_журнала start
Если вы видите подобное, значит инициализация прошла успешно. Можно вернуться в обычного пользователя, выполнив команду exit в сеансе пользователя postgres.
Примечание: Подробнее об этом предупреждении читайте в разделе #Ограничение доступа к суперпользователю по умолчанию.
Совет: Если вы хотите использовать путь отличный от /var/lib/postgres, нужно отредактировать файл службы systemd. Если вы помещаете его в /home, не забудьте отключить ProtectHome.
Важно:
- Если база данных располагается на файловой системе Btrfs, стоит отключить копирование при записи для каталога перед созданием любых баз данных.
- Если база данных располагается на файловой системе ZFS, прочтите ZFS#Databases перед созданием любых баз данных.
Наконец, запустите и включите службу postgresql.service.
Создание первой базы данных
Совет: Если имя роли/пользователя совпадает с именем вашего пользователя в Linux, вы сможете получить доступ к оболочке PostgreSQL без явного указания имени пользователя (что весьма удобно).
Становимся пользователем postgres.
Добавляем нового пользователя базы данных с помощью команды createuser:
[postgres]$ createuser --interactive
Создаём новую базу данных от имени пользователя, имеющего доступ на чтение-запись, с помощью команды createdb (выполните эту команду в оболочке вашего обычного пользователя, если имя будущего владельца базы данных совпадает с вашим именем пользователя в Linux, в ином случае добавьте опцию -O имя-пользователя)
$ createdb имяМоейБазы
Совет: Если вы не выдали разрешение на создание баз данных вашему свежесозданному пользователю, добавьте опцию -U postgres к этой команде.
Знакомство с PostgreSQL
Доступ к оболочке базы данных
Становимся postgres пользователем.
Запускаем основную оболочку базы данных psql, в которой мы сможем создавать, удалять базы данных/таблицы, задавать права и запускать команды SQL.
Используйте опцию -d, чтобы указать название базы данных, которую вы создали (если опцию не указать, то psql попытается подключиться к базе, имя которой совпадает с именем пользователя).
[postgres]$ psql -d имяМоейБазы
Некоторые полезные команды:
Получение справки:
=> help
Подключение к определённой базе данных:
=> c <database>
Список всех пользователей и их уровни доступа:
=> du
Краткая информация о всех таблицах в текущей базе данных:
=> dt
Выход из оболочки psql:
=> q или CTRL+d
Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу.
Для просмотра всех мета-команд введите:
=> ?
Дополнительные настройки
Файл настроек сервера баз данных PostgreSQL — postgresql.conf. Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data. В этой же папке находятся основные файлы настроек включая и pg_hba.conf, который определяет параметры аутентификации, как для локальных пользователей, так и для пользователей с других хостов.
Примечание: По умолчанию эта папка не доступна даже для просмотра (или поиска) от лица обычного пользователя.
Ограничение доступа к суперпользователю по умолчанию
По умолчанию pg_hba.conf разрешает подключение любого локального пользователя к любому пользователю базы данных, в том числе суперпользователю. Скорее всего это не то, что вам нужно, поэтому, чтобы разрешить подключение только пользователю postgres, измените эту строку:
/var/lib/postgres/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust
На эту:
/var/lib/postgres/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peer
Можно добавить дополнительные строки в зависимости от ваших потребностей.
Требование пароля при входе
Измените /var/lib/postgres/data/pg_hba.conf, прописав метод аутентификации для каждого пользователя (или «all» для всех пользователей) на scram-sha-256 (предпочтительно) или md5 (менее безопасно; по возможности стоит его избегать):
/var/lib/postgres/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all пользователь scram-sha-256
Если вы выбрали scram-sha-256, также нужно изменить /var/lib/postgres/data/postgresql.conf:
/var/lib/postgres/data/postgresql.conf
password_encryption = scram-sha-256
Перезапустите службу postgresql.service и заново пропишите пароли для пользователей с помощью SQL-запроса ALTER USER пользователь WITH ENCRYPTED PASSWORD 'пароль';.
Доступ только через Unix-сокет
В разделе «connections and authentication» пропишите:
/var/lib/postgres/data/postgresql.conf
listen_addresses = ''
Это полностью отключит доступ через сеть.
Не забудьте перезапустить службу postgresql.service для применения изменений.
Доступ с удалённых хостов
В разделе «connections and authentication» раскомментируйте или исправьте строку listen_addresses по вашему желанию, например:
/var/lib/postgres/data/postgresql.conf
listen_addresses = 'localhost,мой_локальный_ip'
Можно использовать '*' для прослушивания всех доступных сетевых интерфейсов.
Примечание: PostgreSQL по умолчанию использует TCP-порт 5432 для удалённого доступа. Убедитесь, что этот порт открыт в вашем межсетевом экране и может принимать входящие соединения. Изменить порт можно в этом же файле настроек, под строкой listen_addresses.
Затем измените настройки аутентификации:
/var/lib/postgres/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all ip_адрес/32 md5
где ip_адрес — IP-адрес удалённого клиента.
Смотрите также документацию по pg_hba.conf.
Примечание: Ни отправка простого пароля, ни отправка md5-хэша (использованный в приведённом выше примере) через интернет не являются безопасными, если это не выполняется через защищенное SSL-соединение. Смотрите Secure TCP/IP Connections with SSL, чтобы узнать, как настроить PostgreSQL с использованием SSL.
Перезапустите службу postgresql.service для применения изменений.
В случае проблем посмотрите журнал сервера:
# journalctl -u postgresql.service
Настройка аутентификации через PAM
PostgreSQL предлагает несколько методов аутентификации. Если вы хотите разрешить пользователям аутентифицироваться с их системным паролем, необходимы дополнительные шаги. Сначала вам нужно включить PAM для соединения.
Например, та же конфигурация, что и выше, но с включенным PAM:
/var/lib/postgres/data/pg_hba.conf
# IPv4 local connections: host all all ip_адрес/32 pam
Однако сервер PostgreSQL работает без прав root и не сможет получить доступ к файлу /etc/shadow. Мы можем обойти это, разрешив группе postgres доступ к этому файлу:
# setfacl -m g:postgres:r /etc/shadow
Изменение стандартного каталога данных
По умолчанию PostgreSQL настроен на использование каталога /var/lib/postgres/data для хранения всех баз данных. Для его изменения выполните следующие шаги:
Создайте новый каталог и сделайте пользователя postgres его владельцем:
# mkdir -p /путь/к/pgroot/data # chown -R postgres:postgres /путь/к/pgroot
Войдите в пользователя postgres и выполните инициализацию кластера:
[postgres]$ initdb -D /путь/к/pgroot/data
Отредактируйте службу postgresql.service, создав drop-in файл и переопределив настройки Environment и PIDFile. Например:
/etc/systemd/system/postgresql.service.d/PGROOT.conf
[Service] Environment=PGROOT=/путь/к/pgroot PIDFile=/путь/к/pgroot/data/postmaster.pid
Если вы хотите использовать каталог в /home, добавьте ещё одну строку:
ProtectHome=false
Изменение кодировки новых баз данных на UTF-8
Примечание: Если вы выполнили initdb с опцией --encoding=UTF8 или с использованием локали UTF-8, выполнять эти шаги не нужно.
Когда создаётся новая база данных (например, командой createdb blog), PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ванильный, и template1, который используется по умолчанию и предназначен для редактирования администратором. Один из вариантов изменения кодировки новой базы данных — изменить шаблон template1. Для этого зайдите в оболочку PostgreSQL (psql) и выполните следующее:
Сперва нужно удалить template1. Шаблоны нельзя удалять, так что сперва нужно преобразовать его в обычную базу данных:
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
Теперь можно удалить:
DROP DATABASE template1;
Затем создайте новую базу данных с новой кодировкой по умолчанию, в качестве шаблона используя template0:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
Теперь снова сделайте template1 шаблоном:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
По желанию, если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE:
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
Примечание: Этот шаг может привести к проблемам при обновлении через pg_upgrade.
Теперь вы можете создать базу данных, используя стандартные команды в терминале:
[postgres]$ createdb blog
Если снова войти в psql и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:
l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
Включение подсчёта контрольных сумм
Если файлы вашей базы данных находятся на файловой системе, которая не использует контрольные суммы, то данные в ней могут незаметно повреждаться из-за битфлипов и аппаратных проблем. Хотя такие случаи редки, желательно включить встроенный в PostgreSQL подсчёт контрольных сумм, если вы заботитесь о целостности данных. Эта функция должна быть включена на уровне кластера, а не для отдельных баз данных или таблиц.
Примечание: Ряд предостережений, которые стоит иметь в виду:
- Есть небольшое влияние на производительность, особенно при чтении больших массивов данных с диска. На операции в памяти это не влияет.
- PostgreSQL не может исправить повреждённые данные — он только прервёт транзакции, читающие с повреждённых страниц, чтобы предотвратить дальнейшее повреждение или получение некорректных результатов выполнения.
- Контрольные суммы охватывают только страницы данных (строк) на диске, но не метаданные или управляющие структуры. Страницы в памяти не проверяются. Хранилища с коррекцией ошибок и память с ECC по-прежнему полезны.
- Чтобы включить подсчёт контрольных сумм при создании кластера, добавьте аргумент
--data-checksumsк командеinitdb. - Чтобы проверить, включен ли подсчёт контрольных сумм, выполните
[postgres]$ psql -c "SHOW data_checksums"(выведетсяoffилиon). - Чтобы включить подсчёт контрольных сумм на существующем кластере:
- Остановите службу
postgresql.service. - Выполните команду
[postgres]$ pg_checksums --pgdata /var/lib/postgres/data --enable(или--disable, если вы хотите наоборот выключить эту функцию). Включение подсчёта контрольных сумм приведёт к перезаписи всех страниц базы данных, что займет некоторое время в больших базах данных. - Запустите службу
postgresql.service.
Графические инструменты
- phpPgAdmin — Веб-интерфейс для администрирования PostgreSQL.
- https://github.com/phppgadmin/phppgadmin || phppgadminAUR
- pgAdmin — Комплексный графический интерфейс для управления PostgreSQL.
- https://www.pgadmin.org/ || pgadmin3AUR или pgadmin4
- pgModeler — Инструмент для моделирования баз данных PostgreSQL.
- https://pgmodeler.io/ || pgmodelerAUR
Список инструментов, поддерживающих несколько разных СУБД, можно посмотреть в статье List of applications/Documents#Database tools.
Обновление PostgreSQL
This article or section needs expansion.
Для обновления до новой мажорной версии PostgreSQL (например, с версии 13.x на версию 14.y) необходима специальная процедура.
Важно: Приведённые ниже инструкции потенциально могут привести к потере данных. Не запускайте эти команды вслепую без понимания того, что они делают. Сделайте резервную копию перед началом.
Посмотреть текущую версию базы данных можно так:
# cat /var/lib/postgres/data/PG_VERSION
Чтобы случайно не обновиться до несовместимой версии, рекомендуется запретить обновления пакетов PostgreSQL.
Минорные обновления вполне безопасны. Однако если вы случайно обновитесь до другой мажорной версии, то не сможете получить доступ к данным. Всегда проверяйте домашнюю страницу PostgreSQL, чтобы знать, какие шаги требуются для каждого обновления. Чтобы узнать, почему это так, смотрите политику управления версиями.
Примечание: Если вы используете расширения, смотрите разделы #PostgreSQL не может запуститься после обновления пакета при использовании расширений и #Не удаётся запустить PostgreSQL со старой версией базы данных при обновлении до новой версии с расширениями.
Есть два основных способа обновить базу данных PostgreSQL. Подробности читайте в официальной документации.
pg_upgrade
Утилита pg_upgrade пытается скопировать как можно больше совместимых данных между кластерами и обновить всё остальное. Как правило, это самый быстрый метод обновления большинства экземпляров, хотя он требует доступа к бинарным файлам исходной и целевой версий PostgreSQL. Прочтите справочную страницу pg_upgrade(1), чтобы понять, какие действия он выполняет. Для нетривиальных экземпляров (например, с потоковой репликацией или трансляцией журналов) сперва ознакомьтесь с официальной документацией.
Для тех, кто хочет использовать pg_upgrade, доступен пакет postgresql-old-upgrade, который всегда отстаёт на одну мажорную версию от основного пакета PostgreSQL. Его можно установить параллельно с новой версией PostgreSQL. Для обновления более старых версий PostgreSQL доступны пакеты AUR, например postgresql-12-upgradeAUR. (Нужно использовать команду pg_upgrade из той версии PostgreSQL, на которую вы хотите обновиться.)
Обратите внимание, что каталог кластера баз данных не меняется от версии к версии, поэтому перед запуском pg_upgrade необходимо переименовать старый каталог данных и выполнить миграцию в новый каталог. Новый кластер баз данных необходимо инициализировать с теми же параметрами, что и старый.
Когда вы будете готовы к обновлению, выполните следующие шаги:
- Пока старая база данных всё ещё доступна, соберите аргументы для команды
initdb, которые использовались при создании базы. Команды для просмотра текущих настроек кластера описаны в разделе #Начальная настройка. - Остановите службу
postgresql.service. (Проверьте статус юнита, чтобы убедиться, что PostgreSQL завершился корректно, иначеpg_upgradeне сможет отработать корректно.) - Обновите пакеты postgresql, postgresql-libs и postgresql-old-upgrade.
- Переименуйте каталог со старым кластером и создайте каталог для нового кластера и временный каталог:
# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data /var/lib/postgres/tmp # chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp [postgres]$ cd /var/lib/postgres/tmp
- Инициализируйте новый кластер командой
initdbс теми же аргументами, которые использовались для старого кластера:[postgres]$ initdb -D /var/lib/postgres/data --locale=ru_RU.UTF-8 --encoding=UTF8 --data-checksums
- Обновите кластер, выполнив эту команду (замените
PG_VERSIONна номер старой версии, например13):[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
Примечание: Не забудьте обновить файлы конфигурации (например,
pg_hba.confиpostgresql.conf) для соответствия старому кластеру.Примечание: Если
pg_upgradeзавершается с ошибкойThe source cluster was not shut down cleanly, значит PostgreSQL не был остановлен перед запуском обновления. Остановите его, затем перезапустите кластер со старыми бинарными файлами, чтобы восстановить старые файлы кластера:[postgres]$ /opt/pgsql-PG_VERSION/bin/pg_ctl start -D /var/lib/postgres/olddata && /opt/pgsql-PG_VERSION/bin/pg_ctl stop -D /var/lib/postgres/olddata
После этого можно снова выполнить
pg_upgrade. Если обновиться всё равно не получается, остановите все процессы СУБД, откатитесь на более старую версию PostgreSQL, восстановите предыдущие данные кластера из резервных копий и перезапустите процесс обновления. - Запустите службу
postgresql.service. - Опционально: Выполните
[postgres]$ /usr/bin/vacuumdb --all --analyze-in-stagesдля пересчёта статистики анализатора запросов, что должно улучшить производительность запросов вскоре после обновления (добавление аргумента--jobs=ЧИСЛО_ЯДЕР_ПРОЦЕССОРАможет улучшить производительность этой команды). - Опционально: Сделайте резервную копию каталога
/var/lib/postgres/olddataна случай, если вдруг понадобится вернуть старую версию PostgreSQL. - Удалите каталог
/var/lib/postgres/olddataсо старыми данными кластера. - Удалите каталог
/var/lib/postgres/tmp.
Выгрузка и загрузка вручную
Ещё можно сделать что-то вроде такого (после обновления и установки postgresql-old-upgrade):
Примечание:
- В примере показано обновление с PostgreSQL 13; посмотрите в
/opt/установленную у вас версию postgresql-old-upgrade и исправьте команды по необходимости. - Если вы меняли файл
pg_hba.conf, вам может понадобиться временно разрешить полный доступ к старому кластеру с локальной системы. После обновления не забудьте прописать нужные вам настройки в новом кластере и перезапустить службуpostgresql.service.
Остановите службу postgresql.service.
# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data # chown postgres:postgres /var/lib/postgres/data [postgres]$ initdb -D /var/lib/postgres/data [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ start # cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-13/lib/ # Только если установлен postgis [postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
Запустите службу postgresql.service.
[postgres]$ psql -f /tmp/old_backup.sql postgres
Решение проблем
Ускорение мелких транзакций
Если вы используете PostgreSQL на своей локальной машине для разработки и он медленный, то можете попробовать отключить synchronous_commit в конфигурации. Однако, не забывайте про его особенности.
/var/lib/postgres/data/postgresql.conf
synchronous_commit = off
Запретить запись на диск во время бездействия
PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать жёсткому диску (и изнашивает его), заставляя его шуметь. Однако можно легко и безопасно переместить статистику в ОЗУ с помощью такой настройки:
/var/lib/postgres/data/postgresql.conf
stats_temp_directory = '/run/postgresql'
Проблемы с pgAdmin 4 после обновления до PostgreSQL 12
Если вы видите ошибки вроде string indices must be integers при навигации по дереву слева или column rel.relhasoids does not exist при просмотре данных, удалите сервер из списка соединений в pgAdmin и добавьте его заново. Без этого pgAdmin продолжает считать его сервером PostgreSQL 11, что и приводит к таким ошибкам.
PostgreSQL не может запуститься после обновления пакета при использовании расширений
Причина скорее всего в том, что существующий пакет не скомпилирован для новой версии (а она может быть актуальной), решение — пересобрать пакет вручную или дождаться обновления пакета расширения.
Не удаётся запустить PostgreSQL со старой версией базы данных при обновлении до новой версии с расширениями
Это происходит потому, что старая версия postgres из пакета postgresql-old-upgrade не имеет необходимых расширений (.so файлов) в своём каталоге lib. Предлагаемое здесь решение грязное и может вызвать много проблем, поэтому сохраните резервную копию базы данных на всякий случай. В целом, скопируйте необходимые .so файлы расширений из /usr/lib/postgresql/ в /opt/pgsql-XX/lib/ (не забудьте заменить XX на мажорную версию пакета postgresql-old-upgrade).
Например, для timescaledb:
# cp /usr/lib/postgresql/timescaledb*.so /opt/pgsql-13/lib/
Важно: Хотя копирования .so файлов обычно достаточно, может понадобиться скопировать больше файлов в правильные расположения в /opt/pgsql-XX/.
Чтобы узнать точные файлы для копирования, посмотрите содержимое пакета расширения с помощью команды:
$ pacman -Ql имя_пакета
Важно: Это очень грязное решение, которое может привести к потере данных в базе, поэтому создайте резервную копию перед выполнением этих действий.
