Gathering from the number of hits we got from our What's new in PostgreSQL 9.0, and the large number of slashdot responses we got as well as the fair number of reddit responses, I guess a lot of people are really excited about the upcoming PostgreSQL 9.0 or at least have a lot of opinions about what is still missing in it.
For this discussion, we would like to point out one of the companion adminstration tools that will be packaged in with PostgreSQL 9.0 (and currently packaged in beta 2). This is PgAdmin III, which we will affectionately refer to as the Administrative tool for mere mortals. It is the first administrative tool that most users new to PostgreSQL use and gives them a user-friendly interface to the power behind PostgreSQL. I would say if it were not for this tool and its web cousin PhpPgAdmin, many a scared newbie user would be running away at the vast unencumbered freedom that PostgreSQL/psql and sibling commandline tools offer.
Yes I can hear the die-hard PostgreSQL super users screaming what about PSQL, PSQL is the coolest. Yes PSQL is cool and has new features too. I will say as far as commandline database tools go, PSQL, Pg_restore and Pg_Dump are the best we've ever seen packaged in a database product. MySQL and SQL Server should be ashamed of themselves for packaging comparitively much crappier commandline tools. We haven't used Oracle much so can't speak for Oracle tools. We'll get to PSQL and its commandline friends in another article. I will say though, that even if for whatever reason you can't upgrade to PostgreSQL 9.0 which many will not be able to because of backward compatibility reasons, you can still enjoy many of the new enhancements in PgAdmin III 1.12.0 and the new PSQL and related command line clients that will be packaged with PostgreSQL 9.0.
PhpPgAdmin is cool too. This is the web interface similar to phpMyAdmin and patterned after that except with bells and whistles specific to PostgreSQL (such as schemas, tablespaces, create types etc). We'll cover that in another article.
Okay what's new in PgAdmin III
All the new features in PgAdmin III 1.12.0 are outlined at PgAdmin III 1.12 Visual Tour. Here we'll give a run down of our favorites in priority.
- Support for PostgreSQL 9.0. You can't read 9.0 database with the older PgAdmin III's
- The new backup interface. It gives you so much power on what you can backup and compression and so forth. Since
we are writing a book, where we want to selectively include some tables and not others, this is especially nice.
Note that these features have been available in the pg_dump commandline for a while, but its really nice to have a graphical interface to those features
so you don't have to figure out how to write out those command line switches and paths. The objects tab is our favorite
tab on backup.
- Restore options - this allows you to set the number of jobs and better interface for selecting what you want to restore. Its a bit tricky, so we'll show it in the next section of this article.
- Connection Handling - client losses connection to server you get the option to reconnect now.
- Support for new column level triggers
- Server groups -- ability to group registered PostgreSQL servers
- Ability to quickly save graphic explain plans as a PNG file.
- SQL Editor automatically remembers past run queries and allows you to toggle to them to rerun them
- Support for the new 9.0 extended priviledges (DEFAULT priviledges)
Selective Restore
For compressed backups and tars, you can selectively restore just like you can with the pg_restore commandline. Here are the steps.
- Select the backup file. You can set the number of parallel jobs you want to use for restore.
- Switch to the Objects tab.
and click the View button. - Double-click on the backup file name and you should see all the schemas and if you expand a schema the objects in that schema
- Then selectively select (if you don't want to retore the whole thing) what you want to restore and click the OK button