Working with PostgreSQL

1 hour
  • 3 Learning Objectives

About this Hands-on Lab

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
  1. Add the Postgres repo:

    sudo yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-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
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
    ) ;
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;
  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';

Additional Resources

You have been tasked with standing up a PostgreSQL 11 installation on a provided CentOS 7 server.

On this server is data located in the cloud_user's /home/data directory. This data has been provided by a third-party and has not been validated.

This cities.csv file needs to be placed in the city_info table in the city_data database.

Once there, you will need to validate that the data has imported correctly by ensuring that there are 6 entries from the state of Ohio ('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.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!
Thanks for reaching out!

You’ll hear from us shortly. In the meantime, why not check out what our customers have to say about ACG?