Postgres clusters and cluster upgrades

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

Continue reading “Postgres clusters and cluster upgrades”

How to disable the pager in PostgreSQL

PostgreSQL uses a pager to display the results of an operation you performed when you are using the command line tool (psql).

You can disable the pager by issuing:

pset pager off

with which you specifically request that the pager is disabled. You can re-enable the pager by issuing:

pset pager on

with which you specifically request that the pager is enabled. Alternatively you can toggle the status of the pager by issuing

pset pager

which depending on the current status of the pager will either enable or disable it.

Vertical display output in MySQL and Postgresql

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:

Continue reading “Vertical display output in MySQL and Postgresql”

PostgreSQL pattern matching indexes

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?”

The answer to that actually was No.

Continue reading “PostgreSQL pattern matching indexes”