| 1 | The [http://wiki.postgresql.org/wiki/Apt PostgreSQL Apt Repository] now hosts installs of PostGIS, pgRouting, in addition to Postgresql and PGAdmin3. |
| 2 | |
| 3 | The following describes how to install Postgresql 9.5, PostGIS 2.2, pgRouting, PGAdmin on Ubuntu version 16.04. It is assumed to also work on Linux Mint, Lubuntu, and Xubuntu. |
| 4 | |
| 5 | Run these in terminal: |
| 6 | == Verify what version of Ubuntu you are running == |
| 7 | {{{ |
| 8 | sudo lsb_release -a |
| 9 | }}} |
| 10 | |
| 11 | |
| 12 | == Add Respository to sources.list == |
| 13 | |
| 14 | You'll need to replace the nickname below with what you are running. |
| 15 | Note to replace the word trusty with whatever version lsb states |
| 16 | |
| 17 | Helper on [http://www.postgresql.org/download/linux/ubuntu/] |
| 18 | |
| 19 | For xenial (16.04.2 LTS) |
| 20 | |
| 21 | {{{ |
| 22 | sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt xenial-pgdg main" >> /etc/apt/sources.list' |
| 23 | }}} |
| 24 | |
| 25 | |
| 26 | == Add Keys == |
| 27 | {{{ |
| 28 | wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add - |
| 29 | }}} |
| 30 | |
| 31 | {{{ |
| 32 | sudo apt-get update |
| 33 | }}} |
| 34 | |
| 35 | == Install == |
| 36 | The following will install postgresql 9.6, PostGIS 2.3, PGAdmin4, pgRouting 2.3 and additional supplied modules including the '''adminpack''' extension: |
| 37 | |
| 38 | |
| 39 | {{{ |
| 40 | sudo apt-get install postgresql-9.6 |
| 41 | sudo apt-get install postgresql-9.6-postgis-2.3 postgresql-contrib-9.6 |
| 42 | |
| 43 | #to get the commandline tools shp2pgsql, raster2pgsql you need to do this |
| 44 | sudo apt-get install postgis |
| 45 | }}} |
| 46 | |
| 47 | == To get pgRouting == |
| 48 | {{{ |
| 49 | # Install pgRouting 2.3 package |
| 50 | sudo apt-get install postgresql-9.6-pgrouting |
| 51 | |
| 52 | }}} |
| 53 | == Enable Adminpack == |
| 54 | |
| 55 | While in terminal, log in to the psql console as postgres user: |
| 56 | |
| 57 | {{{ |
| 58 | sudo -u postgres psql |
| 59 | }}} |
| 60 | |
| 61 | |
| 62 | |
| 63 | {{{ |
| 64 | CREATE EXTENSION adminpack; |
| 65 | }}} |
| 66 | |
| 67 | |
| 68 | |
| 69 | Never install PostGIS in the postgres database, create a user database |
| 70 | You can also enable the PostGIS extension here (or with the GUI as described below): |
| 71 | |
| 72 | {{{ |
| 73 | sudo -u postgres psql |
| 74 | }}} |
| 75 | |
| 76 | {{{ |
| 77 | CREATE DATABASE gisdb; |
| 78 | \connect gisdb; |
| 79 | |
| 80 | CREATE SCHEMA postgis; |
| 81 | ALTER DATABASE gisdb SET search_path=public, postgis, contrib; |
| 82 | \connect gisdb; -- this is to force new search path to take effect |
| 83 | CREATE EXTENSION postgis SCHEMA postgis; |
| 84 | SELECT postgis_full_version(); |
| 85 | }}} |
| 86 | |
| 87 | should give you output something like this: |
| 88 | |
| 89 | |
| 90 | {{{ |
| 91 | postgis_full_version |
| 92 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 93 | POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER |
| 94 | (1 row) |
| 95 | }}} |
| 96 | |
| 97 | Installing postgis_sfcgal, if you need advanced 3D support |
| 98 | |
| 99 | |
| 100 | {{{ |
| 101 | CREATE EXTENSION postgis_sfcgal SCHEMA postgis; |
| 102 | SELECT postgis_full_version(); |
| 103 | }}} |
| 104 | |
| 105 | should give you: |
| 106 | |
| 107 | |
| 108 | {{{ |
| 109 | postgis_full_version |
| 110 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| 111 | POSTGIS="2.3.2 r15302" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.2" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" RASTER |
| 112 | (1 row) |
| 113 | }}} |
| 114 | |
| 115 | |
| 116 | |
| 117 | |
| 118 | Install pgRouting |
| 119 | |
| 120 | {{{ |
| 121 | CREATE EXTENSION pgrouting; |
| 122 | SELECT * FROM pgr_version(); |
| 123 | }}} |
| 124 | |
| 125 | should give you: |
| 126 | |
| 127 | |
| 128 | {{{ |
| 129 | version | tag | hash | branch | boost |
| 130 | ---------+--------+-----------+--------+-------- |
| 131 | 2.3.2 | v2.3.2 | 1f2af3c52 | master | 1.58.0 |
| 132 | (1 row) |
| 133 | }}} |
| 134 | |
| 135 | |
| 136 | |
| 137 | |
| 138 | Exit the psql console: |
| 139 | |
| 140 | {{{ |
| 141 | \q |
| 142 | }}} |
| 143 | |
| 144 | |
| 145 | == Open Access to Clients == |
| 146 | |
| 147 | If you need to allow access from external, you can do this as well |
| 148 | {{{ |
| 149 | sudo -u postgres psql |
| 150 | }}} |
| 151 | |
| 152 | {{{ |
| 153 | ALTER SYSTEM SET listen_addresses='*'; |
| 154 | \q |
| 155 | }}} |
| 156 | |
| 157 | You may need to edit to pg_hba.conf and/or pg_ident.conf to allow external access |
| 158 | |
| 159 | {{{ |
| 160 | sudo nano /etc/postgresql/9.6/main/pg_hba.conf |
| 161 | }}} |
| 162 | |
| 163 | If you need external access, scroll to the bottom of the pg_hba.conf file and add a line like this (which willa llow all clients with md5 password encrypt authentication (right after the local rules): |
| 164 | |
| 165 | {{{ |
| 166 | hostssl all all 0.0.0.0/0 md5 |
| 167 | }}} |
| 168 | |
| 169 | Click CTRL-X to save your changes, Y to write them to the file, and enter to exit. |
| 170 | |
| 171 | If you change ip or port, you need to do a service restart. |
| 172 | |
| 173 | {{{ |
| 174 | sudo service postgresql restart |
| 175 | }}} |
| 176 | |
| 177 | Note: you can also do from postgres psql console with below - only for changes that don't require restart) |
| 178 | |
| 179 | {{{ |
| 180 | SELECT pg_reload_conf(); |
| 181 | }}} |
| 182 | |
| 183 | |
| 184 | == Optional: check location of configuration files: == |
| 185 | |
| 186 | From the psql console(see above): |
| 187 | |
| 188 | {{{ |
| 189 | SELECT name, setting FROM pg_settings where category='File Locations'; |
| 190 | }}} |
| 191 | |
| 192 | Which will output something like: |
| 193 | |
| 194 | {{{ |
| 195 | |
| 196 | name | setting |
| 197 | -------------------+------------------------------------------ |
| 198 | config_file | /etc/postgresql/9.6/main/postgresql.conf |
| 199 | data_directory | /var/lib/postgresql/9.6/main |
| 200 | external_pid_file | /var/run/postgresql/9.6-main.pid |
| 201 | hba_file | /etc/postgresql/9.6/main/pg_hba.conf |
| 202 | ident_file | /etc/postgresql/9.6/main/pg_ident.conf |
| 203 | (5 rows) |
| 204 | |
| 205 | }}} |
| 206 | |
| 207 | |
| 208 | |
| 209 | == Create new PGSQL user == |
| 210 | |
| 211 | You can create a new database super user to use instead of the default {{{postgres}}} user. |
| 212 | |
| 213 | While in terminal, run: |
| 214 | |
| 215 | {{{ |
| 216 | sudo su - postgres |
| 217 | }}} |
| 218 | |
| 219 | {{{ |
| 220 | createuser -d -E -i -l -P -r -s yourUserName |
| 221 | }}} |
| 222 | |
| 223 | (be sure to change the username to your desired username) |
| 224 | |
| 225 | Enter your new password when prompted |
| 226 | |
| 227 | Log out as {{{postgres}}} user: |
| 228 | |
| 229 | {{{ |
| 230 | exit |
| 231 | }}} |
| 232 | |
| 233 | Exit terminal: |
| 234 | |
| 235 | {{{ |
| 236 | exit |
| 237 | }}} |
| 238 | |
| 239 | |
| 240 | |
| 241 | == Import SHP files using shp2pgsql-gui == |
| 242 | |
| 243 | Another handy piece of software {{{shp2pgsql-gui}}} tool. This will allow you to quickly connect to your new PostGIS database and import a Shapefile. |
| 244 | |
| 245 | Open terminal, and type: |
| 246 | |
| 247 | {{{ |
| 248 | sudo apt-get install postgis-gui |
| 249 | }}} |
| 250 | |
| 251 | (Note: this is coming from the main Ubuntu software repository, as it seems the PostgreSQL APT repository doesn't package SHP2PGSQL-GUI anymore...) |
| 252 | |
| 253 | Now open the SHP2PGSQL application: |
| 254 | |
| 255 | {{{ |
| 256 | shp2pgsql-gui |
| 257 | }}} |
| 258 | |
| 259 | Follow the on-screen prompts to load your data. |
| 260 | |
| 261 | For more information, visit the [http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01 Boston GIS] tutorial in the section "Load Towns Data" |
| 262 | |