Thread: Running multiple databases PG vs MySQL

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

On Sun, 28 Mar 2004 14:24:15 +0900 Alex <alex@meerkatsoft.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@postgresql.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.

Frank,
pls. apologize. Maybe my description was not so clear. What I was
referring to was the fact that under MySQL you have more freedom to give
individual users of a shared server rights to create and manage their
databases In addition all databases are kept in separate directories
unlike postgres.

As a server administrator, this make life simpler and you dont need to
worry about other users messing around outside their designated environment.

I guess one reason users compare MySQL with Postgres is that they see
the benefits of postgres and intend to switch or at least look into it,
but at the same time they dont want to compromise when it comes to ease
of use and administration.
MySQL is still the default database offered by any web hosting company
and if Postgres wants to become the designated db engine for these
services or become the worlds no.1 open source db then i think lots of
things need to be done. Take for example the admin interface (MySQL
Administrator) for MySQL which is done very professionally or the ease
of setting up Replication. Postgres still is quite far behind there and
for normal users that know MySQL best the transition is probably a too
big step and risk.

But then again, it might not be the aim of postgres to become that no1
open source db and part of every web hosting environment. Instead rather
to be an alternative for the serious databases for corporate use.

Might actually quite interesting to start a discussion on this topic
here :-)

Cheers
Alex


Frank Finner wrote:

>On Sun, 28 Mar 2004 14:24:15 +0900 Alex <alex@meerkatsoft.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@postgresql.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.
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>

On Mon, 29 Mar 2004, Alex wrote:

> Frank,
> pls. apologize. Maybe my description was not so clear. What I was
> referring to was the fact that under MySQL you have more freedom to give
> individual users of a shared server rights to create and manage their
> databases In addition all databases are kept in separate directories
> unlike postgres.

Huh?  Each database under PostgreSQL is kept under a seperate directory on
the server ... always has been that way ..

As to the ability to create/manage their own databases .. pls elaborate on
what issues you've had with this under PostgreSQL, as its a simple ALTER
command to provide a user with both CREATE USER and/or CREATE DATABASE
permisisons ...

> MySQL is still the default database offered by any web hosting company

No it isn't ... I can name three companies that offer PostgreSQL as a
primary database, and at least one of them only has MySQL as a means to do
migrations ... and those are just the ones that I know personally ...

> and if Postgres wants to become the designated db engine for these
> services or become the worlds no.1 open source db then i think lots of
> things need to be done. Take for example the admin interface (MySQL
> Administrator) for MySQL which is done very professionally or the ease

Please provide some examples .. I know any attempt I've made to do
adminstrative stuff under MySQL has ended in a headache, but I've been
using PostgreSQL for almost a decade now, so I find PostgreSQL the easier
of the two ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

> Huh?  Each database under PostgreSQL is kept under a seperate directory on
> the server ... always has been that way ..

Perhaps, but it isn't obvious which directory has which database.  I'm not
not sure which system catalogs provide that information, something that
wasn't obvious from the online docs, either.

> As to the ability to create/manage their own databases .. pls elaborate on
> what issues you've had with this under PostgreSQL, as its a simple ALTER
> command to provide a user with both CREATE USER and/or CREATE DATABASE
> permisisons ...

One of the big differences I see from the perspective of the DBA at an
ISP is that MySQL has better user/customer isolation.  This means
that customer A should not be able to learn ANYTHING about customer
B's database, not even that it exists.

The \l command should only list databases that the current user is
authorized for, the \du command should only list users authorized for
the current database (and perhaps only superusers should get even that
much information), etc.  Perhaps it is possible to set PG to do this,
but that should probably be the default.
--
Mike Nolan

On Sun, 28 Mar 2004, Mike Nolan wrote:

> > Huh?  Each database under PostgreSQL is kept under a seperate directory on
> > the server ... always has been that way ..
>
> Perhaps, but it isn't obvious which directory has which database.  I'm not
> not sure which system catalogs provide that information, something that
> wasn't obvious from the online docs, either.

SELECT oid FROM pg_database WHERE datname = '<database>';

or use the oid2name program that is in contrib ...

> The \l command should only list databases that the current user is
> authorized for, the \du command should only list users authorized for
> the current database (and perhaps only superusers should get even that
> much information), etc.  Perhaps it is possible to set PG to do this,
> but that should probably be the default.

Now, those are good points, and I agree ... only pg-superuse should be
able to see all databases ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

On Sun, 28 Mar 2004, Mike Nolan wrote:

