Paul's personal blog

So, I just had to write one more article. I installed the windows version of PostgreSQL 8.3 on Vista and it workes like a charm. One feature that I thought was a nice addition was the "Application Stack Builder". This add-on allows you to add features like Npgsql (the .NET data provider) or things like Slony-I and Ruby on Rails. Pretty nice add-on...

--Paul

I just noticed that it's been over a year since I wrote my first article on tools that I like. Since then, there have been new tools introduced and some older ones have been enhanced...

Here is a small addition to that article to bring my tools list a little closer to date...

PG Lightning Admin
This is still one of my favorite tools. I manage lots of PostgreSQL databases and although PGAdminIII is a great tool, it does not allow you to "bucket" servers into groups for ease of management. Also, AMSoftware has released a "multi-script" tool that allows me to execute SQL on many postgres database "at the same time" compiling the results for me in a nice tab like screen. Great for when you need to check all of your database nodes for a certain thing. Recently, AMSoftware has released a "debugger" which I hope will be a huge development help some day. Right now, it's only a 1.0 release but it already works pretty well for what it is supposed to do. Debugger Page

You can find info on all of them here...
PG Lightning Admin Page


Aqua Data Studio
I still use an older version of this product basically because it just works for me. I believe there is a newer version "for purchase" that might be worth the money but have not tested it. This tool allows me to script out all of the objects in a database to individual files or one single file and allows me to manage all of the database systems that I need to in one GUI. A good tool to have around...


EMS Enterprise Studio
I really like this product. It has some bugs that can be frustrating but overall it allows me to compare schema's and data across two databases using a nice tool where I had to script this stuff out before. Definitely worth checking out if you have a need to do that sort of stuff...

well, that is the major stuff. look up my older article if you'd like to get a more comprehensive list...

--Paul

Hello,

This is such a simple command that I had to post about it. I found it strange that I did not see a lot of posts on it... Anyway, hope that somebody can use it...


SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;


Regards,

Paul

Hello,

Recently, I needed to ETL 320 Million records (of about 320 bytes each ) from a SQL Server 2000 system to a PostgreSQL 8.2.4 server and could not find a way to do it quickly given that the data was so raw. The data could contain almost any character within the attribute and any import mechanism that I tried blew up. I ended up writing a SQL Server 2000 DTS package (It easily could have been a SQL 2000 SSIS package but the system was a 2000 machine so I kept it in DTS) that used a non-conventional "delimiter" as well as VBScript code to scrub the data. The end result was a very fast ETL of data that could be loaded via the PostgreSQL COPY command to get me just what I needed. I decided to write up this article to help anyone else that might have this similar need...

Here was my problem... I had data that could contain carriage returns and line feeds within a varchar datatype. Also, choosing a common delimiter like comma or pipe was not an option because that could exist in the data too. We all know that as of right now, the COPY command within PostgreSQL only allows you to use a "single" delimiter so using a composit delimiter was not an option as well. I guess all of this could be done by crafting INSERT statements but man, would that have taken forever. I wanted to use the COPY command and I was going to try to find a way to do it.





Here is what I did...

1) The first thing that I did was to create a DTS package within SQL Server. The Package had your traditional "Source" and "Destination" with a "Transformation" piece to the middle of it. I also included an ActiveX Script Task as an initial step to manually override the delimiter. The delimiter that I chose was CHR(137) or "‰" (If you can see that character in this web page...) I chose that delimiter because it was one of the only ASCII or Extended ASCII characters that did NOT live in the data. (I guess I got lucky enough to find one...) This character is what I used to delimit characters and later to teach the PostgreSQL COPY command how to use.


Here is the ActiveX Script Task...
'Here is the code that you would need to copy and paste into an ActiveX Script Task. Make sure
'Look at the image that I've included as well to see the precidence that was set for this. Basically, I run this first,
' and then "On Success" of running this, I begin the ETL.
Function Main()
Dim oConn
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.ConnectionProperties("Column Delimiter").Value = Chr(137)
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function




2) The next step is to add a "Transformation Step" that can do additional cleaning to your data. Here is an example of what I included. I've only included a few attributes so you can get the idea of the power. You could use any of the commands in the VBScript library to help you along...

