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

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [195]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2917]
PostgreSQL [103]
기타정보 [151]
운영체제 [584]
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, 조회 : 168


            사용자        테이블                   저장된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.239.112.140


  LIST

제목 작성자 작성일 조회
Admin  odbc_fdw test    최길호 2021/11/22 138
Admin  ERROR: cross-database references are not implemented / postgres_fdw / psql dblink    최길호 2021/11/15 185
Admin  CREATE EXTENSION postgres_fdw; [ source install ]    최길호 2021/11/15 292
Admin  psql [ 8.4.20 , 8.4.22 ] 여러 Version 운영, upgrade 8.4.20 to 8.4.22 [ 뒤 Version 다르면 그냥 시작 ]    최길호 2021/11/12 116
Admin  source make install [ 8 ~ 14 ]    최길호 2021/11/11 171
Admin  psql odbc make install    최길호 2021/11/09 134
Admin  oracle to psql dblink    최길호 2021/11/09 155
Admin  /usr/bin/initdb -D /var/lib/pgsql/test [ Instance 추가 ]    최길호 2021/11/05 106
Troubleshoot  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?    최길호 2021/11/04 168
Troubleshoot  ERROR: current transaction is aborted, commands ignored until end of transaction block; [ postgresql-odbc upgrade ]    최길호 2021/11/04 183
Troubleshoot  pg_ctl start $PGDATA/pg_log/ 한글/영어 메세지 [ $PGDATA/postgresql.conf lc_messages ]    최길호 2021/10/29 205
Admin  pg_dumpall pg_upgrade    최길호 2021/10/21 149
Admin  통계정보    최길호 2021/10/21 124
Admin  pg_dump / pg_restore[ psql (8.4.20) to psql (14.0) ]    최길호 2021/10/20 168
Admin  PostgreSQL 14 / pgadmin Install [ lc_collate 'ko_KR.UTF-8' order by 정상 ]    최길호 2021/10/17 235
Admin  psql dblink to oracle [ odbc test ] 한글    최길호 2021/04/19 2498
Admin  postgres , enterprisedb(EDB) / [ ERROR: type "number" does not exist ]    최길호 2020/09/01 272
Admin  install postgresql on docker centos:7    최길호 2020/08/31 381
Troubleshoot  psql: server closed the connection unexpectedly [ vi $PGDATA/postgresql.conf / listen_addresses='*' ]    최길호 2020/08/31 412

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

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
11/23
[ORACLE]
acfsutil size: ACFS-03006: sma....
by 최길호
11/07
[운영체제]
Job for sshd.service failed be....
by 최길호
11/04
[ORACLE]
jsp error 위치 확인 [ debug ].
by 최길호