postgres 데이터 파일로 저장하기


https://www.postgresql.org/docs/10/static/sql-copy.html




postgresql 테이블의 데이터를 csv로 저장하려면 copy to 문을 사용하면 된다. 

아래 copy 문법과 예시 참조할 것. 



문법 ) 

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE NOT NULL column_name [, ...] ] ] ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE QUOTE { column_name [, ...] | * } ] ] ]


delimiter는 구분자를 무엇으로 할 지 지정하는 옵션이다. 


force quote는 컬럼에서 null이 아닌 컬럼 내용을 둘러싸는 기능을 이야기 한다. 

컬럼 이름을 설정해서 그 컬럼만 할 수 있고 *를 선택하면 전체 컬럼에 반영이 된다. 

copy to 에서 csv format을 선택할때만 사용할 수 있는 옵션이다. 



If * is specified, non-NULL values will be quoted in all columns. This option is allowed only in COPY TO, and only when using CSV format.



예시 1 ) , 를 구분자로 해서 파일로 저장하는 케이스 





COPY (

    SELECT language, title FROM cms_title WHERE language != 'en' AND title != 'Blog'

) TO '/path/to/csv/cms_title_dump.csv' WITH CSV HEADER DELIMITER ';';







예시 2) 빈 문자열을 구분자로 하고 "로 칼럼을 감싸서 저장하는 케이스 




copy (  select name, name, bldnm from table1 ,table2 where table1.id = table2.id order by namecnt desc)

to '/home/housename.csv' with csv header DELIMITER ' ' FORCE QUOTE *;



output 

name,name,bldnm
"엘지메트로시티2","엘지메트로시티2","엘지메트로시티"
"파크리오","파크리오","파크리오"
"성원","성원","성원아파트"
"잠실엘스","잠실엘스","잠실엘스"



예시 3) 중간에 다른 문자를 포함해서 저장하는 케이스 



copy (  select name, '{', name, bldnm, '}'  from table1 ,table2 where table1.id = table2.id order by namecnt desc)

to '/home/housename.csv' with csv header DELIMITER ' ';





예시 4 ) 탭으로 구분자를 할 경우 





COPY (

    SELECT language, title FROM cms_title WHERE language != 'en' AND title != 'Blog'

) TO '/path/to/csv/cms_title_dump.csv' WITH CSV HEADER DELIMITER E'\t';






















postgresql 테이블에 칼럼 추가 + 커멘트 달기






postgresql 테이블에 칼럼 추가 + 커멘트 달기




칼럼 추가 방식 


ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 제약조건


참고) postgresql numeric type


 



예시 


- int 형의 칼럼 추가하고 코멘트 달기


ALTER TABLE 테이블이름 ADD 칼럼이름 integer;

COMMENT ON COLUMN 테이블이름.칼럼이름 IS '코멘트';


- numeric 형의 칼럼 추가하기


ALTER TABLE 테이블이름 ADD COLUMN "ERROR" numeric(5,3);


칼럼 이름을 대문자로 할 경우 ""로 감싸줍니다. 




- varchar 칼럼 추가하면서 default 값 설정하기  


ALTER TABLE test_tb ADD COLUMN name varchar(50) DEFAULT 'NONAME'


- character varying 사이즈 120인 칼럼 추가하기 


ALTER TABLE test_tb ADD COLUMN name character varying(120)



default 값이 있는 칼럼을 추가할 경우 

postgresql 10 이하 버전에서는 테이블을 다시 생성하기 때문에 

시간도 오래 걸리고 그 동안에 access exclusive lock 에 걸리게 됩니다. 

access exclusive lock 은 lock level 중 제일 높은 레벨로 select 까지 막히게 됩니다. 

(실서비스 하는 데이터베이스에서 매우 곤란한 상황 ; - ; ) 


그럴 경우 한번에 칼럼을 추가하고 default 값을 설정하는 것보다는

아래의 예시와 같이 칼럼을 추가하고 기본 값을 설정하는 것이 좋습니다. 



ALTER TABLE test_tb ADD COLUMN name varchar(50);

update test_tb set name = 'NONAME';


ALTER TABLE items ADD COLUMN updated_at timestamptz;

UPDATE items SET updated_at = now();


 


참고

https://americanopeople.tistory.com/272

https://americanopeople.tistory.com/292?category=695027

