Linux system utilization import in PostgreSQL Database

Question

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

this is not a csv file, please update the question to show the format of the file as it really is, providing a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) — Ereli, Jun 18 '18 at 14:39

Ereli · Answer 1 · 2018-06-14T16:49:08.723

0

Firstly you will need to clean the file from the first two lines: then you will a create a table. finally, you will insert into the table.

There are a few ways to clean the file, but you can run something like

cat /tmp/test.csv|awk 'NR>2' > /tmp/test_clean.csv

Then you'll need to create a table: The syntax is something like this:

CREATE TABLE system_utilzation (
    ts       TIME,
    cpu      TEXT,
    user     NUMERIC,
    nice     NUMERIC,
    system     NUMERIC,
    iowait      NUMERIC, 
    steal      NUMERIC,
    idle     NUMERIC
);

Inserting a CSV file into your table can be done using COPY or \COPY if this the first time you are populating the table. if you want this to run as routine job, you'll want to [INSERT][3] it.

You can do that using an empty temp table. the temp table is unique per connection and will be automatically deleted after you disconnect from Posgres: CREATE TEMP TABLE temp_system_utilzation AS SELECT * FROM system_utilzation WHERE 1=2; then you will populate it using psql and \COPY.

psql -h remotehost -d remote_mydb -U myuser -c "\copy temp_system_utilzation from '/tmp/test_clean.csv' with CSV HEADER"

Lastly, you can insert from your temp table into the full table using something like this: INSERT INTO ystem_utilzation SELECT * FROM temp_system_utilzation;

edited Jun 14 '18 at 16:49

answered Jun 14 '18 at 16:27

Ereli

965
20
34

demo=# copy temp_system_utilzation from '/tmp/test_clean.csv' with CSV HEADER; ERROR: invalid input syntax for type timestamp: "12:10:01 AM all 0.10 0.00 0.05 0.02 0.00 99.83" CONTEXT: COPY temp_system_utilzation, line 2, column ts: "12:10:01 AM all 0.10 0.00 0.05 0.02 0.00 99.83" demo=# – Pawan Sharma Jun 14 '18 at 16:39
better change it from `timestamp` to `time` – Ereli Jun 14 '18 at 16:48
this probably means you don't have a csv file. try to follow this https://stackoverflow.com/questions/15348914/output-sar-command-results-to-a-csv-file to make sure you have a csv file as you may have a file with spaces instead. – Ereli Jun 14 '18 at 17:01
something like this should work `sadf -dh -- -p| tr ';' ','| awk 'NR>3{print $0}'` – Ereli Jun 14 '18 at 17:17
Thanks its working for me but in CPU column it showing -1 instead of all. – Pawan Sharma Jun 18 '18 at 13:23
please update the question to include the file in CSV format – Ereli Jun 18 '18 at 14:08

score -1 · Accepted Answer · answered Jun 18 '18 at 14:10

Working Answer:

Use the below command to create a .csv file.

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

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
    );

Create a temp table using below command.

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

Import data from .csv file to table.

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

Then insert data into main table called system_utilzation

INSERT INTO system_utilzation SELECT * FROM temp_system_utilzation;

Linux system utilization import in PostgreSQL Database

2 Answers2