Import file csv ke database

Posted on by

Berikut ini adalah langkah-langkah untuk import file csv/text ke dalam tabel tertentu ke database postgresSQL.

  1. Terlebih dahulu siapkan satu folder khusus di database stored folder. Dalam kasus saya folder tersebut adalah /var/lib/pgsql/9.1/data, dan jangan lupa ubah kepemilikannya menjadi postgres. 
    mkdir csv
chown postgres csv
chmod 755 csv
  2. Selanjutnya pindahkan file csv yang akan kita import ke folder tersebut. You know la caranya kan ;)
  3. Pastikan juga anda sudah mempersiakan tabel nya, bisa dibuat pakai DBMS semacam PgAdmin atau Navicat kok ;)
  4. Melalui konsol, silakan jalankan perintah berikut: 
    COPY tabel_target
FROM '/usr/local/pgsql/data/csv/dump_file.csv'
WITH DELIMITER ';'

Dicomot dari berbagai sumber, salah satunya gistutor.com 8)

Lifesaver commands

Posted on by

This is just a personal notes of my daily work loads using PostgresSQL and Linux Server

Database backup and save it as compressed file

pg_dump -U [database_user] [database_name] | gzip -9 > [backup_file].sql.gz

Database restore using psql

Due its an easiest way and I’m kind of lazy to do it in a neat way:

psql -U [database_user] [database_target_name] < [backup_file_to_be_restored]

If you are trying to restore a compressed backup file, you must decompress it first. The command can be found at http://www.cyberciti.biz/howto/question/general/compress-file-unix-linux-cheat-sheet.php

File transfer using SCP

If you manage more than one server at a time, you must be familiar with this command. You can transfer files between your server directly.

scp [[user@]from-host:]source-file [[user@]to-host:][destination-file]

That's it :fiuh: