diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index ae5f3fac75..965eb751c0 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,7 +1,7 @@ - Advanced Features + Advanced SQL Features Introduction diff --git a/doc/src/sgml/architecture.sgml b/doc/src/sgml/architecture.sgml new file mode 100644 index 0000000000..4c328c37f1 --- /dev/null +++ b/doc/src/sgml/architecture.sgml @@ -0,0 +1,562 @@ + + + + The Architecture + + + Every DBMS implements basic strategies to achieve a fast and + robust system. This chapter provides an overview about some + of the techniques which are used by + PostgreSQL to reach this aim. + + + + Collabortion of Processes, RAM, and Files + + As is a matter of course, in a client/server architecture + clients do not have direct access to the database. Instead, + at the server side there is a group of processes, which + receive all requests comming from client applications, + handle them, and send resulting information back to + the clients. + These server-side processes work in close cooperation to + each other. Together with their common + they are called an + Instance. + + + + The instance is initiated by the + process. + He loads the configuration files, allocates the + Shared Memory + and starts the comprehensive network of processes: + , + , + , + , + , + , + , and more. + visualizes + main aspects of their collaboration. + + + + Whenever a client application tries to connect to a + , this request is handled + in a first step by the Postmaster + process. He checks the authorization, starts a + new , + and instructs the client application to connect to this + new Backend process. All further + client requests go to this process and are handled + by him. Thus - after the initial phase - each client + application communicates with exactly + one Backend process. + + +
+ Architecture + + + + + + + + + + +
+ + + Client requests (SELECT, UPDATE, ...) usually leads to the + necessity to read or write some data. In an first attempt + the client's Backend process tries + to get the information out of Shared + Memory. This Shared + Memory is a mirror of parts of the + and + files. Because files are + much huger than memory, it is easily possible that + the desired information is not (completely) available + in the RAM. In this case the Backend process + must transfer additional file pages to + Shared Memory. Files are physically + organized in pages. Every transfer between files and + RAM is performed in units of complete pages while retaining + their size and layout. + + + + Reading file pages is notedly slower than reading + RAM. This is the main motivation for the existence of + Shared Memory. As soon as one + of the Backend processes has done + the job, this pages are available for all other + Backend processes for direct + access in RAM. + + + + Shared Memory is limited in size. + Sooner or later it is necessary to overwrite old RAM + pages. As long as the content of such pages hasn't + changed, this is not a problem. But in + Shared Memory also write + actions take place + - performed by any of the Backend + processes (or the + Auto Vacuum or other + processes). + Such modified pages are called + dirty pages. + Before dirty pages can be overwritten, + they must be saved to disk. This is a two-step process. + + + + First, whenever the content of a page changes, a + is created out + of the delta-information (difference between old and + new content) and stored in another area of the + Shared Memory. These + WAL records are read by the + WAL writer process, + who runs in parallel to the Backend + processes and all other processes of + the instance. He writes + the continuously arising WAL + records to + the end of the current WAL + file. Because of the sequential + nature of this writing, it is much faster than + the more or less random access + to data files with heap + and index information. + As mentioned, this WAL-writing happens + in an independent process. Nevertheless all + WAL records created out of one + dirty page must be transfered + to disk before the dirty page + itself can be transfered to disk (???). + + + + Second, the transfer of dirty pages + from Shared Memory to files must + take place. This is the primarily duty of the + Background Writer process. Because + huge I/O activities can block other processes significantly, + he starts periodically and acts only for a short period. + Doing so, his expensive I/O activities are spread over + time avoiding huge I/O peaks. Also the + Checkpointer process transfers + dirty pages to files, see next + paragraph. + + + + The Checkpointer has a special + duty. As its name suggests, he has to create + Checkpoints. Such a + is a point in time + when all older dirty pages, + all older WAL records, and + lastly a special Checkpoint record + have been written and flushed to disk. + In consequence, after a Checkpoint + data files and WAL files are in sync. + In case of a recovery (after a crash of the instance) + it is known, that the information of all + WAL records preceding + the last Checkpoint record + is already integrated into the data files. This + speeds up a possibly occurring recovery. + + + + In correlation with data changes, + WAL records arise and are writen + to WAL files. + Those WAL files - in combination with + a previously taken Base Backup - + are necessary to restore a database after a crash of the + disk, where data files have been stored. Therefore it is + recommended to transfer a copy of the + WAL files + to a second, independent place. The purpose of the + process is to perform + this copy action. + + + + The Stats Collector process collects + counter about accesses to SQL + objects like tables, rows, indexes, pages, + and others. He stores the obtained information in system + tables. + + + + The Log Writer process writes + readable text lines about serious and non-serious events, + which can happen during database access, e.g.: wrong password, + no permission, long-running query, ... . + + + + + + Cluster, Database, Schema + + PostgreSQL organizes data and + meta data in a strict hierarchy and allows connections + - depending on the user's rights - to access objects of + different hierarchy levels. + + + Ownership + + + + shows the relation of internal objects to each other, especially + their hierarchical nature. + + + + On a + exists one or more . + A cluster essentially contains + databases, but also + tablespaces, + replication origins, + subscriptions for + logical replication, + roles, and information about all + database names + within this cluster. + + A database essentially contains + schemas, but also + collations, and - if created - + extensions. + A schema contains such objects + with which users interact mostly: tables, + views, and many more. + + +
+ Hierarchy of Internal Objects + + + + + + + + + + +
+ + + The hierarchy is strict, which means that each object has + exactly one parent, e.g.: each table + or view belongs to a certain + schema, it cannot belong to another + schema. An extension + like a foreign data wrapper + belongs to exactly one + database. If it is needed in another + database as well, it must be installed + twice. + + + + Schemas contain SQL objects + of different type: table, view, + materialized view, index, constraint, sequence, function, + procedure, trigger, data type, operator, and more. + A schema is a namespace for all of its objects, independent + from the object type, and ensures that within its scope object + names are unique. E.g.: It is not possible + to have a table employee and a view + employee within the same + schema. But it is possible to have + two tables employee in different + schemas. In this case the two tables + are different objects and absolutely independent from each + other. + + + + + + Visibility + + Independent from this strict 'belong-to' hierarchy in most + cases the knowledge about objects of the same or another + level is available via the system catalog. + Also the access to them is possible, if appropriate privileges + are granted. + All connections act at the database level (bold ellipse in the + above figure). From here they know about and have access to + nearly all objects: + + + + Their direct child objects schema, extension, + and collation. They can + access different schemas and use all extensions and + collations of this database. + + + + + + Their grandchild objects tables, view, ... + . The sequence of schemas for locating + such objects is defined via the variable + search_path. + + + + + + All tablespaces. If a tablespace is + defined in any database, it is automatically visible and + active in all other databases. It's name cannot be used + in any of the other databases for creating an additional + tablespace. + + + + + + All roles. If a role is defined + in any database, it is visible and active in all + other databases. This implies, that the same + role cannot be created again in a different + database, it + exists already. But a DBA can grant different + privilegs to the same roles depending on databases. + + + + + + All database names of this cluster. + Although the database names are known, the current + connection is not able to switch to a different + database. To do so, a different + connection is necessary. This behaviour results from the + implementation of connections. + + + + + + Cluster limits are unbreachable boarders for a single + connection. No information about other clusters or their + content is visible or accessible. Only + foreing data wrappers and + replication mechanisms are able to communicate + across cluster limits. To do so, they use additional + communication lines. + + + + + + + + + Initial Objects + + + As a result of its initialization process + (initdb) a cluster + contains certain objects with predefined names. When + DBAs create more objects to satisfy their needs, these + user-created objects fit into the same hierarchy + and comply to the same visibility rules. + + shows the objects and their names. + + +
+ Cluster, Database, Schema + + + + + + + + + + +
+ + + By default the command initdb creates the + cluster data. + + + + template0 is the very first + database of any + cluster. template0 + is created by C-routines during the initialization phase + of the cluster. + In a second step template1 is generated + as a copy of template0 and finally + postgres as a copy of + template1. All other + new databases + of this cluster, + such as my_db, are also copied from + template1. Due to the special + role of template0 as the origin + of all other databases, no client + can connect to it. + + + + The schema public acts as the default + schema and contains all such + SQL objects, which are created + within public or without using any schema + name. public should not contain user defined + SQL objects. It is recommended to + create a separate schema which + holds individual objects like application-specific tables or + views. + + + + pg_catalog is a schema for all tables + and views of the . + information_schema is a schema for several + tables and views of the System Catalog + in a way, which conforms to the SQL standard. + + + + + + + Directory Structure + + + PostgreSQL organizes long lasting + data as well as temporary state information about transactions + or replication actions in the file system. Every + has its own root directory + anywhere in the file system. In many cases, the environment + variable PGDATA points to this directory. + The example of the following survey, which is shown in + , uses + data as the name of this root directory. + + +
+ Directory Structure + + + + + + + + + + +
+ + + data contains many subdirectories and + some files, all of which are necessary to store long lasting + as well as temporary data. The following paragraphs + describe the files and subdirectories in + data. + + + + base is a subdirectory in which one + subdirectory per database exists. + The names of those sudirectories consists of numbers. + These are the internal + Object Identifiers (OID), which are + numbers to identify the database definition in the + . + + + + Within the database-specific + subdirectories there are many files. Primarily they contain + the heap and + index. But there are also optimization information + like Free Space Maps or + Visibility Maps. + + + + Another important subdirectory is global. + In analogy to the database-specific + subdirectories, there are files containing information about + s. One type + of such Global Objects are + tablespaces. In + global there are information about + the tablespaces, neither the + tablespaces itself nor the + symbolic links to them. + + + + The subdirectory pg_wal contains + WAL files. + They arise and grow in parallel with data changes in the + cluster and remain alive as long as + they are required for recovery, archiving, or replication. + Please note that they belong to the complete cluster, not + to single databases. + + + + The subdirectory pg_xact contains + information about the status of each transaction. + + + + In pg_tblspc there are symbolic links + that point to directories containing such + SQL objects that are created within + tablespaces. + + + + In the directory data exists also some + files. As long as the + cluster is up and running, the file + postmaster.pid exists and contains the ID of + the Postmaster process. In many cases + the configuration files of this cluster + are stored here. + + + + + diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 3da2365ea9..022c2efda3 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -8,9 +8,10 @@ - - - + + + + @@ -169,6 +170,7 @@ + diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml new file mode 100644 index 0000000000..664d6d23b3 --- /dev/null +++ b/doc/src/sgml/glossary.sgml @@ -0,0 +1,502 @@ + + + + Glossary + + + The glossary contains a collection of + PostgreSQL related terms. + In order to keep such terms together, which are related + to each other or can be easily confused, they are not + ordered alphabetically. + + + In some cases terms are extended by an alternative wording + in parenthesize, e.g.: '(or: Database Server)'. Such deviant + formulations are sometimes used at various points in the + documentation, but will be replaced by the preferred term + in a future release. + + + + + + Server (or: Host) + + + The term server denotes real hardware, + a container, or a Virtual Machine. + + + + + + Instance (or: Database Server, or: Backend Server) + + + An instance is a group + of processes plus their common + Shared Memory running on a single + server. The instance handles all key + features of a DBMS: read and write access to files and + Shared Memory, assurance of the + ACID paradigm, MVCC, + connections to client programms, + backup, recovery, + replication, privileges, … . + + + An instance manages exactly one cluster. + + + Many instances can run on the same + server – as + long as they use different IP ports and manage different + clusters. Different + instances on a + server may use the same or different + versions of PostgreSQL. + + + + + + Cluster (or: Database Cluster) + + + A cluster is a + group of 3 or more databases (template0, + template1, postgres, + ...) plus their + Global SQL objects. The cluster is managed by exactly one + instance. + + + + + + Database + + + A database is + a named collection of SQL objects. + The database is stored in files of a single directory plus + – optionally - in additional tablespaces. + + + + + + Schema + + + A schema is a + namespace for SQL objects, which + reside in the same database. The + names of such objects are unique - even across different + types of objects. Same object names can only + be used in different schemas. + + + There are some predefined schemas: + + + Schema public: A schema for objects, which + were created within this schema or outside of any schema. + It is recommended that users do not store their new + objects in public. Instead, they shall + create their own schema, e.g. my_schema and + store everything over there. + + + Schema pg_catalog: A schema for all tables and + views of the System Catalog. + + + Schema information_schema: A schema for several + tables and views of the System Catalog in a way, + which conforms to the SQL standard. + + + + + + System Catalog + + + The + System Catalog is a collection of tables and + views, which describes the structure of all + SQL objects of this + database and the + Global SQL objects of the + cluster. + The System Catalog resides in the schema + pg_catalog. Main parts are mirrored as + views in the schema information_schema. + + + + + + Catalog + + + The SQL standard uses the standalone term + Catalog + for what is called a database in + PostgreSQL's terminology. + + + + + + SQL Object + + + An SQL object + is a table, view, materialized view, index, + constraint, sequence, function, procedure, trigger, data type, + operator, … . Every SQL object belongs + to exactly one schema. Within this + schema its name is unique. + + + See also: + Global SQL objects. + + + + + + Global SQL Object + + + Not all objects belong to a certain + schema but to their + database or even to the complete + cluster. Therefore they are called + Global SQL objects. + Collations and Extensions + like foreing data wrappers + reside at the database level; + database names, + roles, + tablespaces, + replication origins, and + subscriptions for logical replication + at the cluster level. + + + + + + Shared Memory + + + + Shared Memory is RAM which is used by the processes + of an instance in common. It + mirrors parts of database files, provides an area for + WAL records, and stores some more + common information. Please notice, that Shared Memory + belongs to the complete instance, + not to a single database. + + + Shared Memory is organized in pages. + If such a page gets modified, she is called a + dirty page until she is written + back to the file system. + + + + + + Postmaster + + + Postmaster is the + name for the very first process of an + instance. He starts + the other processes and creates + backend processes on demand. + + + + + + Backend Process + + + + Backend processes are such processes of an + instance, which act + on behalf of client connections + and process their requests. + + + (Don't confuse the term with the similar terms + Background Writer or + Background Worker). + + + + + + Connection + + + A connection is a TCP/IP or + socket line for inter-process + communication. If the two involved processes reside on + different servers, TCP/IP must be used. Otherwise both + techniques are possible. + + + + + + Background Writer + + + The + Background Writer process writes continuously dirty + pages from Shared Memory to the + file system. He starts periodically. But he works only for a + short period in order to distribute his expensive I/O activity + over time instead of generating few and huge I/O peaks, which + could block other processes. + + + + + + Background Worker + + + Background Workers are + individual processes within an + instance, which run system- or + user-supplied code. A typically use case are processes + which handle parts of an SQL query to take advantage of parallel + execution on servers with multiple CPUs. + + + + + + WAL Writer + + + The WAL writer + process writes WAL records + from the Shared Memory to + WAL files. + + + + + + WAL Record (or: Log Record) + + + A WAL record + contains either new or changed heap or index + data or COMMIT, ROLLBACK, SAVEPOINT, or CHECKPOINT + information in binary format. + + + + + + WAL File (or: WAL Segment, or WAL Segment File or: WAL Log) + + + WAL records are continously + written to the end of the current + WAL file. Please notice, + that WAL files as well as WAL records + belong to the complete cluster, + not to a single database. + After a WAL file is full + (default: 16 MB), a new WAL file is created or + - under certain conditions - one of the previous WAL + files is renamed and reused. + + + The sequence of WAL records + in combination with the sequence of WAL files + represents the sequence of changes that have + taken place in the cluster + over time (???). + + + + + + File Segment (or: Segment) + + + If a database file grows in size, he may be split into + multiple physical files. Those files are called + file segments. + + + (Don't confuse the term with the similar term + WAL segment). + + + + + + Archiver + + + The archiver + process copies WAL files to a + different place in the file system. + + + + + + Checkpointer + + + When reaching certain time- or volume-depending criterias + or when an SQL CHECKPOINT command is issued, the + checkpointer + process creates a + Checkpoint. He writes dirty + pages and WAL records + to the file system, and generates a special + Checkpoint record. + + + + + + Checkpoint + + + A + Checkpoint + is a point in time when all older dirty + pages of the Shared Memory, + all older WAL records, and a + special Checkpoint record + have been written and flushed to disk. + + + + + + Savepoint + + + A savepoint + is a special mark (like a timestamp) inside + a transaction. Data modifications after this point in time + may be rolled back to the time of the savepoint. + + + + + + Data Directory (or: Data Area) + + + A data directory + is the root directory of a + cluster (see also: + PGDATA). + + + + + + + Heap + + + A heap contains the original values of row attributes, + i.e. the data. The heap is realized within database files + and mirrored in Shared Memory. + + + + + + + Index + + + An index consists of keys, which are copies of original + row attributes, and pointers to the location of this original + rows. Such key/value pairs are organized as a tree or a hash (???). + The index is realized within database files and mirrored in + Shared Memory. + + + + + + Log Writer + + + If activated and parameterized, the + Log Writer + process writes information about database events into the + current Log file. When reaching + certain time- or volume-dependent criterias, he creates + a new Log file. + + + + + + Log File + + + LOG files + contain readable text lines about serious + and non-serious events, e.g.: use of + wrong password, long-running queries, ... . + + + + + + AutoVacuum + + + The AutoVacuum + processes remove outdated + MVCC records of the heap + and index. + + + + + + Stats Collector + + + The + Stats Collector process collects + statistical information about the + cluster activities. + + + + + + + + diff --git a/doc/src/sgml/images/cluster-db-schema-ink-svgo.svg b/doc/src/sgml/images/cluster-db-schema-ink-svgo.svg new file mode 100644 index 0000000000..6acb2b69fe --- /dev/null +++ b/doc/src/sgml/images/cluster-db-schema-ink-svgo.svg @@ -0,0 +1,160 @@ + + + Server (Hardware, Container, or VM) + + + + + + + + schema 'public' + + + tables, views, ... + + + + (more system schemas) + + + + + + + schema 'public' + + + tables, views, ... + + + + 'my_schema' (optional) + + + tables, views, ... + + + + (more system schemas) + + + + + UML Note + + + + + + + + + + Server (Hardware, Container, or VM) + + + + cluster 'data' (default, managed by one instance) + + + + cluster 'cluster_2' (optional, managed by a different instance) + + + + + database 'template0' + + + + + + database 'template1' + + + + + + database 'postgres' + + + + + + database 'my_db' (optional) + + + + + + Global SQL objects + + + + + + + + + + 1) + + + By default, you work in the cluster 'data', database 'postgres', + + + schema 'public'. + + + 2) + + + More system schemas: pg_catalog, information_schema, + + + pg_temp, pg_toast. + + + 3) + + + Global SQL objects: Some SQL objects are automatically active + + + and known database- or even cluster-wide. + + + 4) + + + The command 'initdb' creates a new cluster with the three + + + databases 'template0', 'template1', and 'postgres'. The command + + + 'createdb' creates a new database. + + + 5) + + + If multiple clusters are active on one server at the same time, + + + each one is managed by an individual instance. Each such instance + + + uses a different port. + + + 6) + + + No client application is allowed to connect to 'template0'. + + + diff --git a/doc/src/sgml/images/cluster-db-schema-ink.svg b/doc/src/sgml/images/cluster-db-schema-ink.svg new file mode 100644 index 0000000000..beb1f00481 --- /dev/null +++ b/doc/src/sgml/images/cluster-db-schema-ink.svg @@ -0,0 +1,482 @@ + + + + + + image/svg+xml + + Server (Hardware, Container, or VM) + + + + + Server (Hardware, Container, or VM) + + + + + + + + + + schema 'public' + tables, views, ... + + (more system schemas) + + + + + + + + schema 'public' + tables, views, ... + + 'my_schema' (optional) + tables, views, ... + + (more system schemas) + + + UML Note + + + + + + + + + + + + + + Server (Hardware, Container, or VM) + + + + cluster 'data' (default, managed by one instance) + + + + cluster 'cluster_2' (optional, managed by a different instance) + + + + + database 'template0' + + + + + database 'template1' + + + + + database 'postgres' + + + + + database 'my_db' (optional) + + + + + Global SQL objects + + + + + + + + + 1) + By default, you work in the cluster 'data', database 'postgres', + schema 'public'. + 2) + More system schemas: pg_catalog, information_schema, + pg_temp, pg_toast. + 3) + Global SQL objects: Some SQL objects are automatically active + and known database- or even cluster-wide. + 4) + The command 'initdb' creates a new cluster with the three + databases 'template0', 'template1', and 'postgres'. The command + 'createdb' creates a new database. + 5) + If multiple clusters are active on one server at the same time, + each one is managed by an individual instance. Each such instance + uses a different port. + 6) + No client application is allowed to connect to 'template0'. + + diff --git a/doc/src/sgml/images/cluster-db-schema-raw.svg b/doc/src/sgml/images/cluster-db-schema-raw.svg new file mode 100644 index 0000000000..a4919165e2 --- /dev/null +++ b/doc/src/sgml/images/cluster-db-schema-raw.svg @@ -0,0 +1,173 @@ + + + + Server (Hardware, Container, or VM) + + + + + + + + + + + + + + schema 'public' + tables, views, ... + + + (more system schemas) + + + + + + + + + + schema 'public' + tables, views, ... + + + 'my_schema' (optional) + tables, views, ... + + + (more system schemas) + + + + UML Note + + + + + + + + + + + + + + + + + + Server (Hardware, Container, or VM) + + + + + cluster 'data' (default, managed by one instance) + + + + cluster 'cluster_2' (optional, managed by a different instance) + + + + + + + database 'template0' + + + + + + database 'template1' + + + + + + database 'postgres' + + + + + + database 'my_db' (optional) + + + + + + Global SQL objects + + + + + + + + + + + 1) + By default, you work in the cluster 'data', database 'postgres', + schema 'public'. + + 2) + More system schemas: pg_catalog, information_schema, + pg_temp, pg_toast. + + 3) + Global SQL objects: Some SQL objects are automatically active + and known database- or even cluster-wide. + + 4) + The command 'initdb' creates a new cluster with the three + databases 'template0', 'template1', and 'postgres'. The command + 'createdb' creates a new database. + + 5) + If multiple clusters are active on one server at the same time, + each one is managed by an individual instance. Each such instance + uses a different port. + + 6) + No client application is allowed to connect to 'template0'. + + + + diff --git a/doc/src/sgml/images/directories-ink-svgo.svg b/doc/src/sgml/images/directories-ink-svgo.svg new file mode 100644 index 0000000000..8dd88784ca --- /dev/null +++ b/doc/src/sgml/images/directories-ink-svgo.svg @@ -0,0 +1,164 @@ + + + Directory structure of a cluster + + + + + + Directory + + + + + + + File + + + + + + + + + + + Directory structure for storing durable and fluctuating information + + + + + ... /pg/ + + + An arbitrary directory + + + + + + data/ + + + Root of cluster 'data' (see: PGDATA) + + + + + + base/ + + + Subdirectory containing per-database subdirectories + + + + + + 1/ + + + Subdirectory for data of first database 'template0' + + + + + + 12992/ + + + Subdirectory for data of second database 'template1' + + + + + + 12999/ + + + Subdirectory for data of third database 'postgres' + + + + + + nnnnn/ + + + Optional: more subdirectories for databases, e.g. 'my_db' + + + + + + global/ + + + Subdirectory with information about Global SQL Objects + + + + + + pg_wal/ + + + Subdirectory for Write Ahead Log files ('pg_xlog' before version 10) + + + + + + pg_xact/ + + + Subdirectory for transaction commit status ('pg_clog' before version 10) + + + + + + pg_tblspc/ + + + Subdirectory containing symbolic links to tablespaces + + + + + + pg_... / + + + Some more subdirectories + + + + + + + 'postmaster.pid' and other files with cluster-wide relevance + + + + + + ... /xyz/ + + + Same or another arbitrary directory + + + + + + cluster_2/ + + + Root of another cluster 'cluster_2' + + + diff --git a/doc/src/sgml/images/directories-ink.svg b/doc/src/sgml/images/directories-ink.svg new file mode 100644 index 0000000000..2b89d02096 --- /dev/null +++ b/doc/src/sgml/images/directories-ink.svg @@ -0,0 +1,397 @@ + + + + + + image/svg+xml + + Directory structure of a cluster + + + + + Directory structure of a cluster + + + + + Directory + + + + + + File + + + + + + + + + + + + Directory structure for storing durable and fluctuating information + + + + ... /pg/ + An arbitrary directory + + + + data/ + Root of cluster 'data' (see: PGDATA) + + + + base/ + Subdirectory containing per-database subdirectories + + + + + 1/ + Subdirectory for data of first database 'template0' + + + + 12992/ + Subdirectory for data of second database 'template1' + + + + 12999/ + Subdirectory for data of third database 'postgres' + + + + nnnnn/ + Optional: more subdirectories for databases, e.g. 'my_db' + + + + global/ + Subdirectory with information about Global SQL Objects + + + + pg_wal/ + Subdirectory for Write Ahead Log files ('pg_xlog' before version 10) + + + + pg_xact/ + Subdirectory for transaction commit status ('pg_clog' before version 10) + + + + pg_tblspc/ + Subdirectory containing symbolic links to tablespaces + + + + pg_... / + Some more subdirectories + + + + + 'postmaster.pid' and other files with cluster-wide relevance + + + + + ... /xyz/ + Same or another arbitrary directory + + + + cluster_2/ + Root of another cluster 'cluster_2' + + diff --git a/doc/src/sgml/images/directories-raw.svg b/doc/src/sgml/images/directories-raw.svg new file mode 100644 index 0000000000..92e766dd89 --- /dev/null +++ b/doc/src/sgml/images/directories-raw.svg @@ -0,0 +1,144 @@ + + + + Directory structure of a cluster + + + + + + + + Directory + + + + + + + File + + + + + + + + + + + + + + + + Directory structure for storing durable and fluctuating information + + + + + ... /pg/ + An arbitrary directory + + + + + data/ + Root of cluster 'data' (see: PGDATA) + + + + + base/ + Subdirectory containing per-database subdirectories + + + + + + 1/ + Subdirectory for data of first database 'template0' + + + + 12992/ + Subdirectory for data of second database 'template1' + + + + 12999/ + Subdirectory for data of third database 'postgres' + + + + nnnnn/ + Optional: more subdirectories for databases, e.g. 'my_db' + + + + + global/ + Subdirectory with information about Global SQL Objects + + + + + pg_wal/ + Subdirectory for Write Ahead Log files ('pg_xlog' before version 10) + + + + + pg_xact/ + Subdirectory for transaction commit status ('pg_clog' before version 10) + + + + + pg_tblspc/ + Subdirectory containing symbolic links to tablespaces + + + + + pg_... / + Some more subdirectories + + + + + + 'postmaster.pid' and other files with cluster-wide relevance + + + + + + ... /xyz/ + Same or another arbitrary directory + + + + + cluster_2/ + Root of another cluster 'cluster_2' + + + diff --git a/doc/src/sgml/images/internal-objects-hierarchy-ink-svgo.svg b/doc/src/sgml/images/internal-objects-hierarchy-ink-svgo.svg new file mode 100644 index 0000000000..71b888b8b0 --- /dev/null +++ b/doc/src/sgml/images/internal-objects-hierarchy-ink-svgo.svg @@ -0,0 +1,85 @@ + + + Hierarchy of Internal Objects + + + + + Hierarchy of Internal Objects + + + + + Server + + + + Cluster + + + + + Database Names + + + + + + Tablespace + + + + + + Replication Origins + + + + + + Subscription for + + + Logical Replication + + + + + + Role + + + + + + Database + + + + + Extension + + + + + + Collation + + + + + + Schema + + + + + Table, View, ... + + + + + + diff --git a/doc/src/sgml/images/internal-objects-hierarchy-ink.svg b/doc/src/sgml/images/internal-objects-hierarchy-ink.svg new file mode 100644 index 0000000000..052726e7ae --- /dev/null +++ b/doc/src/sgml/images/internal-objects-hierarchy-ink.svg @@ -0,0 +1,268 @@ + + + + + + image/svg+xml + + Hierarchy of Internal Objects + + + + + + Hierarchy of Internal Objects + + + + + + Hierarchy of Internal Objects + + + + + Server + + + Cluster + + + Database Names + + + + Tablespace + + + + Replication Origins + + + + Subscription for + Logical Replication + + + + Role + + + + + Database + + + + Extension + + + + Collation + + + + Schema + + + Table, View, ... + + + + + + + diff --git a/doc/src/sgml/images/internal-objects-hierarchy-raw.svg b/doc/src/sgml/images/internal-objects-hierarchy-raw.svg new file mode 100644 index 0000000000..531a24d3be --- /dev/null +++ b/doc/src/sgml/images/internal-objects-hierarchy-raw.svg @@ -0,0 +1,99 @@ + + + + Hierarchy of Internal Objects + + + + + + + + Hierarchy of Internal Objects + + + + + + + Server + + + + Cluster + + + + Database Names + + + + + Tablespace + + + + + Replication Origins + + + + + Subscription for + Logical Replication + + + + + Role + + + + + + + Database + + + + + Extension + + + + + Collation + + + + + Schema + + + + Table, View, ... + + + + + + + + diff --git a/doc/src/sgml/images/ram-proc-file-ink-svgo.svg b/doc/src/sgml/images/ram-proc-file-ink-svgo.svg new file mode 100644 index 0000000000..ed026fc04a --- /dev/null +++ b/doc/src/sgml/images/ram-proc-file-ink-svgo.svg @@ -0,0 +1,288 @@ + + + PG Overall Server Architecture + + + + + + UML Note (200 x 20 px) + + + + + + UML Note (250 x 20 px) + + + + + + UML Note (100 x 35 px) + + + + + + UML Note (170 x 50 px) + + + + + + UML State, big + + + + + + UML State, big + + + + + + Disc + + + + + + + + + Laptop + + + + + + + + + + + + + + + + + + + Client + + + Server + + + + + + maintenance_work_mem (per connection) + + + work_mem (per query operation) + + + autovacuum_work_mem (per worker + + + process) + + + temp_buffer (per connection) + + + ... + + + + Individual Memory + + + + + + shared_buffers (heap and index) + + + wal_buffers (WAL records) + + + ... + + + + Shared Memory (per Cluster) + + + + + Postmaster process + + + + + + 1 + + + + + Backend processes (one per connection) + + + + + + + + 3 + + + + + + + Creates backend processes + + + + + + 2 + + + + + + + + WAL Writer + + + + + + Checkpointer + + + + + + + + Checkpoint + + + Record + + + + + Background Writer + + + + + + Archiver + + + + + + AutoVacuum + + + + + + Log Writer + + + + Stats Collector + + + + + Log + + + (text lines, + + + sequential) + + + + + + + Heap and + + + Index + + + (binary blocks, + + + random) + + + + + + + Read heap and index + + + pages and transfer + + + them to shared_buffers + + + + + + WAL + + + (binary records, + + + sequential) + + + + + + Archived + + + WAL + + + + + + + Via TCP/IP or socket + + + + RAM + + + PROCESSES + + + FILES + + diff --git a/doc/src/sgml/images/ram-proc-file-ink.svg b/doc/src/sgml/images/ram-proc-file-ink.svg new file mode 100644 index 0000000000..5f179c1688 --- /dev/null +++ b/doc/src/sgml/images/ram-proc-file-ink.svg @@ -0,0 +1,846 @@ + + + + + + image/svg+xml + + PG Overall Server Architecture + + + + + PG Overall Server Architecture + + + + + UML Note (200 x 20 px) + + + + UML Note (250 x 20 px) + + + + UML Note (100 x 35 px) + + + + UML Note (170 x 50 px) + + + + + UML State, big + + + + UML State, big + + + + + Disc + + + + + + + + + + + + Laptop + + + + + + + + + + + + + + + + + + + + + + + Client + Server + + + + + maintenance_work_mem (per connection) + work_mem (per query operation) + autovacuum_work_mem (per worker + process) + temp_buffer (per connection) + ... + + Individual Memory + + + + + shared_buffers (heap and index) + wal_buffers (WAL records) + ... + + Shared Memory (per Cluster) + + + + + Postmaster process + + + + + 1 + + + + + Backend processes (one per connection) + + + + + + + 3 + + + + + + Creates backend processes + + + + 2 + + + + + + + + + + WAL Writer + + + + + + + Checkpointer + + + + + + + Checkpoint + Record + + + + + Background Writer + + + + + + + Archiver + + + + + + + AutoVacuum + + + + + + + Log Writer + + + + + Stats Collector + + + + + + + Log + (text lines, + sequential) + + + + + Heap and + Index + (binary blocks, + random) + + + + + Read heap and index + pages and transfer + them to shared_buffers + + + + WAL + (binary records, + sequential) + + + + Archived + WAL + + + + + + Via TCP/IP or socket + + + + RAM + PROCESSES + FILES + + diff --git a/doc/src/sgml/images/ram-proc-file-raw.svg b/doc/src/sgml/images/ram-proc-file-raw.svg new file mode 100644 index 0000000000..ddb9ec6cb6 --- /dev/null +++ b/doc/src/sgml/images/ram-proc-file-raw.svg @@ -0,0 +1,302 @@ + + + + PG Overall Server Architecture + + + + + + + + UML Note (200 x 20 px) + + + + UML Note (250 x 20 px) + + + + UML Note (100 x 35 px) + + + + UML Note (170 x 50 px) + + + + + + UML State, big + + + + UML State, big + + + + + + Disc + + + + + + + + + Laptop + + + + + + + + + + + + + + + + + + + + + + + + + + + + Client + Server + + + + + + + maintenance_work_mem (per connection) + work_mem (per query operation) + autovacuum_work_mem (per worker + process) + temp_buffer (per connection) + ... + + Individual Memory + + + + + + shared_buffers (heap and index) + wal_buffers (WAL records) + ... + + Shared Memory (per Cluster) + + + + + + Postmaster process + + + + + 1 + + + + + + Backend processes (one per connection) + + + + + + + + 3 + + + + + + + Creates backend processes + + + + 2 + + + + + + + + + + + + WAL Writer + + + + + + + + Checkpointer + + + + + + + Checkpoint + Record + + + + + + Background Writer + + + + + + + + Archiver + + + + + + + + AutoVacuum + + + + + + + + Log Writer + + + + + + Stats Collector + + + + + + + + Log + (text lines, + sequential) + + + + + + Heap and + Index + (binary blocks, + random) + + + + + + Read heap and index + pages and transfer + them to shared_buffers + + + + + WAL + (binary records, + sequential) + + + + + Archived + WAL + + + + + + + Via TCP/IP or socket + + + + + RAM + PROCESSES + FILES + + + diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index e59cba7997..05d2862332 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -53,6 +53,7 @@ &start; + &architecture; &query; &advanced; @@ -277,6 +278,7 @@ &sourcerepo; &docguide; &limits; + &glossary; &acronyms;