Showing posts with label Postgre. Show all posts

Sunday, January 16, 2011

Community Guide to PostgreSQL GUI Tools

Alternate Guides

Much of this page is somewhat out of date at this point. A more current and detailed summary is available at Database Administration, Reporting, and Light application development

Open Source / Free Software

pgAdmin III

http://www.pgadmin.org/
MS Windows, GNU/Linux, FreeBSD, Mac OS X, OpenBSD, Solaris
Admin
pgAdmin III is THE Open Source management tool for your PostgreSQL databases. Features full Unicode support, fast, multithreaded query and data editting tools and support for all PostgreSQL object types.
pgAdmin III is bundled with the Windows installer, and you can use that such a client to administer a remote server on another OS. Note that binary packages for platforms like RPM don't show up in every point release, you currently have to go back to v1.8.0 to get the last full set of packages.
Free Administration Centre for the PostgreSQL database. Includes a graphical administration interface, an SQL query tool, a procedural code editor and much more. pgAdmin III is designed to answer the needs of most users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. Available in more than 30 languages and for several operating systems. (2005-04-19)

PGAccess

http://www.pgaccess.org
Most platforms
ERD
The original PostgreSQL GUI, with an MS Access-style database browser, written in Tcl/Tk. Allows browsing, adding and editing tables, views, functions, sequencese, databases, and users, as well as graphically-assisted queries. A form and report designer are also under development by the PGAccess team, who could use help from some more Tcl/Tk coders and doc writers. (JMB 11.2002)
PGAccess has not been actively maintained for some time.

phpPgAdmin

http://sourceforge.net/projects/phppgadmin
browser-based, requires webserver
Admin
Similar to the ever-popular phpMyAdmin, enhanced for PostgreSQL, supports browsing and modification of most types of PostgreSQL database objects, plus execution of ad-hoc queries. Maintained by (who else?) the phpPgAdmin team. (JMB 11.2002).
phpPgAdmin (and the required Apache and PHP packages) may be easily installed using Stack Builder if you are running a one-click-installer PostgreSQL distribution.

OpenOffice.org

http://dba.openoffice.org/drivers/postgresql/index.html
Windows, Linux, Solaris
Verbatim from the above URL :
The postgresql SDBC Driver allows to use the postgresql database from OpenOffice.org without any wrapper layer such as odbc or jdbc. The current version 0.7.6 can be considerded as good beta quality ( with some known issues and missing features). The driver is aimed at the 1.1 OpenOffice.org and OpenOffice.org 2.x versions, it does not work with OOo1.0.x trees. The final aim is to have an easier to use, faster, more feature rich database driver than the jdbc-odbc solution. The current version should already allow this in most places.

The Red Hat Database Graphical Tools, RHDB Administrator and Visual Explain

http://sources.redhat.com/rhdb
Linux
Admin
Red Hat has stood by their word and open-sourced their database tools which ship with Red Hat Database for the benefit of the PostgreSQL community. I haven't used these, a review would be nice. Developed by Red Hat, Inc. (JMB 11.2002)

Xpg: Java PostgreSQL client

http://www.kazak.ws/xpg
Most platforms
Admin
Java-based PostgreSQL database administrator, with access to tables and data entry, query interface and ability to export query results to HTML reports. Developed by Soluciones Kazak, in Spanish, but available in English as well. (JMB 11.2002)
(xpg has not been updated since 2004)

Mergeant

http://www.gnome-db.org
Linux, Unix
Admin
Database administration/user tool for GNOME, based on libgda/libgnomedb, which are a complete database-independent access layer for UNIX systems, with support for PostgreSQL, MySQL, Sybase, MS SQL Server, Oracle, Interbase/Firebird, MS Access files, xBase.

TOra, an Oracle tool with some PostgreSQL support

http://tora.sf.net./
Linux & Windows
Admin
An Oracle database administration interface, with limited ability to browse PostgreSQL databases (tables, views, and functions only). I'm told that if you have the Oracle libraries, Tora's sophisticated function editor will work for PostgreSQL as well. Developed by Henrik Johnson as a Quest Toad clone. (JMB 11.2002)

KNoda

http://www.knoda.org
FreeBSD & Linux
Admin
knoda is a database frontend for KDE. It is based on hk_classes and is released under the GNU General Public License (GPL).

PGInhaler

