{"id":179,"date":"2024-01-06T21:56:35","date_gmt":"2024-01-06T21:56:35","guid":{"rendered":"https:\/\/www.ccsgeo.com\/?p=179"},"modified":"2024-01-06T22:51:06","modified_gmt":"2024-01-06T22:51:06","slug":"deploying-an-open-source-gis-stack-part-2-installing-postgresql-with-postgis","status":"publish","type":"post","link":"https:\/\/www.ccsgeo.com\/index.php\/2024\/01\/06\/deploying-an-open-source-gis-stack-part-2-installing-postgresql-with-postgis\/","title":{"rendered":"Deploying an Open Source GIS Stack (Part 2: Installing PostgreSQL with PostGIS)"},"content":{"rendered":"\n

The foundation of any open source GIS stack is PostgreSQL with the PostGIS extension. In part 1 of this series<\/a>, I laid out the deployment architecture and spun up some Ubuntu Server virtual machines on VMWare ESXI. There are a few steps to deploy the database so that it can be used as a GIS database in QGIS and Geoserver. <\/p>\n\n\n\n

    \n
  1. Install PostgreSQL.<\/li>\n\n\n\n
  2. Install PostGIS.<\/li>\n\n\n\n
  3. Configure PostgreSQL connections.<\/li>\n\n\n\n
  4. Set up postgres user.<\/li>\n\n\n\n
  5. Configure allowed authentication.<\/li>\n\n\n\n
  6. Create GIS database <\/li>\n\n\n\n
  7. Activate PostGIS on database.<\/li>\n\n\n\n
  8. Create new GIS user and GIS data schema.<\/li>\n\n\n\n
  9. Ensure connection from QGIS are successful.<\/li>\n<\/ol>\n\n\n\n

    So let’s get started! <\/p>\n\n\n\n

    Install PostgreSQL<\/h2>\n\n\n\n

    Installing PostgreSQL on Ubuntu<\/a> is pretty straightforward: <\/p>\n\n\n\n

    $ sudo apt install postgresql<\/p>\n\n\n\n

    Install PostGIS<\/h2>\n\n\n\n

    Installing PostGIS is also pretty straightforward: <\/p>\n\n\n\n

    $ sudo apt install postgresql-14-postgis-3<\/p>\n\n\n\n

    Configure PostgreSQL Connections<\/h2>\n\n\n\n

    Once we have installed PostgreSQL, we have to configure it to allow connections from other machines. This is done through the postgresql.conf file. Using nano, a built-in command line text editor, we can modify the file: <\/p>\n\n\n\n

    $ sudo nano \/etc\/postgresql\/14\/main\/postgresql.conf<\/strong><\/p>\n\n\n

    \n
    \"postgresql.conf
    postgresql.conf configuration.<\/figcaption><\/figure>\n<\/div>\n\n\n

    Here we can set the listen_address to ‘*’ so that the PostgreSQL server listens for all incoming ip addresses. You can modify this for your own network configuration but this is an easy catch-all for a development environment. <\/p>\n\n\n\n

    Setup Postgres User <\/h2>\n\n\n\n

    By default, when PostgreSQL is installed on Linux, it creates a user called postgres at the OS level. This user has access to the database via peer authentication<\/a>, which allows a user logged in at the OS level to log in to the database if there is a corresponding user present. Initially there is no password required and you can access the database on the local machine via: <\/p>\n\n\n\n

    $ sudo -u postgres psql <\/strong><\/p>\n\n\n\n

    We can then set a password for the database user using the ALTER ROLE sql command: <\/p>\n\n\n

    \n
    \"Adding
    Adding a password for postgres user.<\/figcaption><\/figure>\n<\/div>\n\n\n

    This password will not be required to sign in when accessing the database from the local machine, but we will need it later on when accessing the database from a remote machine (such as where QGIS is installed). <\/p>\n\n\n\n

    Modify pg_hba.conf<\/h2>\n\n\n\n

    Our final configuration step is to modify the pg_hba.conf file which allows us to connect to the database using the postgres user configured with a password from a remote machine: <\/p>\n\n\n\n

    $sudo nano \/etc\/postgresql\/14\/main\/pg_hba.conf<\/strong><\/p>\n\n\n\n

    At the bottom, I added hostssl all all 0.0.0.0\/0 scram-sha-256<\/strong>:<\/p>\n\n\n

    \n
    \"pg_hba.conf
    pg_hba.conf configuration.<\/figcaption><\/figure>\n<\/div>\n\n\n

    You can narrow the IP range down to reflect your local network subnet. The hostssl parameter only allows for ssl connections and uses the scram-sha-256 method for authentication. Now, we can restart the PostgreSQL service to lock in our changes: <\/p>\n\n\n\n

    $ sudo systemctl restart postgresql.service<\/strong><\/p>\n\n\n\n

    To test the connection, I installed pgAdmin<\/a> on my desktop. You can then right-click on Servers > Register and enter in the database information: <\/p>\n\n\n

    \n
    \"Register
    Register the database server in pgAdmin.<\/figcaption><\/figure>\n<\/div>\n\n\n

    Success! <\/p>\n\n\n

    \n
    \"Successful
    Successful connection.<\/figcaption><\/figure>\n<\/div>\n\n\n

    <\/p>\n\n\n\n

    Create GIS Database <\/h2>\n\n\n\n

    Now we can create a database where we will store our geographic data. You can do this easily through pgAdmin (Right-click Databases > New Database) or through the command prompt on the database machine. First, sign in: <\/p>\n\n\n\n

    $sudo -u postgres psql<\/strong><\/p>\n\n\n\n

    Then use the CREATE DATABASE command: <\/p>\n\n\n\n

    CREATE DATABASE gisgdb2;<\/strong><\/p>\n\n\n\n

    If we have a look at pgAdmin, we should be able to see the new database: <\/p>\n\n\n

    \n
    \"New
    New database visible in pgAdmin.<\/figcaption><\/figure>\n<\/div>\n\n\n

    In order to enable our database to store geographic information, we also need to add the PostGIS extension to the database. You can do this by using the CREATE EXTENSION command: <\/p>\n\n\n

    \n
    \"Adding
    Adding the PostGIS extension to the gisgdb2 database.<\/figcaption><\/figure>\n<\/div>\n\n\n

    <\/p>\n\n\n\n

    Now we have a PostGIS enabled PostgreSQL database we can use for all of our mapping needs! There are just a few other things to take care of. <\/p>\n\n\n\n

    <\/p>\n\n\n\n

    Create new GIS user and GIS data schema<\/h2>\n\n\n\n

    You could technically just use the postgres user and the default public schema to store and access your geographic data. However, I want to model this a bit more like an organization would. So I am going to create a specific GIS user that has their own schema for storing and accessing data. On the PostgreSQL machine, log in to the database:

    $ sudo -u postgres psql <\/strong><\/p>\n\n\n\n

    Create a new user:<\/p>\n\n\n\n

    CREATE USER ccsgeoprod WITH encrypted password ‘ccsgeoprod’;<\/strong><\/p>\n\n\n\n

    Switch into the database created in the step above: <\/p>\n\n\n\n

    postgres=# \\c gisgdb2<\/strong><\/p>\n\n\n\n

    Grant all privileges to the new user on this database: <\/p>\n\n\n\n

    gisgdb2=# GRANT ALL PRIVILEGES ON DATABASE gisgdb2 TO ccsgeoprod;<\/strong><\/p>\n\n\n\n

    Create a schema with the same name as the user: <\/p>\n\n\n\n

    CREATE SCHEMA AUTHORIZATION ccsgeoprod;<\/strong><\/p>\n\n\n\n

    Be sure to test your new user by connecting to the database via pgAdmin. <\/p>\n\n\n\n

    <\/p>\n\n\n\n

    Connect to the Database in QGIS<\/h2>\n\n\n\n

    The final step is to make sure that we can actually make a connection to the new database with the ccsgeoprod user and load some data! To create a database connection in QGIS, navigate to the PostgreSQL option in the Browser > Right-click > New Connection: <\/p>\n\n\n

    \n
    \"QGIS
    QGIS database connection.<\/figcaption><\/figure>\n<\/div>\n\n\n

    As my hba_conf file is configured with the hostssl option, I had to set the SSL mode to ‘allow’. In the Authentication section, I chose basic and entered in the username and password for ccsgeoprod. The test connection was successful and I can now see the two available schemas in the Browser: <\/p>\n\n\n

    \n
    \"The
    The database and schema visible in the QGIS Browser.<\/figcaption><\/figure>\n<\/div>\n\n\n

    I then used the Export to PostgreSQL data loading tool to load a shapefile to my new database: <\/p>\n\n\n

    \n
    \"Export
    Export to PostgreSQL tool. <\/figcaption><\/figure>\n<\/div>\n\n
    \n
    \"The
    The loaded data. <\/figcaption><\/figure>\n<\/div>\n\n\n

    Success! <\/p>\n\n\n\n

    Conclusion <\/h2>\n\n\n\n

    In this part of deploying an open source GIS stack, we stepped through downloading PostgreSQL, configuring connections, creating a database, enabling PostGIS, creating users and schemas, and loading data through QGIS. This database will give us a solid platform for being able to publish and serve out web services via Geoserver, as well as manage and work with our geographic data through QGIS. Stay tuned for part three – deploying Geoserver. <\/p>\n","protected":false},"excerpt":{"rendered":"

    The foundation of any open source GIS stack is PostgreSQL with the PostGIS extension. In part 1 of this series, I laid out the deployment architecture and spun up some Ubuntu Server virtual machines on VMWare ESXI. There are a few steps to deploy the database so that it can be used as a GIS […]<\/p>\n","protected":false},"author":2,"featured_media":218,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,7,12],"tags":[],"class_list":["post-179","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-foss","category-technology","category-workflow"],"_links":{"self":[{"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":24,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"predecessor-version":[{"id":217,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/posts\/179\/revisions\/217"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/media\/218"}],"wp:attachment":[{"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ccsgeo.com\/index.php\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}