Working with PostgreSQL

Hands-On Lab

 

Photo of Michael McClaren

Michael McClaren

Linux Training Architect I in Content

Length

01:00:00

Difficulty

Intermediate

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

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Working with PostgreSQL

Introduction

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

Solution

  1. Begin by logging in to the lab server using the credentials provided on the hands-on lab page:

    ssh cloud_user@PUBLIC_IP_ADDRESS

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. Become the root user:

    sudo su
  2. View the CSV file that contains our data. We will need to remove the extra new-line character at the bottom of the file:

    vi /home/cloud_user/data/cities.csv

    > Note: Be sure to remove the extra line at the bottom of the file.

  3. Copy the CSV file to a location the postres user can access:

    cp -r /home/cloud_user/data /var/lib/pgsql/
  4. Access a login shell for the postgres user:

    sudo -l postgres
  5. 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
    ) ;
  6. Show the list relations of the table:

    dt

Validate the Imported Data

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

    > Note: If you receive an error, be sure that you removed the extra line at the bottom of the cities.csv file.

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

Conclusion

Congratulations — you've completed this hands-on lab!