The process for an exclusive backup is mostly the same as for a

non-exclusive one, but it differs in a few key steps. It does not allow

more than one concurrent backup to run, and there can be some issues on

the server if it crashes during the backup. Prior toPostgreSQL 9.6, this

the server if it crashes during the backup. Prior to&productname; 9.6, this

was the only low-level method available, but it is now recommended that

all users upgrade their scripts to use non-exclusive backups if possible.

</para>

<para>

<indexterm><primary>BGWORKER_SHMEM_ACCESS</primary></indexterm>

Requests shared memory access. Workers without shared memory access

cannot access any of <productname>PostgreSQL's</productname> shared

cannot access any of <productname>&productname;'s</productname> shared

data structures, such as heavyweight or lightweight locks, shared

buffers, or any custom data structures which the worker itself may

wish to create and use.

<note>

<para>

Before <productname>PostgreSQL</> 9.6, <structname>pg_am</structname>

Before <productname>&productname;</> 9.6, <structname>pg_am</structname>

contained many additional columns representing properties of index access

methods. That data is now only directly visible at the C code level.

However, <function>pg_index_column_has_property()</function> and related

<para>

The view <structname>pg_config</structname> describes the

compile-time configuration parameters of the currently installed

version of <productname>PostgreSQL</>. It is intended, for example, to

version of <productname>&productname;</>. It is intended, for example, to

be used by software packages that want to interface to

<productname>PostgreSQL</> to facilitate finding the required header

<productname>&productname;</> to facilitate finding the required header

files and libraries. It provides the same basic information as the

<xref linkend="app-pgconfig"> <productname>PostgreSQL</> client

<xref linkend="app-pgconfig"> <productname>&productname;</> client

application.

</para>

also used. It is recommended to

leave <literal>include_realm</literal> set to the default (1) and to

provide an explicit mapping in <filename>pg_ident.conf</> to convert

principal names to <productname>PostgreSQL</> user names.

principal names to <productname>&productname;</> user names.

</para>

</listitem>

</varlistentry>

also used. It is recommended to

leave <literal>include_realm</literal> set to the default (1) and to

provide an explicit mapping in <filename>pg_ident.conf</> to convert

principal names to <productname>PostgreSQL</> user names.

principal names to <productname>&productname;</> user names.

</para>

</listitem>

</varlistentry>

</para>

<para>

BSD Authentication in <productname>PostgreSQL</> uses

BSD Authentication in <productname>&productname;</> uses

the <literal>auth-postgresql</literal> login type and authenticates with

the <literal>postgresql</literal> login class if that's defined

in <filename>login.conf</filename>. By default that login class does not

exist, and <productname>PostgreSQL</> will use the default login class.

exist, and <productname>&productname;</> will use the default login class.

</para>

<note>

<para>

To use BSD Authentication, thePostgreSQL user account (that is, the

To use BSD Authentication, the&productname; user account (that is, the

operating system user running the server) must first be added to

the <literal>auth</literal> group. The <literal>auth</literal> group

exists by default on OpenBSD systems.

<literal>s3</> and <literal>s4</>.

</para>

<para>

The second syntax was used before <productname>PostgreSQL</>

The second syntax was used before <productname>&productname;</>

version 9.6 and is still supported. It's the same as the first syntax

with <replaceable class="parameter">num_sync</replaceable> equal to 1.

For example, <literal>1 (s1, s2)</> and

determines how messages are delivered to syslog. When on (the

default), messages are split by lines, and long lines are split so

that they will fit into 1024 bytes, which is a typical size limit for

traditional syslog implementations. When off,PostgreSQL server log

traditional syslog implementations. When off,&productname; server log

messages are delivered to the syslog service as is, and it is up to

the syslog service to cope with the potentially bulky messages.

</para>

</para>

<para>

<productname>PostgreSQL</> does not currently support extension scripts

<productname>&productname;</> does not currently support extension scripts

issuing <command>CREATE POLICY</command> or <command>SECURITY LABEL</command>

statements. These are expected to be set after the extension has been

created. All RLS policies and security labels on extension objects will be

+/-Infinity for monotonically-increasing fields (<literal>epoch</>,

<literal>julian</>, <literal>year</>, <literal>isoyear</>,

<literal>decade</>, <literal>century</>, and <literal>millennium</>).

For other fields, NULL is returned. <productname>PostgreSQL</>

For other fields, NULL is returned. <productname>&productname;</>

versions before 9.6 returned zero for all cases of infinite input.

</para>

</note>

</indexterm>

<para>

All indexes in <productname>PostgreSQL</> are <firstterm>secondary</>

All indexes in <productname>&productname;</> are <firstterm>secondary</>

indexes, meaning that each index is stored separately from the table's

main data area (which is called the table's <firstterm>heap</>

in <productname>PostgreSQL</> terminology). This means that in an

in <productname>&productname;</> terminology). This means that in an