http://pginhaler.ifrance.com/pginhaler/
Admin
It's a free Java based PostgreSQL GUI. Haven't used it yet, but worth knowing about. (J.C.)
(PGInhaler has not been updated since 2002)

SQuirreL

http://squirrel-sql.sourceforge.net/
Macintosh/Windows
SQuirreL SQL Client is a graphical SQL client written in Java that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.

AnySQL Maestro

http://www.sqlmaestro.com/products/anysql/maestro/
Windows
AnySQL Maestro is a freeware tool for administering any database engine (PostgreSQL, SQL Server, Oracle, MySQL, MS Access, etc.), which is accessible via ODBC driver or OLE DB provider. Includes Database Designer, Visual Query Builder, BLOB Viewer/Editor, SQL Editor, Data export/import and other features.

SQL Workbench/J

http://www.sql-workbench.net
Java (multi-platform)
An OpenSource SQL GUI tool similar to Squirrel. Data can be edited directly in the result set. It has strong support for exporting and importing data between databases using its own SQL command extension. It can be used in GUI mode or as a console application. All SQL Workbench specific commands can also be run in batch mode to automate export and import task. It supports schema comparison ("diff") and copying data between databases.

PostgreSQL PHP Generator

http://www.sqlmaestro.com/products/postgresql/phpgenerator/
Windows
PostgreSQL PHP Generator is a freeware but powerful PostgreSQL GUI frontend that allows you to generate high-quality PHP scripts for the selected tables, views and queries for the further working with these objects through the web.

WaveMaker Ajax GUI Design Tool

http://www.wavemaker.com/
Windows, Macintosh, Linux
WaveMaker is an Ajax-based GUI design tool for Postgres. WaveMaker is built using itself! WaveMaker generates a standard Java WAR file based on Spring, Hibernate and Dojo. WaveMaker supports Postgres schema creation and import and includes a visual query editor.

Druid III

http://druid.sourceforge.net/
Java (multi-platform)
The druid is a tools that allows users to create databases in a graphical way. The user can add tables, fields, folders to group tables and can modify most of the database options that follow the SQL-92 standard. In addition to sql options, the user can document each table and each field with HTML information. Once the database is created, the druid can generate:

HTML documentation: for all tables, with browsing facilities
PDF documentation: for all tables
Java classes: (one class for each table) that contain tables' constants (such as fields size) plus java code added by the user
A data dictionarythat contains all tables and fields present in the database
SQL script which contains all table definitions that can be piped to the DBMS
And much more info...

Power*Architect

http://www.sqlpower.ca/page/architect
Java (multi-platform)
Power*Architect is an ERD modelling tool that is based on Java and JDBC. Support for forward and reverse engineering PostgreSQL databases is supported. It's OpenSource with a GPL license.

RISE - Model Driven Development using ERD / UML

http://www.risetobloome.com
Windows
RISE is a free software suite for model driven information system development. Use a single source RISE model to generate your entire information solution including database, web services and documentation. Boost development throughput and improve quality and maintainability!
The RISE user experience is based on a project concept, similar to that of most integrated development environments. The user works with resources and drawings that are part of a single development project.
RISE supports a fully graphical approach to information modeling and system engineering, using ERD (Entity Relationship Diagram) as well as UML (Unified Modeling Language). This includes drag-and-drop of entities, their attributes and relations, as well as of views and entire web services.
RISE supports graphic editing of complex objects such as views and orchestrated methods. RISE automatically safeguards the combination process, thus, assuring a technically correct result.
RISE provides several ways to generate code from the model; directly to file, via the RISE Server or as an Internet service. RISE provides code generators for the database layer (SQL/DDL) as well as for the server side application layer (web services and persistent classes). All database scripts are incremental allowing you to install and maintain any number of databases from a single model.
Feature list http://www.risetobloome.com/Page_1_S.aspx?ITEM=1355

RISE PostgreSQL code generator

http://www.risetobloome.com/Page_1_S.aspx?ITEM=1889
Windows
The RISE PostgreSQL code generator generates native PL/pgSQL scripts. Model your information in the free RISE Editor and generate your database script. The script incrementally updates the tables, columns, indexes and constraints in the database to match the RISE model. Once the database model is updated, the views defined in the RISE model are created in the database and possible default data, entered in the model, is inserted.

RISE PHP for PostgreSQL code generator

