PostgreSQL

Apache

Samba

VNC

BakNRot

CakePHP






PostgreSQL

PostgreSQL is available on most Linux installations, as well as other platforms. PostgreSQL is open source software, and is free (free as in freedom, and free as in beer). It is a powerful enterprise-class relational database server comparable to Oracle databases.

PostgreSQL is often compared to MySQL, another open source database server. This is an apples and oranges comparison. MySQL is slightly faster (for what it does). PostgreSQL is more mature and reliable. MySQL is slightly easier to jump into. PostgreSQL has considerably more critical features required by business-class applications. Both cost the same (free). Both are widely supported.

PacStates' position is simple: Use real tools for your business. Don't waste money on core tools that will probably need to be upgraded and rewritten as your application grows. We are not alone in this position. Big Boys like IBM and Red Hat bet on PostgreSQL when choosing a database for THEIR enterprise product offerings.

For almost all small to medium business application development, PacStates recommends PostgreSQL deployed on a solid Linux server. One notable exception would be if PostgreSQL is incompatible with an existing critical application (which is rare).


Setting Up The PostgreSQL Server
These instructions are from the server side. Where applicable, two different instruction sets are available: Command Line and GUI. Use whichever you are more comfortable with (but not both).

Instructions Command Line GUI
Set PostgreSQL to autorun on startup, and start it. As root from a TERMINAL WINDOW:
  • /sbin/chkconfig postgresql on
  • /sbin/service postgresql start
  • Click on Desktop|System Settings|Server Settings|Services
  • May need to enter the root password
  • Scroll down to "postgresql"
    • Check box to run upon startup
    • Click on [save] to save settings
    • Click on [start] to start PostgreSQL
Enable database access from any network interface.

Edit the postgres configuration file:
/var/lib/pgsql/data/postgresql.conf

Within the file, modify the following lines to allow network access to PostgreSQL. (Uncomment the "listen_addresses" and "port" parameters.) This accepts connections from any interface on port 5432.

ORIGINAL:

# - Connection Settings -
#listen_addresses = 'localhost'	# what IP interface(s) to listen on;
				# defaults to localhost, '*' = any
#port = 5432
MODIFIED:
# - Connection Settings -
listen_addresses = '*'		# what IP interface(s) to listen on;
				# defaults to localhost, '*' = any
port = 5432

Enable database access from specific hosts/networks.

Edit the postgres host based access configuration file:
/var/lib/pgsql/data/pg_hba.conf

At the end of the file, modify the following lines to use "trust" based authentication rather than "identity". Two example networks are used below (10.0.0.0/24 and 192.168.1.0/24), as well as the localhost (127.0.0.1).

ORIGINAL:

#TYPE		DATABASE	USER	CIDR-ADDRESS	METHOD
#"local" is for Unix domain socket connections only
local		all		all			ident sameuser
#IPv4 local connections:
host		all		all	127.0.0.1/32	ident sameuser
#IPv6 local connections:
host		all		all	::1/128		ident sameuser
MODIFIED:
#TYPE		DATABASE	USER	CIDR-ADDRESS	METHOD
#"local" is for Unix domain socket connections only
local		all		all			trust
#IPv4 local connections:
host		all		all	127.0.0.1/32	trust
host		all		all	10.0.0.0/24		trust
host		all		all	192.168.1.0/24	trust
#IPv6 local connections:
host		all		all	::1/128		trust

Restart the database service to use new settings. As root from a TERMINAL WINDOW:
  • /sbin/service postgresql restart
  • Click on Desktop|System Settings|Server Settings|Services
  • May need to enter the root password
  • Scroll down to "postgresql"
    • Click on "postgresql" to select
    • Click on [restart] to restart PostgreSQL server


Setting Up A PostgreSQL Database
These instructions are from the server side, but testing should be run on all intended client hosts. Instructions are for a command line interface.

Instructions Terminal Command Line
Create a PostgreSQL user
Login as the system "postgres" user (you may need to be root)
su - postgres
(the dash is important!)
Run the postgresql command line interactive tool
psql template1
(template1 comes with PostgreSQL)
Create a user (Example creates User: dev, Password: developer)
create user dev password 'developer';
(trailing semicolon is important!)
Create a PostgreSQL database
Create a database (Example creates Database: devdb, Owner: dev)
create database devdb owner dev;
(trailing semicolon is important!)
Exit the postgresql command line interactive tool
\q
exit
RECORD THE DATABASE NAME AND USER NAME/PASSWORD!! DO NOT LOSE THESE!!
Test your connection
Login with psql client.
Assumes server is at 10.0.0.99, user is "dev", and database is "devdb"
As root from a TERMINAL WINDOW:
psql -h 10.0.0.99 -U dev devdb
Test this from each approved host/network, as well as the server itself.
Following the above steps, the database location is: /var/lib/pgsql/data.
And because it bears repeating:
RECORD THE DATABASE NAME AND USER NAME/PASSWORD!! DO NOT LOSE THESE!!
You have been warned.

 
About Pacific States Systems Company | Services | Solutions | News Alerts | Request Information | Contact Us | Admin Panel