In the forth in the Linux server setup articles, you will add PostgreSQL to your server. Be sure to check out the first 3 articles, including: Setting up a Linux Rackspace server for WX, Setting up HyperFileSQL and SCM on a Linux Server, and Setting up the WebDev Application Server on a Linux Cloud Server

The PostgreSQL package available from the standard respositories for Centos is fairly old. So the first step is to setup YUM so it will install PostgreSQL from the PostgreSQL.org repository. The first step is to tell you to ignore PostgreSQL packages from the standard repositories. So use VI to edit the repository file with this command

vi /etc/yum.repos.d/CentOS-Base.repo

And place the following line in both the [base] and the [updates] section

exclude=postgresql*

Now download and install the correct RPM file from PostgreSQL.org. You are going to install version 9.2 so the commands are

curl -O http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm
rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

Now you can install PostgreSQL with this command

yum install postgresql92-server

At this time pcSoft does not provide a native PostgreSQL driver for Linux. Please send a request to support letting them know how much you would like them to so. Until they do, you will be using ODBC to connect to the database from WebDev so go ahead and install the PostgreSQL ODBC drivers with this command.

yum install postgresql92-odbc

Once installed you need to initialize the maintenance database with this command

service postgresql-9.2 initdb

Edit the PostgreSQL configuration file with

vi /var/lib/pgsql/9.2/data/postgresql.conf

And set it up to listen to all the IP addresses of the machine on port 5432 by uncommenting and editing the below lines.

listen_addresses = '*'
port = 5432

Setup the connection premissions for PostgreSQL by editing the pg_hba.conf file with this command

vi /var/lib/pgsql/9.2/data/pg_hba.conf

And adding the following lines to the file

local all     postgres                     peer
local all     all                          md5
host  all     all         127.0.0.1/32     md5
host  all     all         0.0.0.0/0        md5

The first line allows the postgres user to log in from the terminal of the machine with out a password, this is similar to a “trusted” connection. The next line requires all other users attempting to log in from the terminal to provide a password and the password will use md5 encryption. The 3rd line requires any user connecting via the TCP loopback interface to provide a password (this is the connection that WebDev will be using), and the final line requires any remote connections to provide a password as well. Start the service with this statement

service postgresql-9.2 start

And make sure that PostgreSQL will restart when the server is rebooted by issuing this command.

chkconfig postgresql-9.2 on

Now you need to open up port 5432 on the firewall so you can access the database remotely. Edit the /sbin/myfirewall file created in the first article and add these lines

# PostgreSQL
iptables -t filter -A INPUT -p tcp --dport 5432 -j ACCEPT
iptables -t filter -A OUTPUT -p tcp --dport 5432 -j ACCEPT

And restart the firewall by running the script

myfirewall

The next step is to setup a user to use for remote administration. Start by impersonating the postgres user with the following command, with will bring up a bash session.

su postgres

Next log into the PostgreSQL command interface. Since you are impersonating postgres, which is using peer authentication, you are not required to provide a password.

psql

Now create the user with the following command, you might want to use a more original password!

create role psqladmin with superuser login password 'password';

Be sure to include that ; on the end of the line. The statement is not execute in PostgreSQL until it hits a ; or a g. And now you can exit from the PostrgreSQL interface with this command

q

And then you can exit from the bash session and return to the terminal with this command.

exit

Initial setup is now completed. You will be tweak a few more parameters later, but for now you can exit the terminal and setup PostgreSQL on your development machine and test a remote connection.

Setting up PostgreSQL on your Development Machine

Technically you only need the pgAdmin III tool on your development machine, but you will probably want to keep test databases on your development machine anyway, so these step will take you through a full install of both the client and server software. You can download the one click installer from “http://www.enterprisedb.com/products-services-training/pgdownload#windows”. Once downloaded, run the installer, which will launch a wizard.

Click Next, and you will be asked for the install location, take the default.

And then the data directory, again take the default.

