Speichern der PL/pgSQL-Ausgabe von PostgreSQL in eine CSV-Datei

Question

Wie kann ich die PL/pgSQL-Ausgabe einer PostgreSQL-Datenbank am einfachsten in einer CSV-Datei speichern?

Ich verwende PostgreSQL 8.4 mit pgAdmin III und dem PSQL-Plugin, von dem aus ich Abfragen ausführe.

Answer 1

Möchten Sie die resultierende Datei auf dem Server oder auf dem Client haben?

Server-Seite

Wenn Sie etwas einfaches wiederverwenden oder automatisieren wollen, können Sie Postgresql's eingebautes KOPIEREN Befehl. z.B.

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

Dieser Ansatz läuft vollständig auf dem entfernten Server - es kann nicht auf Ihren lokalen PC schreiben. Außerdem muss es als Postgres-"Superuser" (normalerweise "Root" genannt) ausgeführt werden, da Postgres es nicht davon abhalten kann, unangenehme Dinge mit dem lokalen Dateisystem des Rechners anzustellen.

Das bedeutet nicht, dass Sie als Superuser verbunden sein müssen (das zu automatisieren wäre ein Sicherheitsrisiko anderer Art), denn Sie können die SECURITY DEFINER Option zu CREATE FUNCTION um eine Funktion zu erstellen, die läuft so, als ob Sie ein Superuser wären .

Das Entscheidende ist, dass Ihre Funktion dazu da ist, zusätzliche Prüfungen durchzuführen und nicht nur die Sicherheit zu umgehen. Sie könnten also eine Funktion schreiben, die genau die Daten exportiert, die Sie benötigen, oder Sie könnten etwas schreiben, das verschiedene Optionen akzeptiert, solange sie eine strenge Whitelist erfüllen. Sie müssen zwei Dinge überprüfen:

1. Welche Dateien Sollte der Benutzer die Möglichkeit haben, auf der Festplatte zu lesen/schreiben? Das kann zum Beispiel ein bestimmtes Verzeichnis sein, und der Dateiname muss einen geeigneten Präfix oder eine Erweiterung haben.
2. Welche Tabellen Soll der Benutzer die Möglichkeit haben, in der Datenbank zu lesen und zu schreiben? Dies wird normalerweise definiert durch GRANT s in der Datenbank, aber die Funktion wird jetzt als Superuser ausgeführt, so dass auf Tabellen, die normalerweise "out of bounds" wären, vollständig zugegriffen werden kann. Sie wollen wahrscheinlich nicht, dass jemand Ihre Funktion aufruft und Zeilen am Ende Ihrer Tabelle "Benutzer" hinzufügt

Ich habe geschrieben einen Blogbeitrag, in dem dieser Ansatz erläutert wird einschließlich einiger Beispiele für Funktionen, die Dateien und Tabellen unter strengen Bedingungen exportieren (oder importieren).

---

Kunden-Seite

Der andere Ansatz ist die Dateiverarbeitung auf der Client-Seite durchführen d.h. in Ihrer Anwendung oder Ihrem Skript. Der Postgres-Server muss nicht wissen, in welche Datei Sie kopieren, er spuckt die Daten einfach aus und der Client legt sie irgendwo ab.

Die zugrunde liegende Syntax hierfür ist die COPY TO STDOUT Befehl, und grafische Werkzeuge wie pgAdmin verpacken ihn für Sie in einen schönen Dialog.

El psql Kommandozeilen-Client hat einen speziellen "Meta-Befehl" namens \copy die die gleichen Optionen wie das "echte" COPY , sondern wird innerhalb des Clients ausgeführt:

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

Beachten Sie, dass es keine abschließende ; weil Meta-Befehle im Gegensatz zu SQL-Befehlen mit einem Zeilenumbruch beendet werden.

De die Dokumente :

Verwechseln Sie COPY nicht mit der Anweisung psql \copy. \copy ruft COPY FROM STDIN oder COPY TO STDOUT auf und holt/speichert dann die Daten in einer Datei, auf die der psql-Client zugreifen kann. Somit hängen der Dateizugriff und die Zugriffsrechte vom Client und nicht vom Server ab, wenn \copy verwendet wird.

Ihre Anwendungsprogrammiersprache Mai haben auch Unterstützung für das Schieben oder Holen der Daten, aber Sie können im Allgemeinen nicht verwenden COPY FROM STDIN / TO STDOUT innerhalb einer Standard-SQL-Anweisung, da es keine Möglichkeit gibt, den Ein-/Ausgabestrom zu verbinden. Der PostgreSQL-Handler von PHP ( no PDO) umfasst sehr grundlegende pg_copy_from y pg_copy_to Funktionen, die in/aus einem PHP-Array kopieren, was bei großen Datensätzen möglicherweise nicht effizient ist.

