Technology Insights

PostgreSQL: Run A SELECT Statement From A Linux Shell Using PSQL

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.

Technology Insights

How To Export From PGAdmin, Export PGAdmin Data To CSV

This afternoon I was using PGAdmin and needed to export some data from a query I had run but realized I wasn’t even sure if PGAdmin had this capability. Typically I would export data using the PSQL command line but since I was already working in PGAdmin I wanted to try and figure out if the capability existed and if so how to do it. After some playing around I was able to figure out how to export PGAdmin data to a CSV (Comma Separated Value) format that would be Microsoft Excel friendly. Follow the directions below to export PGAdmin data to a .CSV file to open in MS Excel.

Technology Insights

PostgreSQL: Server Instrumentation Not Installed. The Server Lacks Instrumentation Functions.

When connecting to a PostgreSQL database using pgAdmin you may receive an error letting you know that the server instrumentation is not installed. This is a warning to let you know that you can install the adminpack contribution which will provide more functionality to pgAdmin including the ability to modify the pg_hba.conf and postgresql.conf configuration files. Below is information on the pop up warning you may receive when logging into a Postgres database using pgAdmin III as well as how to install the adminpack to not receive the error any longer.

Technology Insights

PostgreSQL: PSQL Select Distinct Output * To Generate New Table

Earlier this evening I ran into a unique issue where a PostgreSQL table had been created with no primary key this allowing duplicate rows to exist in the database table. You are unable to delete such rows in a SQL table because there is no primary key. After not being able to delete the rows via the PSQL CLI I opened pgAdmin III to see if I could delete the rows using the GUI interface provided by pgAdmin III however the delete was grey in the menu as shown in the below image.

Technology Insights

Backup and Restore a Postgres Database Using pg_dump and psql

Backing up and restoring a postgreSQL database is easy from the CLI. Some people prefer using pgAdmin III however through the GUI and over a network will use a lot more resources so it is recommended to backup and restore via your Linux CLI. Follow the simple steps below to backup and restore one Postgres database from a Linux server to another Linux server.

  1. Backup One PostgreSQL Database: Login to your server and issue the below command from the CLI. In the below syntax change username to the correct username (such as postgres), the database_name to the proper database, and the filename to something meaningful. The filename should end in .sql so you know it is a postgreSQL backup file which will eventually end in .gz once you compress it using gzip.