Table of Contents

1. Challenge

   Install PostgreSQL

   1. Add the Postgres repo:

      sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm 
      

   2. Install Postgres and initialize the database:

      sudo yum install -y postgresql12-server
      

      sudo /usr/pgsql-11/bin/postgresql-12-setup initdb 
      

   3. Start and enable the service:

      sudo systemctl start postgresql-12
      

      sudo systemctl enable postgresql-12
      

2. Challenge

   Create the Database and Table

   1. Access a login shell for the postgres user:

      sudo su
      

      su -l postgres
      

   2. Log in to the server and create resources. Get the table schema from the CSV file you are importing.

      psql
      
      create database city_data;
      \c city_data
      
      create table city_info(
      id serial NOT NULL,
      LatD numeric,
      LatM numeric,
      LatS numeric,
      NS text,
      LonD numeric,
      LonM numeric,
      LonS numeric,
      EW text,
      City text,
      State text
      ) ;
      

3. Challenge

   Validate the Imported Data

   1. Import the CSV data from a location that the postgres user has permissions to access. For instance, as root:

      cp -r /home/cloud_user/data  /var/lib/pgsql/
      

   2. Once that is complete import the data. If necessary, correct any errors!

      COPY city_info(LatD,LatM,LatS,NS,LonD,LonM,LonS,EW,City,State)
      

   from '/var/lib/pgsql/data/cities.csv' DELIMITER ',' CSV HEADER; ```

   1. From the PSQL prompt, query for State='OH' and count the results. There should be 6 cities in Ohio.

      select city from city_info where state='OH';