http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [71]
02.File Book
File 자료실 [95]
Site Link [17]
개인폴더 [201]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [43]
DB2 [79]
ORACLE [3003]
PSQL [134]
기타정보 [155]
운영체제 [603]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

pg_dump / pg_restore[ psql (8.4.20) to psql (14.0) ]
최길호 [LIST]   2021-10-20 10:59:02, 조회 : 454


            사용자        테이블                   저장된DB
pg_dump -U user_000 -w -t test -v -Ft -f test.tar cell_000
           테이블                  위치한DB
pg_dump -t test -v -Ft -f test.tar cell_000


저장된 DB명, 테이블, 계정 확인 [ 꼭 DB, 계정 만들고 하자 ]
pg_restore -l test.tar
=======================================
;     dbname: cell_000
316; 1259 515405 TABLE public test segio

pg_restore -v -d cell_000 -C -Ft test.tar
FATAL:  database "cell_000" does not exist
-t option으로 백업해서 DB 만들수 없다.
create database cell_000;
create user segio password 'segio';
-- DB만 생성시 postgres 계정으로 만들어진다.
pg_restore 계속 실행 하면 PK 없으면 데이터 중복으로 들어간다.

zipcode만 cell_000 db에 import
pg_restore -v -t zipcode full.tar -d cell_000

테이블 복구
======================================================================
다른 이름 복구 옵션이 없다. [ 복구는 백업 상태로 복구 된다. ]

기존 테이블 rename 후 복구 하는게 쉽다.
=======================================
alter table project_job rename to project_job_bk;
\d project_job_bk
\d+ project_job_bk
pg_restore -v -t project_job -d postgres -C -F t /backup/segio.cell_000.tar

schema 바꿔서 복구 하는 방법
기존 schema를 바꾸고 복구하고 다시 바꿔야 한다.
=======================================
alter schema public rename to pub;
create schema public;

pg_restore -v -t project_job -d postgres -C -F t /backup/segio.cell_000.tar

alter schema public rename to reco ;
alter schema pub rename to public ;
select * from reco.project_job;
\d reco.project_job
\d+ reco.project_job


백업 방법
======================================================================
# login without password
vi ~/.pgpass
*:*:*:segio:segiopwd

-bash-4.1$ psql -Usegio cell_000
psql (8.4.20)

#!/bin/sh
cd /home/backup/tar/
pg_dump -Usegio cell_000 -v -F t -f segio.cell_000.`date +%w`.tar >segio.cell_000.`date +%w`.log 2>&1
gzip segio.cell_000.`date +%w`.tar
# 5G to 0.5G 압축


# 백업 받은 그대로 복구
======================================================================
# -d 없으면 에러 나서 넣는다. -C : create database
gunzip segio.cell_000.1.tar
pg_restore -v -C -d postgres -F t /backup/segio.cell_000.tar
# log
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "cell_000"
pg_restore: connecting to new database "cell_000"
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating TABLE "public.approval_appline_info"
.....
pg_restore: creating CONSTRAINT "public.worker_info_pkey"
pg_restore: creating CONSTRAINT "public.worker_licensed_info_pkey"
pg_restore: creating INDEX "public.memo_m_date_idx"
pg_restore: creating INDEX "public.memo_u_id_idx"
pg_restore: creating INDEX "public.private_m_no_idx"
pg_restore: creating INDEX "public.private_p_date_idx"
pg_restore: creating INDEX "public.private_u_id_idx"
pg_restore: creating INDEX "public.protect_u_id_idx"
pg_restore: creating INDEX "public.stalk_device_uid_idx"
pg_restore: creating INDEX "public.user_info_uid_idx"
pg_restore: creating FK CONSTRAINT "public.approval_appline_info_p_no_fkey"
pg_restore: creating FK CONSTRAINT "public.approval_doc_backup_p_no_fkey"
pg_restore: creating FK CONSTRAINT "public.approval_favorite_doc_no_fkey"
pg_restore: creating FK CONSTRAINT "public.approval_line_p_no_fkey"
pg_restore: creating ACL "public"
pg_restore: warning: errors ignored on restore: 2
-bash-4.2$

-bash-4.2$ psql cell_000
psql (14.0)
Type "help" for help.

cell_000=# \d
                         List of relations
Schema |                Name                 |   Type   |  Owner  
--------+-------------------------------------+----------+----------
public | approval_appline_info               | table    | user_000
public | approval_appline_info_no_seq        | sequence | user_000
public | approval_appline_list               | table    | user_000
public | approval_appline_list_no_seq        | sequence | user_000
public | approval_attach_info                | table    | user_000
public | approval_attach_info_no_seq         | sequence | user_000
public | approval_comment_info               | table    | user_000
public | approval_comment_info_no_seq        | sequence | user_000
public | approval_comment_view               | view     | user_000

-bash-4.2$ echo $LANG
en_US.UTF-8
-bash-4.2$ psql
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
cell_000  | user_000 | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

SecureCRT encoding: UTF-8
한글 표현 잘됨.

utf8 order by 안됨. 조치안: select .... order by 1 collate "ko_KR.utf8"


3.235.60.197


  LIST

제목 작성자 작성일 조회
Admin  psql 10.7 oracle dblink [ oracle_fdw db link ]    최길호 2023/07/25 20
Admin  HA 복구 test    최길호 2023/03/03 31
Admin  log: COPY public.xxxx (xxx, xxx, xxx) TO stdout; / pg_dump 백업시 발생    최길호 2023/03/03 14
Admin  requested timeline x is not a child of this server's history    최길호 2023/03/02 16
Admin  recovery_target_action / archive log 받아서 계속 복구    최길호 2023/02/28 25
Admin  recovery_target_action = 'pause' / select pg_wal_replay_pause(); / select pg_get_wal_replay_pause_state();    최길호 2023/02/27 6
Admin  memory buffer cache    최길호 2023/02/06 14
Admin  active session, pid, lock sql , kill pid / pg_terminate_backend    최길호 2023/02/06 60
Admin  psql HA    최길호 2023/02/05 46
Admin  pg_ctl start -D . | postmaster.pid | /tmp/.s.PGSQL.5432 |    최길호 2023/02/03 31
Admin  pg_waldump    최길호 2023/02/03 9
Troubleshoot  $PGDATA/pg_wal/00000002.history 없는경우 [ ERROR: could not open file "pg_wal/00000002.history": ENOENT ]    최길호 2023/02/02 24
Admin  pg_basebackup 복구 test lv:9   최길호 2023/02/02 23
Admin  shmem_exit(0): 0 before_shmem_exit callbacks to make [ 로그 기록용 ]    최길호 2023/02/01 46
Troubleshoot  LOG: invalid resource manager ID in primary checkpoint record    최길호 2023/02/01 24
Admin  pg_restore [ 다른 계정, 다른 DB, sql 파일로 저장 ]    최길호 2023/01/30 16
Admin  pg_basebackup pg_dump pg_dumpall backup script    최길호 2023/01/30 79
Admin  session 정보    최길호 2023/01/27 21

    목록보기   다음페이지 1 [2][3][4][5][6][7]
       

Copyright 1999-2023 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
12/01
[ORACLE]
dgmgrl 운영, db11st h/w 교체 시나리....
by 최길호
12/01
[ORACLE]
DGMGRL> show configuration;....
by 최길호
11/23
[운영체제]
arch filesystem 100% full 예방 /....
by 최길호