http://www.risetobloome.com/Page_1_S.aspx?ITEM=1888
Windows
The RISE PHP for PostgreSQL code generator renders PHP source code for database access. Model your information and programming interfaces in the free RISE Editor and generate your code. The generated code implements the classes and methods corresponding to the information interfaces specified in the RISE model. This includes classes for database access and, optionally, classes implementing SOAP/JSON web services and proxy classes assisting the implementation of a PHP SOAP client.

Marshal SQL Utility

http://www.risetobloome.com/Page_1_S.aspx?ITEM=1756
Windows
The Marshal SQL Utility is a generic cross database utility with batch execution capabilities. It allows you to test ODBC connections, browse database schemas, discover tables, views and columns.
Key features of Marshal SQL Utility:

Execute any query supported by your database server
Execute batches of queries
Discover tables and views in your database
Discover columns for a selected table or view
Supports BLOBS and CLOBS
Save your results to file
Use any ODBC compliant database such as PostgreSQL.

EMS SQL Manager for PostgreSQL Freeware

http://www.sqlmanager.net/en/tools/free
Windows
EMS SQL Manager for PostgreSQL Freeware (EMS SQL Manager Lite for PostgreSQL) is an excellent and easy-to-use freeware graphical tool for PostgreSQL database administration. It has the minimal required set of tools for those users who are new to PostgreSQL server and need only its basic functionality. EMS SQL Manager for PostgreSQL Freeware allows you to work with servers, databases and schemas, view, edit, search, group, sort and filter any data stored in a database, create and execute SQL queries with powerful SQL editor, handle multiple selected objects at a time and much more.

Proprietary

Lightning Admin for PostgreSQL

http://www.amsoftwaredesign.com/lightning_admin
Windows, other platforms via WINE
Admin
Lightning Admin is one of the best GUI admin tools available and sports a modern tabbed MDI interface and is the least expensive full featured GUI admin tool on the market. Compare to Navicat or EMS and save big. Current price is $19.99 per user.

Borland Kylix

http://www.borland.com/kylix
Linux
ERD
driver: http://www.vitavoom.com/Products/pgExpress_Driver/index.html
The third most popular IDE for Linux, according to a recent Linux Journal poll. Focuses on interface-building for database applications, with some support for browsing database objects (I think). PostgreSQL native drivers are available free from Vita Voom Software. (JMB 11.2002)

DBOne

http://www.dbone.info
Windows
Admin
DBOne is a shareware Database Administration Tool for PostgreSQL and Oracle, MSSQL, Sybase, DB2, SAPDB, Interbase/Firbird, MySQL.

DBTools Manager

http://www.dbtools.com.br
Windows
Admin
Freeware, available for PostgreSQL and MySQL, allows managing all aspects of the database: db, table, triggers, functions, etc. Includes import/export wizards to migrate data and structure to/from other database engines. Developed by DBTools Software.

PgManager

http://www.ems-hitech.com/pgmanager
Windows, Linux version just released
Admin
Basically a proprietary, more powerful version of PGAdmin II or PGAccess. Adds support for trigger and constraint editing, metadata logging, and query monitoring. Also includes multiple-format data import/export tools, which are also available on their own for Linux. Developed by EMS Hitech. (JMB 4.2003).

Rekall

http://www.thekompany.com/products/rekall
Linux
ERD
Designed by The Kompany as a Linux-based competitor to MS Access, this simple interface and database interface-builder is unfortunately designed for MySQL and has some problems with PostgreSQL. Was still buggy in May 2002, but may be improved by now. Supports limited browsing of database objects, creation of data entry forms and reports. (JMB 11.2002)

Data Architect

http://www.thekompany.com/products/dataarchitect
Linux
ERD
I'm trying DataArchitect 2 from theKompany now for 6 month. Highly recommended if you want an easy to use DB-Design Tool, but generated SQL-Scripts needs some rework. PostgreSQL is supported, you easily can create Stored Procedures, Foreign Keys, Views and Triggers. (Bernd, 1.2003)

SyBase Power Designer

http://www.sybase.com/products/enterprisemodeling/powerdesigner
Admin
Sybase PowerDesigner is power tool with built in PostgreSQL support. It support tables, views, triggers, constraints and referential integrity. More advanced than Data Architect.

Microsoft Access

http://office.microsoft.com
Windows + ODBC only
ERD, Admin
Yes, you can use MS Access as a PostgreSQL database interface. Supports data access to PostgreSQL tables and views; many ODBC-based limitations and errors. I don't need to tell anyone what MS Access' other capabilities are. (JMB 11.2002)

eRWin

http://www.ca.com/products/alm/erwin.htm
Windows
ERD
Apparently a high-end tool by Computer Associates with PostgreSQL support as a downloadable addon for some version(s). Haven't tried this. JC

DeZign for Databases

http://www.datanamic.com
Windows
ERD
DeZign is a database development tool using an entity relationship diagram. It visually supports the lay out of the entities and relations and automatically generates SQL schemas for most leading databases including PostgreSQL.

PGExplorer

http://www.PGExplorer.com
Windows (Shareware)
Admin NOTE this software is not maintained and has not had a new release in many years. Because of it's age it does not support schemas on PGSQL versions 7.3 and up.
Postgres Explorer is a full-featured GUI postgres development tool. Features include a tree view of your databases, and database objects. You can reverse engineer SQL statements from your database objects , modify the SQL and execute it. Wizards will guide you through the process to generate SQL for various objects and statements. The latest release includes a Graphical Select Query Designer (anonymous, 4.2003)

Case Studio 2

http://www.casestudio.com
Windows
ERD
I just downloaded the trial version, so far it looks real promising and claims to support PostgreSQL. Handles reverse engineering of existing databases, and has a real nice interface for setting up tables, relationships etc. You can get a lite or full version. The description from the website... Professional database modeling tool for various databases. CASE Studio 2 includes following key features:
LITE version features + Reverse Engineering from various database systems Version Manager Data Flow Diagrams Definition of user defined variables Large COM interface Users, User groups and User permission support and many more.

pgEdit

http://pgedit.com
Macintosh/Windows
pgEdit is a high performance SQL editor and development environment designed specifically for PostgreSQL relational databases. pgEdit features include SQL syntax coloring, direct source code execution, PHP support, integrated documentation, and extensive customizable editing facilities.
pgEdit uses psql, the interactive terminal application included with every PostgreSQL installation. This makes it easy to develop with pgEdit and then transfer your work to any PostgreSQL installation for maintenance and production tasks.
pgEdit is a native application for both Macintosh and Windows. It does not use Java or require installation of external libraries. You have the option of using the version of psql included with pgEdit or a different version installed anywhere on your hard drive.

RazorSQL

http://www.razorsql.com/
Macintosh/Windows/Linux/Solaris
RazorSQL is an SQL query tool, database browser, SQL editor, and database administration tool that supports PostgreSQL and any other JDBC or ODBC compliant database. Some of the major features are tools for creating, dropping, and altering objects such as tables, views, indexes, triggers, functions, users, and databases; a programming editor that supports 20 different programming languages; import and export tools; auto column and table lookup; and a query builder, query scheduler, and SQL formatter.

MicroOLAP Database Designer

http://www.microolap.com/products/database/postgresql-designer/
Windows ODBC
Database Designer for PostgreSQL is an easy CASE tool with intuitive graphical interface allowing you to build a clear and effective database structure visually, see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects. Then you can easily generate a physical database on a server, modify it according to any changes you made to the diagram using fast ALTER statements.

Aqua Data Studio

http://www.aquafold.com/index-postgresql.html
Java: Windows/Linux/Macintosh OSX/Solaris
Aqua Data Studio is a management tool for the PostgreSQL relational database w/ administration capabilities and a database query tool. The visual administration features provide users the ability to browse and modify database structures, including schema objects, database storage and maintain database security. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Aqua Data Studio also provides an import and export tool to allow users to easily move data in and out of the PostgreSQL database in and from different data formats.

Tuples

http://www.tuplesapp.com/
Mac OSX
Tuples is the first native client for PostgreSQL for Mac OSX. It supports connecting to several servers at the same time, exporting data in several formats and profiling queries. As a native client it is fast, lightweight and a good Mac citizen.

EMS Database Management Tools for PostgreSQL

http://www.sqlmanager.net/en/products/postgresql
Windows
PostgreSQL Tools Products Family:

SQL Management Studio for PostgreSQL - single workbench for administering PostgreSQL databases, managing database schema and objects as well as for database design, migration, extraction, query building, data import, export and database comparison.

SQL Manager for PostgreSQL - high performance graphical tool for PostgreSQL database administration and development. It makes creating and editing PostgreSQL database objects easy and fast, and allows you to run SQL scripts, visually design databases, build SQL queries, extract, print and search metadata, import and export PostgreSQL database data and much more.

Data Export for PostgreSQL - tool to export PostgreSQL database data quickly to any of 19 available formats, including MS Access, MS Excel, MS Word, RTF, HTML, TXT, ODF and more. Data Export for PostgreSQL has a friendly wizard, which allows you to set various options of PostgreSQL export process visually and a command-line utility to automate your PostgreSQL export jobs using the configuration file.

Data Import for PostgreSQL - tool to import data to PostgreSQL tables from MS Excel 97-2007, MS Access, DBF, TXT, CSV, MS Word 2007, RTF, ODF and HTML files. This utility allows you to quickly import data to one or several PostgreSQL tables or views at once, save all PostgreSQL import parameters set on current wizard session, use special batch insert mode to import PostgreSQL data at the maximum possible speed and much more.

Data Pump for PostgreSQL - migration tool for converting databases and importing table data from an ADO-compatible source (e.g. MS Access, MS SQL database or any other database with ADO support) to PostgreSQL databases.

Data Generator for PostgreSQL - tool for generating test data to PostgreSQL database tables. The utility can help you to simulate the database production environment and allows you to populate several PostgreSQL database tables with test data simultaneously, define tables for generating data, set value ranges, control a wide variety of generation parameters for each field type and much more.

DB Comparer for PostgreSQL - tool for comparing PostgreSQL database schemas and discovering differences in their structures. You can view all the differences in compared database objects and execute an automatically generated script to synchronize structure of PostgreSQL databases and eliminate these differences.

DB Extract for PostgreSQL - easy-to-use tool for creating PostgreSQL database backups in a form of SQL scripts. This database script utility allows you to save metadata of all PostgreSQL database objects as well as PostgreSQL table data as database snapshots.

SQL Query for PostgreSQL - useful tool that lets you quickly and simply build SQL queries to PostgreSQL databases. Visual PostgreSQL query building as well as direct editing of a query text is available.

Data Comparer for PostgreSQL - tool for PostgreSQL data comparison and synchronization. Using this utility you can view all the differences in compared PostgreSQL tables and execute an automatically generated script to eliminate these differences.

Navicat

http://pgsql.navicat.com/
Windows/Macintosh OSX
Navicat is a powerful PostgreSQL Database Server administration and development tool. It works with PostgreSQL 8.0 version or above and supports most of the PostgreSQL features including Trigger, Function, View, Manage User, and so on. It is also not only sophisticated enough for professional developers, but also easy to learn for new users. With its well-designed GUI, Navicat lets you quickly and easily create, organize, access and share information in a secure and easy way.

SQL Maestro Group products for PostgreSQL

http://www.sqlmaestro.com/products/postgresql/
Windows
SQL Maestro Group offers a number of tools for PostgreSQL.

PostgreSQL Maestro allows you to create, edit, copy, drop and dump database objects easy and fast. You can also design your database as ER diagram, build queries visually, execute SQL queries and scripts, debug PL/pgSQL functions, view and edit data including BLOBs, represent data as diagrams, export and import data to/from most popular file formats, analyze your data summarized into multidimensional views and hierarchies (OLAP cubes), manage PostgreSQL roles, users, groups and privileges, and use a lot of other admin tools designed for making your work with PostgreSQL database server comfortable and efficient.

PostgreSQL Data Wizard provides you with a number of easy-to-use wizards to generate PHP and ASP.NET scripts for the selected tables, views and queries, convert any ADO-compatible database to the PostgreSQL database, export data from PostgreSQL tables, views and queries to most popular formats, and import data into the tables.

PostgreSQL Code Factory is a GUI tool aimed at the SQL queries and scripts development.

SQL Maestro Group also produces similar tools for MySQL, Oracle, MS SQL Server, DB2, SQL Anywhere, SQLite, Firebird and MaxDB.

DB Data Difftective PostgreSQL Edition

http://www.datanamic.com/datadiff/index.html
Windows
DB Data Difftective is a utility for data comparison and synchronization. Compare data for selected tables in two databases, view differences and publish changes quickly and safely. Flexible comparison and synchronization settings will enable you to set up a customized comparison key and to select tables and fields for comparison and for synchronization. DB Data Difftective can be used for data migrations, verification of (corrupt) data, data auditing etc.

DB Schema Difftective PostgreSQL Edition

http://www.datanamic.com/schemadiff/index.html
Windows
DB Schema Difftective is a tool for comparison and synchronization of database schemas. It allows you to compare and synchronize tables, views, functions, sequences (generators), stored procedures, triggers and constraints between two databases.

DB MultiRun PostgreSQL Edition

http://www.datanamic.com/multirun/index.html
Windows
DB MultiRun is a simple tool to execute multiple SQL scripts on multiple databases quickly. Define a list of databases, add SQL scripts to execute on these databases and click "execute" to run those scripts on the databases in the list. The multi-threaded execution of the SQL scripts makes it complete the task fast. After execution of the scripts you can examine the results of the executed scripts on each database.

SQLPro

http://www.vive.net/products/sqlpro.htm
Windows
SqlPro is an easy to use database GUI tool for six popular databases (Oracle, MySQL, PostgreSQL, SQL Server, SQLite and Access). One IDE makes database administration and development faster and error free. SQLPro Key Features: color-coding of the SQL, drag-and-drop of objects into the editor pane to save you from typing their names, retrieval of SQL code for things like stored procedures and triggers from the underlying database, and one-click creation of SELECT and INSERT statements. You can open, save and print SQL scripts. SQLPro uses native drivers to connect to the databases (no ODBS or third party engines to install).

SQL Image Viewer

http://www.sqlimageviewer.com
Windows
SQL Image Viewer allows you to retrieve, view, convert and export images stored in Firebird, MySQL, Oracle, SQLite, SQL Server, and various ODBC-supported databases (e.g. DB2 and PostgreSQL). It supports the following image formats: BMP, GIF, JPG, PNG, PSD, and TIFF.
It also allows you to export binary data, and recognises the following binary file types: PDF, MP3, WAV, 7Z, BZ2, GZ, RAR, ZIP, and has experimental support for DOC, PPT and XLS file types.
A command line version is also included to allow you to perform unattended scheduled exports of binary data.

SQL Data Sets

http://sqldatasets.yohz.com
Windows
SQL Data Sets allows you to generate and save data sets retrieved from PostgreSQL, Oracle, SQL Server and ODBC-supported database engine. Saved data sets are compressed and can optionally be encrypted. Shared data sets can be opened by other users using the free SQL Data Sets Viewer. Saved data sets can also be queried further without the need to connect to any database engine, can be exported to HTML, Excel, XML and plain text files. The SQL Data Sets Viewer can also display images stored in binary fields, and identify PDF, MP3, WAV, 7Z, BZ2, GZ, RAR, ZIP, and unprotected Microsoft Office files.

PostgreSQL Commands

I lost the database password. What can I do to recover it?

You can't. However, you can reset it to something else. To do this, you

edit pg_hba.conf to allow trust authorization temporarily
Reload the config file (pg_ctl reload)
Connect and issue ALTER ROLE / PASSWORD to set the new password
edit pg_hba.conf again and restore the previous settings
Reload the config file again

What is PostgreSQL? How is it pronounced? What is Postgres?

PostgreSQL is pronounced Post-Gres-Q-L. (For those curious about how to say "PostgreSQL", an audio file is available.)
PostgreSQL is an object-relational database system that has the features of traditional proprietary database systems with enhancements to be found in next-generation DBMS systems. PostgreSQL is free and the complete source code is available.
PostgreSQL development is performed by a team of mostly volunteer developers spread throughout the world and communicating via the Internet. It is a community project and is not controlled by any company. To get involved, see the Developer FAQ.
Postgres is a widely-used nickname for PostgreSQL. It was the original name of the project at Berkeley and is strongly preferred over other nicknames. If you find 'PostgreSQL' hard to pronounce, call it 'Postgres' instead.

phpPgAdmin

http://sourceforge.net/projects/phppgadmin/files/phpPgAdmin%20%5Bstable%5D/phpPgAdmin-5.0/phpPgAdmin-5.0.2.zip/download

Mirror, Mirror On The Wall, Which Is The Best Open Source DB Of Them All?

Check this article:

Mirror, Mirror On The Wall, Which Is The Best Open Source DB Of Them All?
With the release of v 9.0 PostgreSQL stakes its claim

By Alan Shimel on Mon, 09/20/10 - 1:51pm.

Share
Tweet This
Email this page
Comments (5)
Print

Ever since Oracle got its anti-open source hands on MySQL via the Sun acquisition, many in the open source community have been hoping for a new champion to fill the perceived void that Oracle "selling" MySQL presents. With the release of version 9.0, the PostgreSQL community and EnterpriseDB, the company trying to be the "Red Hat" for PostgreSQL, say that PostgreSQL is the worlds most advanced open source database and the one everyone should be using.
I had a chance to sit down and talk with Robin Schumacher, director of product strategy for EnterpriseDB and Selena Decklmann, a major contributor and leader of the PostgreSQL community. The conversation is about 20 minutes long and you can listen to it below.
With the JavaOne conference going on right now in San Francisco, the issue of Oracle's stewardship of open source project is on a lot of people's minds. As Microsoft subnet Blogger, Robert Mullins writes, this is going to be a seminal event for the Java community to see what direction Oracle will be taking Java. Some say the signs already point to disappointment for the pro-open source crowd by the fact that as Mullins writes, "they put JavaOne at the kids table" instead of in the Moscone Center. A not so subtle message for sure.
But beyond Java, the JavaOne conference is also seen as a bellwether in the open source DB community as well. The MySQL community is looking to read the tea leaves about what Oracle might have planned for the open source DB as well.
Now of course Oracle will tell you that MySQL is not a competitor to the Oracle product. That it is aimed down market of their flagship DB. But as Schumacher, who worked at MySQL prior to joining EnterpriseDB says, the Oracle sales reps are very good at selling what is going to give them the highest commission. It is totally counter-intuitive to think that they will sell MySQL when they can instead sell Oracle into an account. MySQL will be the sale "of last resort". Meaning that only if they cannot possibly sell Oracle into an account will they fall back to MySQL.
The bigger battle going on though maybe for the hearts and minds of the development community. If the perception is that Oracle is not pushing MySQL, the PostgreSQL folks thing that they should instead line up behind PosgreSQL. As Selena mentions in the podcast, because of the license that PostgreSQL is under, it is not a candidate for any one commercial vendor to take over. It offers a true open source environment for developers and users alike.
The new 9.0 version has many features that put it on parity and even surpass some of the MySQL functionality. The new features include:

Hot standby
Streaming replication
In-place upgrades
64-bit Windows builds
Easy mass permissions management
Anonymous blocks and named parameter calls for stored procedures
New windowing functions and ordered aggregates

These were the biggest requests by the community and PostgreSQL thinks they have given the community what it wants with this release.
EnterpriseDB wants to be for the PostgreSQL community what Red Hat is for Linux. Their CEO Ed Boyajian, is a former GM of Red Hat. They are offering enterprise class service and support for PostgreSQL. They think that this is the last call for MySQL and the move to PostgreSQL will soon turn into a stampede.
One thing is for sure, MySQL showed that there was a real market for an open source database alternative. If Oracle fumbles the MySQL hand off and the community is set adrift in search of another option, the PostgreSQL folks will be waiting with open arms.

Why and what ?PostgreSQL 9.0

The PostgreSQL Global Development Group recently released PostgreSQL 9.0, with major new features and more than 200 addons and improvements for the popular database.
If you look at the release notes you'll find a ton of new features and enhancements to existing features. For example, this release brings better error messages for unique constraints, improvements in PL languages for stored procedures, and a lot more. Wading through the PostgreSQL 9.0 release notes is a DBA's delight, but what are the top features in this release? I pinged PostgreSQL core team member Josh Berkus and got some input on the most important features for PostgreSQL 9.

Binary Replication

At the top of the list? Binary replication. Berkus says this is comparable to Oracle's DataGuard. Binary replication is actually two features, hot standby and streaming replication.
The upshot of the two features landing in PostgreSQL 9.0 means that you have better load balancing, and the first replication feature that ships as part of PostgreSQL. Third party replication features have been available for some time, but this is the first that is part of PostgreSQL's official release.
Berkus also notes that this will make PostgreSQL better for cloud hosting environments.

In-place Upgrades

One area where PostgreSQL has been a bit less than perfect is in upgrades. Specifically, upgrading a major version of PostgreSQL often meant extra hardware or downtime. With PostgreSQL 9.0, organizations that have PostgreSQL 8.3 or 8.4 to upgrade in-place to 9.0.
For organizations with mission-critical systems, this makes PostgreSQL even more attractive. For organizations that can tolerate the downtime, it means the IT department doesn't have to schedule upgrades for late-night hours or weekends to minimize the impact on services. This could be known as the "give database administrators and system administrators a life" feature.