https://www.postgresql.org/docs/10/datatype-numeric.html









postgresql 테이블 사이즈 확인하기



postgresql 테이블 사이즈 확인하기



* 전체 테이블 사이즈 확인하기 


 select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;



* 테이블별 사이즈 확인하기 


select pg_total_relation_size('테이블이름');



pg_size_pretty


# select pg_total_relation_size('테이블이름');

 pg_total_relation_size

------------------------

             9829687296

(1 row)


MB, GB같은 단위로 보고싶으면 pg_size_pretty를 사용한다. 



# select pg_size_pretty(pg_total_relation_size('테이블이름'));

 pg_size_pretty

----------------

 9374 MB

(1 row)



라이트세일(lightsail) mysql 데이터베이스 및 언어셋 uft8mb4로 변경하기




lightsail mysql 데이터베이스 및 언어셋 uft8mb4로 변경하기





서비스를 운영하다보니 사용자가 이모지(emoji)를 입력할 경우가 있는데 

기존 캐릭터 인코딩에서 오류가 나는 문제가 발생하였다. 

참고로 character set이 utf8인 상태에서 emoji를 데이터베이스에 저장하면 에러가 발생한다 ㅠㅠ


이모지는 4byte 문자이기 때문에 utf8의 상위 호환인 utf8mb4를 사용해야 저장할 수 있다. 




일단 기존 캐릭터 셋을 확인한다. 




라이트세일에서 mysql 설정파일은 아래 위치에 있다. 

/opt/bitnami/mysql/my.cnf

/opt/bitnami/mysql/bitnami/my.cnf




/opt/bitnami/mysql/my.cnf 에 기본적인 설정 내용이 있고

 !include로 /opt/bitnami/mysql/bitnami/my.cnf 의 추가 내용을 포함하도록 구성되어 있다. 


/opt/bitnami/mysql/my.cnf

캐릭터셋 관련 변경 내용


[mysqladmin]

default-character-set=utf8mb4


[mysqld]

skip-character-set-client-handshake

init_connect="SET collation_connection=utf8mb4_unicode_ci"

init_connect="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci



[client]

default-character-set=utf8mb4


[mysql]

default-character-set=utf8mb4


[mysqldump]

default-character-set=utf8mb4




설정 파일 변경후 mysql을 재시작한다. 



sudo /opt/bitnami/ctlscript.sh restart mysql



그리고 다시 캐릭터셋을 확인해본다. 



SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';




캐릭터셋 변경이 완료된 것을 확인할 수 있다.

 

데이터베이스의 변경이 완료된후 각 테이블의 캐릭터 셋을 변경한다. 




mysql은 데이터베이스 캐릭터셋을 변경하여도 

기존에 테이블이 있으면 자동으로 변경되지 않기 때문에 

다시 테이블별로 캐릭터셋을 변경해줘야한다. 


아래 alter문을 사용하면 테이블 및 칼럼의 캐릭터셋을 한번에 변경할 수 있다. 

그리고 변경된 테이블 이름의 칼럼 이름과 캐릭터 셋 이름을 선택하여 변경사항이 제대로 되었는지 확인하였다. 


ALTER TABLE 테이블이름 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


SELECT column_name, character_set_name FROM information_schema.`COLUMNS` WHERE table_name = '테이블이름'



이런식으로 이모지가 들어가야 하는 테이블을 수정 완료하였다. 











참고 : https://inma.tistory.com/100


https://www.letmecompile.com/mysql-utf8-utf8mb4-migration/


https://velog.io/@sang-eun/Mysql-utf8mb4%EC%84%A4%EC%A0%95



mysql 데이터베이스 백업 스크립트




mysqldump를 사용하여 데이터베이스를 매일 백업하는 쉘스크립트를 작성하였다. 



mysqldump 사용법 

   mysqldump는 DB에서 원하는 DB만, table만 백업 할 수 있고, 전체 DB를 백업 할 수 있다.


  (1) 전체 백업

    mysqldump -u계정이름 -p -A > all.sql


  (2) 특정 DB 백업

    mysqldump -u계정이름 -p 특정DB명 > 특정DB명.sql


  (3) 특정 DB에 특정 테이블 백업

    mysqldump -u계정이름 -p 특정DB명 특정table명 > 특정DB명.특정table.sql


  복원법

     mysql -u [userId] -p [password] [DB명] < 특정DB(특정table).sql


