Direct answer and solution
As noted in ruakh's answer, PostgreSQL meta-commands must occupy a single line. I work around this by creating a temporary table for \copy to process.
A quick transformation of your file, fp_query.txt, would look like this.
CREATE TEMP TABLE "temp_unique_name" AS
SELECT created_at::date, COUNT(*)
FROM ela_snapshots
WHERE created_at::date > CURRENT_DATE - 30
GROUP BY 1;
\copy "temp_uniq_name" to 'ELA_comp_tot_daily_sess.csv' with CSV HEADER
There are other improvements you might consider.
Additional discussion
Randomized name for temporary table
It's not likely you'll run into table-name collisions but, just the same, avoiding them is quite simple by creating a random name...
SELECT 'tmp_' || (RANDOM()*1e15)::INT8 AS table_name
\gset
... and using the name like this.
CREATE TEMP TABLE ":table_name" AS
[...query...]
\copy ":table_name" to 'ELA_comp_tot_daily_sess.csv' with CSV HEADER
Note the following about the psql features used here.
The \gset meta-command runs the query in the buffer and sets non-null values as variables named like the column aliases. For more information, look for \gset here. Importantly, note that the query does not end with a semicolon, as this would immediately execute the buffer!
Like all psql variables, the variables created by \gset are used by pre-pending a colon to the variable name, like :table_name.
The value of RANDOM()... should be CAST/converted to an INT in order to eliminate any decimal point, which would be troublesome in an unquoted table name and possibly confusing even when quoted. Here, I use a BIGINT (or the alias INT8) because of the large factor I'm multiplying by.
All that said, it's unlikely you are creating enough temporary tables to create conflicting names.
Dynamic filename for output
As long as you're using \gset to create a table-name, you might consider creating a dynamic filename for output.
SELECT 'tmp_' || (RANDOM()*1e15)::INT8 AS table_name,
'ELA_comp_tot_' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HHMMSS') || '.csv' AS file_name
\gset
Now, you can use the following.
\copy ":table_name" to ":file_name" with CSV HEADER
As written, this would output to something like ELA_comp_tot_20180404_142329.csv. The .csv extension doesn't alter the output, by the way, but better indicates the file contents, particularly to GUI based filesystem interfaces.
Syntax of \copy meta-command
Your sample code (which I've copied verbatim to avoid confusion), is using a COPY command syntax from prior to version 9.0, as mentioned here. Using the newer syntax, it would look like this:
\COPY ":table_name" TO ":file_name" WITH (FORMAT CSV, HEADER TRUE)
This is a minor point, as the syntax is still officially supported through version 9.x. It also doesn't matter that I like to capitalize KEYWORDS. ;-)