> The \l command should only list databases that the current user is
> authorized for, the \du command should only list users authorized for
> the current database (and perhaps only superusers should get even that
> much information), etc.  Perhaps it is possible to set PG to do this,
> but that should probably be the default.

Just curious ... restricting \l itself isn't too difficult ... but how
does MySQL restrict the ability to do something like:

SELECT datname FROM pg_database;

or does it not have an equivalent to that?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 28 March 2004 06:06 pm, Alex wrote:
> Frank,
> pls. apologize. Maybe my description was not so clear. What I was
> referring to was the fact that under MySQL you have more freedom to give
> individual users of a shared server rights to create and manage their
> databases In addition all databases are kept in separate directories
> unlike postgres.
>
> As a server administrator, this make life simpler and you dont need to
> worry about other users messing around outside their designated
> environment.

Given, MySQL is "easier" to administrate - at least on the first glance. But
so is MS Access :-)
Personally I found the whole access rights table in MySQL a pain in the butt,
but maybe that's just me.
The point is, that MySQL is easier to set up in a shared webhosting
environment, however looking at quite some of the "professional" hosters, one
has full access to everyone's database, so it obviously isn't that easy after
all...

> I guess one reason users compare MySQL with Postgres is that they see
> the benefits of postgres and intend to switch or at least look into it,
> but at the same time they dont want to compromise when it comes to ease
> of use and administration.

Severly at the cost of data integrity.

> MySQL is still the default database offered by any web hosting company
> and if Postgres wants to become the designated db engine for these
> services or become the worlds no.1 open source db then i think lots of
> things need to be done. Take for example the admin interface (MySQL
> Administrator) for MySQL which is done very professionally or the ease
> of setting up Replication. Postgres still is quite far behind there and
> for normal users that know MySQL best the transition is probably a too
> big step and risk.

That MySQL is the "default" database for webhosters is most likely the same
reason why Access is the "default" database on Windows boxes: You only need
minimal knowledge to get it running - no matter how flawed the result.

> But then again, it might not be the aim of postgres to become that no1
> open source db and part of every web hosting environment. Instead rather
> to be an alternative for the serious databases for corporate use.

I sincerely hope not. PostgreSQL is THE free database that can reach the
production quality of the major databases (Oracle, DB2). The only remaining
feature it lacks out of the box is replication and some HA abilities the big
ones have. And for that it's really free as in beer and speech. Not a fake
type of hybrid license like MySQL.

> Might actually quite interesting to start a discussion on this topic
> here :-)

PLEASE: Could anyone set up a "MySQL versus PostgreSQL" mailing list that's
directly routed to /dev/null ? It's really boring to hear the same arguments
over and over again. If you love the "features" of MySQL - feel free to use
it.

>
> Cheers
> Alex
>
> Frank Finner wrote:
> >On Sun, 28 Mar 2004 14:24:15 +0900 Alex <alex@meerkatsoft.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@postgresql.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.
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faqs/FAQ.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

- --
    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAZ6b5jqGXBvRToM4RAi3aAKCXI8dfL4Kg+ZioKiXIcDc/SG6NXwCfR7kC
Fh8HCkRASFEhvN5RIp1irmU=
=iJCR
-----END PGP SIGNATURE-----

> > Perhaps, but it isn't obvious which directory has which database.  I'm not
> > not sure which system catalogs provide that information, something that
> > wasn't obvious from the online docs, either.
>
> SELECT oid FROM pg_database WHERE datname = '<database>';

Thanks.  That should be easier to find in the documentation, perhaps it
should be mentioned in the docs for the pg_database system catalog.

From an ISP's or DBA's point of view, it would be preferable if there was
a way to determine which directory held which database without having
to actually log into the database.  I can envision circumstances under
which postmaster might not be running when that information is needed.
--
Mike Nolan

> Just curious ... restricting \l itself isn't too difficult ... but how
> does MySQL restrict the ability to do something like:
>
> SELECT datname FROM pg_database;
>
> or does it not have an equivalent to that?

I'm not much of an expert in MySQL, but on my ISP 'show databases' only
shows MY databases.

I find MySQL's security tables arcane and confusing, but it may be that
I'm just more familiar with the way PG does it, because from the traffic
on the pgsql-general list it seems like questions about how to set up
the pg_hba.conf and pg_ident.conf are commonplace.

I also wonder how well the pg_hba.conf method will scale.  What happens
if there are hundreds of client databases or thousands of entries in
pg_hba.conf?
--
Mike Nolan

