Monday 13 March 2017

Backup & Restore PostgreSQL

Status : Draft

Dengan pg_dump & pg_restore & psql 


psql -U <username> -d <dbname> -1 -f <filename>.sql 
pg_restore -U <username> -d <dbname> -1 <filename>.dump

Backup Restore Berdasarkan SQL Query

Contoh :

Untuk backup

psql -h localhost -d banksoal -U postgres -W -c "copy (select * from soal_naskah where id_soal in(48624,48603)) to '/tmp/copy_soal/soal_naskah.sql'"

Untuk restore

copy soal_naskah from '/tmp/copy_soal/soal_naskah.sql'

Backup Restore Beda Versi PostgreSQL

Studi kasus, backup dari PostgreSQL 9.6.8 ke PostgreSQL 11.1. 

PostgreSQL 9.6.8 menggunakan extension tsearch2, sedangkan di 11.1 extension tersebut sudah tidak ada, maka akan error : 

pg_restore: WARNING:  type input function public.gtrgm_in should not be volatile
pg_restore: WARNING:  type output function public.gtrgm_out should not be volatile
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 275; 1255 16414 FUNCTION dex_init(internal) banksoal
pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/tsearch2": No such file or directory
    Command was: CREATE FUNCTION public.dex_init(internal) RETURNS internal
    AS '$libdir/tsearch2', 'tsa_dex_init';


  1. Export specific rows from a PostgreSQL table as INSERT SQL script,

No comments:

Post a Comment