Working with 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
   

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
   ) ;
   

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';