Alex wrote:
> MySQL is still the default database offered by any web hosting company
> and if Postgres wants to become the designated db engine for these
> services or become the worlds no.1 open source db then i think lots of
> things need to be done. Take for example the admin interface (MySQL
> Administrator) for MySQL which is done very professionally or the ease
> of setting up Replication. Postgres still is quite far behind there and
> for normal users that know MySQL best the transition is probably a too
> big step and risk.

But then again, real admin uses CLI :-)

Trust me, administering PG is not at all harder than MySQL, Apache,
Bind, Qmail, FB, etc. The only extra thing I need to do compared to
MySQL or FB is that I need to run VACUUM from time to time, but that's
so easy to do and autovacuum might be the way of the future anyway.

As to usage, PG is also very easy and convenient to use. Want some proofs?

1) PG's command line client is *much better* than MySQL's.

2) There are lots of webhosting provider offering PostgreSQL.

3) API/binding to virtually any language/environment (I don't think I've
seen .NET Data Provider or Parrot binding for MySQL);

4) LIMIT clause (with nicer syntax), autoincrementing column, easy
BLOB/TEXT, full-text search, replication, etc.? You got it.


But of course, if you're looking for other MySQL "conveniences" such as
silently chopping your string, silently converting your column data
type, allowing entering invalid values in your ENUM column, allowing
invalid dates, allowing breaking FK integrity, etc. then PG does not
have those. But I find them scary anyway :-)

--
dave

Hi david and PGSQL lovers,

I think that PGSQL devellopers and users must not be upset
by all the ongoing comparisons between MySQL and PostgreSQL.
This is the direct illustration of the postgresql success,
it gives me the feelings that its like a shameful desire
of MySQL users to come to a more "professional" DB.
It is a "demonstration" of the technical scale in DBMS
Access/MySQL/PostgreSQL (IMHO). Like for every domain in
the common life; if you want to make things better, you need
to do it "harder", but at the same time, depending of your needs,
before trying to do things better (migrating from MySQL to
PostgreSQL) you want to check if the investment is worthwhile.
'cause the last 10% oftently costs 90% of the effort.
So, don't turn the back to people trying to understand
the diff. and simil. between the DBMS, or the pro's and con's.
Explain (as you aleady does) that the 2 systems do not
fullfill the same needs and does not requires the same skill,
like the diff. between Access and MySQL.
Perhaps there is a page on the web which explain that?
I didn't found it, but may be somebody know one!

The probleme is also linked, as can be seen from the amount
of mail on general-psql list, to the fact that MySQL is available
natively on Window (and very easy to install there),
while PostgreSQL not yet, and a lot of people (me included)
are waiting this.

These 2 points explain the wealth of mail on general list and I really
understand that PostgreSQL  devellopers, programmers and exclusive users
could be upset by mail avalanche on this subject. So, apart creating
a mailing list for native window users, it could be of some interest to
create a "MySQL migration" list to clear general list. I must admit that
I am incline to unsuscribe, although it is a nice chanel to stay
informed and continue to learn from other users experiences,
but too much information kills information.

All the best to the postgresql team,
Pierre

PS: I hope that my english is understable, and that I didn't hurt
anybody with misunderstandings or mistakes.




David Garamond wrote:
> Alex wrote:
>
>> MySQL is still the default database offered by any web hosting company
>> and if Postgres wants to become the designated db engine for these
>> services or become the worlds no.1 open source db then i think lots of
>> things need to be done. Take for example the admin interface (MySQL
>> Administrator) for MySQL which is done very professionally or the ease
>> of setting up Replication. Postgres still is quite far behind there
>> and for normal users that know MySQL best the transition is probably a
>> too big step and risk.
>
>
> But then again, real admin uses CLI :-)
doing this only 24hours per day ;-)
>
> Trust me, administering PG is not at all harder than MySQL, Apache,
> Bind, Qmail, FB, etc. The only extra thing I need to do compared to
> MySQL or FB is that I need to run VACUUM from time to time, but that's
> so easy to do and autovacuum might be the way of the future anyway.
>
> As to usage, PG is also very easy and convenient to use. Want some proofs?
>
> 1) PG's command line client is *much better* than MySQL's.

Common basic users love window interface, and even more experienced ones
like them because that are (WI) more "self explanatory", give you an easy
way to use all the possibilities of commands, without having to remember
all the tiny tricks hiden everywhere in corners.

>
> 2) There are lots of webhosting provider offering PostgreSQL.
>
> 3) API/binding to virtually any language/environment (I don't think I've
> seen .NET Data Provider or Parrot binding for MySQL);
>
> 4) LIMIT clause (with nicer syntax), autoincrementing column, easy
> BLOB/TEXT, full-text search, replication, etc.? You got it.
>
>
> But of course, if you're looking for other MySQL "conveniences" such as
> silently chopping your string, silently converting your column data
> type, allowing entering invalid values in your ENUM column, allowing
> invalid dates, allowing breaking FK integrity, etc. then PG does not
> have those. But I find them scary anyway :-)
>
These are among the technical advantages of PostgreSQL,
and I experienced somes, introducing weird data in my tables ;-o