And next is the password for the postgres user, which is the initial administrator for the server.

Next is the port the server will listen on. The default is 5432.

Next is the Local. This is a very advanced subject, you can get some information from my case sensitivity articles. Unless you have a specific reason take the default.

The next screen lets you know the wizard is completed and the install is about to begin, click next.

When the install completes you are given an opportunity to install Stack Builder, which will make it easier to install add-ons and updates later.

After Stack Build Installs it will run. For now just cancel out of it.

You will need to make similar changes to the configuration to allow connections to the local sever from the outside world if you need to. For your current purposes, all you need is the pgAdmin III tool, so run it. You will see that it has one sever configured, which is the local server. To add a new server click the first icon, that looks like an electrical plug (hey don’t ask me I don’t design this stuff!).

Fill in the appropriate host, port, username and if you don’t want to provide a password each time, fill that in as well and check the “Store password”.  If you double click on the server you will see the databases on the server, which at this point is just postgres (the maintenance database).

Creating a Login and Database for Testing

Go ahead and create a user to use to test the ODBC connection by right clicking on Login Roles, and choosing New Login Role from the popup menu.

Fill in the name on the first tab

The password on second tab.

Click OK to create the login, now create a database to use to test the ODBC connection by right clicking on Databases, and choosing New Database from the popup menu.

On the first tab enter the name of the database to create and from the owner drop down chose the login you just created. Then click OK.

You will now see the new database in the Object browser pane on the left.

Click on the test database, when you do the SQL button on the toolbar is enabled. Click on it to start a query session.

Now for a quick configuration tip, notice that the editor window shows the script to create the database. Whatever object you have selected when you start a Query session will be copied into the editor window.

I suppose that might be a handy shortcut for someone but I find it pretty irritating. To disable this “feature”, close the editor window, which returns you to the pgAdmin. Then chose Options from the File menu. This brings up a configuration window, click on Query editor in the Query tool section and uncheck the box labeled “Copy SQL from main window to query tool”

Now the Query editor will open up without any pre-filled text. Create a table and some sample data with the following script.

CREATE TABLE customer
(
 custsysid serial NOT NULL,
 firstname character varying(20),
 lastname character varying(20),
 CONSTRAINT pkcustomer PRIMARY KEY (custsysid)
);

insert into customer(firstname,lastname) values('John','Jones');
insert into customer(firstname,lastname) values('Steve','Smith');
insert into customer(firstname,lastname) values('Suzy','Johnson');
insert into customer(firstname,lastname) values('Sally','Williams');
insert into customer(firstname,lastname) values('Mary','Brown');
insert into customer(firstname,lastname) values('Bob','Davis');

grant all on customer to test;

Now you can close the SQL session to return to the pdAdmin and then click on the local host server, and repeat the same steps to create the login and database on the local server so you can test locally as well. When all done you should have both a test user and database both on the local server and the Linux server.

Now you need to configure an ODBC data source. More than likely your development machine is a 64bit machine, and Microsoft did a horrible job of giving both 32bit and 64bit ODBC configuration interfaces, but that is a conversation for another article. If you haven’t had to deal with this Google will be your friend. To configure the data source run C:windowssyswow64odbcad32.exe, not the 64 bit one that Windows offers to you via the control panel.

The tool opens in the User DSN tab. If you define a data source on this tab it will only be available to the user you are current logged into windows as. So switch to the System DSN tab instead so that the data source will be available globally.

Click Add and select PostgresSQL ODBC Driver (UNICODE) driver from the list and click Finish

In the configuration screen that comes up next enter the following:

Data Source = test    <— Be careful with this entry as it is what will be reference for your connection
Database = test          <— This is the database you just created
Server = localhost    <– Since you are setting up a test for the local server
User Name = test     <– This is the login you just created – for production you would leave this blank so that it will be required at runtime
Password = *****    <– Enter the password that you used when you created the login, again this would be blank for production.

If you configured everything correctly, pressing the Test button should produce a successful result.

