Re: postgres maintenance db

On 07/26/2012 03:07 PM, hartrc wrote:
> I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11.
> After successful installation I by default have one database installed
> called postgres.
>
> I'm starting the process of migrating some database schemas off Oracle and
> mysql onto postgres but I want to understand how to best set up the
> "databases".
>
> What is the purpose of the postgres database? I try and drop it and get
> "maintenance database can't be dropped" error.
Start here:
http://www.postgresql.org/docs/9.1/interactive/manage-ag-createdb.html
>
> Should I create a separate database that has all my application schemas in
> it and let the postgres database be stand-alone, or should I put my
> application schemas inside the postgres database?
> I didn't really want my database to be called postgres, can it be renamed?
>
Ignore postgres, template0 and template1 "system" databases. Create your
user-database(s) with whatever name(s) you wish.

As to how to the proper way to migrate, that depends on what you are
trying to achieve. Are these databases that you are migrating separate
standalone databases being migrated to one machine, do queries need to
reference tables on the different databases (i.e. are you merging
various databases in the process), etc.?

It helps to have an overview.

In PostgreSQL a database "cluster" is a collection of separate named
databases. A cluster is managed by one master process regardless of the
number of databases it contains. A cluster reads a single
postgresql.conf file for configuration. User and group information is
shared across the entire cluster. That is, there is only one user
"steve" in the cluster so "steve" is the same user in any database
created in the cluster so while steve may or may not have permission to
access certain databases, tables, etc., you cannot have a different user
steve in database1 than in database2. And a cluster listens on the
assigned address(es) and port(s).

One host can have multiple clusters running each with its own
configuration, ports, addresses and storage area.

One cluster can contain many databases.

Each database has one or more schemas (by default all new databases have
a schema called "public"). Schema, in this context, is more of a
namespace and should not be confused with "schema" in the sense of the
layout of your database tables and references. See:
http://www.postgresql.org/docs/9.1/static/ddl-schemas.html .

Things like foreign keys require tables be within the same database
(though the tables can be in different schemas).

Queries can join data from different databases, or even different
clusters, but that requires use of SQL-MED and/or some contrib modules
and can introduce a host of performance, isolation and other issues.

Hope this helps.

Cheers,
Steve