Mengubah Schema Default yang Diakses Oleh User Postgres

Status : Draft

Dari [1] :

ALTER USER user SET search_path TO accountschema;

Referensi

1. Best way to set a PostgreSQL schema using PHP PDO, http://stackoverflow.com/questions/21714096/best-way-to-set-a-postgresql-schema-using-php-pdo

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