If you save the ODBC connection as is, then anyone would be able to access the database via the data source. So remove the User Name and Password before saving, so that the application is required to provide them then click Save and the new DSN will be shown in the list.

Click OK to create close the ODBC administrator.

Creating a Simple WebDev Project to Test with.

Now its time to create a simple WebDev project to test your PostgreSQL and ODBC connections. You could use the native PostgreSQL drive on the development machine and if you are using a Windows WebDev WAS the native driver would also be avaialbe. Since this series of article was able setting up a Linux WebDev WAS, the native driver is not available, so it makes sense to use an ODBC connection on the development machine as well.

Start WebDev and create a new Internet Site project.

Which starts the project creation wizard. On the first panel it wants to know the name of the project and where it will be located on the development machine. Call it psqlodbc and press next.

Then next panel allows you to attach support documents, just click next. Next is the Creation mode, choose Create a blank project and click next.

Next next panel is for type of site, chose Dynamic WebDev site and click next.

Next is project estimate dates, I think we will be able to finish this project quickly, so just click next :-). Which will bring you to the SCM setup, this is just a simple test project so no need to put it in the SCM, so just choose No, do not use SCM and click next.

Next is the programming charter, just take the default and click next. Which brings you to the Project Skin panel, choose the iStyle skin and click next.

The next panel is for the Language of the project. There are a couple of different theories in the community on this, what I prefer to do is, click Add and chose American

Which returns you to the Project Languages and you now have both English and American with English being the default. Select English and click delete, which will remove it and make American the default. Then click next.

The next panel is the database configuration, chose Yes, use an existing database and click next.

The next screen gives you the opportunity to use a Analysis that has already been created, since this is a new project and database, chose import from existing database and click next.

This will launch a new wizard for the Analysis creation. The first panel ask for the Analysis name and location, the defaults will name it the same as the project and store it in the same folder. Click next.

The next panel wants to know the type of databases being used, unclick HyperFile classic and click PostgreSQL. Then click next.

The next panel gives you the opportunity to name you connection. Name as you like and press Next.

And next panel wants to know if you will be using the native connection or ODBC. I have had trouble with importing tables in ODBC mode, so select Native now and you will change the connection later. Note: the Native PostgreSQL driver for Windows is a free download from Windev.com. 

The next panel wants to know the name of the server. Enter LocalHost and press next.

And the next panel request the user name and password, enter the login information you created earlier.

And the next panel is requesting that database name, enter the database name and click next.

And the next screen is asking if you want to convert the data or use it in its current format. Chose current format and click next.

The next panel will show you a list of tables to import. When it first opens there are a quite a few system items in the list, uncheck the Show the system tables box, and the list will just show the tables user tables. Select the customer table and use the right arrow button to move it to the list of tables to import and press next.

Now the It’s Done panel will be show click next.

A few screens will flash by as WebDev configures everything for your new project and then it will show you the what do you want to do? panel.

Click on WebDev editor, which will bring you into the WebDev IDE with the Analysis open. You will see the customer table has been created, however it shows as a PostgreSQL table.

To change the connection to an ODBC connection, chose Connections from the Analysis menu.

And you will see the PsqlodbcConnection you just created.

Change the Connection by drop down to ODBC Access via OLEDB. Which will blank out the Data Source drop down as well.

Now for what appears to be another little bug in the interface, the Data Source drop down doesn’t show any selections. But if you click the … button next to the drop down, then click cancel on the window that pops up. Now the drop down has choices, select the data source created earlier. Note: This bug seems persistent if you later come back to the Analysis the connection will show a blank data source, but it works fine.

Click the green check mark to save the changes and answer yes to the confirmation message that pops up. And now the customer tables shows that it is an OLEDB table.

Now create a new blank page for the site. And from the Analysis pane drag the customer table onto the page to create a table control showing the customer table.

x

[suffusion-the-author display=’author’]

[suffusion-the-author display=’description’]

 

• WX