In this chapter, we explored the basic building blocks of PostgreSQL. There are several shared objects across the database cluster. These shared objects are roles, tablespaces, databases including template databases, template procedural languages, and some setting parameters. The tablespace is a defined storage used normally by the databases administrator for optimization or maintenance purposes.
The template1
database is cloned each time a database is created. It can be loaded with extensions that should be available for all new databases. The template0
database provides a fallback strategy in case the template1
database is corrupted. Also, it can be used if the template1
locale is not the required locale.
The role has several attributes such as login, superuser, createdb, and so on. The role is named a user
in the old PostgreSQL version if it can log in to the database, and a group if it cannot. Roles can be granted to other roles; this allows the database administrators to manage permissions easily.
PostgreSQL has more than two hundred settings that control the database behavior. These settings can have different contexts, namely, internal, postmaster, backend, user, superuser, and sighup. To have a quick look at these settings, one can use the view pg_settings
, which describes all the PostgreSQL settings.
The user database is the container for schemas, tables, views, functions, ranges, domain, sequences, and indexes. The database access permissions can be controlled via the create
, temporary
, and connect
access privileges. Several aspects of the database behavior can be controlled by the ALTER DATABASE
statement. The pg_database
catalog table describes all the databases in the PostgreSQL cluster.
The schema is used to organize objects within the database. By default, each database has a public schema.
PostgreSQL provides a rich set of data types, including numeric, text, and date/time data types. Choosing a data type is an important task; thus, one should balance between extensibility and storage consumption when choosing a data type.
One should be careful when performing operations on a mixture of different data types due to implicit conversion. For example, one should know how the system behaves when comparing text data type with the varchar data type. This is applied to time and date data types.
Tables are the major building blocks in PostgreSQL; they are used internally to implement views as well as sequences. A table can be categorized as temporary or permanent. In streaming replication, unlogged tables are not replicated to the slave nodes.
3.145.155.91