[SOLVED] Linux system utilization import in PostgreSQL Database

Issue

I want to import the daily Linux system utilization file in PostgreSQL Database.

# ls /var/log/sa
sar -f sa13 >>/tmp/test_clean.csv

I am able to generate a .csv file using above command, but this format is allowing me to import into PostgreSQL database.

root#> less /tmp/test_clean.csv
<Linux redhat version> (servername)      <date>      _x86_64_        (2 CPU)

12:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
12:10:01 AM     all      0.10      0.00      0.05      0.02      0.00     99.83
12:20:01 AM     all      0.12      0.00      0.06      0.02      0.00     99.80
12:30:01 AM     all      0.08      0.00      0.05      0.02      0.00     99.85
12:40:01 AM     all      0.06      0.00      0.05      0.02      0.00     99.88
12:50:01 AM     all      0.07      0.00      0.05      0.02      0.00     99.86
01:00:01 AM     all      0.09      0.00      0.05      0.02      0.00     99.84
01:10:01 AM     all      0.07      0.00      0.05      0.02      0.00     99.86

Any Solution..!!

Solution

Working Answer:

1. Use the below command to create a .csv file.

   sadf -dh -- -p| tr ';' ','| awk 'NR>1{print $0}' > /tmp/test_clean.csv
   

2. Then, create the table

   CREATE TABLE system_utilzation (
        hostname  text,
        interval  text,
        ts       TIME,
        cpu      TEXT,
        users     NUMERIC,
        nice     NUMERIC,
        system     NUMERIC,
        iowait      NUMERIC, 
        steal      NUMERIC,
        idle     NUMERIC
       );
   

3. Create a temp table using below command.

    CREATE TEMP TABLE temp_system_utilzation AS SELECT * FROM system_utilzation WHERE 1=2;
   

4. Import data from .csv file to table.

   copy temp_system_utilzation from '/tmp/test_clean.csv' with CSV HEADER;
   

5. Then insert data into main table called system_utilzation

   INSERT INTO system_utilzation SELECT * FROM temp_system_utilzation;
   

Answered By - Pawan Sharma

Answer Checked By - David Marino (WPSolving Volunteer)