Monday, December 31. 2012
We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this:
http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
SELECT * into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype = 'T';
DELETE from pg_default_acl WHERE defaclobjtype = 'T';
If anyone else has further input on this, I'd be interested.
Monday, June 27. 2011
Continue reading "Using PgAdmin PLPgSQL Debugger"
I'm one of those old-fashioned folks that debugs with print lines and raise notices. They're nice.
They always work, you can put clock time stops in there and don't require any fancy configuration.
At a certain point you do have to pull out a real debugger to see what is going on. This often
happens when your one-liners are no longer good enough and now you have to write 20 liners of plpgsql code.
Such is the case with geocoding and the PostGIS tiger geocoder specifically. Lots of interest has revived
on that with people submitting bug reports and we've got paying clients in need of a fairly easy and speedy drop-in geocoder
that can be molded to handle such things as road way locations, badly mis-spelled real estate data, or just simply
to get rid of their dependency on Google, Yahoo, MapQuest, ESRI and other online or pricey geocoding tools.
So I thought I'd take this opportunity to supplement our old-fashioned debugging with plpgsqldebugger goodness.
In this article, we'll show you how to configure the plpgsql debugger integrated in PgAdmin and run with it.
Friday, May 06. 2011
Continue reading "Navigating PostgreSQL 9.1 beta 1 with PgAdmin III 1.14.0 Beta 1"
We've started to play with PostgreSQL 9.1beta and the PgAdmin III 1.14.0 Beta 1. We'll briefly go over the cool gems
found in PgAdmin III beta 1. Most of the new features are for navigating the upcoming PostgreSQL 9.1. Well first obstacle we ran into
was we can't get our favorite extension, PostGIS, to compile against PostgreSQL 9.1beta though it did with the alphas, so you won't be seeing any windows experimental builds until we resolve this issue.
Details of ticket here? PostGIS 2.0 won't compile for PostgreSQL 9.1 beta1
Despite that minor set back, we decided to push on and navigate the new features by using PgAdmin III 1.14.0 as our Tour Guide. Below is a list of new features you can experience
via PgAdmin III 1.14.0 Beta 1. I'm sure there are more we missed, but these are the ones that were most flashing.
Friday, October 29. 2010
Continue reading "pgAdmin pgScript"
pgAdmin has this feature called a pgScript. Its a very simple scripting language for running
tasks in a pgAdmin SQL window. The documentation is PgScript manual.
Why would you use it over say writing a plpgsql function?
One main reason we use it is to run quick ad-hoc batch jobs such as geocoding addresses and so forth. The
benefit it has over running a stored function is that you don't have to run it as a single transaction.
This is important for certain kinds of tasks where you just want to run something in a loop and have each loop commit
separately. To us the syntax with the @ resembles SQL Server Transact-SQL more than it does any PostgreSQL language. WhenI first
saw pgScript I thought Wow PgAdmin talks Transact-SQL; now -- what will they think of next :).
Monday, October 11. 2010
Continue reading "PgAdmin III 1.13 - change in plugin architecture and PostGIS Plugins"
One of the neat changes already present in the PgAdmin III 1.13dev, is the change in plugin architecture. Version 1.13 dev allows for multiple plugin*.ini files. How does this work.
Well if you have a plugins.d folder in your PgAdmin III version folder, it will read all the inis in that folder and load them as plugins.
Recall in PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF Loader,
we demonstrated how to load the PostGIS shapefile and dbf loader as a plugin in PgAdmin III, well this time we will demonstrate how to do it using PgAdmin version 1.13. Better yet, we'll show you the new and improved
PgAdmin III Shapefile and DBF Loader in the works for PostGIS 2.0.
Thursday, August 26. 2010
Continue reading "Explain Plans PostgreSQL 9.0 - Part 2: JSON and JQuery Plan Viewer"
In part 1 of this series on PostgreSQL 9.0 planner outputs, we demonstrated how to render explain plans in YAML, JSON, and XML using the new explain features in PostgreSQL 9.0. In this second part,
In part 3 we'll do something similar using XML and XSLT programming.
You can check it out at JQUERY.
We are not experts in JQuery, but from what we have used of it, we really like it and the fact the base package is MIT licensed, fairly light weight and lots of plugins available for it are real pluses.
The most difficult thing I think most people find about reading explain plans is that they are upside down; it starts with a conclusion and backtracks how to arrive at it. Humans by nature think about planning steps from start to finish.
In order to make an explain plan understandable to mere mortals, we generally display them upside down or having the child-nodes shown left most. We shall follow that approach.
Thursday, July 29. 2010
Continue reading "Explain Plans PostgreSQL 9.0 Text, JSON, XML, YAML - Part 1: You Choose"
One of the new features of PostgreSQL 9.0 is the ability to specify the format of an explain plan.
In prior versions your only choice was text (and graphic explain with tools like PgAdmin III and other GUIS), but in 9.0 on, you have the additional options of
The main benefit of the JSON, XML, YAML formats is that they are easier
to machine parse than the default text version. This will allow for creative renderings of planner trees with minimal coding.
In Part 1 of this series, we'll demonstrate how to output the plans in these various formats and what they look like.
to transform these into works of art you can hang on your wall.
-- START POSTGIS IN ACTION ASIDE --
We just submitted the third major revision of Chapter 3 Data Modeling
of our upcoming PostGIS in Action book.
The second major revision we never submitted and threw it out because it wasn't worldly enough and was too involved. We may
use it later on for an example.
Chapter 3 should be up on Manning Early Access Program (MEAP) soon. If you haven't bought the book yet Buy now.
You don't want to miss out on a major masterpiece in the making. Okay we exaggerate a bit.
-- END POSTGIS IN ACTION ASIDE --
Monday, July 05. 2010
Continue reading "What is new in PgAdmin III 1.12.0"
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.
Thursday, December 24. 2009
Continue reading "PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF Loader"
PgAdmin 1.9+ has a simple Plug-In architecture which makes it relatively simple to introduce new plugins. It is pretty much all controlled by the file plugins.ini. In that file you can register any executable you want in there. If you want the executable to get passed database configuration settings, there is an option for that and you just have to build your executable to accept commandline switches.
You can download the windows version from http://postgis.net/windows_downloads. If you just want it without the PostGIS 1.5 binaries -- just download the one labeled PostGIS ESRI Shapefile GUI.
Monday, September 07. 2009
Continue reading "Database Administration, Reporting, and Light application development"
One of the most common questions people ask is Which tools work with PostgreSQL. In a sense the measure of a database's
maturity/popularity are the number of vendors willing to produce management and development tools for it. Luckily there are a lot of vendors producing tools for PostgreSQL and the list is growing.
One set of tools people are interested in are Database administration, ER diagramming, Query tools, and quickie application generators (RAD).
For this issue of our product showcase, we will not talk about one product, but several that fit in the aforementioned category.
All the listed products work with PostgreSQL and can be used for database administration and/or architecting or provide some sort of
light reporting/rapid application building suite. By light reporting/application building, we mean
a tool with a simple wizard that a novice can use to build somewhat functional applications in minutes or days. This rules out all-purpose development
things like raw PHP, .NET, Visual Studio, database drivers etc. Things we consider in this realm are things like OpenOffice Base and
MS Access. Most of these tools are either free or have 30-day try before you buy options.
You can't really say one tool is absolutely better than another since each has its own strengths and caters to slightly different audiences and also
you may like the way one tool does one important thing really well, though it may be mediocre in other respects. We also left out a lot of products we are not familiar with and may have gotten
some things wrong.
If we left out your favorite product and you feel it meets these criteria, or you feel we made any errors, please let us know, and we'll add or correct it.
We will be including Free open source as well as proprietary products in this mix. If we left out what you consider an
important criteria, please let us know and we'll try to squeeze it in somewhere.
Wednesday, November 26. 2008
We had the pleasure of doing a fresh install of PostgreSQL 8.3.5 on RedHat EL4 box and when using the Yum repository, we noticed a couple of changes from last time we did this.
This could have been an oversight in our documentation before.
Changes to Yum Install for 8.3.5?
In our April 2008 issue we had An Almost Idiot's Guide to PostgreSQL YUM
and that article still seems to be surprisingly popular.
In the first step we had:
yum install postgresql
and that as I recall installed the postgresql server in addition to some client libraries.
For 8.3.5 fresh install it seems they are separated and to get the postgresql server you need to do:
Continue reading "Yum addendum for 8.3.5 and PgAgent"
yum install postgresql
yum install postgresql-server
Wednesday, September 24. 2008
Continue reading "PgAdmin III 1.9 First Glance"
We've been playing around with the snapshot builds of PgAdmin III 1.9 and would like to summarize some
of the new nice features added. PgAdmin III 1.9 has not been released yet, but has a couple of neat features brewing.
For those interested in experimenting with the snapshot builds and src tarballs, you can download them from http://www.pgadmin.org/snapshots/
Wednesday, July 02. 2008
Continue reading "How to Inherit, Unherit and Merge Inherit"
A lot of this information is nicely tucked away in the PostgreSQL docs in http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html,
but since the docs are so huge and rich, one may tend to miss
While there are numerous interesting use cases for the PostgreSQL inheritance structure, one of the key reasons people use it is for table partitioning strategies.
How do you make a stand-alone table a child of another table?
The first question that comes to mind is why would you ever need a table to adopt another table. There are 2 reasons that come to mind.
- When you are loading huge amounts of data especially of a read only nature - its often convenient to not have that table be visible to your applications until
you are done with the loading process. So you may want to make it a child after the loading.
- Your tables seemed fairly unrelated when you started out and then one day you realized you really were talking about apples and apples and need to report on them together at a higher level.
One situation like this to give a somewhat real-world perspective - lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with
the basic fields needed to track some additional ones of their own. Then higher forces
came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views
that union stuff together or institute a round-up-the-children-and-adopt-them program.
This fits into one of the categories of things that PostgreSQL lets you do that PgAdmin III doesn't have a graphical way to let you do it.
If you try to inherit in PgAdmin III from a table that already exists, that option is just greyed out. So you have to resort to DDL SQL statements. Luckily its fairly trivial. Well this really only works
for PostgreSQL 8.2+. I don't think PostgreSQL 8.1 and below supported INHERIT/NO INHERIT in the ALTER TABLE statement.
Saturday, April 05. 2008
Continue reading "How to SELECT ALL EXCEPT some columns in a table"
People have asked us on several occasions if there is such a construct
SELECT * EXCEPT(...list) FROM sometable. Sadly we do not think such a
thing exists in the ANSI SQL Specs nor in PostgreSQL.
The above feature would come in handy when you have certain fields in your tables that are common
across tables, but you need to leave them out in your query. A common case of this is when you have PostGIS tables loaded using shp2pgsql
with a fields called gid and the_geom which are not terribly useful for simple data queries.
There are 2 common ways we use to achieve this result.
- Using PgAdmin's CREATE SELECT script feature. This exists in other GUI tools as well.
- Using an Information Schema script hack to construct the SELECT statement
Saturday, March 29. 2008
You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. How do you rename this database?
If you are using PgAdmin III, you will not see this option. Just one of the ways PgAdmin III lets us down. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error.
- Connect to some other database other than the one you are trying to rename such as say the postgres db.
- Kick everyone out of the database you are trying to rename - to figure out users, you can run
WHERE datname = 'myolddbname_goes_here'
- Now just run this command -
ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here