PostgreSQL 데이터베이스의 PL / pgSQL 출력을 CSV 파일로 저장하는 가장 쉬운 방법은 무엇입니까?

쿼리를 실행하는 pgAdmin III 및 PSQL 플러그인과 함께 PostgreSQL 8.4를 사용하고 있습니다.

답변

서버 나 클라이언트에서 결과 파일을 원하십니까?

서버 측

재사용하거나 자동화하기 쉬운 것을 원한다면 Postgresql의 내장 COPY 명령을 사용할 수 있습니다 . 예 :

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

이 방법은 전적으로 원격 서버에서 실행되며 로컬 PC에는 쓸 수 없습니다. 또한 Postgres는 해당 시스템의 로컬 파일 시스템에서 불쾌한 일을 멈출 수 없기 때문에 Postgres “슈퍼 유저”(일반적으로 “루트”)로 실행해야합니다.

당신이 사용할 수 있기 때문에 실제로는 수퍼 유저로 연결되어 있어야 의미하지 않는다 (즉, 다른 종류의 보안 위험이 될 것 자동화) 하는 옵션을 함수 만들기 위해 당신은 슈퍼 유저 인 것처럼 실행을 .SECURITY DEFINERCREATE FUNCTION

중요한 부분은 함수가 보안을 우회하지 않고 추가 검사를 수행해야한다는 것입니다. 따라서 필요한 정확한 데이터를 내보내는 함수를 작성하거나 다양한 옵션을 허용하는 한 작성할 수 있습니다 엄격한 화이트리스트를 만나십시오. 두 가지를 확인해야합니다.

어떤 파일은 사용자가 디스크의 읽기 / 쓰기로 허용해야 하는가? 예를 들어이 디렉토리는 특정 디렉토리 일 수 있으며 파일 이름에 적합한 접두사 또는 확장자가 있어야합니다.
어떤 테이블 사용자가 데이터베이스에 읽기 / 쓰기 할 수 있어야한다? 이것은 일반적으로 GRANT데이터베이스에서 s 로 정의 되지만 함수는 이제 수퍼 유저로 실행되므로 일반적으로 “범위를 벗어난”테이블에 완전히 액세스 할 수 있습니다. 다른 사람이 함수를 호출하고 “사용자”테이블의 끝에 행을 추가하지 못하게하고 싶을 것입니다.

필자는 엄격한 조건을 충족하는 파일 및 테이블을 내보내거나 가져 오는 함수의 예를 포함 하여이 접근법을 확장하는 블로그 게시물을 작성 했습니다 .

고객 입장에서

다른 방법은 클라이언트 측 , 즉 응용 프로그램이나 스크립트에서 파일 처리 를 수행하는 것 입니다. Postgres 서버는 어떤 파일을 복사하고 있는지 알 필요가 없으며 데이터를 뱉어 내고 클라이언트는 파일을 어딘가에 넣습니다.

이것에 대한 기본 구문은 COPY TO STDOUT명령이며 pgAdmin과 같은 그래픽 도구는 멋진 대화 상자로 감싸줍니다.

psql명령 줄 클라이언트 라는 특별한 “메타 명령”이 \copy“진짜”모든 같은 옵션을 소요 COPY하지만, 클라이언트 내에서 실행됩니다 :

\copy (Select * From foo) To '/tmp/test.csv' With CSV

;메타 명령은 SQL 명령과 달리 줄 바꿈으로 종료되므로 종료는 없습니다 .

에서 워드 프로세서 :

COPY를 psql 명령 \ copy와 혼동하지 마십시오. \ copy는 CODY FROM STDIN 또는 COPY TO STDOUT을 호출 한 다음 psql 클라이언트가 액세스 할 수있는 파일로 데이터를 페치 / 저장합니다. 따라서 파일 접근성과 접근 권한은 \ copy가 사용될 때 서버가 아닌 클라이언트에 의존합니다.

응용 프로그램 프로그래밍 언어 는 데이터 푸시 또는 페치에 대한 지원도 제공 할 수 있지만 입 / 출력 스트림을 연결하는 방법이 없기 때문에 일반적으로 표준 SQL 문 내에서 COPY FROM STDIN/를 사용할 수 없습니다 TO STDOUT. PHP의 PostgreSQL의 처리기 ( 되지 PDO)은 매우 기본적인 포함 pg_copy_from하고 pg_copy_to대용량 데이터 세트에 대한 효율적인하지 않을 수 PHP 배열로부터 / 복사 기능한다.

답변

몇 가지 해결책이 있습니다.

1 `psql`명령

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

