Slashdot Mirror

Being able to use SQL Server Management Studio makes up for any cost savings that PostgreSQL might deliver.

SSMS makes it trivial to manage and develop for SQL Server, and it makes the admins and devs extremely efficient.

There aren't any development tools for PostgreSQL that come close to being as capable and efficient as SSMS is.

pgAdmin is really half arsed compared to SSMS, in my experience.

pgAdmin III was a shitty wxWidgets-based app. Even something as simple as copy and pasting was all buggered the last time I had to use it, about a year ago.

pgAdmin 4 is even worse, basically being a web app that they've wrapped and bundled with a browser. Its UI is inconsistent, it's slow, and it I ran into all kinds of bugs when I last had to use it. I wanted to report the bugs, but their shitty bug tracking system forces you to sign up and log in before you can even see a list of bugs! No thanks, I don't have time to waste with idiocy like that.

There are some third party tools that support PostgreSQL, but they're usually quite costly, and none of them are as good as SSMS is, either.

It does no good to save a relatively small amount of money by using PostgreSQL, only to have much-costlier admins and devs slowed down thanks to PostgreSQL's terrible management and development tooling.

pgAdmin

A google search for postgres gui should return http://www.pgadmin.org/ on the first page. It seems largely equivalent to MySQL workbench (which I understand replaces the MySQL Admin gui / query browser?)

Check http://pgadmin.org/ that's 'almost official' PostgreSQL GUI.

And if you need a web-based GUI, use phpPgAdmin (http://phppgadmin.sourceforge.net).

pgAdmin3 is the way to go, it works really well and pretty much has a "just give me a damn database" button.

I started with PostgreSQL and had to use MySQL for some projects later. I find MySQL and its tools to be cumbersome, but that's likely just due to my familiarity with PostgreSQL.

Add in pgAdmin and Postgres is a snap to administer. It's just the initial security setup that takes some work and that's because it tries to be safe by default.

1. Download the pre-built binary package from enterprisedb.com
2. Make the changes to /etc/sysctl.conf suggested by the installer (increased shared memory)
3. Reboot
4. Run the Postgresql installer again, letting it do its thing
5. Set the admin user/password in the nice installer GUI
6. Download and install pgadmin3 from pgadmin.org
7. Fire up pgadmin3 and pop in the username/password specified during the Postgres install
8. Create my users, databases, tables, etc. as desired

If this is too complex for you, I humbly submit MySQL may be too tough as well.

pgAdmin III http://www.pgadmin.org/

I've heard the arguments that postgres is as easy as MySQL, and they're bullshit.

Lets see:

Postgres has no good GUI applications that can compare with MySQL's

Why you need more than one is beyond me. Isn't Pgadmin enough?

their command line application is just as good in its own way

Well, ok. Whatever 'in its own way' means.

and the market share that ensures you need to google multiple times to find the info you're looking for.

Postgres has some of the best documentation of any open source project I've seen. Sure, MySQL is good as well, but lets not spread bullshit here.

Installing postgres is also a nightmare compared to MySQL.

You mean in a download-the-msi-and-double-click-on-it way, or the apt-get-install-it way?

To sum up: free > $millions, easy > full-featured (in many circumstances).

Well, it's fully-featured, but not necessarily all of those features at the same time. Try doing full-text indexing on a database with foreign-keys on it in MySQL sometime.

I can't comment on the speed of the two first hand, though I've read very positive things about PostgreSQL's performance against MySQL with the MyISAM and InnoDB table types and especially so with very complex queries. I'm not putting MySQL down - I have extensive experience with it professionally, but for projects where I've had a choice (just home projects) I've gone with PostgreSQL. I can tell you that it is robust, more so than MySQL. And it is powerful. There are a few moments where if you're moving to it from MySQL you'll stop and go "huh?", e.g. good luck setting a column to autoincrement. ;) But don't let anything put you off learning it. It is a great database - very powerful and capabale. If you want shortcuts, look at pgAdmin which provides a nice GUI to it (but learn to do things properly).

I hope that helps. I use both databases and enjoy working with both. But it's PostgreSQL that I am most impressed by.