--
Pierre
--------------------------------------------------------------------------
DIDELON :@: pdidelon_at_cea.fr        Phone : 33 (0)1 69 08 58 89
CEA SACLAY - Service d'Astrophysique  91191 Gif-Sur-Yvette Cedex
--------------------------------------------------------------------------

On Sun, 28 Mar 2004, Mike Nolan wrote:

> I'm not much of an expert in MySQL, but on my ISP 'show databases' only
> shows MY databases.

Right, show databases == \l, I believe ... but, how is security on the
table(s) that 'show databases' dealt with ... can you access those
directly, by passing the "security" on 'show databases'?

> I also wonder how well the pg_hba.conf method will scale.  What happens
> if there are hundreds of client databases or thousands of entries in
> pg_hba.conf?

I'd be more concerned with how any server would scale to having thousands
of databases running on it myself ... but, right now, my largest server is
running ~165 databases, servicing 4 physical servers, and the server
itself is still highly responsive.  Note that the server that that
database is running on runs two other instances of PostgreSQL (7.2 and
7.4), 2 of MySQL (4.0 and 4.1), one of Firebird and about 25 other
"virtual servers" ... loadavg is generally <2 ...

Only time I've ever really had a problem with it was when we ran
Mnogosearch for the archives ... major dog on resources, since it wasn't
really designed for anything by MySQL ...

 ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

On Sun, 28 Mar 2004, Mike Nolan wrote:

> > > Perhaps, but it isn't obvious which directory has which database.  I'm not
> > > not sure which system catalogs provide that information, something that
> > > wasn't obvious from the online docs, either.
> >
> > SELECT oid FROM pg_database WHERE datname = '<database>';
>
> Thanks.  That should be easier to find in the documentation, perhaps it
> should be mentioned in the docs for the pg_database system catalog.
>
> From an ISP's or DBA's point of view, it would be preferable if there was
> a way to determine which directory held which database without having
> to actually log into the database.  I can envision circumstances under
> which postmaster might not be running when that information is needed.

Two valid points ... I've brought them up onto hackers to see about
getting both rectified ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Marc,
I guess we both agree that postgress is in no way as popular with web
hosting companies as MySql (even though you know of 3 such providers
which most likely at the same time also offer MySQL), and I believe with
good reasons as it is more complex than MySQL and most of the
administrators and support stuff are not no dbas either.

I am in no way lobbying for MySQL as I also like to work with Postgres
and have used it for a few years now. It not only saved me and my
clients a lot of money by opting for Postgres instead of Sybase or
Oracle but it is also fun to work with it.

The argument for one or the other probably has been discussed here for
the past 5 years and most likely both dbs will at one point cover the
same functionality or really become specialized for a particular market
segment.

Uwe in a mail mentioned his so called "fake type of hybrid license like
MySQL". I wonder if postgres isn't following the same (natural) path (of
the money).

A



Marc G. Fournier wrote:

>On Mon, 29 Mar 2004, Alex wrote:
>
>
>
>>Frank,
>>pls. apologize. Maybe my description was not so clear. What I was
>>referring to was the fact that under MySQL you have more freedom to give
>>individual users of a shared server rights to create and manage their
>>databases In addition all databases are kept in separate directories
>>unlike postgres.
>>
>>
>
>Huh?  Each database under PostgreSQL is kept under a seperate directory on
>the server ... always has been that way ..
>
>As to the ability to create/manage their own databases .. pls elaborate on
>what issues you've had with this under PostgreSQL, as its a simple ALTER
>command to provide a user with both CREATE USER and/or CREATE DATABASE
>permisisons ...
>
>
>
>>MySQL is still the default database offered by any web hosting company
>>
>>
>
>No it isn't ... I can name three companies that offer PostgreSQL as a
>primary database, and at least one of them only has MySQL as a means to do
>migrations ... and those are just the ones that I know personally ...
>
>
>
>>and if Postgres wants to become the designated db engine for these
>>services or become the worlds no.1 open source db then i think lots of
>>things need to be done. Take for example the admin interface (MySQL
>>Administrator) for MySQL which is done very professionally or the ease
>>
>>
>
>Please provide some examples .. I know any attempt I've made to do
>adminstrative stuff under MySQL has ended in a headache, but I've been
>using PostgreSQL for almost a decade now, so I find PostgreSQL the easier
>of the two ...
>
>
>----
>Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
>Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664
>
>
>
>

