PostgreSQL & PostGIS

Install postgresql

After configuring the yum source, you can find that the version of PostgreSQL is 9.2.23 by using yum info postgresql. If you want to install the latest version, you can refer to the following operation

https://www.postgresql.org/download/linux/redhat/

http://docs.nextgis.com/docs_ngweb/source/install-centos7.html

http://www.postgresonline.com/journal/archives/362-An-almost-idiots-guide-to-install-PostgreSQL-9.5,-PostGIS-2.2-and-pgRouting-2.1.0-with-Yum.html

sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm

# View the packages available in the above warehouse
yum list | grep postgresql95
# Install PostgreSQL client server 
sudo yum install postgresql95 postgresql95-server postgresql95-libs \
postgresql95-contrib postgresql95-devel
# view help
psql --help
rpm -qa | grep postgresql*  # View installed software
# Initialize the database and set it to start with the system
sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
sudo systemctl start postgresql-9.5.service # service postgresql-9.5 start
sudo systemctl enable postgresql-9.5.service

# Edit validation parameters (nano editor is used here, vim is used)
# After psql enters the postgres database, use show hba_file; view file location
sudo nano /var/lib/pgsql/9.5/data/pg_hba.conf 
sudo vim /var/lib/pgsql/9.5/data/pg_hba.conf 

Modify ident ity to md5

PostgreSQL ident and peer authentication based on operating system users PostgreSQL Authentication Method

If it is peer, there may be a failure of peer authentication

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

# Posgresql.conf can be further modified to monitor the target address
su postgres   # Switch to postgres or sudo su postgres
psql -U postgres   #Log in to the database, no password by default
ALTER USER postgres WITH PASSWORD 'Password'; #Modify password sudo passwd postgres
# Or use the password command
\password postgres 
select * from pg_shadow; # View database information
\q #Sign out
psql -V
sudo systemctl restart postgresql-9.5.service # Restart the service, or service postgresql-9.5 restart

# Create a new psql database user (ngw_admin) using postgres users
# \ The du command allows you to view user information
# - P denotes the password and - e denotes the display command. Use - s or - superuser to grant superuser privileges
sudo -u postgres createuser ngw_admin -P -e
# New database (db_ngw), owned by ngw_admin
sudo -u postgres createdb -O ngw_admin --encoding=UTF8 db_ngw

### Installing postgis

https://postgis.net/install/

The postgis2_95-client contains the PostGIS commandline tools shp2gpsql, pgsql2shp, raster2pgsql that are useful for loading or exporting spatial data.

sudo yum install epel-release # If no epel source is configured
sudo yum install postgis2_95 postgis2_95-client # install
# yum install pgrouting_95 when pgRouting is required
# Log in to the corresponding database
psql -U ngw_admin -d db_ngw
# Extending postgis functionality for existing databases (db_ngw)
CREATE EXTENSION postgis;
SELECT PostGIS_Full_Version(); # Test whether the database contains postgis functionality

Or execute directly in the shell

sudo psql -u postgres -d db_ngw -c 'CREATE EXTENSION postgis;'
sudo psql -u postgres -d db_ngw -c \
'ALTER TABLE geometry_columns OWNER TO ngw_admin;'
sudo psql -u postgres -d db_ngw -c \
'ALTER TABLE spatial_ref_sys OWNER TO ngw_admin;'
sudo psql -u postgres -d db_ngw -c \
'ALTER TABLE geography_columns OWNER TO ngw_admin;'
psql -h localhost -d db_ngw -U ngw_admin -c "SELECT PostGIS_Full_Version();"

Create a postgis database directly using template and specify the owner

# Log in to the database
# Posgis_21_sample is version 2.1 postgis database
create  database  geodataont   template   postgis_21_sample   owner   gdo;
# perhaps
createdb -O gdo -U postgres -T postgis_22_sample geodataont

Relevant SQL Enabling PostGIS Function

DO NOT INSTALL it in the database called postgres.

# After connecting to the database, execute the following sql commands to enable the corresponding functions
# Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
# Enable Topology
CREATE EXTENSION postgis_topology;
# Enable PostGIS Advanced 3D
# and other geoprocessing algorithms
# sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
# fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
# rule based standardizer
CREATE EXTENSION address_standardizer;
# example rule data set
CREATE EXTENSION address_standardizer_data_us;
# Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

CREATE EXTENSION pgrouting;
SELECT * FROM pgr_version();
# yum install ogr_fdw95
CREATE EXTENSION ogr_fdw;