Postgres has the ability to create clusters of databases that run on the same machine concurrently. One thing that I did not know about postgres is that you can also run multiple versions of postgres at the same machine concurrently along with the mutliple clusters mentioned earlier. But how can one view information about all these instances and clusters and how can we connect to each of them and how can one upgrade the clusters running on one version to another?
I am running Debian on my personal computer and the rest of this post is going to be a very brief description of how I managed to upgrade and tidy up after my postgres instances running on my local computer. Please note that this is not an in depth post nor I suggest in any way this approach for a live / production upgrade of postgres.
So let’s get started!
First of all let’s see which versions of postgres are running on our box. As a super user execute the following command
When using the command line clients of MySQL or Postgresql it is often the case that the output of some select statement does not display very well, either because the query returns too many columns or the contents of a column are too long. In such cases it would be really nice to display the query results line by line in a vertical listing instead of the horizontal listing. A similar display can be found in both MySQL and Postgresql. So, let’s assume that you have a table that was created with the following SQL statement:
The two basic types of indexes in PostgreSQL are B-trees and Hash. Personally I have only used the B-trees, but recently a friend of mine asked me a question about the way PostgreSQL handles indexes on text fields internally. The question was: “Assume that you have created an index on a varchar field and then you issue a query to retrieve some results. Does your query use the index with the LIKE operator?”