Contoh Kasus

  1. Tidak bisa restore data karena ada error

    a. invalid command \N

    b. trailing junk after numeric literal at or near

    c. syntax error at or near

Sudah mencoba install macam-macam “extension” si PostgreSQL seperti dan mencoba berbagai macam cara tetap saja tidak bisa. Bagaimana cara trace nya?

How To

  1. Saya menggunakan psql -U user_database -d database_kamu -f database_restore_file.sql untuk restore data, utk trace error, gunakan
psql -U user_database -d database_kamu -f database_restore_file.sql 2>&1 | tee restore_trace_log.txt
  1. Cek file restore_trace_log.txt, ternyata error nya karena permission yang belum lengkap
psql:database_restore_file.sql:31: ERROR:  must be owner of schema public
psql:database_restore_file.sql:41: ERROR:  permission denied for schema public
psql:database_restore_file.sql:51: ERROR:  permission denied for schema public
  1. Karena permission di PostgreSQL ini lebih “ribet” daripada MySQL, tambahkan dulu beberapa permission
\c database_kamu

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_database;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user_database;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO user_database;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO user_database;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO user_database;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO user_database;
  1. Atau biar lebih mudah, kamu bisa restore data menggunakan “Superuser”

CREATE ROLE admin_superuser WITH LOGIN SUPERUSER PASSWORD 'your_password';
\q