Using COPY in psql script does not write to file

Question

I have a script with multiple queries I am triying to write to a csv file:

copy (
   select /* very long query */
) to '/tmp/report.csv' with csv header; 

And I run the script like this

psql -d mydb -f produce_reports.sql

The script seems to work but nothing is written to /tmp.

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

Any idea why COPY report success but don`t write anything?

P.S tried runnig with sudo, with psql, and tries granting mydb user superuser.

Answer 1

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

No all of the commands don't have to be on the same line, but that's not the problem here. COPY is saving the file on the server. You want it on the client, that's what the psql command \COPY does. I assume you're connecting to the remote server which is where the file is. Likely you could just scp or rsync it back to the client or the like, but that's a bad idea. You're better off doing it with just a client.

There are numerous ways to put the \copy on a different line. For instance, take this test.psql

CREATE TEMP TABLE t AS VALUES (1,2), (2,3);
\COPY t TO /tmp/1
CREATE TEMP TABLE b AS VALUES (1,2), (2,3);
\COPY t TO /tmp/2

You can run it with psql -d test -f test.psql

• COPY is a PostgreSQL server-command that is made to look like SQL.
• \COPY is a PostgreSQL client (psql)-command that is made to look like COPY

Answer 2

Use the psql -c option.

[pol@UNKNOWN ~]$ psql --version
psql (PostgreSQL) 10.1

You can run a command like:

> psql -c  'SELECT * FROM bank_account;' test > bank_details.txt

(I have a database called test) and then:

> more bank_details.txt

which gives:

 account_id | first_name |   last_name   | address_number |         address_street1         |         address_street2         |          address_town           | address_county | balance 
------------+------------+---------------+----------------+---------------------------------+---------------------------------+---------------------------------+----------------+---------
          1 | 83fbc      | 613f37db7f46  |             65 | a91fd                           | af5aae2642a7f418c306da          | 02                              | Dublin         |     668
          2 | cbca6      | c36eabe7b07f  |             28 | 295301                          | 45f260962b7eed                  | b4bdf5a03bf6ab                  | Dublin         |     438
          3 | 62a47      | 9ca84de       |             66 | c7bdbafb606d5bd605e8254         | c6d2a6104f457f2                 | ce8c579526c009ee50fd6c728da363  | Dublin         |     358
          4 | 371e       | 27ce5752d5    |             21 | ec2401c9db6                     | ed7d8a1507a                     | 1bcef72a619e7239215a            | Dublin         |     730
          5 | 70bb6      | a767ad3       |              6 | fe76de803945b87a01aebded        | 708193a5484239284fb15cf68d5d5   | 67face4e59f38f793ab10c5         | Dublin         |     785

(not real data - needless to say :-) )

Edit:

Now, if you wish to run several commands one after the other and have data being written to various files, then you have to do something like this:

Create your script file pg.sh - chmod 755 it.

[pol@UNKNOWN pgtest]$ more pg.sh
psql -c "\o mydata.txt" -c "SELECT * FROM bank_account;" -d test
psql -c "CREATE TABLE mytest(f1 INTEGER, f2 VARCHAR (50));" -d test
psql -c "INSERT INTO mytest VALUES (34, 'First record test...');" -d test
psql -c "INSERT INTO mytest VALUES (36, 'Second record test'  );" -d test
psql -c "\o another_file.txt" -c "SELECT * FROM mytest;" -d test
psql -c "\i /home/pol/pgtest/test.sql" -d test
[pol@UNKNOWN pgtest]$ 

Then simply

> ./pg.sh

And then you have two new files - mydata.txt and another_file.txt with your data in them! Plus you've carried out manipulations in between time!

Also, input yet another sql script - test.sql - see below.

I also created a file called test.sql

[pol@UNKNOWN pgtest]$ more test.sql 
SELECT * FROM bank_account;
SELECT * FROM mytest;

And run this using

psql -f sql.sh -d test

you can redirect to the file of your choice! Or run this script from insider another as per above!

You can also peruse the PostgreSQL manual pages (which are excellent!) here:

-c command --command=command Specifies that psql is to execute the given command string, command. This option can be repeated and combined in any order with the -f option. When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands within a -c option. To achieve that, you could use repeated -c options or pipe the string into psql, for example:

psql -c '\x' -c 'SELECT * FROM foo;' or

echo '\x \ SELECT * FROM foo;' | psql (\ is the separator meta-command.)

Each SQL command string passed to -c is sent to the server as a single query. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. Also, psql only prints the result of the last SQL command in the string. This is different from the behavior when the same string is read from a file or fed to psql's standard input, because then psql sends each SQL command separately.

Because of this behavior, putting more than one command in a single -c string often has unexpected results. It's better to use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:

psql << EOF \x SELECT * FROM foo; EOF

So, it's good for integrating into your scripts - make sure that any passwords are stored securely and not visible using ps -ef or similar command!

Answer 3

Any idea why COPY report success but don`t write anything?

First we need to rule out the obvious: that the server can run on a different machine than the client, so their respective /tmp would be different. Since your invocation of psql does not have any -h option, let's assume that it's a local connection.

The next cause would be that, since the write doesn't fail, it writes into a directory that differs from the directory you're checking in.

For instance, systemd may assign private /tmp and /var/tmp directories to certain services. You may see PrivateTmp in systemd docs, and check if the startup script of your OS uses that, or something similar.

I'm also finding this related PostgreSQL question on DBA.se, which is about reading not writing, but the same root cause: Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

The easy solution is to not use /tmp for these export files. Create a specific directory somewhere below /var with permissions such that postgres can write into it and your user(s) can read from it.