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 
  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.

    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.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?