PostgreSQL is an open source object-relational database management system (ORDBMS) distributed under a very permissive license, and developed by an active community.

There are a number of PostgreSQL-related services and software (https://wiki.postgresql.org/wiki/PostgreSQL_derived_databases), either open source or not, that are provided by other software companies. Here, we discuss how to recognize which one you are using.

It is not so easy to detect the variant of PostgreSQL from the name; many of the products and services involving PostgreSQL include the word Postgres or PostgreSQL.

However, if you need to check the documentation, or to buy services such as support and consulting, you need to find out exactly what type your server is, as the available options will vary.

If you are paying a license fee or a cloud service...

PostgreSQL has internal version numbers for the data file format, database catalog layout, and crash recovery format. Each of these is checked as the server runs to ensure that the data doesn't become corrupt. PostgreSQL doesn't change these internal formats for a single release; they only change across releases.

From a user's perspective, each release differs in terms of the way the server behaves. If you know your application well, then it should be possible to assess the differences simply by reading the release notes for each version. In many cases, a retest of the application is the safest thing to do.

If you experience any general problems related to setup and configuration with your database, then you'll need to double-check which version of the server you have. This will help you to report a fault or to consult...

You may wonder, how long has it been since the server started?

For instance, you might want to verify that there was no server crash if your server is not monitored; or to see when the server was last restarted, for instance, to change the configuration. We will find this out by asking the database server.

Issue the following SQL from any interface:

postgres=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

You should get the output as follows:

     uptime 
--------------------------------------
 2 days 02:48:04

Database server files are initially stored in a location referred to as the data directory. Additional data files may also be stored in tablespaces, if any exist.

In this recipe, you will learn how to find the location of these directories on a given database server.

You'll need to get operating system access to the database system, which is what we call the platform on which the database runs.

The following are the system default data directory locations:

• Debian or Ubuntu systems: /var...

The database server's message log is a record of all messages recorded by the database server. This is the first place to look if you have server problems, and a good place to check regularly.

This log will include messages that look something like the following:

2016-09-01 19:37:41 GMT [2507-1] LOG:  database system was shut down at 2016-09-01 19:37:38 GMT

2016-09-01 19:37:41 GMT [2506-1] LOG:  database system is ready to accept connections

We'll explain some more about these logs once we've located the files.

You'll need to get operating system access to the database system, which is what we call the platform on which the database runs.

The...

Each database server has a system identifier assigned when the database is initialized (created). The server identifier remains the same if the server is backed up, cloned, and so on.

Many actions on the server are keyed to the system identifier, and you may be asked to provide this information when you report a fault.

In this recipe, you will learn how to display the system identifier.

You need to connect as the Postgres OS user, or another user with execute privileges on the server software.

When we connect to PostgreSQL, we always connect to just one specific database on any database server. If there are many databases on a single server, it can get confusing, so sometimes you may just want to find out which databases are parts of the database server.

This is also confusing because we can use the word database in two different, but related, contexts. Initially, we start off by thinking that PostgreSQL is a database in which we put data, referring to the whole database server by just the word database. In PostgreSQL, a database server (also known as cluster) is potentially split into multiple, individual databases, so, as you get more used to working with PostgreSQL, you'll start to separate the two concepts.

The number of tables in a relational database is a good measure of the complexity of a database, so it is a simple way to get to know any database. But the complexity of what? Well, a complex database may have been designed to be deliberately flexible in order to cover a variety of business situations, or a complex business process may have a limited portion of its details covered in the database. So, a large number of tables might likely reveal a complex business process, or just a complex piece of software.

In this recipe, we will show you how to compute the number of tables.

From any interface, type the following SQL command:

SELECT count(*) FROM information_schema...