<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Managing Databases</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Server Administration" HREF="admin.html"><LINK REL="PREVIOUS" TITLE="Functions and Triggers" HREF="perm-functions.html"><LINK REL="NEXT" TITLE="Creating a Database" HREF="manage-ag-createdb.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="CHAPTER" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="perm-functions.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="user-manag.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="client-authentication.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="manage-ag-createdb.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="CHAPTER" ><H1 ><A NAME="MANAGING-DATABASES" ></A >Chapter 18. Managing Databases</H1 ><DIV CLASS="TOC" ><DL ><DT ><B >Table of Contents</B ></DT ><DT >18.1. <A HREF="managing-databases.html#MANAGE-AG-OVERVIEW" >Overview</A ></DT ><DT >18.2. <A HREF="manage-ag-createdb.html" >Creating a Database</A ></DT ><DT >18.3. <A HREF="manage-ag-templatedbs.html" >Template Databases</A ></DT ><DT >18.4. <A HREF="manage-ag-config.html" >Database Configuration</A ></DT ><DT >18.5. <A HREF="manage-ag-dropdb.html" >Destroying a Database</A ></DT ><DT >18.6. <A HREF="manage-ag-tablespaces.html" >Tablespaces</A ></DT ></DL ></DIV ><A NAME="AEN19637" ></A ><P > Every instance of a running <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > objects (<SPAN CLASS="QUOTE" >"database objects"</SPAN >). This chapter describes the properties of databases, and how to create, manage, and destroy them. </P ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="MANAGE-AG-OVERVIEW" >18.1. Overview</A ></H1 ><A NAME="AEN19645" ></A ><P > A database is a named collection of <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > objects (<SPAN CLASS="QUOTE" >"database objects"</SPAN >). Generally, every database object (tables, functions, etc.) belongs to one and only one database. (But there are a few system catalogs, for example <TT CLASS="LITERAL" >pg_database</TT >, that belong to a whole cluster and are accessible from each database within the cluster.) More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function). </P ><P > When connecting to the database server, a client must specify in its connection request the name of the database it wants to connect to. It is not possible to access more than one database per connection. (But an application is not restricted in the number of connections it opens to the same or other databases.) Databases are physically separated and access control is managed at the connection level. If one <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > server instance is to house projects or users that should be separate and for the most part unaware of each other, it is therefore recommendable to put them into separate databases. If the projects or users are interrelated and should be able to use each other's resources they should be put in the same database, but possibly into separate schemas. Schemas are a purely logical structure and who can access what is managed by the privilege system. More information about managing schemas is in <A HREF="ddl-schemas.html" >Section 5.8</A >. </P ><P > Databases are created with the <TT CLASS="COMMAND" >CREATE DATABASE</TT > command (see <A HREF="manage-ag-createdb.html" >Section 18.2</A >) and destroyed with the <TT CLASS="COMMAND" >DROP DATABASE</TT > command (see <A HREF="manage-ag-dropdb.html" >Section 18.5</A >). To determine the set of existing databases, examine the <TT CLASS="STRUCTNAME" >pg_database</TT > system catalog, for example </P><PRE CLASS="SYNOPSIS" >SELECT datname FROM pg_database;</PRE ><P> The <A HREF="app-psql.html" ><SPAN CLASS="APPLICATION" >psql</SPAN ></A > program's <TT CLASS="LITERAL" >\l</TT > meta-command and <TT CLASS="OPTION" >-l</TT > command-line option are also useful for listing the existing databases. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > The <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > standard calls databases <SPAN CLASS="QUOTE" >"catalogs"</SPAN >, but there is no difference in practice. </P ></BLOCKQUOTE ></DIV ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="perm-functions.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="manage-ag-createdb.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Functions and Triggers</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="admin.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Creating a Database</TD ></TR ></TABLE ></DIV ></BODY ></HTML >