Earlier today I was troubleshooting some resource issues on a PostgreSQL server and needed to test some various SELECT statements to see if any of them were causing problems. I also wanted to verify the amount of resources a SELECT statement made using PGAdmin versus the amount of resources the same SELECT statement used running it directly on the Postgres server itself. Below is information on how to run a SELECT statement from a Linux shell. In this example the server is running CentOS Linux and PostgreSQL 8.4.4.
Run Postgres SELECT Statement From Linux Shell:
- psql -U someuser somedatabase -c "select timestamp,percentage,device from batteries WHERE userid = '11111' AND timestamp > '2010-09-01 00:00:00+00' AND timestamp < '2010-09-30 00:00:00+00'" >> somefilename
The above command can be run directly from a Linux shell. Below is a description of each portion of the above command that will search the batteries table for entries relating to the userid specified.
- psql –> this runs the PostgreSQL interactive terminal
- -U someuser –> This specifies the Postgres user that will connect to the database. In this example it is someuser which should be replaced by the proper username for your PostgreSQL database server
- somedatabase —> This is the database on the PostgreSQL server that contains the PostgreSQL table you want to query. In this example somedatabase is used and should be replaced with the database you need to query.
- -c –> This is the switch used to specify the query that will run on the specified Postgres database. Anything in he double quotes is part of the query and would be the exact same as what can be run from the PostgreSQL command line.
- >> –> The double greater than signs tell the output to go to a file which in this case is named somefile. Replace that with a filename that can easily be remembered for the data you are inputing to it.
Using psql to issue select, insert, etc. commands from the Linux CLI can be very useful so you don’t have to connect to the database and it is much easier to output the results to a file from a Bash shell for instance.
The above command also shows that you can specify specific columns you want in the output instead of specifying a “*” which would output all columns. In the example noted above the timestamp, percentage, and deviceid will be output only.