What's the quickest way to periodically export PostgreSQL data to a local file on Windows?

Question

I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed.

Ideally, I'd like to have the results of a few queries saved to local text files that I can then parse as needed, but if SQL dumping everything works, I'm fine with that too.

While I have experience with databases and programming, I'm not strong with either Windows or PostgreSQL.

Should I just install PostgreSQL on this Windows machine and use the command line tools that would then be found there to create a batch file? Or is there a better option?

• user1822: No need to install Postgres (although that is quite easy). But it's probably the easiest way to get the command line client psql. There are many other SQL clients for Postgres. Several of them also support exporting data into different formats: wiki.postgresql.org/wiki/…

Answer 1

Install the psql client (I believe it's in the pgAdmin III install). Add it to your PATH.

Put the following in %appdata%\postgresql\pgpass.conf (you may need to create that file):

hostname:port:database:username:password

In cmd:

psql -c "\copy (select * from foo) to 'fooLocal.txt';" --host $hostname --dbname $dbname --user $username

then fooLocal.txt will be a tab-delimited representation of the output of your query.

\copy (as opposed to COPY) can copy data from a remote server and doesn't need superuser privileges.

If you want to run that psql command on a schedule, you can use Task Scheduler (GUI) or schtasks (CLI), which is somewhat like cron for Windows.

• Craig Ringer: schtasks is just the command line UI for the Scheduled Tasks system. A Windows admin may be much more comfortable setting up the task via the UI. For that matter, I use cron daily and I still like the task scheduler UI on Windows.

Answer 2

The best way to copy a result set into a file is through COPY.

i.e.:

COPY (SELECT ...) TO 'filename.csv';

To load the data, you can use the FROM clause. This command is Operating System agnostic, as it is a plain formatted text file.

• user1822: But that will create the file on the server not on the client.
• Craig Ringer: @a_horse_with_no_name We really need a prominent note in the COPY docs that points people at psql's \copy command. It's non-obvious to most people (understandably) and not very discoverable. I should send a patch...