postgresql中cluster、catalog、database、schema、table的区别_postgresql database、schema、catalog

For example, a company could have two different software development teams. One writes software to manage the warehouses while the other team builds software to manage sales and marketing. Each dev team has their own database, blissfully unaware of the other’s.

But the IT operations team took a decision to run both databases on a single computer box (Linux, Mac, whatever). So on that box they installed Postgres. So one database server (database cluster). In that cluster, they create two catalogs, a catalog for each dev team: one named 'warehouse' and one named 'sales'.

Each dev team uses many dozens of tables with different purposes and access roles. So each dev team organizes their tables into schemas. By coincidence, both dev teams do some tracking of accounting data, so each team happens to have a schema named 'accounting'. Using the same schema name is not a problem because the catalogs each have their own namespace so no collision.

Furthermore, each team eventually creates a table for accounting purposes named 'ledger'. Again, no naming collision.

You can think of this example as a hierarchy…

Each dev team's software makes a connection to the cluster. When doing so, they must specify which catalog (database) is theirs. Postgres requires that you connect to one catalog, but you are not limited to that catalog. That initial catalog is merely a default, used when your SQL statements omit the name of a catalog.

So if the dev team ever needs to access the other team's tables, they may do so if the database administrator has given them privileges to do so. Access is made with explicit naming in the pattern: catalog.schema.table. So if the 'warehouse' team needs to see the other team’s ('sales' team) ledger, they write SQL statements with sales.accounting.ledger. To access their own ledger, they merely write accounting.ledger. If they access both ledgers in the same piece of source code, they may choose to avoid confusion by including their own (optional) catalog name, warehouse.accounting.ledger versus sales.accounting.ledger.

---

By the way…

You may hear the word schema used in a more general sense, meaning the entire design of a particular database's table structure. By contrast, in the SQL Standard the word means specifically the particular layer in the Cluster > Catalog > Schema > Table hierarchy.

Postgres uses both the word database as well as catalog in various places such as the CREATE DATABASE command.

Not all database system provides this full hierarchy of Cluster > Catalog > Schema > Table. Some have only a single catalog (database). Some have no schema, just one set of tables. Postgres is an exceptionally powerful product.

原文：https://stackoverflow.com/questions/7022755/whats-the-difference-between-a-catalog-and-a-schema-in-a-relational-database
https://www.yiibai.com/html/postgresql/2013/080441.html