On Mon, 29 Mar 2004, Alex wrote:

> Uwe in a mail mentioned his so called "fake type of hybrid license like
> MySQL". I wonder if postgres isn't following the same (natural) path (of
> the money).

How so?  There are no financial requirements when using PostgreSQL, nor
will there ever be ... there are several *support* companies out there
that you can pay for support, but it isn't a requirement to use either ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Monday 29 March 2004 06:33 am, Marc G. Fournier wrote:
> On Mon, 29 Mar 2004, Alex wrote:
> > Uwe in a mail mentioned his so called "fake type of hybrid license like
> > MySQL". I wonder if postgres isn't following the same (natural) path (of
> > the money).
>
> How so?  There are no financial requirements when using PostgreSQL, nor
> will there ever be ... there are several *support* companies out there
> that you can pay for support, but it isn't a requirement to use either ...
>

The "fake" in MySQL is that, as discussed a thousand times, you can't use it
in any commercial project without buying a license. With MySQL you either use
GPL, or proprietary commercial licenses. Since this includes all client libs
a system like OpenOffice can offer MySQL support, StarOffice basically can't
since it's not under GPL.
I used the work "fake" here because it's pretty much like those "free checking
bank accounts". You have no idea when you will be charged a fee. Since the
legal side of when a license has to be bought for MySQL isn't really clear, I
decided against using or supporting MySQL. This dual policy of "unless it's
100% GPL what you're doing, buy a license" is very hard to follow. Where is
the line of 100% GPL ? Legally my lawyer thinks that MySQL AB could enforce
the "buy a license" if you write a closed source application in PHP. Usually
the GPL ends at the interpreter. However if you write the PHP app to require
MySQL, then you could be busted. Ok, nobody ever heard of someone who was
forced to buy a license for that - but if there is a lot of money in it,
companies suddenly turn around (see SCO vs. IBM and the rest of the world)

So I rather stick with a database that is not only technically superior, but
also guarantees that neither my company, nor any of our cutomers ever has to
pay for the database. They can elect to buy support from us or from any other
company offering PostgreSQL support. But they don't HAVE TO.

    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAaEpOjqGXBvRToM4RAmf0AKDB+FpjDyBjwY3hRDjFRtq5hnjZHgCgqnPP
/ec0r+cobpcltMPPIAtIz/g=
=eCfr
-----END PGP SIGNATURE-----

> I also wonder how well the pg_hba.conf method will scale.  What happens
> if there are hundreds of client databases or thousands of entries in
> pg_hba.conf?

Although I personally would like to see a pg_hba table instead of the
file, I would have to seariously question your implementation if you had
hundreds of databases on a single machine.

If you need separate data spaces for each customer but the application
uses the same schema, use namespaces within a single database.

Sincerely,

Joshua D. Drake



> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

On Mon, 29 Mar 2004, Joshua D. Drake wrote:

> If you need separate data spaces for each customer but the application
> uses the same schema, use namespaces within a single database.

What does that buy you that doing seperate databases doesn't?  Either way,
you have one connection per client, and each connection is still going to
open the same number of files to do their work ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

> > I also wonder how well the pg_hba.conf method will scale.  What happens
> > if there are hundreds of client databases or thousands of entries in
> > pg_hba.conf?
>
> Although I personally would like to see a pg_hba table instead of the
> file, I would have to seariously question your implementation if you had
> hundreds of databases on a single machine.

I know of an ISP who has a large number of customers (in excess of 400)
running similar small (probably under 100MB each) MySQL databases.  Since
I know each customer has access only to his own data, I assume it is
implemented using a different database for each customer.  Whether or not
it is on one or several machines is a detail I'm not sure of.

Without knowing much about how pg uses the pg_hba.conf file, I don't
know what problems porting that ISP to pg might raise, I only cite it
as an example of an extreme case that might not have been anticipated
and thus possibly an inherent limit in the pg_hba.conf method.

I think it is possible to discuss MySQL features in comparison to pg
without getting into an Annie Oakley/Frank Butler-style argument here.
--
Mike Nolan

On Mon, 29 Mar 2004, Joshua D. Drake wrote:

> > I also wonder how well the pg_hba.conf method will scale.  What happens
> > if there are hundreds of client databases or thousands of entries in
> > pg_hba.conf?
>
> Although I personally would like to see a pg_hba table instead of the
> file, I would have to seariously question your implementation if you had
> hundreds of databases on a single machine.
>
> If you need separate data spaces for each customer but the application
> uses the same schema, use namespaces within a single database.

