Debian 10 GIS Server: Unterschied zwischen den Versionen

Aus Geoportal
 
(16 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 11: Zeile 11:
 
apt-get -y install curl dos2unix htop mlocate p7zip unzip vim mc
 
apt-get -y install curl dos2unix htop mlocate p7zip unzip vim mc
 
apt-get clean
 
apt-get clean
  +
</pre>
  +
== Install further helpful things ==
  +
<pre>
  +
apt-get -y install apache2 libapache2-mod-php cgi-mapserver postgresql-11-postgis-2.5 gdal-bin postgresql-11-ogr-fdw php-xml libproj13
  +
apt-get clean
  +
</pre>
  +
== Activate mapserver ==
  +
<pre>a2enmod cgi
  +
systemctl restart apache2</pre>
  +
== Adopt epsg file to support beta2007 transformation ==
  +
<pre>
  +
cd /usr/share/proj
  +
cp epsg epsg_old
  +
sed -i "s/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
</pre>
  +
  +
== Create postgres cluster and start it ==
  +
<pre>
  +
pg_createcluster 11 {clustername}
  +
pg_ctlcluster 11 {clustername} start
  +
</pre>
  +
Path for conf files: /etc/postgresql/11/{clustername}
  +
  +
The port of the first cluster will be 5433, it will increment with the number of clusters.
  +
== Edit conf files ==
  +
<pre>vi /etc/postgresql/11/{clustername}/postgresql.conf</pre>
  +
* Uncomment listen_addresses ...
  +
* exchange localhost with *
  +
== Restart cluster ==
  +
<pre>pg_ctlcluster 11 {clustername} restart</pre>
  +
== Install first db on cluster ==
  +
<pre>
  +
su postgres
  +
createuser -S -D -R -P -p 5433 {dbusername}
  +
createdb -p 5433 -O {dbusername} db1 -E utf-8
  +
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis'
  +
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis_topology'
  +
psql -p 5433 -d db1 -f /usr/share/postgresql/11/contrib/postgis-2.5/legacy.sql
  +
psql -p 5433 -d db1 -c 'CREATE SCHEMA {schemaname}'
  +
psql -p 5433 -d db1 -c 'ALTER DATABASE db1 SET search_path TO {schemaname},public'
  +
</pre>
  +
== Update PostGIS to use beta2007 transformation ==
  +
<pre>
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31466"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31467"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31468"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31469"
  +
</pre>
  +
== Allow access from remote ==
  +
<pre>
  +
su
  +
echo "#add for postgis user" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
echo "local db1 postgres trust" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
echo "local db1 {dbusername} md5" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
pg_ctlcluster 11 {clustername} restart
  +
</pre>
  +
== Grant all rights to special dbuser ==
  +
<pre>
  +
su postgres
  +
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, USAGE, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE , EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE ON SCHEMA public TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE ON SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT ALL ON SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
 
</pre>
 
</pre>

Aktuelle Version vom 24. Juli 2020, 09:28 Uhr

Installation

Howto turn a Debian 10 box (netinstall) into a OWS Server - with apache2 & PostgreSQL

Update System

apt-get update
apt-get -y upgrade
apt-get clean

Install Admin Tools

apt-get -y install curl dos2unix htop mlocate p7zip unzip vim mc
apt-get clean

Install further helpful things

apt-get -y install apache2 libapache2-mod-php cgi-mapserver postgresql-11-postgis-2.5 gdal-bin postgresql-11-ogr-fdw php-xml libproj13
apt-get clean

Activate mapserver

a2enmod cgi
systemctl restart apache2

Adopt epsg file to support beta2007 transformation

cd /usr/share/proj
cp epsg epsg_old
sed -i "s/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg

Create postgres cluster and start it

pg_createcluster 11 {clustername}
pg_ctlcluster 11 {clustername} start

Path for conf files: /etc/postgresql/11/{clustername}

The port of the first cluster will be 5433, it will increment with the number of clusters.

Edit conf files

vi /etc/postgresql/11/{clustername}/postgresql.conf
  • Uncomment listen_addresses ...
  • exchange localhost with *

Restart cluster

pg_ctlcluster 11 {clustername} restart

Install first db on cluster

su postgres
createuser -S -D -R -P -p 5433 {dbusername}
createdb -p 5433 -O {dbusername} db1 -E utf-8
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis'
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis_topology'
psql -p 5433 -d db1 -f /usr/share/postgresql/11/contrib/postgis-2.5/legacy.sql
psql -p 5433 -d db1 -c 'CREATE SCHEMA {schemaname}'
psql -p 5433 -d db1 -c 'ALTER DATABASE db1 SET search_path TO {schemaname},public'

Update PostGIS to use beta2007 transformation

psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31466"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31467"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31468"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31469"

Allow access from remote

su
echo "#add for postgis user" >> /etc/postgresql/11/{clustername}/pg_hba.conf
echo "local   db1       postgres                              trust" >> /etc/postgresql/11/{clustername}/pg_hba.conf
echo "local   db1       {dbusername}                              md5" >> /etc/postgresql/11/{clustername}/pg_hba.conf
pg_ctlcluster 11 {clustername} restart

Grant all rights to special dbuser

su postgres
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, USAGE, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT,  UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT USAGE , EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  USAGE ON SCHEMA public TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  USAGE ON SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT ALL  ON SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'