PostgreSQL Concepts: Difference between revisions
Line 13: | Line 13: | ||
The default port is 5432. | The default port is 5432. | ||
=Cluster= | =Cluster= |
Revision as of 20:47, 6 October 2023
External
Internal
Connection Types
Local Socket
This is the default type of connection, then the psql client is collocated with the database.
TCP
The default port is 5432.
Cluster
A collection of databases and global SQL objects, and their common static and dynamic metadata. A clusters is also referred to as instance. There is no relationship within the PostgreSQL cluster and the SQL term "CLUSTER".
Cluster operations:
Instance
An instance is a group of backend and auxiliary processes that communicate using a common shared memory area. There is one postmaster process that manages the instance. One instance manages exactly one database cluster with all its databases. Many instances can run on the same server machine as long as the TCP ports do not conflict. The instance handles all key features of a RDBMS: read and write access to files and shared memory, enforcement of ACID properties, connections to clients, privilege verification, crash recovery, replication, etc.
Schema
Each database has by default a public schema.
Tablespace
Authentication
User
Users are shared across databases.
The "user" concept is equivalent with the "role" concept. They mean the same thing.
Master User
An administrative user that exists when the RDBMS instance is created and that has privileges to create other database and other users. It is used to bootstrap the administration of the RDBMS instance, by defining all users, objects, and permissions in the databases of your DB instance. Master Username must start with a letter. The RDS documentation refers to it as "Master username".
Role
The "role" concept is equivalent with the "user" concept. They mean the same thing.
Role Attributes
A specific role may:
- be a superuser
- create another role
- create a database
Data Types
Identity
- http://www.postgresqltutorial.com/postgresql-identity-column/
- http://www.postgresqltutorial.com/postgresql-serial/
Timestamp
Numeric Types
SMALLINT
2 byte integer.
INT
4 byte integer. This is a typical choice for integers.
BIGINT
8 byte integer.
DECIMAL
NUMERIC
REAL
DOUBLE PRECISION
SERIAL
BIGSERIAL
TODO