Working with PostgreSQL

In this hands-on lab, we will be standing up a Postgres installation and then importing formatted data.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Install PostgreSQL

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

Install Postgres and initialize the database:

sudo yum install -y postgresql11-server.x86_64

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

Start and enable the service:

sudo systemctl start postgresql-11

sudo systemctl enable postgresql-11

Create the Database and Table

Access a login shell for the postgres user:
```
sudo su
```
```
su -l postgres
```

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

Validate the Imported Data

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/
```

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;

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

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Working with PostgreSQL

About this Hands-on Lab

Learning Objectives

Additional Resources

What are Hands-on Labs

Newsletter