Setup database server on ubuntu
From DreamsteepWiki
Complete install instructions to build a postgis server on Ubuntu , (lucid lynx 10.04, postgres 8.4)
For a command line only version go to
setup database via command line
| Return Notes_from_Keith |
|
Be aware this will install two separate postgres installs that may magically work together , and may need some configuring, you may want to simply install FGS and skip the rest (I havent tried JUST fgs yet, so I only know that I did it the way I am describing below and it works)
Step 1 - begin with a new Ubuntu install of 10.4
start with a fresh machine , install ubuntu 10.4 open update manager and run all updates, restart
Step 2 - install postgres and apache
edit software sources - set to ubuntu.osuosl.org (oregon state, This probably doesn't matter, I just am following the steps I took) go to Applications->ubuntu software center -> then search packages for "postgis" #Install the two packages geographic objects support for PostgreSQL 8.4 geographic objects support for PostgreSQL 8.4 - common files go to Applications->ubuntu software center -> then search packages for "postgis" #Install the following packages ApacheHTTP Server common files (not binary) multiuser MPM for Apache2.2
Server should be running now , if you need to restart for any reason
sudo /etc/init.d/apache2 restart
At this point you should be able to open a browser and go to http:/127.0.0.1/
and it should say "it works"
The next steps are optional, if you want to connect to the postgres database from an outside machine (via pgadmin) continue , if you plan on doing everything on one database machine then just go to step 3
Edit the postgres config and configure hosts
sudo gedit /etc/postgresql/8.4/main/pg_hba.conf
Change from ident to md5
# "local" is for Unix domain socket connections only local all all md5
at bottom of file add in each host , ip , subnet mask , and authentication
host all all 192.168.0.12 255.255.255.0 md5
Next you have to configure server to accept outside connections
sudo gedit /etc/postgresql/8.4/main/postgresql.conf
locate and change the two lines
- listen_addresses = ‘localhost’ :
- password_encryption = on :
TO
listen_addresses = ‘*’ password_encryption = on
Now server will accept outside connections
Step 3 - Install and configure php
Install the following package
php-cgi module for Mapserver (Although I am not using this version of mapserver , I use FGS )
Run this command to get postgres and php talking
sudo apt-get install php5-pgsql
Restart Apache to update internals
sudo /etc/init.d/apache2 restart
At this point make a simple php script to test it is working
docroot is /var/www , you will need root privileges
note that I have not used a root user so far , I have just sudo-ed with default user to allow root privileges just run "sudo gedit"
OPTIONAL - if you want to enable root user on the machine then run this and enter a pasword
sudo passwd root
save the following file as /var/www/foo.php
<html>
<body>
<?php
echo "hello cruel world!";
?>
</body>
</html>
Open a browser and go to
http://127.0.0.1/foo.php
It should say "hello cruel world"
Step 4 - get postgres talking to php
install pdadmin3
sudo apt-get install pgadmin3
If you need to make sure postgres server is running
sudo /etc/init.d/postgresql-8.4 restart
OPTIONAL - Make a system user "postgres" if it does not exist already, either use the GUI or run the command
su root adduser postgres passwd postgres
Next make a database just to test out stuff
su postgres createuser -D -A -P dbuser createdb -O dbuser mydatabase
You should be able to fire up pgadmin3 and connect to the database at this point
Now insert some data into the database
either use the psql command or the query tool in pgadmin3
CREATE TABLE cartoons(id serial,cartoon varchar(30)); CREATE TABLE characters(id int4,character varchar(15)); INSERT INTO characters(id,character) VALUES(1,'Shaggy'); INSERT INTO characters(id,character) VALUES(1,'Daphne'); GRANT SELECT,INSERT,UPDATE,DELETE on characters to dbuser;
Next make a test php file to check connectivity
save the following in /var/www as post.php
<?php
$DBNAME = 'mydatabase';
$USER = 'dbuser';
$PASSWORD = 'password';
$HOST = '127.0.0.1';
$dbconn = pg_connect("host=$HOST dbname=$DBNAME user=$USER password=$PASSWORD")
or die('Could not connect: ' . pg_last_error());
$query = "SELECT character FROM characters";
$search_db= pg_query($query);
$results= pg_num_rows($search_db);
if ($results < 1)
{
echo 'No Records Found';
} else {
while ($row = pg_fetch_array($search_db))
{
echo $row[0];
}
}//
pg_close($dbconn);
?>
open a browser and go to localhost/post.php
It should return a page that says:
ShaggyDaphne (no space between)
Step 5 - install postgis
sudo su postgres createlang plpgsql mydatabase psql -d mydatabase -f /usr/share/postgresql/8.4/contrib/postgis.sql psql -d mydatabase -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql psql -d mydatabase -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql
UPDATED SEPTEMBER 15 ,2010 - A NEW VERSION OF POSTGIS CAME OUT , CONSEQUENTLY THE FILES MOVED TO
/usr/share/postgresql/8.4/contrib/postgis-1.5
UPDATED SEPTEMBER 15 ,2010
Use pg admin and run the following query
SELECT * FROM postgis_full_version();
Step 6 - install FGS
Go get the FGS .bin file (I used the extended one)
For other versions go to :
http://www.maptools.org/fgs/index.phtml?page=downloads.html
install it with the sh command
These are the defaults (the best case is port 5432 , if it goes to 5433 you have to point FGS to that , see below)
From the FGS help page:
If you are installing this as a regular user Port 80 and any port under 1024 are restricted to root access
Here are some deafults my installer used , this seems to be different on different machines
# port 8080 # tcp port 5432 # /home/[user]/fgs #to access # http://127.0.0.1:8080/
OPTIONAL --- IF IT GOES TO PORT 5433 , EDIT THE FOLLOWING FILE AND CHANGE IT IN FGS $FGS/etc/fgs/pkgs/postgresql-server/pgsql.conf
PGHOME=/home/keith/fgs/apps/pgsql PGUSER= PGPORT=5432 PGDATA=/home/keith/fgs/apps/pgsql/data PGLOG=/home/keith/fgs/apps/pgsql/pgsql.log PGLIB=/home/keith/fgs/lib
IMPORTANT! Be aware there are TWO postgres servers running , the deafult one (port5432) and the FGS one , changing the above file sets FGS to use the default server, however , the steps above were performed in the DEFAULT server. When you type psql , it is running on the default postgres server.
Typing "/home/keith/fgs/bin/createdb" is not the same as typing "createdb" , (they are two separate commands)
make sure you restart the computer after editing fgs/pgsql.conf
if you get errors like "database does not exist" you are likely working under the wrong server IMPORTANT!
If you are not familiar with FGS , you have to set the environment to use the fgs command , you will need to do this each restart unless you set it to launch automatically , there are instructions for this on the FGS help pages
cd /home/keith/fgs/ . setenv.sh fgs start
TO TEST THE INSTALL OUT :
Move the post.php file from /var/www to /home/[user]/fgs/www/htdocs , (or /opt/fgs/www/htdocs if you installed as root)
http://127.0.0.1:8080/post.php
It should return the same result :
ShaggyDaphne
Now open the post.php file and change the query to test if postgis is enabled
<?php
$DBNAME = 'mydatabase';
$USER = 'dbuser';
$PASSWORD = 'password';
$HOST = '127.0.0.1';
$dbconn = pg_connect("host=$HOST dbname=$DBNAME user=$USER password=$PASSWORD")
or die('Could not connect: ' . pg_last_error());
$query = "SELECT postgis_full_version()";
$search_db= pg_query($query);
$results= pg_num_rows($search_db);
if ($results < 1)
{
echo 'No Records Found';
} else {
while ($row = pg_fetch_array($search_db))
{
echo $row[0];
}
}//
pg_close($dbconn);
?>
It should return :
POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
Congratulations , it works.
Server Setup Notes
For some other misc. notes :
