190
>Numeric or String with Unit (Memory &
193
>Numeric with Unit:</I
193
> These have an implicit unit, which is
194
either kilobytes, blocks (typically eight kilobytes),
195
milliseconds, seconds, or minutes. A unadorned numeric
196
value will use the default, which can be found by referencing
196
Some numeric parameters have an implicit unit, because they describe
197
quantities of memory or time. The unit might be kilobytes, blocks
198
(typically eight kilobytes), milliseconds, seconds, or minutes.
199
An unadorned numeric value for one of these settings will use the
200
setting's default unit, which can be learned from
198
202
CLASS="STRUCTNAME"
201
205
CLASS="STRUCTFIELD"
204
a different unit can also be specified explicitly via a string
205
value. It is case-sensitive and may include whitespace between
206
the value and the unit.
208
For convenience, settings can be given with a unit specified explicitly,
212
> for a time value, and they will be
213
converted to whatever the parameter's actual unit is. Note that the
214
value must be written as a string (with quotes) to use this feature.
215
The unit name is case-sensitive, and there can be whitespace between
216
the numeric value and the unit.
363
404
>18.1.3. Parameter Interaction via SQL</A
367
408
CLASS="PRODUCTNAME"
368
409
>PostgreSQL</SPAN
369
410
> provides three SQL
370
commands to establish configuration defaults that override those
371
configured globally. The evaluation of these defaults occurs
372
at the beginning of a new session, upon the user issuing <A
373
HREF="sql-discard.html"
375
>, or if the server forces the session to
376
reload its configuration after a <SPAN
411
commands to establish configuration defaults.
412
The already-mentioned <A
413
HREF="sql-altersystem.html"
416
provides a SQL-accessible means of changing global defaults; it is
417
functionally equivalent to editing <TT
421
In addition, there are two commands that allow setting of defaults
422
on a per-database or per-role basis:
388
HREF="sql-altersystem.html"
390
> command provides an
391
SQL-accessible means of changing global defaults.
397
430
HREF="sql-alterdatabase.html"
398
431
>ALTER DATABASE</A
399
> command allows database
400
administrators to override global settings on a per-database basis.
432
> command allows global
433
settings to be overridden on a per-database basis.
406
439
HREF="sql-alterrole.html"
408
> command allows database
409
administrators to override both global and per-database settings
410
with user-specific values.
441
> command allows both global and
442
per-database settings to be overridden with user-specific values.
415
> Once a client connects to the database PostgreSQL provides
416
two additional SQL commands to interact with session-local
417
configuration settings. Both of these commands have equivalent
418
system administration functions.
447
> Values set with <TT
454
are applied only when starting a fresh database session. They
455
override values obtained from the configuration files or server
456
command line, and constitute defaults for the rest of the session.
457
Note that some settings cannot be changed after server start, and
458
so cannot be set with these commands (or the ones listed below).
461
> Once a client is connected to the database, <SPAN
465
provides two additional SQL commands (and equivalent functions) to
466
interact with session-local configuration settings:
540
>, command-line options can be
576
> During server startup, parameter settings can be
541
577
passed to the <TT
544
> command directly via the
584
> command-line parameter. For example,
550
586
CLASS="PROGRAMLISTING"
551
587
>postgres -c log_connections=yes -c log_destination='syslog'</PRE
553
Settings provided this way override those resolved globally (via
589
Settings provided in this way override those set via
556
592
>postgresql.conf</TT
559
595
>ALTER SYSTEM</TT
561
are otherwise treated as being global for the purpose of database
597
so they cannot be changed globally without restarting the server.
573
>, command-line options can be
602
> When starting a client session via <SPAN
606
parameter settings can be
574
607
specified using the <TT
577
610
> environment variable.
578
When connecting to the server, the contents of this variable are
579
sent to the server as if they were being executed via SQL <A
582
> at the beginning of the session.
585
> However, the format of <TT
611
Settings established in this way constitute defaults for the life
612
of the session, but do not affect other sessions.
613
For historical reasons, the format of <TT
589
used when launching the <TT
617
similar to that used when launching the <TT
593
Specifically, the <TT
621
command; specifically, the <TT
596
624
> flag must be specified.
598
627
CLASS="PROGRAMLISTING"
599
>env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql</PRE
628
>env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql</PRE
603
632
> Other clients and libraries might provide their own mechanisms,
604
633
via the shell or otherwise, that allow the user to alter session
605
settings without requiring the user to issue SQL commands.
634
settings without direct use of SQL commands.
615
644
NAME="CONFIG-INCLUDES"
616
>18.1.5. Configuration File Includes</A
645
>18.1.5. Managing Configuration File Contents</A
651
> provides several features for breaking
655
> files into sub-files.
656
These features are especially useful when managing multiple servers
657
with related, but not identical, configurations.
620
In addition to parameter settings, the <TT
661
In addition to individual parameter settings,
622
664
>postgresql.conf</TT
665
> file can contain <I
625
666
CLASS="FIRSTTERM"
626
>include directives</I
628
another file to read and process as if it were inserted into the
629
configuration file at this point. This feature allows a configuration
630
file to be divided into physically separate parts.
631
Include directives simply look like:
669
>, which specify another file to read and process as if
670
it were inserted into the configuration file at this point. This
671
feature allows a configuration file to be divided into physically
672
separate parts. Include directives simply look like:
633
674
CLASS="PROGRAMLISTING"
634
675
>include 'filename'</PRE
636
If the file name is not an absolute path, it is taken as relative to
637
the directory containing the referencing configuration file.
638
Inclusions can be nested.
677
If the file name is not an absolute path, it is taken as relative to
678
the directory containing the referencing configuration file.
679
Inclusions can be nested.
644
685
>include_if_exists</TT
645
686
> directive, which acts
649
> directive, except for the behavior
650
when the referenced file does not exist or cannot be read. A regular
691
when the referenced file does not exist or cannot be read. A regular
654
695
> will consider this an error condition, but
657
698
>include_if_exists</TT
658
699
> merely logs a message and continues
659
processing the referencing configuration file.
700
processing the referencing configuration file.
665
706
>postgresql.conf</TT
666
707
> file can also contain
670
> directives, which specify an entire directory
671
of configuration files to include. It is used similarly:
711
> directives, which specify an entire
712
directory of configuration files to include. These look like
673
714
CLASS="PROGRAMLISTING"
674
715
>include_dir 'directory'</PRE
676
Non-absolute directory names follow the same rules as single file include
677
directives: they are relative to the directory containing the referencing
678
configuration file. Within that directory, only non-directory files whose
679
names end with the suffix <TT
717
Non-absolute directory names are taken as relative to the directory
718
containing the referencing configuration file. Within the specified
719
directory, only non-directory files whose names end with the
682
> will be included. File
683
names that start with the <TT
723
> will be included. File names that
686
> character are also excluded,
687
to prevent mistakes as they are hidden on some platforms. Multiple files
688
within an include directory are processed in file name order. The file names
689
are ordered by C locale rules, i.e. numbers before letters, and uppercase
690
letters before lowercase ones.
727
> character are also ignored, to
728
prevent mistakes since such files are hidden on some platforms. Multiple
729
files within an include directory are processed in file name order
730
(according to C locale rules, i.e. numbers before letters, and
731
uppercase letters before lowercase ones).
693
> Include files or directories can be used to logically separate portions
694
of the database configuration, rather than having a single large
734
> Include files or directories can be used to logically separate portions
735
of the database configuration, rather than having a single large
697
738
>postgresql.conf</TT
698
739
> file. Consider a company that has two
699
database servers, each with a different amount of memory. There are likely
700
elements of the configuration both will share, for things such as logging.
701
But memory-related parameters on the server will vary between the two. And
702
there might be server specific customizations, too. One way to manage this
703
situation is to break the custom configuration changes for your site into
704
three files. You could add this to the end of your
740
database servers, each with a different amount of memory. There are
741
likely elements of the configuration both will share, for things such
742
as logging. But memory-related parameters on the server will vary
743
between the two. And there might be server specific customizations,
744
too. One way to manage this situation is to break the custom
745
configuration changes for your site into three files. You could add
746
this to the end of your <TT
707
748
>postgresql.conf</TT
708
> file to include them:
710
752
CLASS="PROGRAMLISTING"
711
753
>include 'shared.conf'
712
754
include 'memory.conf'
713
755
include 'server.conf'</PRE
715
All systems would have the same <TT
757
All systems would have the same <TT
719
with a particular amount of memory could share the same
761
server with a particular amount of memory could share the
723
>; you might have one for all servers with 8GB of RAM,
724
another for those having 16GB. And finally <TT
765
>; you might have one for all servers
766
with 8GB of RAM, another for those having 16GB. And
728
have truly server-specific configuration information in it.
770
> could have truly server-specific
771
configuration information in it.
731
> Another possibility is to create a configuration file directory and
732
put this information into files there. For example, a <TT
774
> Another possibility is to create a configuration file directory and
775
put this information into files there. For example, a <TT
736
directory could be referenced at the end of<TT
779
directory could be referenced at the end of <TT
738
781
>postgresql.conf</TT
741
784
CLASS="PROGRAMLISTING"
742
785
>include_dir 'conf.d'</PRE
744
Then you could name the files in the <TT
787
Then you could name the files in the <TT
747
> directory like this:
749
793
CLASS="PROGRAMLISTING"
752
796
02server.conf</PRE
754
This shows a clear order in which these files will be loaded. This is
755
important because only the last setting encountered when the server is
756
reading its configuration will be used. Something set in
798
This naming convention establishes a clear order in which these
799
files will be loaded. This is important because only the last
800
setting encountered for a particular parameter while the server is
801
reading configuration files will be used. In this example,
759
804
>conf.d/02server.conf</TT
760
> in this example would override a value
763
808
>conf.d/01memory.conf</TT
767
> You might instead use this configuration directory approach while naming
768
these files more descriptively:
812
> You might instead use this approach to naming the files
770
815
CLASS="PROGRAMLISTING"
772
817
01memory-8GB.conf
773
818
02server-foo.conf</PRE
775
This sort of arrangement gives a unique name for each configuration file
776
variation. This can help eliminate ambiguity when several servers have
777
their configurations all stored in one place, such as in a version
778
control repository. (Storing database configuration files under version
779
control is another good practice to consider).
820
This sort of arrangement gives a unique name for each configuration file
821
variation. This can help eliminate ambiguity when several servers have
822
their configurations all stored in one place, such as in a version
823
control repository. (Storing database configuration files under version
824
control is another good practice to consider.)