To understand PostgreSQL, you need to see it in use. An empty database is like a ghost town without houses.

For now, we will assume that you've already got a database. There are already more than 1,000 books on how to design your own database from nothing. So here we aim to help people who already have access to a PostgreSQL database but are still learning to use the PostgreSQL database management system.

The best way to start is by asking some simple questions to orient yourself and begin the process of understanding. Incidentally, these are also questions you'll need to answer if you ask someone else for help.

If you experience problems, then you'll need to double-check which version of the server you have. This will help you report a fault or to consult the correct version of the manual.

We will find out the version by directly querying the database server.

Connect to the database and issue the following command:

postgres # SELECT version();

You'll get a response that looks something like this:

PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

That's probably too much information all at once!

You may wonder, How long is it since the server started?

As mentioned in the previous recipe, 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 an output as follows:

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

Postgres stores the server start time, so we can access it directly, as follows:

postgres=# SELECT pg_postmaster_start_time...

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.

On Debian or Ubuntu systems, the default data directory location is /var/lib/postgresql/R.r/main.

Here, R.r...

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 have messages in it 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 [2507-2] LOG:  MultiXact member wraparound protections are now enabled
2016-09-01 19:37:41 GMT [2506-1] LOG:  database system is ready to accept connections
2016-09-01 19:37:41 GMT [2511-1] LOG:  autovacuum launcher started

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

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.

Connect as the postgres OS user, or another user with execute privileges on the server software.

We just need...

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 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.

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.tables
WHERE table_schema NOT IN ('information_schema',
'pg_catalog');

You can also look at the list of tables directly and judge whether the list is a small or large number.

In psql, you can see your own tables using the following command:

postgres@ebony:~/9.5/main$ psql -c "\d"
        List of relations
 Schema |   Name   | Type  |  Owner...

For planning or space monitoring, we often need to know how big the database is.

We can do this in the following ways:

• Look at the size of the files that make up the database server
• Run a SQL request to confirm the database size

If you look at the size of the actual files, you'll need to make sure that you include the data directory and all subdirectories, as well as all other directories that contain tablespaces. That can be tricky, and it is also difficult to break out all the different pieces.

The easiest way is to just ask the database a simple query, like this:

SELECT pg_database_size(current_database());

However, this is limited to only the current database...

How big is a table? What is the total size of all the parts of a table?

We can see the size of a table using this command:

postgres=# select pg_relation_size('pgbench_accounts');

The output of this command is as follows:

pg_relation_size
------------------
           13582336
(1 row)

We can also see the total size of a table including indexes and other related spaces, as follows:

postgres=# select pg_total_relation_size('pgbench_accounts');

The output is as follows:

pg_total_relation_size
------------------------
         15425536
(1 row)

We can also use a psql command, like this:

postgres=# \dt+ pgbench_accounts
                        List of relations
 Schema...

We've looked at getting the size of a specific table, so now it's time to widen the problem to related areas. Rather than having an absolute value for a specific table, let's look at the relative sizes.

The following basic query will tell us the 10 biggest tables:

SELECT table_name
,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

The tables are shown in descending order of size, with at the most 10 rows displayed. In this case, we look at all the tables in all the schemas, apart from tables in information_schema...

Counting is one of the easiest SQL statements, so it is also many people's first experience of a PostgreSQL query.

From any interface, the SQL command used to count rows is as follows:

SELECT count(*) FROM table;

This will return a single integer value as the result.

In psql, the command looks like the following:

postgres=# select count(*) from orders;
count
───────

     345
(1 row)

PostgreSQL can choose between two techniques available to compute the SQL...

We don't always need an accurate count of rows, especially on a large table that may take a long time to execute. Administrators often need to estimate how big a table is so that they can estimate how long other operations may take.

We can get a quick estimate of the number of rows in a table using roughly the same calculation that the Postgres optimizer uses:

SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size(oid)*reltuples/(8192*relpages)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;

This gives us the following output:

estimated_count
---------------------
             293
(1 row)

It returns a row count...

Every PostgreSQL database contains some objects that are brought in automatically when the database is created. Every user will find a pg_database system catalog that lists databases, as shown in the Listing databases on this database server recipe. There is little point in checking whether these objects exist, because even superusers are not allowed to drop them.

On the other hand, PostgreSQL comes with tens of collections of optional objects, called modules, or equivalently extensions. The database administrator can install or uninstall these objects depending on the requirements. They are not automatically included in a newly created database, because they might not be required by every use case. Users will install only the extensions they actually need, and when they need them; an extension can be installed while a database is up and running...

In most databases, there will be dependencies between objects in the database. Sometimes, we need to understand these dependencies to figure out how to perform certain actions. Let's look at this in detail.

We'll use the following simple database to understand the issues and investigate them. There are two tables, as follows:

CREATE TABLE orders (
orderid integer PRIMARY KEY
);
CREATE TABLE orderlines (
orderid integer
,lineid smallint
,PRIMARY KEY (orderid, lineid)
);

Now, we add a link between them to enforce what is known as Referential Integrity, as follows:

ALTER TABLE orderlines ADD FOREIGN KEY (orderid)
REFERENCES orders (orderid);

If we try to drop the referenced table...