PostgreSQL

From GISWiki

Jump to: navigation, search

Contents

Description

PostgreSQL is an object-relational database management system (ORDBMS). It is released under a BSD-style license and is thus free software. As with many other open-source programs, PostgreSQL is not controlled by any single company, but relies on a global community of developers and companies to develop it.

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium.

PostgreSQL is a powerful, open source relational database system. It has more active development since 1989 and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. Some general PostgreSQL limits are included in the table below.

Installationscript

See http://www.postgresql.org/docs (PostgresSQL-Homepage) for Installations manuals This Skript is just a little helper and has been used under SuSE Linux 10.3 and Postgres 8.3.3.

Requirements

  • GNU make (gmake)
  • ISO/ANSI C compiler (gcc)
  • Tar
  • GNU Readline libary
  • Zlib compression library

Skript

#!/bin/bash
echo ============================================================================================
echo "Installationscript PostgreSQL - PostgreSQL Installationskript " 
echo ============================================================================================
 
echo ""
echo "Variables"
echo "--------------------------------------------------------------------------------------------"
WGET_PGSQL_FILENAME=postgresql-8.3.3
WGET_EXT=.tar.gz
WGET_PGSQL_PATH=http://ftp.de.postgresql.org/mirror/postgresql//source/v8.3.3/$WGET_PGSQL_FILENAME$WGET_EXT
DIR_BASE=/usr/local/src
SUBDIR_DOWNLOAD=pgsql
DIR_DOWNLOAD=$DIR_BASE/$SUBDIR_DOWNLOAD
DIR_EXTRACT=$DIR_DOWNLOAD/$WGET_PGSQL_FILENAME
 
echo "WGET_PGSQL_FILENAME : "$WGET_PGSQL_FILENAME
echo "WGET_EXT            : "$WGET_EXT
echo "WGET_PGSQL_PATH     : "$WGET_PGSQL_PATH
echo "DIR_BASE            : "$DIR_BASE
echo "SUBDIR_DOWNLOAD     : "$SUBDIR_DOWNLOAD  
echo "DIR_DOWNLOAD        : "$DIR_DOWNLOAD
echo "DIR_EXTRACT         : "$DIR_EXTRACT
 
echo ""
echo "Prepare directories"
echo "--------------------------------------------------------------------------------------------"
cd $DIR_BASE
if [ ! -d $SUBDIR_DOWNLOAD ]; then 
	mkdir $SUBDIR_DOWNLOAD ; 
else
	echo "Directory for installation ["$SUBDIR_DOWNLOAD"] allready exists";
fi
echo "change to directory" 
cd $SUBDIR_DOWNLOAD
 
echo ""
echo "Download PostgreSQL file "
echo "--------------------------------------------------------------------------------------------"
echo "From: "$WGET_PGSQL_PATH
echo "To "$DIR_DOWNLOAD$WGET_PGSQL_FILENAME$WGET_EXT
if [ ! -f $WGET_PGSQL_FILENAME$WGET_EXT ]; then 
	wget $WGET_PGSQL_PATH; 
else
	echo "Downloadfile ["$WGET_PGSQL_PATH"] allready exists.";
fi
 
echo ""
echo "Extract content to "$WGET_PGSQL_FILENAME 
echo "--------------------------------------------------------------------------------------------"
if [ ! -d $WGET_PGSQL_FILENAME ]; then 
	tar xvf $WGET_PGSQL_FILENAME$WGET_EXT; 
else
	echo "Directory ["$WGET_PGSQL_FILENAME"] allready exists"; 
	echo "Extraction canceled (assuming allready done)";
fi
 
echo ""
echo "Change to directory "$WGET_PGSQL_FILENAME
echo "---------------------------------------------------------------------------------------------"
cd $WGET_PGSQL_FILENAME
echo "File operations finished"
 
echo ""
echo "install see -> http://www.postgresql.org/docs/8.3/static/install-short.html"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to continue configure"
read tmp 
 
./configure
 
 
echo ""
echo "configure fini
shed"
echo "gmake"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to continue to gmake"
read tmp 
gmake
 
 
echo ""
echo "gmake finished"
echo "gmake install"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to continue to gmake install"
read tmp 
#su
gmake install
 
echo ""
echo "'gmake install' finished"
echo "adduser postgres"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to continue to adduser postgres"
read tmp 
useradd postgres
 
 
echo ""
echo "adduser postgresl finished"
echo "create postgres directories"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to create postgres directories"
read tmp 
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
mkdir /var/lib/pgsql
chown postgres /var/lib/pgsql
 
 
echo ""
echo "create postgres directories finished"
echo "run tests"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to run tests"
read tmp 
 
su - postgres
 
echo ""
echo "create postgres directories finished"
echo "initdb -D /usr/local/pgsql/data"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to run initdb"
read tmp 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
 
echo ""
echo "initdb finished"
echo "logfile"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to run /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &"
read tmp 
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
 
echo ""
echo "logfile finished"
echo "createdb test"
echo "---------------------------------------------------------------------------------------------"
echo "Press return to run createdb test step 1"
read tmp 
/usr/local/pgsql/bin/createdb test
echo "Press return to run createdb test step 2"
read tmp 
/usr/local/pgsql/bin/psql test

Configuration (Post-Setup)

  • For having easy access to postgres commands you should add these lines to /etc/profile.local (SuSE specific)) 
PATH=/usr/local/pgsql/bin:$PATH
 export PATH
 MANPATH=/usr/local/pgsql/man:$MANPATH
 export MANPATH

Weblinks

External links

About PostgreSQL

Documentation

Performance tuning documentation

External projects

The developers of PostgreSQL try to keep the system itself down to a set of "core" features, rather than encouraging extensions to be rolled into the main system. Here are places where "secondary" projects are managed:

Retrieved from "http://www.en.giswiki.org/wiki/PostgreSQL"

Support GISWiki !


 

 

 
Personal tools
other languages