Answer 2

Es gibt mehrere Lösungen:

1 psql Befehl

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

Dies hat den großen Vorteil, dass man es über SSH benutzen kann, wie ssh postgres@host command - die es Ihnen ermöglichen

2 Postgres copy Befehl

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

3 psql interaktiv (oder nicht)

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

Sie können alle in Skripten verwendet werden, aber ich bevorzuge Nr. 1.

4 pgadmin, aber das ist nicht skriptfähig.

Answer 3

Setzen Sie im Terminal (während Sie mit der Datenbank verbunden sind) die Ausgabe auf die cvs-Datei

1) Setzen Sie das Feldtrennzeichen auf ',' :

\f ','

2) Setzen Sie das Ausgabeformat auf unaligned:

\a

3) Nur Tupel anzeigen:

\t

4) Ausgang einstellen:

\o '/tmp/yourOutputFile.csv'

5) Führen Sie Ihre Abfrage aus:

:select * from YOUR_TABLE

6) Ausgang:

\o

Sie können dann Ihre csv-Datei an diesem Ort finden:

cd /tmp

Kopieren Sie es mit der Taste scp Befehl oder mit nano bearbeiten:

nano /tmp/yourOutputFile.csv

Answer 4

CSV-Export Vereinheitlichung

Diese Informationen sind nicht wirklich gut dargestellt. A

Der beste Weg, dies zu tun (CSV aus Postgres herauszuholen), ist die Verwendung der COPY ... TO STDOUT Befehl. Allerdings sollten Sie es nicht so machen, wie in den Antworten hier gezeigt. Die richtige Art, den Befehl zu verwenden, ist:

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

Erinnern Sie sich an einen einzigen Befehl!

Es ist ideal für die Verwendung über ssh:

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

Es ist ideal für die Verwendung innerhalb von Docker über ssh:

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

Es funktioniert sogar auf dem lokalen Rechner:

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

Oder innerhalb von Docker auf dem lokalen Rechner?

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

Oder auf einem Kubernetes-Cluster, in Docker, über HTTPS :

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

So vielseitig, viele Kommas!

Wissen Sie das überhaupt?

Ja, das habe ich, hier sind meine Notizen:

Die COPYses

Verwendung von /copy führt effektiv Dateivorgänge auf dem System aus, das der psql unter dem der Befehl ausgeführt wird, als der Benutzer, der ihn ausführt 1 . Wenn Sie eine Verbindung zu einem entfernten Server herstellen, ist es einfach, die Dateien auf dem System zu kopieren, das die psql zum/vom entfernten Server.

COPY führt Dateivorgänge auf dem Server als Benutzerkonto des Backend-Prozesses aus (Standard postgres ), werden Dateipfade und Berechtigungen geprüft und entsprechend angewendet. Bei Verwendung von TO STDOUT dann werden die Prüfungen der Dateiberechtigungen umgangen.

Beide Optionen erfordern eine anschließende Dateiverschiebung, wenn psql nicht auf dem System ausgeführt wird, auf dem die resultierende CSV-Datei letztendlich gespeichert werden soll. Dies ist meiner Erfahrung nach der wahrscheinlichste Fall, wenn Sie hauptsächlich mit entfernten Servern arbeiten.

Die Konfiguration eines TCP/IP-Tunnels über ssh zu einem entfernten System ist für eine einfache CSV-Ausgabe etwas komplexer, aber für andere Ausgabeformate (binär) ist es möglicherweise besser /copy über eine getunnelte Verbindung, die Ausführung einer lokalen psql . Ähnlich verhält es sich bei großen Importen, wenn Sie die Quelldatei auf den Server verschieben und mit COPY ist wahrscheinlich die leistungsstärkste Option.

PSQL-Parameter

Mit psql-Parametern können Sie die Ausgabe wie eine CSV-Datei formatieren, aber es gibt auch Nachteile, z. B. müssen Sie daran denken, den Pager zu deaktivieren, und Sie erhalten keine Kopfzeilen:

$ 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,,

Andere Tools

Nein, ich möchte nur CSV von meinem Server abrufen, ohne ein Tool zu kompilieren und/oder zu installieren.

Answer 5

Die neue Version - psql 12 - wird unterstützen --csv .

psql - Entwicklung

--csv

Wechselt in den Ausgabemodus CSV (Comma-Separated Values). Dies ist gleichbedeutend mit \pset csv-Format .

---

csv_fieldsep

Gibt das Feldtrennzeichen an, das im CSV-Ausgabeformat verwendet werden soll. Wenn das Trennzeichen im Wert eines Feldes vorkommt, wird dieses Feld gemäß den CSV-Standardregeln in doppelten Anführungszeichen ausgegeben. Die Vorgabe ist ein Komma.

Verwendung:

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv