Backup & Restore PostgreSQL

Status : Draft

Dengan pg_dump & pg_restore & psql 

Restore 

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

    LANGUAGE c

    AS '$libdir/tsearch2', 'tsa_dex_init';

Referensi

1. Export specific rows from a PostgreSQL table as INSERT SQL script, http://stackoverflow.com/questions/12815496/export-specific-rows-from-a-postgresql-table-as-insert-sql-script