Monday, September 3, 2012

Installing a Headless PostgreSQL Server, Step-by-Step

Introduction

It isn't necessary to install PostgreSQL with its GUI frontend.  It runs just fine without it and consumes a smaller footprint.  If you're comfortable using the command line interface, installing headless is the way to go.  In some environments, it may even be necessary.  This step-by-step procedure shows you how.  This step-by-step installs an older version of PostgreSQL, 8.4.8-1, but is applicable to all.  It will install the postgres8_4-x86_64 package group, which contains three packages:
  • postgresql-libs-8.4.8-1PGDG.rhel5.x86_64.rpm
  • postgresql-8.4.8-1PGDG.rhel5.x86_64.rpm
  • postgresql-server-8.4.8-1PGDG.rhel5.x86_64.rpm
and it will create the following subdirectories:
  • /var/log/pgsql
  • /var/lib/pgsql
  • /usr/share/pgsql
  • /usr/lib64/pgsql
  • /etc/sysconfig/pgsq
This procedure was tested on a headless CentOS 5.5 enterprise server. Total installation time is about one hour.  This procedure concludes by providing steps for changing the database server password and adding new users.  Good luck!

Procedure

1) Open a terminal on the target server.

2) Log into the root account.

3) Install the PostgreSQL server package and its dependencies using YUM.  Be sure to disable gpg signature checking, in order to avoid annoying warnings:

[root]# yum install postgresql-server --nogpgcheck

4) Initialize the database.  Note that database initialization on RHEL (and thus also CentOS) will be somewhat different than other Linux flavors:

[root]# service postgresql initdb –D /var/lib/pgsql/data

Or, you may need to initialize the database using this approach:

[root]# /etc/init.d/postgresql start

5) Now, start the service:

[root]# /etc/rc.d/init.d/postgresql start

6) The default location on RHEL (and thus also CentOS) systems is /var/lib/pgsql.  However, applications such as bash will expect the location to be /usr/local/pgsql.  This can be fixed via soft symlink:

[root]# ln –s /var/lib/pgsql   /usr/local/pgsql

7) Check to make sure that the postgres account owns the pgsql directory and subdirectories:

[root]# ls –l /var/lib

If not, you will need to change ownership.  When changing ownership, do so recursively.

8) Test the account by logging into it and then performing stop and start commands:

[root]# su – postgres
-bash-3.2$ pg_ctl –stop
-bash-3.2$ pg_ctrl start

9) Stop the PostgreSQL instance so that you can edit configuration settings files:

[root]# service postgresql stop

10) Save a copy of the original configuration file:

[root]# cp /var/lib/pgsql/data/postgresql.conf   /var/lib/pgsql/data/postgresql.conf.ORIGINAL

11) Update the PostgreSQL configuration file to enable it to listen in on the server IP address at the default port:

[root]# vi /var/lib/pgsql/data/postgresql.conf

Uncomment the line “listen_addresses” and set it equal to ‘*’

Uncomment the “port” and leave it set to the default port (5432)

12) Save a copy of the original security access file:

[root]# cp /var/lib/pgsql/data/pg_hba.conf   /var/lib/pgsql/data/pg_hba.conf.ORIGINAL

13) Update the PostgreSQL security access file to enable client access to this PostgreSQL instance from within the subnet:

[root]# vi /var/lib/pgsql/data/pg_hba.conf

Add this line:

host all all 192.168.0.0/24  md5

Additionally, if you want to be able to use a GUI to interact with this postgresql instance over a VPN, you’ll need to identify the IP address that is received by the PostgreSQL instance and add this to the list of hosts allowed to connect.  For example, if your VPN IP address was “10.58.150.8”, then to allow only your “10.0.0.0” IP address to be able to connect you would add the following:

host all all 10.58.150.8/32  md5

14) Startup the postgressql service:

[root]# service postgresql start

15) This completes this PostgreSQL installation procedure.

Troubleshooting the Installation

If you encounter the following error, while trying to perform a YUM install…