ordinary index scan, each row retrieval requires fetching data from both

the index and the heap. Furthermore, while the index entries that match a

given indexable <literal>WHERE</> condition are usually close together in

</para>

<para>

To solve this performance problem, <productname>PostgreSQL</>

To solve this performance problem, <productname>&productname;</>

supports <firstterm>index-only scans</>, which can answer queries from an

index alone without any heap access. The basic idea is to return values

directly out of each index entry instead of consulting the associated heap

If these two fundamental requirements are met, then all the data values

required by the query are available from the index, so an index-only scan

is physically possible. But there is an additional requirement for any

table scan in <productname>PostgreSQL</>: it must verify that each

table scan in <productname>&productname;</>: it must verify that each

retrieved row be <quote>visible</> to the query's MVCC snapshot, as

discussed in <xref linkend="mvcc">. Visibility information is not stored

in index entries, only in heap entries; so at first glance it would seem

that every row retrieval would require a heap access anyway. And this is

indeed the case, if the table row has been modified recently. However,

for seldom-changing data there is a way around this

problem. <productname>PostgreSQL</> tracks, for each page in a table's

problem. <productname>&productname;</> tracks, for each page in a table's

heap, whether all rows stored in that page are old enough to be visible to

all current and future transactions. This information is stored in a bit

in the table's <firstterm>visibility map</>. An index-only scan, after

SELECT f(x) FROM tab WHERE f(x) &lt; 1;

</programlisting>

as an index-only scan; and this is very attractive if <literal>f()</> is

an expensive-to-compute function. However, <productname>PostgreSQL</>'s

an expensive-to-compute function. However, <productname>&productname;</>'s

planner is currently not very smart about such cases. It considers a

query to be potentially executable by index-only scan only when

all <emphasis>columns</> needed by the query are available from the index.

indexable <literal>WHERE</> clauses to the index column. It will usually

get this right in simple queries such as shown above, but not in queries

that involve joins. These deficiencies may be remedied in future versions

of <productname>PostgreSQL</>.

of <productname>&productname;</>.

</para>

<para>

not need to recheck that part of the <literal>WHERE</> clause at run time:

all entries found in the index necessarily have <literal>success = true</>

so this need not be explicitly checked in the

plan. <productname>PostgreSQL</> versions 9.6 and later will recognize

plan. <productname>&productname;</> versions 9.6 and later will recognize

such cases and allow index-only scans to be generated, but older versions

will not.

</para>

<para>

The source directory <filename>contrib/intarray/bench</> contains a

benchmark test suite, which can be run against an installed

<productname>PostgreSQL</> server. (It also requires <filename>DBD::Pg</>

<productname>&productname;</> server. (It also requires <filename>DBD::Pg</>

to be installed.) To run:

</para>

<literal>INFO</>, or <literal>LOG</> (in a notice message).

This is identical to the <symbol>PG_DIAG_SEVERITY</> field except

that the contents are never localized. This is present only in

reports generated by <productname>PostgreSQL</> versions 9.6

reports generated by <productname>&productname;</> versions 9.6

and later.

</para>

</listitem>

<title>Progress Reporting</title>

<para>

<productname>PostgreSQL</> has the ability to report the progress of

<productname>&productname;</> has the ability to report the progress of

certain commands during command execution. Currently, the only command

which supports progress reporting is <command>VACUUM</>. This may be

expanded in the future.

</para>

<para>

While <productname>PostgreSQL</>'s Serializable transaction isolation

While <productname>&productname;</>'s Serializable transaction isolation

level only allows concurrent transactions to commit if it can prove there

is a serial order of execution that would produce the same effect, it

doesn't always prevent errors from being raised that would not occur in

</indexterm>

<para>

<productname>PostgreSQL</> can devise query plans which can leverage

<productname>&productname;</> can devise query plans which can leverage

multiple CPUs in order to answer queries faster. This feature is known

as parallel query. Many queries cannot benefit from parallel query, either

due to limitations of the current implementation or because there is no

</para>

<para>

<productname>PostgreSQL</> supports parallel aggregation by aggregating

<productname>&productname;</> supports parallel aggregation by aggregating

twice. First, each process participating in the parallel portion of the

query performs an aggregation step, producing a partial result for each

group of which that process is aware. This is reflected in the plan as

shared_preload_libraries = 'pg_query_state'

</programlisting>

<para>

It will require to restart the <productname>PostgreSQL</productname>

It will require to restart the <productname>&productname;</productname>

instance.

</para>

</sect2>

<para>

Data type names often need translation. For example, in Oracle string

values are commonly declared as being of type <type>varchar2</>, which

is a non-SQL-standard type. In <productname>PostgreSQL</productname>,

is a non-SQL-standard type. In <productname>&productname;</productname>,

use type <type>varchar</> or <type>text</> instead. Similarly, replace

type <type>number</> with <type>numeric</>, or use some other numeric

data type if there's a more appropriate one.

<listitem>

<para>

This option is a comma-separated list of names

of <productname>PostgreSQL</> extensions that are installed, in

of <productname>&productname;</> extensions that are installed, in

compatible versions, on both the local and remote servers. Functions

and operators that are immutable and belong to a listed extension will

be considered shippable to the remote server.

<literal>INFO</>, or <literal>LOG</> (in a notice message).

This is identical to the <literal>S</> field except

that the contents are never localized. This is present only in

messages generated by <productname>PostgreSQL</> versions 9.6

messages generated by <productname>&productname;</> versions 9.6

and later.

</para>

</listitem>

<!--

doc/src/sgml/ref/create_access_method.sgml

PostgreSQL documentation

&productname; documentation

-->

<refentry id="sql-create-access-method">

<para>

<command>CREATE ACCESS METHOD</command> is a

<productname>PostgreSQL</> extension.

<productname>&productname;</> extension.

</para>

</refsect1>

from <firstterm>index-only scans</> by including certain columns in the

index, the value of which would otherwise have to be obtained by reading

the table's heap. Having these columns in the <literal>INCLUDING</> clause

in some cases allows <productname>PostgreSQL</> to skip the heap read

in some cases allows <productname>&productname;</> to skip the heap read

completely. This also allows <literal>UNIQUE</> indexes to be defined on

one set of columns, which can include another set of column in the

<literal>INCLUDING</> clause, on which the uniqueness is not enforced upon.

<!--

doc/src/sgml/ref/drop_access_method.sgml

PostgreSQL documentation

&productname; documentation

-->

<refentry id="sql-drop-access-method">

<para>

<command>DROP ACCESS METHOD</command> is a

<productname>PostgreSQL</> extension.

<productname>&productname;</> extension.

</para>

</refsect1>

<note>

<para>

In versions prior to <productname>PostgreSQL</> 9.6, this flag

In versions prior to <productname>&productname;</> 9.6, this flag

matched only tables, not any other type of relation.

</para>

</note>

<note>

<para>

Before <productname>PostgreSQL</> 9.6, SQL commands in script files

Before <productname>&productname;</> 9.6, SQL commands in script files

were terminated by newlines, and so they could not be continued across

lines. Now a semicolon is <emphasis>required</> to separate consecutive

SQL commands (though a SQL command does not need one if it is followed

<listitem>

<para>

Before <productname>PostgreSQL</productname> 9.6,

Before <productname>&productname;</productname> 9.6,

the <option>-c</option> option implied <option>-X</option>

(<option>--no-psqlrc</>); this is no longer the case.

</para>

contains any volatile or expensive functions. With that behavior, the

order of function evaluations is more intuitive and there will not be

evaluations corresponding to rows that never appear in the output.

<productname>PostgreSQL</> will effectively evaluate output expressions

<productname>&productname;</> will effectively evaluate output expressions

after sorting and limiting, so long as those expressions are not

referenced in <literal>DISTINCT</literal>, <literal>ORDER BY</literal>

or <literal>GROUP BY</literal>. (As a counterexample, <literal>SELECT

<note>

<para>

<productname>PostgreSQL</> versions before 9.6 did not provide any

<productname>&productname;</> versions before 9.6 did not provide any

guarantees about the timing of evaluation of output expressions versus

sorting and limiting; it depended on the form of the chosen query plan.

</para>

at <filename>/etc/systemd/system/postgresql.service</filename>):<indexterm><primary>systemd</primary></indexterm>

<programlisting>

[Unit]

Description=PostgreSQL database server

Description=&productname; database server

Documentation=man:postgres(1)

[Service]

Consider carefully the timeout

setting. <application>systemd</application> has a default timeout of 90

seconds as of this writing and will kill a process that does not notify

readiness within that time. But a <productname>PostgreSQL</productname>

readiness within that time. But a <productname>&productname;</productname>

server that might have to perform crash recovery at startup could take

much longer to become ready. The suggested value of 0 disables the

timeout logic.

owned by root and have group read access (that is, <literal>0640</literal>

permissions). That setup is intended for installations where certificate

and key files are managed by the operating system. The user under which

the <productname>PostgreSQL</productname> server runs should then be made a

the <productname>&productname;</productname> server runs should then be made a

member of the group that has access to those certificate and key files.

</para>