Thursday, January 21. 2010
Printer Friendly
Every once in a while, especially if you have a fairly large database, you may find the need to do select backups of certain tables.
Your criteria might be based on name or how relatively recently data has changed in the table.
Below are some of the tricks we use. Some use our favorite hack of scripting command line scripts with SQL.
Continue reading "Making backups of select tables"
Thursday, December 24. 2009
Printer Friendly
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.
Continue reading "PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF Loader"
Saturday, November 28. 2009
Printer Friendly
In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL
box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box,
you can skip the Upgrade step.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use
this one instead since it seems more up to date than the other. http://yum.pgrpms.org/
UPDATEWe have instructions for installing PostgreSQL 9.0 via yum.
Updgrading from PostgreSQL 8.* to PostgreSQL 8.4
If you are starting from scratch -- just skip this section.
If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple
yum update postgresql and skip the rest of this article.
If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice,
particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.
Continue reading "An almost idiot's guide to Install and Upgrade to PostgreSQL 8.4 with Yum"
Monday, October 26. 2009
Printer Friendly
This is an unfortunate predicament that many people find themselves in and does cause a bit of frustration. You bring in some tables into your PostgreSQL
database using some column name preserving application, and the casings are all preserved from the source data store. So now you have to quote all the fields
everytime you need to use them. In these cases, we usually rename the columns to be all lower case using a script. There are two approaches we have seen/can think of for doing this
one to run a script that generates the appropriate alter table statements and the other is to update the pg_attribute system catalog table directly.
Continue reading "Lowercasing table and column names"
Wednesday, October 21. 2009
Printer Friendly
Vacuuming and analyzing is the process that removes dead rows and also updates the statistics of a table.
As of PostgreSQL 8.3, auto vacuuming (the process that runs around cleaning up tables), is on by default. If you are
creating a lot of tables and bulk loading data, the vacuumer sometimes gets in your way. One way to get around that is to
disable auto vacuuming on the tables you are currently working on and then reenable afterward.
You can also do this from the PgAdmin III management console.
Continue reading "Enable and Disable Vacuum per table"
Wednesday, September 09. 2009
Printer Friendly
One of the small little treats provided in PostgreSQL 8.4 is the new pg_terminate_backend function. In the
past when we wanted to kill runaway postgresql queries issued by a database or user or hmm us, we would call the pg_cancel_backend function.
The problem with that is it would simply cancel the query in the backend process, but often times the offending application would simply launch the same query again.
In PostgreSQL 8.4 a new function was introduced called pg_terminate_backend. This doesn't completely replace pg_cancel_backend, but basically does
what you do when you go into say a Windows Task manager and kill the offending postgres process or on Linux, you call a kill command on a postgres process. Its nicer
in the sense that you can do it all within PostgreSQL and you can use the pg_stat_activity query to help you out a bit. Also you don't run the risk as easily of
killing the root postgres process and killing the postgres service all together.
Continue reading "Terminating Annoying Back Ends"
Monday, September 07. 2009
Printer Friendly
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.
Continue reading "Database Administration, Reporting, and Light application development"
Tuesday, June 30. 2009
Printer Friendly
We have covered this briefly before, but its an important enough concept to cover again in more detail.
Problem: You are running out of disk space on the drive you keep PostgreSQL data on
Solution:
Create a new tablespace on a separate drive and move existing tables to it, or create a new tablespace and use for future tables.
What is a tablespace and how to create a tablespace
A tablespace in PostgreSQL is similar to a tablespace in Oracle and a filegroup in SQL Server. It segments a piece of physical disk space
for use by the PostgreSQL process for holding data. Below are steps to creating a new tablespace. Tablespaces have existed since PostgreSQL 8.0.
More about tablespaces in PostgreSQL is outlined in
the manual PostgreSQL 8.3 tablespaces
While it is possible to create a table index on a different tablespace from the table, we won't be covering that.
Continue reading "Managing disk space using table spaces"
Tuesday, June 09. 2009
Printer Friendly
You'll often hear the term planner statistics thrown around by database geeks. Did you update your statistics. This lingo isn't even limited
to PostgreSQL, but is part and parcel to how most decent databases work. For example in PostgreSQL you do a vacuum analyze to update your planner statistics in addition
to cleaning up dead space. In SQL Server you do an UPDATE STATISTICS . In MySQL you do an
ANALYZE TABLE or a more invasive OPTIMIZE TABLE .
Normally all this "update your stats so your planner can be happy" is usually unnecessary unless
you just did a bulk load or a bulk delete or you are noticing your queries are suddenly slowing down. These stat things are generally updated behind the scenes by most databases
on an as needed basis.
What makes SQL really interesting and a bit different from procedural languages is that it is declarative (like functional and logical programming languages) and relies on the database planner to come up with strategies for navigating the data. Its strategy is not fixed as it is in procedural languages.
A big part of this strategy is decided on by the query planner which looks at distributions of data. Given different WHERE conditions for similar queries, it could come up with vastly different strategies if one value has a significantly
higher distribution in a table than another. This is also the mystery of why it sometimes refuses to use an index on a field because it has decided a table scan is more efficient and also why some people consider HINTS evil because they pollute the imperative nature of the language.
Continue reading "Planner Statistics"
Sunday, April 26. 2009
Printer Friendly
Different Linux distros have their preferred place of where stuff goes and of course the default location on windows is completely different from that too. So there isn't really one default location where you can find PostgreSQL data cluster. Of course user's can pick their
locations as well. So what is a casual DBA supposed to do?
The pg_settings table
PostgreSQL has a convenient system table view called pg_settings that stores a lot of information. It stores the location of the data cluster, the pg_hbafile and other conf files.
In additon to that you can interogate it to find out information you will find in the postgresql.conf file. Why sift thru that postgresql.conf file (assuming you can already query your postgresql server) when you can find the answers you are looking
for with an SQL query?
Continue reading "Where is my data and other stuff"
Saturday, March 28. 2009
Printer Friendly
Even though others have blogged about this in the past and its well-documented in the docs, its a frequently enough asked question, that we thought we'd post it here again
with a couple of additional twists.
How to determine the size of a database on disk
SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize;
How to determine the size of a database table on disk
NOTE: There are two functions in PostgreSQL - pg_relation_size and pg_total_relation_size. The pg_relation_size just measures the size of the actual table where
as the pg_total_relation_size includes both the table and all its toasted tables and indexes.
SELECT pg_size_pretty(pg_total_relation_size('someschema.sometable')) As fulltblsize,
pg_size_pretty(pg_relation_size('someschema.sometable')) As justthetblsize;
How to determine the size of a database schema
When you are doing a lot of data loading for later massaging to dump into your production tables, you will often generate junk tables in the process and worse yet other
people may generate junk tables period. As a general practice, we like to create a schema just for junk. The reason for that is that it is extremely easy to
exclude schemas from being backed up and to load individual schemas. So with this approach we create or move all tables we are not quite sure if they are needed or we only need temporarily
into a schema
called junk, scratch, possiblejunk or whatever you want to call the schema and exclude it from our backups.
Something of the form:
CREATE SCHEMA scratch;
ALTER TABLE someschema.joeyplaying SET SCHEMA scratch;
After saving the world from junk, you would like to know how much space you have saved your backups from backing up. So you would employ a query something of the form:
SELECT pg_size_pretty(pg_database_size('mycurrentdb')) As fullprod,
pg_size_pretty(CAST(pg_database_size('mycurrentdb') - (SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables WHERE table_schema = 'scratch') As bigint)) As tobebackedup_size,
pg_size_pretty(CAST((SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables
WHERE table_schema = 'scratch') As bigint) ) As junk_size;
Size of Geometries in PostGIS tables
PostGIS has a companion function for measuring geometry size which is useful when you want to get a sense of how much space your geometries are taking up on disk
for a set of records.
SELECT ST_Mem_Size(ST_GeomFromText('LINESTRING(220268 150415,220227 150505,220227 150406)'));
SELECT pg_size_pretty(CAST(SUM(ST_Mem_Size(the_geom)) As bigint) ) as totgeomsum
FROM sometable WHERE state = 'MA';
Saturday, December 20. 2008
Printer Friendly
This question was asked on the PostgreSQL novice newsgroup recently and Tom Lane fielded the question. Its a common thing to want to know if you are using the new 8.3 Enum feature. So we felt it useful to restate it.
Question: Given an enum, is there a query you can run to list out all the elements allowed by the enum?
Answer: Yes.
Below is just a regurgitation of the news item http://archives.postgresql.org/pgsql-novice/2008-12/msg00043.php
CREATE TYPE myenum as enum ('red','green','blue');
SELECT enumlabel
FROM pg_enum
WHERE enumtypid = 'myenum'::regtype
ORDER BY oid;
enumlabel
-----------
red
green
blue
(3 rows)
http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html
Tuesday, December 16. 2008
Printer Friendly
In our Product Showcase section of this issue, we introduced Fusion Charts which is a flash-based
charting product that makes beautiful flash charts. It comes in both a free and a non-free more bells and whistles
version.
In this 3-part series article we shall demonstrate using this with a PostgreSQL database, building a simple dashboard
with ASP.NET and PHP. We shall demonstrate both C# and VB.NET both using the PostgreSQL NPGSQL driver.
For this first part we shall simply load the database, do a quick analysis of what we've got to report on and create some views to help
us with our PHP and ASP.NET apps that will follow in parts 2 and 3.
We will be testing this on 8.3, but since the database is an old one, it should work just fine on older versions of
PostgreSQL. We'll try to refrain from using new features of PostgreSQL.
Continue reading "Fusion Charts and PostgreSQL Part 1: Database Analysis of USDA DB"
Wednesday, November 26. 2008
Printer Friendly
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:
yum install postgresql
yum install postgresql-server
Continue reading "Yum addendum for 8.3.5 and PgAgent"
Monday, November 17. 2008
Printer Friendly
Sometimes when you are testing or setting up a server or just porting things to another server, you just want to install the same set of users as you had before without restoring any databases.
In PostgreSQL, the users (Login Roles) and group roles are stored at the server level and only the permissions to objects are stored at the database level.
Question: How do you restore just the users and roles without having to do a full pg_dumpall of your server?
Continue reading "Backing up Login Roles aka Users and Group Roles"
|