Skip to main content

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 if there is one.

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

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

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

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

Validate the Imported Data

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