Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostGres 9.2/PostGIS 2.1 install notes Ubuntu 13
--FROM http://blog.mackerron.com/2012/06/01/postgis-2-ubuntu-12-04/
mkdir -p src
# First install PostgreSQL 9.2, plus contributed packages and any missing prerequisites
# ===
# add the Postgres PPA
echo 'deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' \
| sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \
| sudo apt-key add -
sudo aptitude update
# the following is necessary on 13.04 (and possibly 12.10?)
sudo aptitude install postgresql-common --target-release raring
# then
sudo aptitude install postgresql-9.2 postgresql-server-dev-9.2 postgresql-contrib-9.2 \
build-essential checkinstall libjson0-dev libxml2-dev libproj-dev python2.7-dev swig
# Now for GEOS, GDAL and PostGIS
# ===
# download and compile geos in /opt/geos
cd ~/src/
wget http://download.osgeo.org/geos/geos-3.4.1.tar.bz2
tar xvjf geos-3.4.1.tar.bz2
cd geos-3.4.1/
./configure --prefix=/opt/geos --enable-python
make -j2
sudo checkinstall # uninstall with: sudo dpkg -r geos
# download and compile gdal in /opt/gdal
cd ~/src/
wget http://download.osgeo.org/gdal/1.10.0/gdal-1.10.0.tar.gz
tar xvzf gdal-1.10.0.tar.gz
cd gdal-1.10.0/
./configure --prefix=/opt/gdal --with-geos=/opt/geos/bin/geos-config \
--with-pg=/usr/lib/postgresql/9.2/bin/pg_config --with-python
make -j2
sudo checkinstall # uninstall with: sudo dpkg -r gdal
# download and compile postgis in default location
cd ~/src/
wget http://download.osgeo.org/postgis/source/postgis-2.1.0.tar.gz
tar xvzf postgis-2.1.0.tar.gz
cd postgis-2.1.0/
./configure --with-geosconfig=/opt/geos/bin/geos-config \
--with-gdalconfig=/opt/gdal/bin/gdal-config
make -j2
sudo checkinstall # uninstall with: sudo dpkg -r postgis
# for command-line tools:
echo 'export PATH="$PATH:/opt/geos/bin:/opt/gdal/bin:/usr/lib/postgresql/9.2/bin"' >> .bashrc
# so libraries are found:
echo '/opt/geos/lib
/opt/gdal/lib' | sudo tee /etc/ld.so.conf.d/geolibs.conf
sudo ldconfig
# restart postgres
sudo /etc/init.d/postgresql restart
# restore a pg_dump -Fc backup from an earlier PostGIS version
echo 'create database mydb;' | sudo -u postgres psql
echo 'create extension postgis; create extension postgis_topology;' \
| sudo -u postgres psql -d mydb
--Make postgres user/pass
sudo -u postgres psql postgres
\password postgres
--enter password twice
Ctrl-D to exit prompt
--create DB
sudo -u postgres createdb mydb
--Alter Ubuntu's postgres password
sudo passwd postgres
--enter new password
--Update pg_hba.conf to allow external connections
su - postgres
cd /etc/postgresql/9.2/main
pico pg_hba.conf
--Allow SpatialDev router connections
-add
host all all 0.0.0.0/0 md5
--Update postgresql.conf
pico postgresql.conf
--Uncomment the listen_address = "localhost" to "*"
--restart postgres as ubuntu user
sudo /etc/init.d/postgresql restart
--OPEN PORT 5432
sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
In PGAdmin, create fspreader as readonly user.
Then:
-- Grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fspreader;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT
ON TABLES
TO fspreader;
-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO fspreader;
ALTER DEFAULT PRIVILEGES
IN SCHEMA public -- omit this line to make a default across all schemas
GRANT SELECT, USAGE
ON SEQUENCES
TO fspreader;
--Allow readonly user to access topology schema
-- Grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA topology TO fspreader;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
IN SCHEMA topology -- omit this line to make a default across all schemas
GRANT SELECT
ON TABLES
TO fspreader;
-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA topology TO fspreader;
ALTER DEFAULT PRIVILEGES
IN SCHEMA topology -- omit this line to make a default across all schemas
GRANT SELECT, USAGE
ON SEQUENCES
TO fspreader;
GRANT SELECT ON ALL TABLES IN SCHEMA nigeria_states TO fspreader;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
IN SCHEMA nigeria_states -- omit this line to make a default across all schemas
GRANT SELECT
ON TABLES
TO fspreader;
-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA nigeria_states TO fspreader;
ALTER DEFAULT PRIVILEGES
IN SCHEMA nigeria_states -- omit this line to make a default across all schemas
GRANT SELECT, USAGE
ON SEQUENCES
TO fspreader;
GRANT USAGE ON SCHEMA topology TO PUBLIC;
GRANT USAGE ON SCHEMA nigeria_states TO PUBLIC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment