Encode dan Decode bade64 Postgresql

Beberapa sahabat pernah bertanya, bagaimana cara Encode dan Decode base64 pada PostgreSQL.

Berikut saya kasih contoh :

--DECODE

SELECT CONVERT_FROM(DECODE(prospect_name, 'base64'), 'UTF-8') FROM master_customer WHERE vcust_id='HIRTM-TEKO-312'

"SkFVSEFSSQ=="

--ENCODE

SELECT customer_id,ENCODE(CONVERT_TO(prospect_name, 'UTF-8'), 'base64') FROM master_customer ORDER BY customer_id LIMIT 10;

Semoga bermanfaat.

Baca Database Postgresql dari HDD atau OS nya crash

Buat sahabat yang pernah mengalami Harddisk atau OS crash dan punya database postgresql di dalamnya, saya pernah mengalaminya dan case nya pada OS Linux Centos.

Berikut step-stepnya:

1. Install Centos Linux pada komputer lain dan pastikan Postgresql terinstall.
2. Stop service postgresql # /etc/init.d/postgresql stop
3. Mount HDD yang postgresql nya mau di perbaiki.
    Contoh :# mount /dev/sdb2 /mnt/ 
4. Backup folder data postgresql pada Centos baru kita
    # mv -r /var/lib/pgsql/data /var/lib/pgsql/data_ori
    Copy folder "data" (cari directory data postgresql biasanya di /lib/pgsql/data)
    #cp -r /mnt/lib/pgsql/data /var/lib/pgsql/
5. Lakukan langkah berikut:
   # chmod -R 700 /var/lib/pgsql/data
   # chown -R postgres /var/lib/pgsql/data
   # su postgres
   # postmaster -D /var/lib/pgsql/data
   # /etc/init.d/postgresql start

Semoga bisa membantu
  
   

Fungsi DBLINK pada PostgrSQL

Apakah anda seorang Perogrammer atau DB Admin, saya punya tips jika kebetulan database yang anda gunakan adalah PostgreSQL. Kadang kita punya tidak hanya satu database yang harus kita gunakan dalam waktu yang bersamaan, atau kita membutukhan data dari dua database yang berbeda, tapi dalam satu bentuk keluaran. PostgreSQL mempunyai satu Fungsi yang sangat membantu dalam hal ini, fungsi itu adalah DBLINK.

Jika pada versi awal fungsi ini memang belum including, maka jalankan dulu script dibawah ini melalui Query,

-- dblink_connect now restricts non-superusers to password
-- authenticated connections
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_connect (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE 'C' STRICT;

-- dblink_connect_u allows non-superusers to use
-- non-password authenticated connections, but initially
-- privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION dblink_connect_u (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;

CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_disconnect (text)
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text,text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text,text,bool)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text,text,text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text,text,text,bool)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text,int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text,int,bool)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text,text,int,bool)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text,bool)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text,text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text,text,bool)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink (text,text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink (text,text,bool)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink (text,bool)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text,text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text,text,bool)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE 'c' STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text,bool)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE 'c' STRICT;

CREATE TYPE dblink_pkey_results AS (position int4, colname text);

CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS '$libdir/dblink','dblink_get_pkey'
LANGUAGE 'c' STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int4, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_insert'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int4, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_delete'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int4, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_update'
LANGUAGE 'C' STRICT;

CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
AS '$libdir/dblink','dblink_current_query'
LANGUAGE 'C';

Setelah script success dijalan kan, ikuti contoh script berikut untuk mencoba:

Misalnya kita punya dua database,

- database test1

- database test2

kita bisa mendapatkan data dari database "test1" lewat jendela Query database "test2", contoh:

SELECT * FROM dblink('host=localhost dbname=test1 user=postgres password=password',

'

SELECT vid_user, vname

FROM master.tbl_master_user

ORDER BY vname

') AS TBL_PREMIUM

(

vid_user varchar (20),vname varchar (50)

)

Dan selanjutnya anda bisa mengkreasi query hasil Query anda tersebut, mau di JOIN dengan tabel lain dan selanjutnya terserah anda.

Selamat mencoba.

Jika ada yang butuh untuk share silahkan email ke : arifdhena@gmail.com