Skip site navigation (1) Skip section navigation (2)

Re: Running multiple databases PG vs MySQL

From: Frank Finner <postgresql(at)finner(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Running multiple databases PG vs MySQL
Date: 2004-03-28 07:14:50
Message-ID: 20040328091451.1c569088.postgresql@finner.de (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, 28 Mar 2004 14:24:15 +0900 Alex <alex(at)meerkatsoft(dot)com> sat down, thought long and then
wrote:

> Hi,
> what is the recommended way to run multiple databases under postgres.
> 
> In MySQL it is rather simple to give different users or websites their 
> own database with all the access rights.
> 
> Any suggestion or links to documents are highly appreciated.
> 
> Alex
> 
> 

If you call "createdb -?" within a shell you will get the following:

---
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --location=PATH       alternative place to store the database
  -E, --encoding=ENCODING   encoding for the database
  -O, --owner=OWNER         database user to own the new database
  -T, --template=TEMPLATE   template database to copy
  -e, --echo                show the commands being sent to the server
  -q, --quiet               don't write any messages
  --help                    show this help, then exit
  --version                 output version information, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -W, --password            prompt for password

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs(at)postgresql(dot)org>.
---

So, to create a database from shell, you have to call 
"createdb -O <valid database user> <databasename>". You should do this as valid database superuser
who may add databases!

Another way is to connect to template1 as the future owner:
"psql template1 <valid database user>"
and create the database with "CREATE DATABASE <databasename>...". Every valid user may connect to
template1. You don´t need to use psql, this works for example also with PgAdmin, it´s even simpler
because you don´t need to remember the syntax, just click around.

Of course, the <valid database user> must be enabled to create databases, therefore it must have
been created either by calling "createuser -d <valid database user> ..." or with an appropriate SQL
command "CREATE USER <valid database user> ... CREATEDB" by a database superuser, or again with a
tool like PgAdmin.

BTW: Why do so many people comparisons with MySQL syntax during the last days? "MySQL can do this,
in MySQL I can do this that way" and so on. Next time I´ d like to read something like "In DB2 I can
simply add a database by whatever." or "With MS-SQL-Server you just have to do the following 32
steps to create a backup.". :-)

Who cares about how something works in MySQL? They are NOT the providers of a standard everybody has
to use!

Regards, Frank.



In response to

Responses

pgsql-general by date

Next:From: David GaramondDate: 2004-03-28 13:08:11
Subject: Re: win32 users list (Re: Native Win32 port - PLEASE!)
Previous:From: AlexDate: 2004-03-28 05:24:15
Subject: Running multiple databases PG vs MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group