Re: Running multiple databases PG vs MySQL

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.