since the purpose of the pg_hba.conf file is to ensure that you never
manage to lock yourself out of your database, might it make sense to have
a pg_hba table in each database that can be / will be / should be(???)
overidden by the pg_hba.conf file, thus ensuring you never get locked out,
but allowing the vast majority of connection configuration to be handled
by tables, with the pg_hba.conf as an emergency procedure used to get the
warp engines online in case some drunken ensign starts singing "I'll take
you home Kathleen" and shuts them down. (i.e. "delete from pg_hba" or
something like it.)???

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> since the purpose of the pg_hba.conf file is to ensure that you never
> manage to lock yourself out of your database, might it make sense to have
> a pg_hba table in each database that can be / will be / should be(???)
> overidden by the pg_hba.conf file,

I don't think we want user authentication driven off of actual tables.
That would mean paying *all* the costs of backend launch before we could
reject an invalid connection request.

It might be possible to do something with a flat file as an intermediary
between the postmaster and the tables that are the master data.  We
already do this for pg_shadow passwords, and I've been thinking of
proposing that we add a flat file for the database name -> OID mapping
so we could get rid of the horrid hack that is GetRawDatabaseInfo().
Per-database flat files would be a bit messy though.

            regards, tom lane

On Mon, 29 Mar 2004, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > since the purpose of the pg_hba.conf file is to ensure that you never
> > manage to lock yourself out of your database, might it make sense to have
> > a pg_hba table in each database that can be / will be / should be(???)
> > overidden by the pg_hba.conf file,
>
> I don't think we want user authentication driven off of actual tables.
> That would mean paying *all* the costs of backend launch before we could
> reject an invalid connection request.
>
> It might be possible to do something with a flat file as an intermediary
> between the postmaster and the tables that are the master data.  We
> already do this for pg_shadow passwords, and I've been thinking of
> proposing that we add a flat file for the database name -> OID mapping
> so we could get rid of the horrid hack that is GetRawDatabaseInfo().
> Per-database flat files would be a bit messy though.

Actually, I had thought of pg_hba as being a global table, not a per
database one.  That would mean only one flat file, wouldn't it?

And while we're at it, maybe we should have a setting somewhere should
someone execute the famous "update pg_shadow set usesuper = false" that
someone did a while back to be able to force an account to be a superuser
account.  In postgresql.conf or something like it.  While it's another
problem, it falls under the same "keeping people from locking themselves
out" thread.

> since the purpose of the pg_hba.conf file is to ensure that you never
> manage to lock yourself out of your database, might it make sense to have
> a pg_hba table in each database that can be / will be / should be(???)
> overidden by the pg_hba.conf file, thus ensuring you never get locked out,
> but allowing the vast majority of connection configuration to be handled
> by tables, with the pg_hba.conf as an emergency procedure used to get the
> warp engines online in case some drunken ensign starts singing "I'll take
> you home Kathleen" and shuts them down. (i.e. "delete from pg_hba" or
> something like it.)???

How about some kind of 'include table pg_hba' statement in the
pg_hba.conf file?

Anything prior to that could not be overridden by entries in the pg_hba
table, and the absence of an include statement means that only the file
entries are used, preserving the current behavior.
--
Mike Nolan

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> And while we're at it, maybe we should have a setting somewhere should
> someone execute the famous "update pg_shadow set usesuper = false" that
> someone did a while back to be able to force an account to be a superuser
> account.

We already have an adequate solution for that one: shut down the
postmaster and run a standalone backend.  You are always superuser in
a standalone backend, so you can create a new superuser or just reverse
the UPDATE command.

            regards, tom lane

Alex wrote:

> MySQL is still the default database offered by any web hosting company
> and if Postgres wants to become the designated db engine for these
> services or become the worlds no.1 open source db then i think lots of
> things need to be done. Take for example the admin interface (MySQL
> Administrator) for MySQL which is done very professionally or the ease
> of setting up Replication. Postgres still is quite far behind there and
> for normal users that know MySQL best the transition is probably a too
> big step and risk.

How easy is it to setup a reliable replication with failover, switchover
and especially switchback? I have never done that in MySQL, but you seem
to know quite a bit about it. If I have one master and 3 slaves, and I
need to shutdown the master for maintenance purposes, what do I have to
do to switch over to one of the slaves, let the other two slaves
replicate against that new master, and when I'm done and the original
master could take over again, what's the procedure to get it back into
the master role? Keep in mind that our databases are quite a few
gigabytes in size and that longer interruption of accessibility is not
acceptable (that's what we do that replication stuff for in the first
place).

