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 postgresql11-server.x86_64 

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

3. Start and enable the service:

sudo systemctl start postgresql-11

sudo systemctl enable postgresql-11

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;

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