| 20 | | * Add Spherical Mercator projection (900913. See http://www.cadmaps.com/gisblog/?p=81 for 54004): |
| 21 | | {{{ |
| 22 | | # Not needed with PostGIS-1.5 :) |
| 23 | | #psql gis |
| 24 | | #INSERT into spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values (900913 ,'EPSG',900913,'GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563,AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], NIT["degree",0.017453292519943295], AXIS["Longitude", EAST], AXIS["Latitude", NORTH],AUTHORITY["EPSG","4326"]], PROJECTION["Mercator_1SP"],PARAMETER["semi_minor", 6378137.0], PARAMETER["latitude_of_origin",0.0], PARAMETER["central_meridian", 0.0], PARAMETER["scale_factor",1.0], PARAMETER["false_easting", 0.0], PARAMETER["false_northing", 0.0],UNIT["m", 1.0], AXIS["x", EAST], AXIS["y", NORTH],AUTHORITY["EPSG","900913"]] |','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs'); |
| 25 | | }}} |
| 26 | | * Allow remote access: |
| 27 | | {{{ |
| 28 | | passwd postgres |
| 29 | | |
| 30 | | vim /etc/postgresql/8.4/main/postgresql.conf |
| 31 | | listen_addresses = '*' |
| 32 | | |
| 33 | | vim /etc/postgresql/8.4/main/pg_hba.conf |
| 34 | | host all all my.ip.add.ress/24 md5 |
| 35 | | |
| 36 | | /etc/init.d/postgresql restart |
| 37 | | |
| 38 | | vim /etc/iptables.rules |
| 39 | | -A INPUT -p tcp --dport 5432 -j ACCEPT |
| 40 | | |
| 41 | | reboot |
| 42 | | |
| | 20 | |
| | 21 | Add Spherical Mercator projection (900913. See http://www.cadmaps.com/gisblog/?p=81 for 54004): |
| | 22 | * Not needed with PostGIS-1.5 :) |
| | 23 | {{{ |
| | 24 | psql gis |
| | 25 | INSERT into spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) values (900913 ,'EPSG',900913,'GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563,AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], NIT["degree",0.017453292519943295], AXIS["Longitude", EAST], AXIS["Latitude", NORTH],AUTHORITY["EPSG","4326"]], PROJECTION["Mercator_1SP"],PARAMETER["semi_minor", 6378137.0], PARAMETER["latitude_of_origin",0.0], PARAMETER["central_meridian", 0.0], PARAMETER["scale_factor",1.0], PARAMETER["false_easting", 0.0], PARAMETER["false_northing", 0.0],UNIT["m", 1.0], AXIS["x", EAST], AXIS["y", NORTH],AUTHORITY["EPSG","900913"]] |','+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs'); |
| | 26 | }}} |
| | 27 | |
| | 28 | Create a user to access the db: |
| | 29 | {{{ |
| 45 | | }}} |
| 46 | | (Don't do remote access as 'postgres' - to do so means can't simply access psql via 'su postgres') |
| 47 | | |
| 48 | | * Adjust postgresql.conf for performance relative to resources available (http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) |
| 49 | | {{{ |
| 50 | | vim /etc/sysctl.conf |
| 51 | | # Increase Shared Memory available for PostgreSQL |
| 52 | | # 512Mb |
| 53 | | kernel.shmmax = 279134208 |
| 54 | | # 1024Mb (may need more) |
| 55 | | #kernel.shmmax = 536870912 |
| 56 | | kernel.shmall = 2097152 |
| 57 | | |
| 58 | | sysctl -w kernel.shmmax=58720256 |
| 59 | | sysctl -w kernel.shmall=2097152 |
| 60 | | |
| 61 | | vim /etc/postgresql/8.4/main/postgresql.conf |
| 62 | | |
| 63 | | # This default is probably ok |
| 64 | | # adjust upward _or_ add a connection pooling layer like pgpool if running out of connections for clients |
| 65 | | max_connections = 100 |
| 66 | | |
| 67 | | # shared_buffers = 24MB |
| 68 | | # Ideal is if all frequently accessed indexes and table rows can fit in here - this is often unrealistic |
| 69 | | # setting too high relative to system resources can be detrimental to the system. |
| 70 | | # At the high end this should be no more than 1/4 to 1/3 the available memory (ie that which is not being used by the OS or other processes). |
| 71 | | # Values suggested are quite conservative, so you can push them up a bit if there are resources available for it |
| 72 | | # but absolutely no more than 1/3 available memory. |
| 73 | | # 512Mb system suggest starting at: |
| 74 | | shared_buffers = 56MB |
| 75 | | # 1024Mb system suggest starting at: |
| 76 | | #shared_buffers = 160MB |
| 77 | | |
| 78 | | # effective_cache_size = 128MB |
| 79 | | # 512Mb system suggest starting at: |
| 80 | | effective_cache_size = 256MB |
| 81 | | # 1024Mb system suggest starting at: |
| 82 | | #effective_cache_size = 512MB |
| 83 | | |
| 84 | | #work_mem = 1MB |
| 85 | | # This is a per sort setting, so setting this too high with a high number of max_connections can be bad. |
| 86 | | # If the database load turns out to have a lot of sorting and not a lot of separate connections, you should increase this |
| 87 | | # and drop the max_connections. |
| 88 | | # 512Mb system suggest starting at: |
| 89 | | work_mem = 2MB |
| 90 | | # 1024Mb system suggest starting at: |
| 91 | | #work_mem = 4MB |
| 92 | | |
| 93 | | # As you get more memory in the system, moving this up can be quite useful if you notice vacuum processes taking forever. |
| 94 | | maintenance_work_mem = 16MB |
| 95 | | |
| 96 | | /etc/init.d/postgresql restart |
