On 07/26/2012 03:07 PM, hartrc wrote:
Start here: http://www.postgresql.org/docs/9.1/interactive/manage-ag-createdb.htmlI'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.
Ignore postgres, template0 and template1 "system" databases. Create your user-database(s) with whatever name(s) you wish.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?
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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[Postgresql Jobs] [Postgresql Admin] [Postgresql Performance] [Linux Clusters] [PHP Home] [PHP on Windows] [Programming PHP] [Kernel Newbies] [PHP Classes] [Find Someone Nice] [PHP Books] [PHP Databases] [Postgresql & PHP] [Yosemite]