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?

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.

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.