I don't see what this has over EXPLAIN and an appropriate graphical display tool like PgAdmin-III. There are large numbers of tools that display graphical query plans - and unlike this simple SQL parser, they know how the database will actually execute the query once the query optimiser is done with it.

Furthermore, a simple SQL parser has no idea about what indexes are present, available working memory for sorts and joins, etc. It can't know how the DB will really execute the query, without which it's hard to tell what performance issues may or may not arise.

See comment 24461217 for a more detailed explanation of why this whole idea makes very little sense.

Also, if you are going to try it out, please also have a look at the wonderful PgAdminIII. Brix will be shat, as they say... ;-)

this may work:

http://www.pgadmin.org/docs/1.6/slony/failover.html

http://slony.info/

This is one of those things that's painful only until you've crossed a certain threshold of information, then it never bothers you again. It's better now but still harder to put the pieces together than it should be.

Start with the documentation on creating a cluster: http://www.postgresql.org/docs/current/static/creating-cluster.html In 8.3 the default of using auth you mentioned has been removed, for the reasons you described. So it now runs as unsecured for local users by default and you have to worry about this yourself, which since it reduces the frustration at getting started was deemed an improvement.

That page suggests some options you can pass to initdb to adjust the default security level. Now you want to look at the initdb docs: http://www.postgresql.org/docs/current/static/app-initdb.html See that if you use -W/--pwprompt (same thing) you can assign a password to the database superuser at cluster creation time. If you do that, you can now change the default authentication scheme to password-based (-A md5 passed to initdb will do that), you'll be secure, and you'll have one user you can login as (postgres) to create more.

To see what other authentication methods are available and to learn what your options are look at http://www.postgresql.org/docs/current/static/client-authentication.html The one you really need to dive into is pg_hba.conf which is the magic text file to edit here. A new one of those will be put in the base directory of your new database cluster. Open that file up, look at the documentation, and you'll need to add a line to add network support like one those in the examples. Probably something like

host postgres all 192.168.12.0/24 md5

(allow access to anybody on the 192.168.12 subnet access the database with a password)

That should get you past the ugly initial hurdles. The next document you may need is how to add more users: http://www.postgresql.org/docs/current/static/sql-createrole.html

again look at the examples first and then backtrack to the parameters, will make more sense that way. After that you'll want to create more databases with createdb: http://www.postgresql.org/docs/current/static/app-createdb.html

And then you should be able to find your away around from there using the psql command line tool.

Note that once you get past accepting connections over the network, you could use a tool like pgAdmin III to handle the rest of this work using a slicker interface. There's even a copy of it bundled with the Windows installer you can use on such a client to administer a remote server running a real OS. It's of course possible to install pgAdmin manually on other platforms as well, see http://www.pgadmin.org/ for other verions (note that binary packages for platforms like RPM don't show up in every release, you have to go back to v1.8.0 to get the last full set of packages).

For anyone interested in an open source Navicat alternative, check out Yoursql.