Loaded plugins: fastestmirror, rhnplugin
Determining fastest mirrors
Could not retrieve mirrorlist
http://mirrorlist.centos.org/?release=5&arch=x86_64&repo=os error was
[Errno 4] IOError: <urlopen error (-3, 'Temporary failure in name resolution')>
Error: Cannot find a valid baseurl for repo: base"


this likely indicates that YUM on this server was attempting to check an external list using an external repository that your environment is not using.  The solution involves deleting all files in your external repository on the target machine.  For example, your external repository might be:  /etc/yum.repos.d/. 

Delete all the files in this repository and then try again.

Changing the Database Server Password

1) Open a terminal on the target server

2) Login to root

# sudo su - root

3) Super into the postgres account

[root] # su - postgres

4) Open a postgresql shell to the PostgreSQL service database

[root] # psql -d template1 -U postgres

5) Alter the postgres account’s password

[root] # ALTER USER postgres WITH PASSWORD 'postgres_password';

The password change take effect immediately.

6) This concludes this procedure.

Creating a New User

1) Open a terminal

2) Login to root

3) Login to the postgres account

[root] # su - postgres

4) Create new database user for Jive SBS, assigning the password immediately, then responding to the prompts as shown:

-bash-3.2$ createuser -P sbs
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n


The new user is available immediately.

5) This concludes this procedure

Summary

This step-by-step procedure walked you through the installation of a headless PostgreSQL database server onto a CentOS enterprise server.  For additional installation tips and details, consult the references below.  Happy Coding!

References

Sunday, September 2, 2012

TIP: Hiding DataView Web Part Checkboxes

Introduction

Its frequently the case that when you insert a Listview or Dataview web part onto a SharePoint 2010 page, you don't want the "item checkboxes" to appear.  The reason being that they can take up valuable space on the page and they can sometimes skew the appearance and placement of items in the list.  Removing this checkbox is easily accomplished, and there is plenty of discussion providing details for removing them by adding the "TabularView" parameter to the  <View > sub node of the <XmlDefinition> node and setting this parameter to "FALSE" [1, 2, & 3] like so:
<View Name=... TabularView="FALSE"...
However, there really is no need to do any code modification.  All you really have to do is to make a settings change to the list's application page, and then re-insert the Dataview web part; or make the settings change before you even insert the DataView web part, and then you won't have to fiddle with the web part again.

Discussion

By default, a view of a list, on the list's application page, provides checkboxes (shown at leftmost column) enabling multiple list rows to be selected.  By default, the tabular view is enabled.  This causes checkboxes to be displayed in the list. This allows, for example, for multiple list rows to be deleted simultaneously.  When you insert a DataView web part into a page, and then connect it to a list, that web part is configured per the list's default view. 

You can see this by looking at the <View> subnode of the dataview's <XmlDefinition> node.  This subnode incorporates all of the configuration parameters of the list's view.  Note the Name parameter: this is the object name of the view.  Listed here as subnodes of the <View> node, will be all of the fields displayed by the view.

Once you insert a DataView web part, SharePoint Designer takes a snapshot of the current view's properties, and then builds the web part code and inserts it into the ASPX page.  This is a static snapshot, not a dynamic one.  Once SharePoint Designer 2010 inserts the parameters for the web part, that's it: it doesn't do anything more.  The XSL for that web part is frozen.  Therefore, if you later make a change to the view that the web part connects to, such as changing the Tabular View parameter, in the view's Settings page, that change will not be reflected in the DataView web part.  To reflect that change in the web part, you will either need to adjust the parameter manually, by editing the code, or by re-inserting the web part.

Summary

This TIP showed you how to remove the checkboxes displayed next to items listed in a ListView or DataView web part.  Specifically, it discussed why the checkboxes appear and how to remove them either in code or in the list's view settings.  Happy Coding!

References

  1. XsltListViewWebPart – remove checkboxes
  2. Hide the item checkboxes and the select all checkbox in list view webparts
  3. Remove checkbox next to list items XSLT List view webpart Sharepoint 2010