- DB 백업 스크립트 작성  

# vi /root/dbbackup.sh


mysqldump를 이용하여 디비나 테이블을 백업하고 백업한 파일은 gzip을 이용하여 압축하여 백업 폴더에 저장한다. 


- db 계정정보를 스트립트 안에 명시하는 경우 

  (스크립트에 명시적으로 계정정보를 입력하는 것은 보안상 위험 있음. 사용 예시로만 참고할 것


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

DB_USER="username"

DB_PASSWD="passwd"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump --user=$DB_USER --password=$DB_PASSWD $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";






- db 계정정보를 my.cnt에 명시하는 경우 


my.cnt 




[mysqldump]


user=사용자이름

password=암호




스크립트 


#!/bin/bash

DB_BACKUP="/home/dbbackup/"

db="dbname"

table="tablename"


# Remove backups older than 3 days

find $DB_BACKUP -ctime +3 -exec rm -f {} \;




# 데이터베이스를 모두 백업할경우 

mysqldump  -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




# 데이터베이스를 백업할경우 

mysqldump  $db | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";



# 데이터베이스의 특정 테이블을 백업할경우 

mysqldump  $db $table | gzip > "$DB_BACKUP/mysqldump-$db-$table-$(date +%Y-%m-%d).gz";









/root/dbbackup.sh 를 실행하여 정상적으로 스크립트가 작동하는지 확인한다. 


위와 같이 스크립트를 실행하면 정상적으로 되는데 crontab에서 실행할때는 아래와 같이  오류가 발생할 수 있다. 

cat /home/bitnami/backup_db.log
dbbackup.sh: line 8: ./mysqldump: No such file or directory




이럴 경우 which로 mysqldump 명령어의 전체 경로를 확인하여 스크립트에 명시해주면 된다. 


$ which mysqldump
/opt/bitnami/mysql/bin/mysqldump


아래와 같이 해주면 된다. 

# 데이터베이스를 모두 백업할경우 

/opt/bitnami/mysql/bin/mysqldumpmysqldump --user=$DB_USER --password=$DB_PASSWD -A | gzip > "$DB_BACKUP/mysqldump-$db-$(date +%Y-%m-%d).gz";




정상적으로 스크립트가 실행이 되면 보안을 위해 root만 수정하고 실행할수 있도록 권한 설정 

 chmod 700 /root/dbbackup.sh


- 크론택에 작업 등록 

#crontab -e



00 04 * * * /root/dbbackup.sh




매일 새벽 4시마다 디비 백업을 하도록 크론택을 등록하고 작업 완료. 




psycopg2.OperationalError: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?





postgresql 오류 처리 



어느날 서버가 갑자기 다운되어서 재시작을 한 후 

매번 잘 돌아가던 python 스크립트에서 갑자기 오류를 내기 시작했다. 

참고로 스크립트에서 변경된 사항은 전혀 없었다. 




Traceback (most recent call last):

  File "getInfoPost.py", line 379, in <module>

    conn = pc.connect("dbname=realdbname user=username password=realpass")

  File "/root/anaconda3/lib/python3.6/site-packages/psycopg2/__init__.py", line 130, in connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

psycopg2.OperationalError: could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?




ps afx | grep postgres로 postgresql 서버가 떠 있는것도 확인했다.  

  심지어 crontab에 등록해놓은 기존 스크립트는 문제없이 돌아가고 있었는데 

python3 scriptname.py로 실행할때만 저런 오류 메세지가 나왔다. 

혹시 몰라서 postgresql 도 다시 시작해보고 이런 저런 삽질을 하고 있었는데

문제는 간단하게 풀렸다. 

psycopg2 db 접속 함수에 host=localhost만 추가로 입력해주니 문제없이 작동을 했다 ㅠㅠ (내시간 ㅠㅠ) 



import psycopg2 as pc    

conn = pc.connect("host=localhost dbname=realdbname user=username password=realpass")



뭐가 원인이였는지는 모르겠지만 앞으로는 host도 명시적으로 명기해야겠다고 생각했다(...)






sed로 " 안의 , 제거하기




load data infile로 csv 파일 내용을 mysql 테이블로 import했는데 

warning 개수가 넘 많다 -_-;; 






LOAD DATA INFILE '/var/lib/mysql-files/sample.csv' INTO TABLE sample

FIELDS TERMINATED BY ','

IGNORE 1 LINES;


Query OK, 12147957 rows affected, 65535 warnings (1 min 44.92 sec)





show warnings; 로 경고 메세지를 확인하니 아래와 같이 테이블의 input columns 개수보다 

칼럼 개수가 많다고 한다. 





 Warning | 1265 | Data truncated for column '전용면적' at row 3758                             |

| Warning | 1262 | Row 3758 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3759                             |

| Warning | 1262 | Row 3759 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3760                             |

| Warning | 1262 | Row 3760 was truncated; it contained more data than there were input columns |

| Warning | 1265 | Data truncated for column '전용면적' at row 3761                             |





귀찮지만 데이터 확인 ㄱㄱ

, 를 기준으로 필드를 분리하는데 건물 이름에 , 가 들어가 있어서 필드 개수가 더 많아진 것임 ㅇㅇ




2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,4,48000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,3,49000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가,다)(구한전사택)",60.69,4,48000000