For a Postgres GUI, check out pgAdmin (I've had some stability issues with this one, but there's not much else out there that's Free and Open.

http://pgadmin.org/ is the most used open source tool.

You mean like this or
webmin anyone? or
this if you want a non-web version

I looked at a couple of the popular ones, installed Awffull and played with it for a bit. But it wasn't immediately
obvious to me that any of the common ones supported aggregating stats across domains / hosts. Eg, I have 10 virtual servers on this
Apache box, give me a sorted list of hits per domain/host. Probably one or more of the popular open-source stats packages
*does* do this, but I didn't feel like spending hours examining different ones and installing them. Since my needs were very basic
I just wrote something of my own.

Since all my domains are ultimately served by a Java webapp running on JBoss (I redirect from Apache to JBoss with mod_jk) I just wrote a servlet filter to write hits to a postgresql database. That's it,one table with the hostname, date-time, user-agent, and a handful of other things I care about. Now, getting the info I need is a simple as a quick sql query with pgadmin III. Although I'm looking at using the Eclipse BIRT stuff for looking at the data, as my next project.

It's not pgAdmin is it? It has an OSX port, and I actually use it quite frequently. It's gotten to the point where it's pretty much as good on mac as on pc. Early versions were unstable and I actually ended up just using VNC / RDP to use pgAdmin on a windows box most of the time.

http://www.pgadmin.org/

I don't know exactly what you're looking for, but I use pgAdmin on windows, freebsd, and osx, and I think it's a pretty nice piece of software.

http://www.pgadmin.org/

PostgreSQL has it all...

Windows port: http://www.postgresql.org/ftp/binary/v8.1.3/win32/
GUI Tool: http://pgadmin.org/ (comes with the windows install)
Lots of documentation: http://www.postgresql.org/docs/8.1/interactive/ind ex.html
Books: http://www.postgresql.org/docs/books/

And don't repeat my mistakes, you should always mind scalability.

Vs pgAdminIII ?
Nice GUI admin tool. I like that much better than silly web applications.
beeing multiuser is just as easy in postgresql as mysql..

But if you for some unknown reason must have a web tool, there is phppgadmin

The Windows version of PostgreSQL 8.1 comes bundled with the newly released pgAdmin III 1.4 (2005-11-07).

pgAdmin III v1.4.0 contains a number of new features over previous releases, including:

Slony-I support.
An SQL/shell job scheduling agent.
PostgreSQL 8.1 support:
Autovacuum administration.
Roles.
Stored procedures (ie. Functions with OUT parameters).
Prepared transactions.
Allow server registration without connect.
Allow connection selection in the query tool.
Major source tree and architecture overhaul to allow further extensibility.
Unix domain socket support.
'Guru Hints' to help new users.
Password can be stored in .pgass/pgpass.conf files.
New graphics throughout.
Alert the user when a query finishes in the query tool if the window is not active.
Graphical EXPLAIN.
Support for Asynchronous Notifications in the query tool.

The complete changelog is at:
http://www.pgadmin.org/development.php#changelog

The URL for pgAdmin III would be http://www.pgadmin.org/ :-)

Debian: apt-get install postgresql (or use Synaptic)
Gentoo: emerge postgresql
Fedora: rpm -Uvh postgresql-8.0.3.i386.rpm (or select the "Database" package during install)
Windows: setup.exe

Easy administration from Windows, OS X, Linux, and BSD with PgAdmin.

Or were you talking about a manual install? Sure that's harder, but most of us don't do manual installs. Just those crazy Slackware folks and their ilk. ;-)

I haven't used Toad for a while as I've been using PostgreSQL more now and haven't used Oracle in a while. So I can't compare the current versions of Toad to other products. PGAdmin3, which installs if you install PostgreSQL on windows. Probably not as good as Toad but you can do most of the things you'd want to. Also tools like CaseStudio are good modelling tools that work with PostgreSQL. I'm still looking for a good open source data modelling tool that can reverse and forward engineer with PostgreSQL.

Goodbye Oracle, hello PostgreSQL. Now I can have a mostly SQL92 compliant database with ACID, transactions and now PITR and tablespaces that I can use on the server and on a win32 desktop.

For those of you wanting a great frontend, try PGAdmin3. It works on Win32 and Linux.

PGAdmin II. It is based on a mature, open source, cross-platform GUI toolkit and written in C++. And that toolkit does not have any restrictions on where you can compile for it or what you can port it to.

Try PGAdmin III.

pgadmin

Per a previous post, there is and has been one called pgAdmin. It may not be as pretty as the one just announced, but it's cross-platform and does a fairly good job IMNSHO.

Take a look

There's also a great free front end for Oracle but its name escapes me at the moment. It's from Sweden I think.

There are lots of client programs for PostgreSQL that run on Windows. Hell, even Access is one of them (though not recommended). My favorite is PgAdmin.

With PHPPgAdmin, any platform with a web browser can interface with PostgreSQL. I don't think client platforms would be a problem.

Now, if the server is running Windows, well... how about not running Windows on your database server. Really, why would you want Windows on your class' database server?

If your IT guys won't let you bring in a little Linux box for the class (and universities throw away computers every day that would suffice), you can always run the CygWin version. It's not recommended for serious tasks, but it will still be a hell of a lot more stable than Access.