codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>23.1. Overview</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="managing-databases.html" title="Chapter 23. Managing Databases" /><link rel="next" href="manage-ag-createdb.html" title="23.2. Creating a Database" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">23.1. Overview</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="managing-databases.html" title="Chapter 23. Managing Databases">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="managing-databases.html" title="Chapter 23. Managing Databases">Up</a></td><th width="60%" align="center">Chapter 23. Managing Databases</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="manage-ag-createdb.html" title="23.2. Creating a Database">Next</a></td></tr></table><hr /></div><div class="sect1" id="MANAGE-AG-OVERVIEW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">23.1. Overview <a href="#MANAGE-AG-OVERVIEW" class="id_link">#</a></h2></div></div></div><a id="id-1.6.10.4.2" class="indexterm"></a><p>3   A small number of objects, like role, database, and tablespace4   names, are defined at the cluster level and stored in the5   <code class="literal">pg_global</code> tablespace.  Inside the cluster are6   multiple databases, which are isolated from each other but can access7   cluster-level objects.  Inside each database are multiple schemas,8   which contain objects like tables and functions.  So the full hierarchy9   is: cluster, database, schema, table (or some other kind of object,10   such as a function).11  </p><p>12   When connecting to the database server, a client must specify the13   database name in its connection request.14   It is not possible to access more than one database per15   connection. However, clients can open multiple connections to16   the same database, or different databases.17   Database-level security has two components: access control18   (see <a class="xref" href="auth-pg-hba-conf.html" title="21.1. The pg_hba.conf File">Section 21.1</a>), managed at the19   connection level, and authorization control20   (see <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>), managed via the grant system.21   Foreign data wrappers (see <a class="xref" href="postgres-fdw.html" title="F.38. postgres_fdw — access data stored in external PostgreSQL servers">postgres_fdw</a>)22   allow for objects within one database to act as proxies for objects in23   other database or clusters.24   The older dblink module (see <a class="xref" href="dblink.html" title="F.12. dblink — connect to other PostgreSQL databases">dblink</a>) provides a similar capability.25   By default, all users can connect to all databases using all connection methods.26  </p><p>27   If one <span class="productname">PostgreSQL</span> server cluster is planned to contain28   unrelated projects or users that should be, for the most part, unaware29   of each other, it is recommended to put them into separate databases and30   adjust authorizations and access controls accordingly.31   If the projects or users are interrelated, and thus should be able to use32   each other's resources, they should be put in the same database but probably33   into separate schemas;  this provides a modular structure with namespace34   isolation and authorization control.35   More information about managing schemas is in <a class="xref" href="ddl-schemas.html" title="5.9. Schemas">Section 5.9</a>.36  </p><p>37   While multiple databases can be created within a single cluster, it is advised38   to consider carefully whether the benefits outweigh the risks and limitations.39   In particular, the impact that having a shared WAL (see <a class="xref" href="wal.html" title="Chapter 30. Reliability and the Write-Ahead Log">Chapter 30</a>)40   has on backup and recovery options. While individual databases in the cluster41   are isolated when considered from the user's perspective, they are closely bound42   from the database administrator's point-of-view.43  </p><p>44   Databases are created with the <code class="command">CREATE DATABASE</code> command45   (see <a class="xref" href="manage-ag-createdb.html" title="23.2. Creating a Database">Section 23.2</a>) and destroyed with the46   <code class="command">DROP DATABASE</code> command47   (see <a class="xref" href="manage-ag-dropdb.html" title="23.5. Destroying a Database">Section 23.5</a>).48   To determine the set of existing databases, examine the49   <code class="structname">pg_database</code> system catalog, for example50</p><pre class="synopsis">51SELECT datname FROM pg_database;52</pre><p>53   The <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> program's <code class="literal">\l</code> meta-command54   and <code class="option">-l</code> command-line option are also useful for listing the55   existing databases.56  </p><div class="note"><h3 class="title">Note</h3><p>57    The <acronym class="acronym">SQL</acronym> standard calls databases <span class="quote">“<span class="quote">catalogs</span>”</span>, but there58    is no difference in practice.59   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="managing-databases.html" title="Chapter 23. Managing Databases">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="managing-databases.html" title="Chapter 23. Managing Databases">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="manage-ag-createdb.html" title="23.2. Creating a Database">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 23. Managing Databases </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 23.2. Creating a Database</td></tr></table></div></body></html>