If you don't know the answers to that, I assume it isn't that easy as
people try to make believe. And in case the answer is "that is not
possible but ...", then you better think again what you want that
replication setup for.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On Mon, 29 Mar 2004, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > And while we're at it, maybe we should have a setting somewhere should
> > someone execute the famous "update pg_shadow set usesuper = false" that
> > someone did a while back to be able to force an account to be a superuser
> > account.
>
> We already have an adequate solution for that one: shut down the
> postmaster and run a standalone backend.  You are always superuser in
> a standalone backend, so you can create a new superuser or just reverse
> the UPDATE command.

Ahhh.  Good point.  Any chance of having the same behaviour for pg_hba as
a table?  I.e. you accidentally remove all connectability and you could
restore it to a pg_hba table?  Does that even make sense?  I'm not sure.

On Mon, 29 Mar 2004, Tom Lane wrote:

> It might be possible to do something with a flat file as an intermediary
> between the postmaster and the tables that are the master data.  We
> already do this for pg_shadow passwords, and I've been thinking of
> proposing that we add a flat file for the database name -> OID mapping
> so we could get rid of the horrid hack that is GetRawDatabaseInfo().
> Per-database flat files would be a bit messy though.

The ability to manipulate pg_hba via interfaces stuck as phpPgAdmin and
PgAdmin would definitely be a plus, even if it just results in something
like pg_shadow ... in an ISP environment, pg_hba is about the only
'hassle' that I think really exists ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

On Mon, 29 Mar 2004, Mike Nolan wrote:

> I know of an ISP who has a large number of customers (in excess of 400)
> running similar small (probably under 100MB each) MySQL databases.  Since
> I know each customer has access only to his own data, I assume it is
> implemented using a different database for each customer.  Whether or not
> it is on one or several machines is a detail I'm not sure of.

Note that we are actually talking about two different things here ... I
have a server with 165 databases running on it for clients ... no client
can access another clients database, as access is restricted to a user
created specifically for the client that owns the database, as well as the
IP that they will be connecting from ...

Now, that doesn't preclude clients from seeing the names of another
clients database using \l, but unless there is gross mis-management of the
pg_hba.conf, seeing the names of other databases doesn't give other
clients any benefits ...

> Without knowing much about how pg uses the pg_hba.conf file, I don't
> know what problems porting that ISP to pg might raise, I only cite it as
> an example of an extreme case that might not have been anticipated and
> thus possibly an inherent limit in the pg_hba.conf method.

To be honest, I can't see much in the way of issues with migrating the
above scenario from MySQL -> PostgreSQL ... other then the obvious
migration of table structures and data, but there are more then enough
scripts out here for mysql2pg conversion ...

> I think it is possible to discuss MySQL features in comparison to pg
> without getting into an Annie Oakley/Frank Butler-style argument here.

Agreed, else I wouldn't have jump'd in ... you've brought up a couple of
points that I've raised on the -hackers list ... but, I don't consider
them to be 'high priority' issues, mainly because there is no security
reasons to adopt a 'hide it all' policy similar to MySQL ...

In fact, I'd almost go to the extent of saying that MySQL model of hiding
things would result in a slightly more lazy admin maintaining the server,
since they would be relying more on MySQL to provide security for them,
instead of them providing it themselves ... we (PgSQL) tend to be more
aware of our servers *because* we have to think about whether we've setup
the security properly ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

> Now, that doesn't preclude clients from seeing the names of another
> clients database using \l, but unless there is gross mis-management of the
> pg_hba.conf, seeing the names of other databases doesn't give other
> clients any benefits ...

That rather depends upon what those clients are doing, doesn't it?

I can see benefits from being able to completely isolate one client/database
from another,  even to the point of not giving them any hints that they're
sharing the same database server.  (Depending on how fanatical I am about
it, there are other solutions, such as separate instances or completely
separate physical systems, but those present a different set of
administrative issues.)

It may be more of a marketing issue than a technical one.  If we want
increased commercial acceptance, that may be one of the higher priority
features from an ISP's (or his clients') point of view, if not from ours.
--
Mike Nolan

On Mon, 29 Mar 2004, Mike Nolan wrote:

> > Now, that doesn't preclude clients from seeing the names of another
> > clients database using \l, but unless there is gross mis-management of the
> > pg_hba.conf, seeing the names of other databases doesn't give other
> > clients any benefits ...
>
> That rather depends upon what those clients are doing, doesn't it?

I don't know, does it?  I can't think of any circumstance that this would
give a client any advantage ... can you?

