Thread: Management tool support and scalibility

Management tool support and scalibility

From
Kevin
Date:
At my old job, we used PostgreSQL exclusively.  I was doing programming,
and it was great.

At my new job I've had a chance to work with SQL Server and Oracle.  I
don't like either of them from a SQL point of view.  They just don't
compare (at least for what should be easy stuff, like dates).  However,
they did have a strong point where I see PostgreSQL lacking at the
moment.

(hint, if you read the subject, you've guessed it)

E.g., Oracle 8i had a very nice management console (and from the glimpse
of 9i I got, it's even better).  You could look at things like database
schemas via a tree view, database physical layouts, user information,
and connection information.  (I don't have it in front of me at the
moment, so I'm sure there is more.)  In particular I was very interested
in the ability to view not only what query a connection was doing, but
which operations it did that took a long time to process (full table
scans, etc), which one it is working on now, and how long that one will
take.  I know a while ago someone was working on a way to get similar
kinds of information by attaching to the backends via gdb or something
equally dangerous/hackish/error-prone.  When would such an ability be
put into the system itself?  (I believe Oracle does it through system
tables, which I would think might be good for PostgreSQL, as it would be
hard, and slow, to query each backend every time.)

The other ability that Oracle had that I was impressed with was the
ability to do partitioning.  You could break a database up into pieces
and put them on, say, different drives, files, whatever.  This seems
like a good idea, and one I don't believe PostgreSQL has now.  I suppose
if you wanted to put a single table on another drive, you could move it
and symlink it, but that sounds like another dirty hack.  The other
thing it could do was take a single table and partition it into separate
physical files based on ranges in a column.  This could be used for
archiving, for example.

I know that there exist some pretty nifty third party solutions for
distributed and/or replicated databases (as listed on freshmeat.net),
but having a separate program responsible for it seems like a bad idea
for maintenance.

By now you are probably saying 'If you want these features, why don't
you implement them?'.  Well, I really wouldn't know where to begin. 
I've been on this mailing list since last July with thoughts of working
on PostgreSQL, but more than anything it's convinced me that I wouldn't
know where to begin. ;{  I've purused the source and even read a few of
the interals documents, but I still don't think I would know what really
needs to be done.  (Not to mention that this isn't a short list of easy
features.)

What are all your thoughts on these items?  Is PostgreSQL not at a point
where it should be thinking of this stuff?  I know you're adding some
new features and tweaks to the engine still, but I think the above
features would make alot of people more interested in PostgreSQL.  Many
people still think that open source products are just not
user-friendly.  I think these features would go a long way towards that.

--Kevin


Re: Management tool support and scalibility

From
Peter Eisentraut
Date:
Kevin writes:

> E.g., Oracle 8i had a very nice management console (and from the glimpse
> of 9i I got, it's even better).  You could look at things like database
> schemas via a tree view, database physical layouts, user information,
> and connection information.  (I don't have it in front of me at the
> moment, so I'm sure there is more.)

Most of that information is now accessible via system views[1], so the
problem reduces mainly to writing a GUI for that.  In my mind, the problem
with writing such a GUI is that there isn't an easy choice of toolkit, and
most of us (active PostgreSQL developers) aren't well-versed in writing
GUIs.  Not that that's an excuse.

A point aside:  There's a MySQL GUI[2], which seems to be doing exactly
what you have in mind.

[1] http://developer.postgresql.org/docs/postgres/monitoring-stats.html
[2] http://www.mysql.com/downloads/gui-mysqlgui.html

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Management tool support and scalibility

From
Andrew McMillan
Date:
On Sun, 2002-02-03 at 18:25, Kevin wrote:
> 
> E.g., Oracle 8i had a very nice management console (and from the glimpse
> of 9i I got, it's even better).  You could look at things like database
> schemas via a tree view, database physical layouts, user information,
> and connection information.  (I don't have it in front of me at the
> moment, so I'm sure there is more.)  In particular I was very interested
> in the ability to view not only what query a connection was doing, but
> which operations it did that took a long time to process (full table
> scans, etc), which one it is working on now, and how long that one will
> take.  I know a while ago someone was working on a way to get similar
> kinds of information by attaching to the backends via gdb or something
> equally dangerous/hackish/error-prone.  When would such an ability be
> put into the system itself?  (I believe Oracle does it through system
> tables, which I would think might be good for PostgreSQL, as it would be
> hard, and slow, to query each backend every time.)

I believe that TOra is starting to have support for PostgreSQL now,
although I haven't managed to get it working for myself yet :-)

Some of the guys in our office use it for Oracle management and seem to
think pretty highly of it.  I know it supports MySQL  as well - can't
wait until the PostgreSQL support is fully available.

Regards,                Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267      Are you enrolled at
http://schoolreunions.co.nz/yet?
 



Re: Management tool support and scalibility

From
Oleg Bartunov
Date:
On 3 Feb 2002, Andrew McMillan wrote:

> On Sun, 2002-02-03 at 18:25, Kevin wrote:
> >
> > E.g., Oracle 8i had a very nice management console (and from the glimpse
> > of 9i I got, it's even better).  You could look at things like database
> > schemas via a tree view, database physical layouts, user information,
> > and connection information.  (I don't have it in front of me at the
> > moment, so I'm sure there is more.)  In particular I was very interested
> > in the ability to view not only what query a connection was doing, but
> > which operations it did that took a long time to process (full table
> > scans, etc), which one it is working on now, and how long that one will
> > take.  I know a while ago someone was working on a way to get similar
> > kinds of information by attaching to the backends via gdb or something
> > equally dangerous/hackish/error-prone.  When would such an ability be
> > put into the system itself?  (I believe Oracle does it through system
> > tables, which I would think might be good for PostgreSQL, as it would be
> > hard, and slow, to query each backend every time.)
>
> I believe that TOra is starting to have support for PostgreSQL now,
> although I haven't managed to get it working for myself yet :-)

does it require KDE/Gnome stuff ?

>
> Some of the guys in our office use it for Oracle management and seem to
> think pretty highly of it.  I know it supports MySQL  as well - can't
> wait until the PostgreSQL support is fully available.
>
> Regards,
>                     Andrew.
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



Re: Management tool support and scalibility

From
Andrew McMillan
Date:
On Sun, 2002-02-03 at 21:39, Oleg Bartunov wrote:
> > I believe that TOra is starting to have support for PostgreSQL now,
> > although I haven't managed to get it working for myself yet :-)
> 
> does it require KDE/Gnome stuff ?

Lib QT, but not any KDE, AFAIC tell.

Cheers,                Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267      Are you enrolled at
http://schoolreunions.co.nz/yet?
 



Re: Management tool support and scalibility

From
Michael Meskes
Date:
On Sun, Feb 03, 2002 at 09:27:02PM +1300, Andrew McMillan wrote:
> I believe that TOra is starting to have support for PostgreSQL now,

It does, through qt3.

> although I haven't managed to get it working for myself yet :-)

How about using Debian GNU/Linux? There's a tora package available. :-)

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!


Re: Management tool support and scalibility

From
Michael Meskes
Date:
On Sun, Feb 03, 2002 at 11:39:19AM +0300, Oleg Bartunov wrote:
> does it require KDE/Gnome stuff ?

Only QT3.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!


Re: Management tool support and scalibility

From
Dave Page
Date:
Try pgAdmin II (http://pgadmin.postgresql.org). It runs on Windows but has
plenty of features. It's main limitation is that it can only do things that
are possible via ODBC so you can't control the Postmaster or configure the
server with it (yet).

Regards, Dave.

> -----Original Message-----
> From: Kevin [mailto:TenToThe8th@yahoo.com] 
> Sent: 03 February 2002 05:25
> To: PGSQL Hackers
> Subject: [HACKERS] Management tool support and scalibility
> 
> 
> At my old job, we used PostgreSQL exclusively.  I was doing 
> programming, and it was great.
> 
> At my new job I've had a chance to work with SQL Server and 
> Oracle.  I don't like either of them from a SQL point of 
> view.  They just don't compare (at least for what should be 
> easy stuff, like dates).  However, they did have a strong 
> point where I see PostgreSQL lacking at the moment.
> 
> (hint, if you read the subject, you've guessed it)
> 
> E.g., Oracle 8i had a very nice management console (and from 
> the glimpse of 9i I got, it's even better).  You could look 
> at things like database schemas via a tree view, database 
> physical layouts, user information, and connection 
> information.  (I don't have it in front of me at the moment, 
> so I'm sure there is more.)  In particular I was very 
> interested in the ability to view not only what query a 
> connection was doing, but which operations it did that took a 
> long time to process (full table scans, etc), which one it is 
> working on now, and how long that one will take.  I know a 
> while ago someone was working on a way to get similar kinds 
> of information by attaching to the backends via gdb or 
> something equally dangerous/hackish/error-prone.  When would 
> such an ability be put into the system itself?  (I believe 
> Oracle does it through system tables, which I would think 
> might be good for PostgreSQL, as it would be hard, and slow, 
> to query each backend every time.)
> 
> The other ability that Oracle had that I was impressed with 
> was the ability to do partitioning.  You could break a 
> database up into pieces and put them on, say, different 
> drives, files, whatever.  This seems like a good idea, and 
> one I don't believe PostgreSQL has now.  I suppose if you 
> wanted to put a single table on another drive, you could move 
> it and symlink it, but that sounds like another dirty hack.  
> The other thing it could do was take a single table and 
> partition it into separate physical files based on ranges in 
> a column.  This could be used for archiving, for example.
> 
> I know that there exist some pretty nifty third party 
> solutions for distributed and/or replicated databases (as 
> listed on freshmeat.net), but having a separate program 
> responsible for it seems like a bad idea for maintenance.
> 
> By now you are probably saying 'If you want these features, 
> why don't you implement them?'.  Well, I really wouldn't know 
> where to begin. 
> I've been on this mailing list since last July with thoughts 
> of working on PostgreSQL, but more than anything it's 
> convinced me that I wouldn't know where to begin. ;{  I've 
> purused the source and even read a few of the interals 
> documents, but I still don't think I would know what really 
> needs to be done.  (Not to mention that this isn't a short 
> list of easy
> features.)
> 
> What are all your thoughts on these items?  Is PostgreSQL not 
> at a point where it should be thinking of this stuff?  I know 
> you're adding some new features and tweaks to the engine 
> still, but I think the above features would make alot of 
> people more interested in PostgreSQL.  Many people still 
> think that open source products are just not user-friendly.  
> I think these features would go a long way towards that.
> 
> --Kevin
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
> majordomo@postgresql.org
> 


Re: Management tool support and scalibility

From
Jean-Michel POURE
Date:
This email demonstrates how important GUIs are for database users. It seems 
like users are judging PostgreSQL on the ability to create, view and modify 
PostgreSQL objects.

Dave Page wrote a very nice GUI called pgAdmin II 
(http://padmin.postgresql.org).

It gives access to all PostgreSQL features. It is a must-have, especially if 
you want to use PostgreSQL 7.2 and its CREATE OR REPLACE FUNCTION which are 
supported.

I would also like to take the opportunity to point out (again) how important 
are on the to-do list : CREATE OR REPLACE VIEW, CREATE OR REPLACE TRIGGER. In 
addition, we would like to have a better CREATE TABLE AS with a choice of 
preserving/dropping linked objects (primary key, triggers, rules) and 
hopefully an ALTER TABLE ALTER COLUMN clause.

Why not concentrate on these very simple features before going further? This 
would bring a bunch of people from beginner tools (MySQL) as well as advanced 
ones (Oracle, MS SQL Server) to PostgreSQL.

Best regards,
Jean-Michel POURE


Re: Management tool support and scalibility

From
Alessio Bragadini
Date:
On Sun, 2002-02-03 at 11:57, Michael Meskes wrote:

> How about using Debian GNU/Linux? There's a tora package available. :-)

Tried, and it looks great, but I've only managed to use it with MySql,
since the Debian package libqt3-pgsql is not available. Anyone knows
why?

I will try to later to build the package myself or to use tora via ODBC.

-- 
Alessio F. Bragadini        alessio@albourne.com
APL Financial Services        http://village.albourne.com
Nicosia, Cyprus             phone: +357-22-755750

"It is more complicated than you think"    -- The Eighth Networking Truth from RFC 1925



Re: Management tool support and scalibility

From
kkennedy@kenzoid.com (Ken Kennedy)
Date:
On Tue, Feb 05, 2002 at 04:00:51PM +0200, Alessio Bragadini wrote:
> On Sun, 2002-02-03 at 11:57, Michael Meskes wrote:
>
> > How about using Debian GNU/Linux? There's a tora package available. :-)
>
> Tried, and it looks great, but I've only managed to use it with MySql,
> since the Debian package libqt3-pgsql is not available. Anyone knows
> why?

libqt3-psql is available...I think that's it. It's in non-us. I
haven't connected to a PG db with it yet, though.
> I will try to later to build the package myself or to use tora via ODBC.

Ken Kennedy    | http://www.kenzoid.com    | kenzoid@io.com

Re: Management tool support and scalibility

From
"Ross J. Reedstrom"
Date:
On Tue, Feb 05, 2002 at 04:00:51PM +0200, Alessio Bragadini wrote:
> On Sun, 2002-02-03 at 11:57, Michael Meskes wrote:
> 
> > How about using Debian GNU/Linux? There's a tora package available. :-)
> 
> Tried, and it looks great, but I've only managed to use it with MySql,
> since the Debian package libqt3-pgsql is not available. Anyone knows
> why?

Where did you guys find the Debian TOra package? I've hunted around and
can't seem to find it.

Ross


Re: Management tool support and scalibility

From
Michael Meskes
Date:
On Tue, Feb 05, 2002 at 12:04:28PM -0600, Ross J. Reedstrom wrote:
> > Tried, and it looks great, but I've only managed to use it with MySql,
> > since the Debian package libqt3-pgsql is not available. Anyone knows
> > why?

Sorry, I missed this one. libqt3-psql as all the other PostgreSQL stuff is
available only via non-US. Just look at
http://nonus.debian.org/debian/pool/non-US/main/libq/libqt3-psql/

> Where did you guys find the Debian TOra package? I've hunted around and
> can't seem to find it.

It's not in testing aka woody yet. You can only get it from unstable aka sid
under pool/main/t/tora.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!


Re: Management tool support and scalibility

From
Peter Eisentraut
Date:
Michael Meskes writes:

> Sorry, I missed this one. libqt3-psql as all the other PostgreSQL stuff is
> available only via non-US.

Why?

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Management tool support and scalibility

From
Andrew McMillan
Date:
On Wed, 2002-02-06 at 18:21, Peter Eisentraut wrote:
> Michael Meskes writes:
> 
> > Sorry, I missed this one. libqt3-psql as all the other PostgreSQL stuff is
> > available only via non-US.
> 
> Why?

It links against encryption libraries - US export regulations.

Cheers,            Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267      Are you enrolled at
http://schoolreunions.co.nz/yet?