' This is the code that you would copy and paste into an ActiveX Script Transformation.
' You can set this up by turning your Transformations component into a ActiveX script
'
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("AttributeA") = DTSSource("AttributeA")
DTSDestination("AttributeB") = DTSSource("AttributeB")
' Imagine lot's of other columns here...
' This was done to further remove unwanted characters like "line feeds" and "carriage returns".
If Len (DTSSource("AttributeC")) > 0 then DTSDestination("AttributeC") = Replace(Replace(Replace(Replace(DTSSource("AttributeC") , chr(13),""),chr(10),""),chr(0),""),","," ") else DTSDestination("AttributeC") = NULL end if
Main = DTSTransformStat_OK
End Function



3) I included a file destination and wrote to a CSV file. This file was not truly a CSV because I was delimiting on CHAR(137) but it still worked.


4) The next step is to teach the PostgreSQL COPY command how to use this custom file. It turns out that you can specify a HEX or OCTAL code to the COPY command to customize your ETL. I found very few examples on this (other than the fact that it specified you could do this in the documentation...) This ended up being the key to the whole thing working...

Here is the command that I used to import the file that I had created...
COPY mytableinpostgresql FROM '/pgdata01/mylargedatafile.csv' WITH DELIMITER '\x89' CSV NULL AS '';

The key to that entire command is the '\x89' which specifies that I want to use the Extended ASCII character. x means that I'm specifying a Hex digit. Here is the table from the PostgreSQL docs specifiying what you can use as a delimiter...

Sequence Represents
\b Backspace (ASCII
\f Form feed (ASCII 12)
\n Newline (ASCII 10)
\r Carriage return (ASCII 13)
\t Tab (ASCII 9)
\v Vertical tab (ASCII 11)
\digits Backslash followed by one to three octal digits specifies the character with that numeric code
\xdigits Backslash x followed by one or two hex digits specifies the character with that numeric code


If you look at the first link I've included below, you'll see that in the Extended ASCII Codes there is a symbol for #137. This turned out to be the one symbol that did not exist and one that I decided to use.

That is pretty much it. This allowed me to move large volumes of data from SQL Server into PostgreSQL using the most optimal tools each had. I'll post more info on timed results once I get a chance to put those together...


If you have any questions, feel free to email me at pasilveira@gmail.com.

Regards,

Paul




Appendix:

Here are some sites that you could use to figure out what characters you'd like to use if you need to customize your ETL...
ASCII Tables...

More ASCII Tables and Nice Hex to Decimal stuff...

Even More ...

We have configured PostgreSQL 8.2 to use HA and DRBD(Network RAID1) and I have to be honest, it works really smoothly. The management commands are not that difficult to learn and configuring it is only semi-painful. All in all, this solution works pretty well. Additionally, We have started using Zabbix to monitor our PostgreSQL Systems and it has a really good "Maps" that tell us how our clusters are running. Like peanut butter and jelly.

-Paul

I attended a "PostgreSQL Performance Tuning" class for PostgreSQL 8.2 at the Open Technology Group's facility in Morrisville NC two weeks ago along with 2 other people from my company. This class was really a cross between performance tuning and an advanced class teaching all types of advanced performance tricks of the trade.

We covered cool things like Slony-I replication, Inheritance, Warm Standby/Log Shipping, LISTEN/NOTIFY, pgpool, and even memcached... There was at least 3 things that I learned from that class that I was able to apply directly to the architectures that I'm developing right now.

If your looking for an advanced class, I would highly recommend it... Here is the link I used when I made my decision to take the course.

http://www.otg-nc.com/training-courses/coursedetail.php?courseid=47&cat_id=8



-Paul

I was working on an automation piece that would allow me to poll all of the PostgreSQL servers in my environment to collect their configuration data so that I could run reports to find settings that were set incorrectly on them. I've found that it's easy to have switches set differently on servers that should be set the same for concurrency across a farm of servers. (for example, autovacuum is set to true on 10 servers but off on 1, 'log_min_duration_statement' left to 0 for an extended period, etc...)

I wrote this script to go out and collect the config settings on a postgres box and save them into a postgres table on a central server. The script initially goes out and queries a server table to get the servers to poll and then poles each on in a loop. Special thanks to Susan Cassidy who helped me with some scripting logic to get it working. I posted a question on nabble and within a day, it was answered. (You got to love community...)

Here is the code for anyone who would like to use it. I haven't completly finished the project but at least the data aggregation piece is complete. I have placed a "collectiontime" timestamp in the serverconfigsetting table so that you can group by on it to be able to compare collections to each other.


CREATE TABLE public.server(
serverid serial NOT NULL,
servername varchar(30),
instancename varchar(30),
instanceport integer,
serverclass integer,
isactive boolean DEFAULT false
)WITHOUT OIDS;
ALTER TABLE public.server OWNER TO postgres;


CREATE TABLE public.serverclass(
serverclassid serial NOT NULL,
serverclassdesc varchar(20),
CONSTRAINT serverclass_pkey PRIMARY KEY (serverclassid)
)WITHOUT OIDS;
-- Owner
ALTER TABLE public.serverclass OWNER TO postgres;


CREATE TABLE public.serverconfigsetting(
servername varchar(500) NOT NULL,
instanceport varchar(30) NOT NULL,
collectiontime timestamp NOT NULL,
name varchar(100) NOT NULL,
setting varchar(300),
category varchar(100),
short_desc varchar(500),
context varchar(100),
vartype varchar(50),
source varchar(100),
min_val text,
max_val text
)WITHOUT OIDS;
ALTER TABLE public.serverconfigsetting OWNER TO postgres;



# create a file and paste the below code in it. I've called my postgresconfig.sh
#!/bin/bash

# ###############################################################################
# This script will poll a list of servers for their current configuration settings
# via pg_settings and will output it to a database.
# ###############################################################################


BATCHTIME=$(date)

psql Admin -Upostgres -hMYSERVER --quiet --no-align --field-separator ' ' -t -c"SELECT servername, instanceport from server where serverclassid=3 and isactive='True'" |
while read -a SVRDATA;
do
# echo "Getting Data from:${SVRDATA[0]} port:${SVRDATA[1]}"
psql -Upostgres -h ${SVRDATA[0]} -p ${SVRDATA[1]} -A -t -c"SELECT '${SVRDATA[0]}', '${SVRDATA[1]}', '$BATCHTIME', name, setting, category, short_desc, context, vartype, source, min_val, max_val FROM pg_settings;" | psql Admin -Upostgres -hMYSERVER -t -c"COPY serverconfigsetting FROM STDIN WITH DELIMITER '|' NULL AS '';"
# echo -n "Done with:${SVRDATA[0]} port:${SVRDATA[1]}"
done

Hello,

I've been meaning to blog about this but have been so busy that I've let it slip... Until Now!!!

The first Mid-Atlantic PostgreSQL Users Group (OK. Maybe it was really a dinner. ) was held on September 21st, 2006 at Ted's Montana Grill in Raleigh, NC. Attendees were: Andrew Dunstan, Ken VanDine, Chander Ganesan and Myself. I had been emailing around to try to find people in the region that had been using PostgreSQL and came up with this initial list. We had a really good time. We talked about new features in 8.2, replication stories, and projects that we are working on. Korry Douglas and his wife were going to drive down from VA (he's a diehard...) but could not make the first meeting due to a last minute issue. (that's ok Korry, we used your credit card. )

So do you live in the Mid-Atlantic region (anywhere from VA to SC...) and would like to meet once in a while (maybe every other month) to discuss PostgreSQL? If so, drop me a line and let me know. I think it would be cool if we met with some agenda and discussed either new features or some cool projects that we are working on in the field. Attendees could present those topics and teach other attendees what they know in that area. We have a choice of two offices in the RDU area that we could use to host the meetings and I'm sure we'll find more to suite people that need to travel. (Both meeting area's are minutes away from RDU airport if you want to do a mapquest to see how far of a ride it would be for you...)

So drop me a line. Let me know if you can see yourself as a "MAD PUG" member. (I can see the images of an elephant with giant teath being created as our official logo already...)

-Paul
pasilveira@gmail.com

I have recently wanted to play with linking multiple instances of PostgreSQL together and found myself wondering how to get multiple instances of PostgreSQL running on the same Windows server. I have documented my steps here...

This setup assumes you have installed a base instance via the .msi installer found on the Postgres.org website. If you tried to follow these instructions before you did a base install, some of the initial setup stuff won't be done. (creation of the postgres local users account for the OS, etc...)

Let's get started...

First, create a folder where you want to install the second instance of PostgreSQL. Ensure that the local postgres user id has sufficient privileges on that folder. (Read/Write)

Second, you'll want to open a command prompt as the postgres local user account. Right click on "command window" inside of your PostgreSQL install folder and choose "Run As". Enter in the postgres user id and password. You are now running as the postgres user id from within the command prompt.

Next, run the following INITDB command specifying the folder you want to install your second instance into. My location for this example was a 8.1-test folder...
initdb -D "C:\Program Files\PostgreSQL\8.1-test"

Now, you must modify the pg_hba.conf and postgresql.conf file as you usually would to ensure that you can access postgres. In a secure install, I would replace the trust setting with md5 but for this examle, this is fine.

pg_hba.conf
Here is an example of what could be entered…
host all all 127.0.0.1/32 trust


postgresql.conf
listen_addresses = '*'
port = 5433 # whatever port you choose on this…
log_destination = 'eventlog'



Next, run the following command to register the instance as a service in your services console. This will allow you to start and stop the service instead of keeping the dos window open...

Open another command prompt like you did above (For the initdb) but make sure you are using an ID that has administrative privileges. (You'll need admin privileges to register a service)
pg_ctl register -N "PostgreSQL 8.1.4 Instance-2" -U postgres -P PostgresOSPassword -D "C:\Program files\PostgreSQL\8.1-test"

You can now go to your services console and you'll see your named instance there. (In this example, it’s called "PostgreSQL 8.1.4 Instance-2")

Start the service.

Next Run Psql to connect to Postgres via command line. Remember to pass in the new Port that you've defined in your postgresql.conf file.

psql -Upostgres -p5433


Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#



Next, I like to modify the postgres user password. Run the ALTER USER command to supply the database Postgres ID with a password. Don't forget the semi-colon after the password.


postgres=# ALTER USER postgres WITH PASSWORD '';
postgres-# ;
ALTER ROLE
postgres=#




To de-install this instance you would run…

1) pg_ctl unregister -N "PostgreSQL 8.1.4 Instance-2" -U postgres -P

2) Delete the folder specified in your INITDB from the beginning of this exercise.


Hopefully, this helps you install your second instance with ease. Let me know if this helped...

Regards,

Paul

OK. So I did my first linux install in about 2 years using RHL ES 4.0. I've been spending so much time on the Windows platform that I forgot about how easy linux really was. After the default OS install, I installed Postgres and Webmin. (good old Webmin...)

The rest was a snap. I migrated a DB that I had on Windows 2003 and performed some benchmark tests and saw very similar performance. (actually slighly better. )

My impotus behind doing this was Slony-I. Slony runs on Linux well but does not run on Windows yet. Also Kerberos is supported on Linux but not on Windows.

I'll keep the posts coming with how I make out on installing both of those...

Regards,

Paul

I wanted to take a moment to document all of the tools that I have been using since I have been ramping up my usage of Postgres 8.1.4. These tools are mostly on the Microsoft Stack (Windows XP or Windows 2003) but I will eventually be extending these articles to included Linux as well.

- PGAdminIII
Ok. This was a no brainer. I wanted to take the time to pay my respects to all who have contributed to this Developers and Administrators tool because it is the defacto tool used by everyone. This tool has great coverage for most features and is installed as an option while performing the server install.

- PG Lighning Admin
I found this nifty tool one day when I was researching 3rd party tools for Postgres. At first, I thought it had lots of the same features that PGAdminIII had, but I soon found out that it was definitely worth the whopping 49 bucks.

Here is the URL...
PG Lightning Admin

Once you've installed it, take a look at the "Grant Wizard". PGAdminIII has a grant wizard but this one is on Steroids. It alone is worth the price of admission. The Development team is quick to fix bugs as well. Pretty cool to know that they are constantly making it better.

I like the ability to use the checkbox's to provide the granular permissions that I want. Really makes it easy to visualize...


- Aqua Data Studio
Aqua Data Studio

This tool has excellent OS Support
- Windows
- Linux
- OSX
- Solaris
- Java[tm]

And DB Support
PostgreSQL, Oracle, DB2 UDB, MS SQL Server, Sybase ASE, Sybase Anywhere, Informix (IDS), MySQL, Generic JDBC, Generic ODBC


And is nice to have around if you need to switch between RDBMS's to do compares and want to keep within one tool. This tool is free for Educational and Personal Use and is 149 bucks for a commercial license.


That's it for now. (Basically because it's midnight and I'm tired...)

I'll write up a Part II to this to talk about EMS products, as well as EnterpriseDB and an open source project that I have just started playing with called "Visual WebGui Enterprise Manager".

Visual WebGui Enterprise Manager

Here is the SourceForge location
SourceForge Location


This nice little Web Project integrates with "SQL Server" and "Postgres" which is exactly what I like to do.

Regards,

Paul

Hello,

I have been using PostgreSQL since the 7.x days but have really ramped up my usage this past year with the 8.x version. I mostly work in the windows environment so most of my posts will target users that need help in that domain. My articles will mostly be around scaling Postgres "Up and Out" on Windows Servers and integrating it with other RDBMS versions like Microsoft SQL Server and Oracle.

Regards,

Paul