Thread: postgres maintenance db
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. 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? Thank you Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-maintenance-db-tp5718134.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi, On 27 July 2012 08:07, hartrc <rhart2@mt.gov> wrote: > What is the purpose of the postgres database? I try and drop it and get > "maintenance database can't be dropped" error. 'postgres' database is something like 'mysql' database in MySQL. You should be able to see additional database like 'template0' and 'template1' > 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? You should create your own database (as many as you need) and create all schemas/tables/... there. See http://www.postgresql.org/docs/9.1/static/manage-ag-createdb.html -- you can use "CREATE DATABASE" or createdb command. > I didn't really want my database to be called postgres, can it be renamed? That's the system database let it be. -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
On Thu, 2012-07-26 at 15:07 -0700, 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? It's the default database for tools like createdb, createuser, createlang, dropdb, dropuser, etc. > I try and drop it and get > "maintenance database can't be dropped" error. > This is a pgAdmin message. It doesn't mean you can't drop it with the usual way (DROP DATABASE statement, or the dropdb tool), or with pgAdmin (but you first need to change the maintenance database of your server... BTW, the maintenance database is an expression specific to pgAdmin). > 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? Your choice :) > I didn't really want my database to be called postgres, can it be renamed? > Well, you can drop it or rename it. It will make your life quite hard with the usual tools (createdb and the like). It's way better to add a new database, and keep the postgres database. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
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