backup.sh

#DB 정보설정
dbname=postgre
port=5432
username=postgre
if [ "" != "$1" ]; then
	file_name=$1
else
	host_name=db.xxxx.com
	file_name=db_xxxx_backup.gz
fi
 
read -p "백업하려는서버($host_name): " name
 
if [ -n "$name" ]; then
	host_name="$name"
fi
 
startDate=$(date)
echo "================================"
echo "== 장고-데이터를 백업시작합니다."
echo "== 서버 : $host_name"
echo "== start time : ${startDate}"
echo "================================"
 
<<comment
Postgresql의 pg_dump를 이용한 백업을 실시합니다.
comment
 
pg_dump --dbname=${dbname} --host=${host_name} --port=${port} --username=${username} --clean | gzip > ${file_name}
 
if [ $? -eq 0 ]; then
  endDate=$(date)
  echo "=========== 백업성공============"
  echo "== 서버명 : ${host_name}"
  echo "== 파일명 : ${file_name}"
  echo "== end time : ${endDate}"
  echo "================================"
else
  echo "================================"
  echo "Failure!"
  echo "================================"
fi

restore.sh

# DB 정보설정
dbname=dbname
port=5432
username=postgresql
if [ "" != "$1" ]; then
	file_name=$1
else
	host_name=db.xxxx.com
	file_name=db_xxxx_backup.gz
fi
 
read -p "대상서버: " name
 
if [ -n "$name" ]; then
	host_name="$name"
fi
 
echo "================================"
echo "== 장고-데이터를 복원합니다.  =="
echo "== 서버       : $host_name"
echo "== 백업파일명 : $file_name"
echo "================================"
 
<<comment
PostgreSQL을 복원합니다.
comment
 
gunzip -c ${file_name} | psql --dbname={dbname} --host=${host_name} --port=${port} --username=${username}
 
if [ $? -eq 0 ]; then
  echo "================================"
  echo "== 서버명  - ${host_name}  =="
  echo "== 복원성공- ${file_name}  =="
  echo "================================"
else
  echo "================================"
  echo "Failure!"
  echo "================================"
fi

---SELECT tablename FROM pg_tables WHERE schemaname = current_schema()
 
DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;
 
---SELECT tablename FROM pg_tables WHERE schemaname = current_schema()

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DELETE FROM  ' || quote_ident(r.tablename) ;
    END LOOP;
END $$;