> It may be more of a marketing issue than a technical one.  If we want
> increased commercial acceptance, that may be one of the higher priority
> features from an ISP's (or his clients') point of view, if not from
> ours.

Coming from the ISP side, I can't say that, in the 8 or so years I've been
providing PostgreSQL, I've ever had anyone voice a concern about being
able to see other clients databases ... most of my clients don't even see
the database, as they are using such thinks as OpenACS (they plug in the
connection details, and OpenACS loads the schema/data for them) or
phpPgAdmin (which has an option to hide databases not owned by them) ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

On Mar 29, 2004, at 7:44 PM, Marc G. Fournier wrote:

> On Mon, 29 Mar 2004, Mike Nolan wrote:
>
>>> Now, that doesn't preclude clients from seeing the names of another
>>> clients database using \l, but unless there is gross mis-management
>>> of the
>>> pg_hba.conf, seeing the names of other databases doesn't give other
>>> clients any benefits ...
>>
>> That rather depends upon what those clients are doing, doesn't it?
>
> I don't know, does it?  I can't think of any circumstance that this
> would
> give a client any advantage ... can you?

Depends what you name the databases, honestly. Never underestimate the
power of the belief in
confidentiality (paranoia to the rest of us) inherent in certain
industries - actual advantage
gained means nothing compared to the perception thereof. I do much work
in the financial and
environmental industries - you can't spit without offending a lawyer
(particularly in the environmental
space. The lawyers are the only ones who make any money...).


--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com

Gregory Wood wrote:
> Jan Wieck wrote:
>> If you don't know the answers to that, I assume it isn't that easy as
>> people try to make believe. And in case the answer is "that is not
>> possible but ...", then you better think again what you want that
>> replication setup for.
>
> Although I agree with your points (especially having set up
> contrib/dbmirror in the past, and anticipating Slony all the more
> because of that experience), I would like to point out that
> failover/high-availability is not the only reason to set up replication.
> Lots of people just want the ability to load balance between several
> servers.

Exactly. And as you have correctly read between the lines, my real point
is that people are looking at replication from different points of view
and that everyone sees a different benefit from it, or a different set
of problems solved by it. And because of that, a statement like "the
ease of setting up replication" is a marketing phrase that has no value.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan Wieck wrote:
> If you don't know the answers to that, I assume it isn't that easy as
> people try to make believe. And in case the answer is "that is not
> possible but ...", then you better think again what you want that
> replication setup for.

Although I agree with your points (especially having set up
contrib/dbmirror in the past, and anticipating Slony all the more
because of that experience), I would like to point out that
failover/high-availability is not the only reason to set up replication.
Lots of people just want the ability to load balance between several
servers.

Personally, I'd want the failover capabilities as well, just as I'd much
rather run RAID 0+1/10 or RAID 5 than RAID 0. That's not to say that
there aren't people that only care about speed through parallelism
though. In which case, your scenario isn't necessarily valid, with
regards to ease of setup/maintenence of replication.

Greg

At 06:16 PM 3/29/2004 -0600, Mike Nolan wrote:

> > Now, that doesn't preclude clients from seeing the names of another
> > clients database using \l, but unless there is gross mis-management of the
> > pg_hba.conf, seeing the names of other databases doesn't give other
> > clients any benefits ...
>
>That rather depends upon what those clients are doing, doesn't it?
>
>I can see benefits from being able to completely isolate one client/database
>from another,  even to the point of not giving them any hints that they're
>sharing the same database server.  (Depending on how fanatical I am about
>it, there are other solutions, such as separate instances or completely
>separate physical systems, but those present a different set of
>administrative issues.)

It would just be better to run separate postgres instances.

The resources used may be higher, but the isolation is better.

If RAM was cheaper I'd give each customer their own virtual machine.

Regards,
Link.

Is this a TODO here, perhaps dumping authentication from tables?

---------------------------------------------------------------------------

Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > since the purpose of the pg_hba.conf file is to ensure that you never
> > manage to lock yourself out of your database, might it make sense to have
> > a pg_hba table in each database that can be / will be / should be(???)
> > overidden by the pg_hba.conf file,
>
> I don't think we want user authentication driven off of actual tables.
> That would mean paying *all* the costs of backend launch before we could
> reject an invalid connection request.
>
> It might be possible to do something with a flat file as an intermediary
> between the postmaster and the tables that are the master data.  We
> already do this for pg_shadow passwords, and I've been thinking of
> proposing that we add a flat file for the database name -> OID mapping
> so we could get rid of the horrid hack that is GetRawDatabaseInfo().
> Per-database flat files would be a bit messy though.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073