sed 로 " 안의 , 를 제거하였다. 




sed -e 's/\(".*\),\(.*"\)/\1 \2/g' sample.csv &> sample2.csv



-e 옵션은 정규식 옵션이고 

's/A/B/g' 는 A 패턴을 B 패턴으로 바꾸겠다는 것이다. 

()는 정규식 서브 패턴으로  A패턴에서 나오는 순서대로 B패턴에서 \1, \2 , 로 표시한다. 

참고로 \은 escape character이다.



위 식이 의미하는 것은 ,를 기준으로 앞의 "를 서브패턴1로 보고 

뒤 "를 서브패턴2으로 보아서 

최종적으로 " 아파트이름(문자열, 문자열) " 에서 ,를 제거하는 것이다. 


아래는 아래 정규식을 적용한 결과 파일이다. 





2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",51.81,1,40000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",77.11,3,62000000

2016,01,4211011200,강원 춘천 춘천로64번길 21-0,강원,춘천,효자,,0,688,6,,"엘피스빌(가 다)(구한전사택)",77.11,4,60000000




이제 정상적으로 경고 없이 파일 내용이 테이블로 로드되었다. 






 



  • 2017.10.25 14:05 ADDR 수정/삭제 답글

    비밀댓글입니다

postgreSQL 사용법 - 테이블 업데이트




postgresql 테이블 업데이트 방법 


( how to update postgresql table ) 





다른 테이블 필드 값으로 업데이트 




update table2 as f set field1 = h.field1 from  table1 as h where f.field2 = 2 and f.id = h.id;



같은 테이블 필드를 분리해서 업데이트 




update 테이블이름 set 본번 = split_part(지번, '-',1);



특정 값으로 전체 필드 업데이트 



update 테이블이름 set 칼럼이름='문자열' 









[ postgreSQL] 다른 테이블 복사하기





PostgreSQL 사용법 


다른 테이블 구조 & 데이터 복사하기 








다른 테이블 구조와 데이터 복사하기 




CREATE TABLE newtable AS SELECT * FROM oldtable;






다른 테이블 구조만 복사하기



CREATE TABLE newtable ( LIKE oldtable ); 






데이터 & 인덱스 &  constraint 등의 정보 다 같이 복사하기 


아래 두개의 sql 문을 차례로 실행한다. 복사 속도는 위의 방식이 더 빠르지만 인덱스 정보가 같이 복사됨.



 


create table newtable (like "oldtable" including all);

insert into newtable ( select * from "oldtable");




다른 테이블의 일부 필드만 복사하기 





insert into items_ver(item_id, item_group, name)

select * from items where item_id=2;



insert into items_ver (item_id, name, item_group)

select item_id, name, item_group from items where item_id=2;












  • 1122 2018.02.28 11:35 ADDR 수정/삭제 답글

    초대장 하나 받고싶어요 도와주세요 ramen1225@naver.com

postgresql 테이블 조인(join) 예시



1. 테이블 사이에 이름이 같은 필드가 없을 경우 


그냥 필드 이름을 나열하고 from 이후 두 개의 테이블을 나열하고 테이블 join 조건을 where에 채워주면 됨 



select name,  area, deposit_date as adate , deposit_min as aprice from table1, table2 where table1.id = table2.hid and type = '1';