이것은 당신처럼, SSH를 통해 그것을 사용 할 수 있다는 큰 장점이있다 ssh postgres@host command취득 할 수 있도록을 –

2 postgres `copy`명령

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql 인터랙티브

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

모두 스크립트에서 사용할 수 있지만 # 1을 선호합니다.

4 pgadmin이지만 스크립팅 할 수 없습니다.

답변

터미널에서 (db에 연결된 동안) 출력을 cvs 파일로 설정하십시오.

1) 필드 구분 기호를 ','다음으로 설정하십시오 .

\f ','

2) 출력 형식을 정렬되지 않은 상태로 설정하십시오.

\a

3) 튜플 만 표시 :

\t

4) 출력 설정 :

\o '/tmp/yourOutputFile.csv'

5) 쿼리를 실행하십시오.

:select * from YOUR_TABLE

6) 출력 :

\o

그러면이 위치에서 csv 파일을 찾을 수 있습니다.

cd /tmp

scp명령을 사용하여 복사 하거나 nano를 사용하여 편집하십시오.

nano /tmp/yourOutputFile.csv

답변

헤더와 함께 특정 테이블의 모든 열에 관심이 있다면

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

이보다 조금 더 간단합니다

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

내가 아는 한, 그것은 동등합니다.

답변

CSV 수출 통일

이 정보는 실제로 잘 표현되지 않았습니다. 이것이 내가 이것을 이끌어 내야 할 두 번째이기 때문에, 나는 아무것도 없다면 나 자신을 상기시키기 위해 여기에 넣을 것입니다.

이 작업을 수행하는 가장 좋은 방법은 postgres에서 CSV를 가져 오는 것입니다 COPY ... TO STDOUT. 명령 을 사용하는 것 입니다. 여기 답변에 표시된 방식으로 수행하고 싶지는 않습니다. 명령을 사용하는 올바른 방법은 다음과 같습니다.

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

하나의 명령 만 기억하십시오!

ssh보다 사용하기에 좋습니다.

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

ssh의 docker 내부에서 사용하기에 좋습니다.

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

로컬 컴퓨터에서도 훌륭합니다.

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

또는 로컬 컴퓨터의 도커 내부?

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

또는 kubernetes 클러스터의 docker에서 HTTPS를 통해 ??? :

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

다재다능하고 많은 쉼표!

당신 도요?

그렇습니다, 여기 내 메모가 있습니다 :

사본

를 사용 /copy하면 psql명령을 실행하는 사용자로서 명령이 실행중인 모든 시스템에서 파일 작업을 효과적으로 실행할 수 있습니다 1 . 원격 서버에 연결하면 실행중인 시스템의 데이터 파일을 원격 서버와 간단하게 복사 할 수 있습니다 psql.

COPY백엔드 프로세스 사용자 계정 (기본값 postgres), 파일 경로 및 권한이 확인되고 그에 따라 적용 되므로 서버에서 파일 작업을 실행합니다 . 사용하는 TO STDOUT경우 파일 권한 검사가 무시됩니다.

psql결과 CSV를 최종적으로 상주하려는 시스템에서 실행하지 않는 경우이 두 옵션 모두 후속 파일 이동이 필요 합니다. 내 경험에 따르면 대부분 원격 서버로 작업 할 때 가장 가능성이 높습니다.

간단한 CSV 출력을 위해 ssh를 통한 TCP / IP 터널과 같은 원격 시스템에 TCP / IP 터널과 같은 것을 구성하는 것이 더 복잡하지만, 다른 출력 형식 (2 진)의 경우 /copy로컬 연결을 실행하여 터널링 된 연결 보다 낫습니다 psql. 비슷한 맥락에서, 대량 수입의 경우 소스 파일을 서버로 옮기고 사용하는 COPY것이 아마도 가장 높은 성능 옵션 일 것입니다.

PSQL 파라미터

psql 매개 변수를 사용하면 CSV와 같은 출력 형식을 지정할 수 있지만 호출기를 비활성화하고 헤더를 가져 오지 않아야한다는 단점이 있습니다.

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,
3,Truck,1,2017-10-02,,t,,0,,
4,Truck,2,2017-10-02,,t,,0,,

다른 도구들

아니요, 도구를 컴파일하거나 설치하지 않고 서버에서 CSV를 가져오고 싶습니다.

답변

오류 메시지가 나타 났으므로 \ COPY를 사용해야했습니다.

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

그래서 나는 다음을 사용했다.

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

그리고 그것은 작동하고 있습니다

답변

psql 당신을 위해 이것을 할 수 있습니다 :

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

man psql여기에 사용 된 옵션에 대한 도움말을 참조 하십시오.

답변