Database‎ > ‎

PostgreSQL

PostgreSQL from Windows XP and Linux

Here is my current attempt of learning to use PostgreSQL runningon Linux from WindowsXP and SQLSever on WindowsXP. Now I'm using SQL Server on Windows Server 2008 at work.

Index

Setting up WindowsXP to use PostgreSQL running on a Linux box

While I was visiting Japan, I bought a Japanese computer magazine which had a special coverage on PostgreSQL (Software Design June, 2000).This magazine had various introductions to PostgreSQL and this magazineinspired me to try PostgreSQL. Since I was starting out as a novice, I picked a book called Practical PostgreSQL (by J.C. Worsley and J.D. Drake, O'Reilly & Associates, 2002). It came with a CD which had a sample database called booktown, eventhough the version of PostgreSQL is an older version.I also bought PostgreSQL (by Korry and Susan Douglas, Sams Publishing,2003). This book has the information on PostgreSQL version 7.3 and has more information on programming side of SQL.

Linux Preparation

Installing PostgreSQL on RedHat 9 was no-brainer. You checkSystem Setting → Application Add/Remove → SQL database server.You modify the startup script /etc/init.d/postgresqlwhere you modify to be the following

  
	su -l postgres -s /bin/sh -c '/usr/bin/pg_ctl -D $PGDATA \   
	-p /usr/bin/postmaster -o '-i -p ${PGPORT}' start > /dev/null 2>&1< /dev/null

where -i option allows clients to connect via TCP/IP. Without this,only local UNIX domain socket connections are accepted. Another way to dothe same thing is to edit /var/lib/pgsql/postgresql.conf. Uncommenttcpip_socket=false and change false to true.

One more thing you have to do is to change setting in /var/lib/pgsql/data/pg_hba.conf on RedHat 9.0. Here you set the permission for accessing PostgreSQL data. In particular, you set the permission for host for TCP/IP connection.I added my WindowsXP IP address (192.168.0.1) to host. You can read the details on how to set up this file at http://www.redhat.com/docs/manuals/database/. Note that this pg_hba.conf is read on startupand when the postmaster receives a SIGHUP signal. After editing, you do

   pg_ctl reload -D /var/lib/pgsql/data

Then I installed the sample database "booktown" which is on the CD fromthe book. Unfortunately, booktown.sql cannot be installed as is, sinceit refers to /usr/local/posgresql... /plpgsql.so. RedHat 9 installsthis shared library in /usr/lib/pgsql/plpgsql.so. Thus you copybooktown.sql into /tmp. First becomes root and then "su postgres".

   psql -U postgres template 1 -f /tmp/booktown.sql

WindowsXP Preparation

Next download PostgreSQL ODBC driver from http://gborg.postgresql.org/project/psqlodbc/projdisplay.php.Make sure that you pick Manage DSN and pick Unicode.Without Unicode, you cannot get connected to Visual Studio .NET.After install, check Control Panel → Administrative Tools → Data Sources (ODBC) → System DSN → Add. Create New Data Source. Pick PostgreSQL. More detailed info is here.

Testing the connection

Try access PostgreSQL running on Linux from Mircrosoft Access 2002. (I'm fortunate enough to have Access 2002.) Microsoft Access 2002 ismore tolerant on the driver. You can use non-unicode driver and Access worksfine with PostgreSQL, compared with Visual Studio requiring Unicode.

Pick File → New. Create a new database. Then you have a new menu appear in File menucalled "Get External Data". Click that to pick "Link Tables". Pick"Files of Type" as "ODBC database". Then another dialog "Select Data Source"pops up. Pick tab called "Machine Data Source" and pick PostgreSQL.(If you did not do the change required earlier in /var/lib/pgsql/data/pg_hba_conf, then you will get the same error I got: "ODBC-call failed. FATAL: No pg_hba.conf entry for host 192.168.0.1, user tosa, database booktown (#210)." After setting /var/lib/pgsql/data/pg_hba.conf on Linux side anddo "pg_ctl reload -D /var/lib/pgsql/data", I was able to link all booktowntables loaded on Microsoft Access 2002 and even edit the data.

Top

Using the DB from C# Web application

Make sure that your Linux running PostgreSQL is up.

Create a project

Start Visual Studio .NET. File → New → Project → New Project. Pick Visual C# Project and Select ASP.NET Web Application. Name the application as MyWebForm in the location box. In Solution Explorer, right-click the project name. Add → Add Component → Add New Item → Component Class. Click Open.

Add Data-Access Component

View → Toolbox → Data → OleDbDataAdapter . Drop it ontoWebform1.aspx. Data Adapter Configuration Wizard dialog opens up.Pick New Connection. Then Data Link Properties dialog opens up.Pick Provider as Microsoft OLE DB Provider for ODBC drivers. Click Next.Connection 1. Use data source name to be the one you named in the driver setup.2, User name and passwd. 3. Initial catalog to use "booktown" in my case.Then, click on "Test Connection". I got "Test Connection Succeeded".. However, when I click OK, I get

The connection you have created does "not" work with the current dataadapter.  Create a new connection not using the Microsoft OLEDB Providerfor ODBC drivers, or select a new data adapter that is compatible with your connection.

If your test connection works and still get this message (like I did notdo for the first time),

  • you have to pick PostgreSQL ODBC driver DSN option to be UNICODE.
  • you have to pick provider as OdbcDataAdapter, not OleDbDataAdapter(even though test connection works).

Now you get dialog called "Choose a Query Type". Pick Use SQL statements. Pick Query Builder.In Tables dialog, pick the table and click Add, then Close.In the Query Builder dialog, check the necessary query for the table.If you click OK, you get odbcDataAdapter1 and odbcConnection1 added tothe form.

An alternative to the above, you can use the server explorer.Right-click Data Connections. Pick Provider to be Microsoft OLEDB Provider for ODBC Drivers.

Click Next. Now we are seeingConnection page. Pick Use data source name as PostgreSQL.Set User name and password. Initial catalog to use is booktown in mycase. Do click Test Connect. Works! Now I see 192.168.0.8.tosaappears in Data Connection in Server Explorer view. Dragging one of the tablesto the web page will do the same thing as the previous paragraph explained.Interestingly, if you had picked non-Unicode version of the driver, thenyou cannot drag one onto the table.

Right click on the odbcDataAdaptor1 and pick Generate DataSet.It will show you the table you picked. Make sure to check "Addthis dataset to the designer".

On Linux PostgreSQL side, locale of /var/lib/pgsql/initdb.i18n must match that of /var/lib/pgsql/data/postgresql.confIf they are different, /etc/init.d/postgresql fails to start.

Top

What is the connection string for ODBC SQL?

Unfortunately the automatic method does not exist for creating ODBC connectionto MS SQL server. The on-line help is not quite to the point.

        new OdbcConnection("Driver={SQL Server};Server=UDON\\UDONSQL;Trusted_Connection=yes;database=pubs");

where Server is the one which appear in the server explorer.

Top

Setting up an ODBC data source for PostgreSQL for WinXP

Step by step pictures. Start Menu &rarr Control Panel &rarrData Source (ODBC). Pick the tab for System DSN.

Press Add button

Pick PostgreSQL Unicode [Beta]

Fill out empty boxes and Save. Now your new DSN is in.

Top

Why PostgreSQL cannot use my own shared library?

One of the usefulness of PostgreSQL is that user can create own functions asshared libraries to be loaded into PostgreSQL. When I did that, I get

     stat failed on '/path/myshared.so': Permission defined

No book talks about this problem. I finally found the answer! after three days of evenings to look around why. The answer is:

     RedHat 9 user top directory permission is drwx------

You have to change the directory permission to 755 so that postmaster canread the directory.

Top

How can I debug my server extension C code to PostgreSQL?

This information came from Korry Douglas, one of the authors of PostgreSQL (Sams Publishing, 2003). Thank you, Korry.

This is done on the Linux side (server).Make sure you compile your C code with -g. Let us call it "filesize.so". I added the path to this shared library to /var/lib/pgsql/data/postgresql.conf:

    dynamic_library_path='$libdir:/home/tosa/pgsqlDev/lib'

Start your postgresql serverby

     psql -d movies -f /usr/share/pgsql/contrib/misc_utils.sql

This will load utilities into movies database, one of them is called backend_pid(). Now start your server again:

     psql -d movies     movies=# load 'filesize.so';     movies=# select backend_pid();      backend_pid     ------------             2378

Actually you don't need the utilities, but do ps aux | grep postgres.It will come up with four postgres owned processes, one of them is "postgres: tosa movies [local] idle". That is the backend server process. Note that it is owned by postgres, not you, nor root.You don't have to issue "load the shared library" if you create $HOME/.psqlrc file which contains

     load 'filesize.so';     select backend_pid();

Now we are ready to debug. I use ddd for debugging, since it is aGUI debugger. Note that the backend server is owned by postgresand thus you have to be postgres or root to attach to the process. Unfortunately the power of "postgres" is quite limited. One of them isthat it cannot open display. Therefore, I became root.

      $ su root      passwd:      # ddd /usr/bin/postgres 2378

You will see many shared libraries listed, one of them is filesize.sowhose symbol is loaded. Note that if you did not load 'filesize.so', then you cannot debug ;-) Now I set a break point in filesize.c inddd command window:

      break filesize.c:18      continue

Now you can type SQL command in psql console. In particular, if youtry to run your function like

      movies=# SELECT filesize('/bin/bash');

ddd will show you your source code and will break at the break point.

Top

How can I set up unixODBC on Linux?

Linux can talk ODBC (Open Database Connectivity) also.

Download source code from http://www.unixodbc.org/. Mine was version 2.2.6.www.rpmfind.net found rpm for RedHat9 whose version was 2.2.3.

There is a gui to set up ODBCConfig. Unfortunately, when itopens up, everything was blank. First setup driver. Click Driver taband click Add. Name and Desription can be anything. Drivermust be /usr/lib/libodbcpsql.so and Setup must be /usr/lib/libodbcpsqlS.so.

Next pick SystemDSN and click Add. Here you fix the name of Data Source (DSN) for a particular database and its port.PostSQL uses port 5432.

Top

Why do I get "Driver not found for URI" error for JDBC?

This is the case of irresponsibility on RedHat.RedHat8 and 9 installs GNU gcj and gij as /usr/bin/javac and /usr/bin/javaif you pick gcc-java option. Therefore, you think that it is compatible with SUN version of Java. Unfortunately they are not if you deal with JDBC. The error indicates that your java program is using gnu version of java and its libraries. It is so confusing that you'd better remove gnu java. I wasted two days on this. Note that even ifyou remove gcc-java option, the library libgcj*.so stays!

Top

Why am I getting "Exception in thread "main" java.lang.NoClassDefFoundError" error?

Read the error message carefully. It is saying that the class cannotbe found. That is, CLASSPATH must include the current directory.All the examples I see in books do not do this. Don't forget "." inCLASSPATH. Note that you don't have to add SUN's jre.jar to CLASSPATH.For postgresql, you'd better do

For cshell   setenv CLASSPATH .:/usr/share/pgsql/postgresql.jarFor bash   export CLASSPATH=".:/usr/share/pgsql/postgresql.jar"

You could put your own jar file in "path_to_jre"/lib/extdirectory and never set CLASSPATH. In particular, copy or linkpostgresql.jar to this directory.

Top

Home

Updated 9/14/2009