How to Execute PostgreSQL Commands Inside Unix Shell Scripts

Question: How do I executed PostgreSQL Commands inside a Linux / UNIX shell script?

Answer: With the help of the psql interactive terminal, you can execute the psql commands from the shell script. For this purpose, you should enable the password less login by pg_hba.conf, or .pgpass.

Syntax

psql DBNAME USERNAME << EOF
     statement 1;
     statement 2;
     .
     .
     statement n;
EOF

PostgreSQL: Execute SQL from shell scripts

EOF 블록 사이에 postgreSQL 모든 명령어 입력

dbname="test"
username="test"
psql $dbname $username << EOF
SELECT * FROM test;
EOF

PostgreSQL: Using variables in SQL from shell scripts

EOF 블록 내부에 쉘 스트립트 변수를 사용할 수 있음

dbname="test"
username="test"
wherecond="tgs"
psql $dbname $username << EOF
SELECT * FROM test WHERE col_name = '$wherecond';
EOF

이를 이용해서 스키마(schema)를 삭제하고 복구(restore)하는 실행파일 작성

dbname="test"

schemaname="test"

psql $dbname << EOF

DROP SCHEMA IF EXISTS $schemaname CASCADE;

\q

EOF

pg_restore --username "username" --dbname "test" --no-password --no-owner --verbose 백업파일(절대경로)

[출처] How to Execute PostgreSQL Commands Inside Unix Shell Scripts