PostgreSQL 10, Export/Import

PostgreSQL 10, Export/Import

---

1.PostgreSQL Export

 1-1. META ONLY

   - pg_dump -U TEST -E UTF8 -s -v -f /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.log

 1-2. DATA ONLY

   - pg_dump -U TEST -E UTF8 -a -v -f /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.log

 1-3. 주요사용 옵션(대소문자 주의) 

1. -U username

2. -d dbname

3. -W password / -w no password

4. -a data only

5. -E encoding

6. -f file

7. -s schema only

8. -t table : include table

9. -T talbe : exclude table

10. --help

2. DB 및 계정 RENAME

- TEST_201903.dmp 파일 안에 TEST 단어를 TESTDB로 치환

- find . -name "TESTDB_dataonly_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

- find . -name "TESTDB_schema_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

3. PostgreSQL Import

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_schema_201903.dmp

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_dataonly_201903.dmp

상세 옵션: https://www.postgresql.org/docs/10/app-pgdump.html