Easier Database Object Privilege Management

PostgreSQL 9.0 adds new SQL commands to help improve privileges for objects in a database with many tables, views, and/or functions. PostgreSQL 9.0 adds a global GRANT/REVOKE statement, so it's easy to add or revoke permissions globally without having to script the interaction.
And 9.0 provides a feature to alter default privileges, so new objects will have the proper permission as well as the existing objects in a database.
As an aside, this release also provides a module to check poor passwords for short passwords, passwords that contain the username, and so on. It's not a perfect solution, but it could be used to find some of the worst passwords that might make it in.

Join Removal

The first three features cover security, replication, and upgrades. What haven't we talked about so far? Speed. Users don't care how secure your database is, or whether you have a backup, or what version of PostgreSQL is running. They care about how fast their applications return information, which is dependent on how fast PostgreSQL returns queries.
To speed up queries, or some anyway, PostgreSQL 9.0 adds Join Removal. Berkus describes this as a way to speed up "messy" queries generated by GUI-driven reporting systems and object-relational mappers (ORMs) that might create less-than-optimized queries. And the feature is automatic, so it requires no intervention to work. Upgrade to PostgreSQL 9.0 and they throw in the extra speed for free.

Windows 64-bit Builds

Yes, yes. This is Linux.com, who cares about Windows builds? Well, like it or not, many enterprises do want 64-bit builds of PostgreSQL for Windows. In most environments, Linux is running side-by-side with Windows, and cross-platform support can be important. Also, a 64-bit release means better compatibility with 64-bit versions of PostgreSQL on Linux and versions of Unix.
If that doesn't sway you, consider that 64-bit versions of PostgreSQL provide a viable alternative to SQL Server and other proprietary databases on Windows that are already 64-bit. Displacing SQL Server or other proprietary databases can be a step towards moving away from Windows down the road.

Summary

PostgreSQL 9.0 is a major update over the 8.x series, and well worth a look. If your organization is already using PostgreSQL, it's time to start planning the upgrade. If not, it's time to take a look at PostgreSQL.

http://developer.postgresql.org/pgdocs/postgres/sql-do.html

DO

Name

DO -- execute an anonymous code block

Synopsis

DO [ LANGUAGE lang_name ] code

Description

DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.
The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.
The optional LANGUAGE clause can be written either before or after the code block.

Parameters

code: The procedural language code to be executed. This must be specified as a string literal, just as in CREATE FUNCTION. Use of a dollar-quoted literal is recommended.
lang_name: The name of the procedural language the code is written in. If omitted, the default is plpgsql.

Notes

The procedural language to be used must already have been installed into the current database by means of CREATE LANGUAGE. plpgsql is installed by default, but other languages are not.
The user must have USAGE privilege for the procedural language, or must be a superuser if the language is untrusted. This is the same privilege requirement as for creating a function in the language.

Examples

Grant all privileges on all views in schema public to role webuser:

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_schema, table_name FROM information_schema.tables
             WHERE table_type = 'VIEW' AND table_schema = 'public'
    LOOP
        EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
    END LOOP;
END$$;

Compatibility

There is no DO statement in the SQL standard.

Download Java PostgreSQL Server Bundles

Download OpenJDK 6 One-click Installers (21-Jun-10)

These One-click installers quickly lay down OpenJDK 6 Build 20 executables. Just set your PATH and JAVA_HOME environment variables and use with your favorite Java software.

Windows 32 Bit (UNIX Utils for Win32)	[Tutorial]
Linux 32 Bit Linux 64 Bit	[Tutorial]
Mac OSX Intel 32 Bit	[Tutorial]

Download Java PostgreSQL Server Bundles (09-Jan-11)

Developer friendly Java PostgreSQL Server Bundles provide an integrated download of the latest & greatest versions of PostgreSQL & OpenJDK with Tomcat, JBoss or Resin.

Download Eclipse-OpenJDK Developer Bundles (25-Nov-10)

Includes Eclipse Helios 3.6.1 for Java EE Developers and OpenJDK 6 Build 20.

Windows 32 Bit
Linux 32 Bit Linux 64 Bit	[Tutorial]

Digg	Slashdot	Fark	Stumble	Reddit
MIXX	del.icio.us	Newsvine	Technorati	Facebook
Buzz up!	Tweet This