Backup Restore Database

Backup Restore Cara Instan 

Untuk backup, gunaka pgadmin, dan pilih tipe plain.

Untuk restore login via command line ke database yang ingin kita restore :

Hapus schema tempat tabel-tabel berada, misal public  :

drop schema public cascade;
create schema public;

Restore

\i nama_file_backup.sql 

Ubah ownership (jika perlu) :

REASSIGN OWNED BY old_role [, ...] TO new_role

atau

--ubah ownership table
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'schema_name';" db_name` ; do  psql -c "alter table schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership sequence
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'schema_name';" db_name` ; do  psql -c "alter sequence schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership views
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'schema_name';" db_name` ; do  psql -c "alter view schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership function
IFS=$'\n'
for fnc in `psql -qAt -c "SELECT  p.proname||''||'(' || oidvectortypes(p.proargtypes) ||')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'schema_name';" db_name`
do
  psql -c "ALTER FUNCTION schema_name.$fnc OWNER TO schema_name" db_name ; 
done