Common Postgre Command

# Restore database from .sql
psql -d db_name -U userdb_name < dump.sql

# Dump database
pg_dump -U postgres -d -O db_name

# To dump a database:
$ pg_dump mydb > db.out

# To reload this database:
$ psql -d database -f db.out

# To dump a database called mydb that contains large objects to a tar file:
$ pg_dump -Ft -b mydb > db.tar

# To reload this database (with large objects) to an existing database called newdb:
$ pg_restore -d newdb db.tar

Common SSL Commands

OPENSSL
=======

CREATE OWN CA (Example: CLaYCA)
1. Setting .cfg file n setting directories:

     CLaYCA
        |-- ca
        |-- server
        |-- user

2. Create Private Key (Remember the password)
openssl genrsa -des3 -out ca/CLaYCA.key 1024

3. Create CSR
openssl req -new -key ca/CLaYCA.key -out ca/CLaYCA.csr

4. Self-sign certificate:
openssl x509 -req -days 3650 -in ca/CLaYCA.csr-out ca/CLaYCA.crt -signkey ca/CLaYCA.key

5. To check the certificate
openssl x509 -in ca/CLaYCA.crt -text

6. Create DER Format from private key
openssl pkcs8 -topk8 -in ca/CLaYCA.key -outform DER -out ca/CLaYCA.der -nocrypt
Read the rest of this entry »

Get Elapsed Hours between 2 Dates

For PostgreSQL:

SELECT
    jam.days,
    jam.hours,
    jam.minutes,
    jam.seconds,
    ((to_number(jam.days, 9999)*24) + to_number(jam.hours, 9999) ||':'|| to_number(jam.minutes, 9999) )as jumlah
from
 (SELECT
    to_char(age(date1 ,date2), 'DD') as days,
    to_char(age(date1 ,date2), 'HH24') as hours,
    to_char(age(date1 ,date2), 'MI') as minutes,
    to_char(age(date1 ,date2), 'SS') as seconds
  FROM
    table_name ) as jam

Regex Date

ddMMyyyy:
^(0[1-9]|[12][0-9]|3[01])(0[1-9]|1[012])(19|20)[0-9]{2}$

dd/MM/yyyy or dd-MM-yyyy or dd.MM.yyyy:
^(0[1-9]|[12][0-9]|3[01])[- /.](0[1-9]|1[012])[- /.](19|20)[0-9]{2}$

Regex Hour

HH:mm (from 00:00 to 23:59)
^((?:[01]\d)|(?:2[0-3])):([0-5]\d)$