Chapter 4: Server Control

The recipes in this chapter will show you how to control the database server directly. Database servers in the cloud do not give access to the privileges that are required to perform many of the actions listed in this chapter, but there are things worth considering if you want to understand what is happening within.

This chapter covers the following recipes:

• Overview of controlling the database server
• Starting the database server manually
• Stopping the server safely and quickly
• Stopping the server in an emergency
• Reloading the server configuration files
• Restarting the server quickly
• Preventing new connections
• Restricting users to only one session each
• Pushing users off the system
• Deciding on a design for multitenancy
• Using multiple schemas
• Giving users their own private databases
• Running multiple servers on one system
• Setting up a connection pool
• Accessing multiple servers using the same host...

Overview of controlling the database server

PostgreSQL consists of a set of server processes, the group leader of which is named the postmaster, though that name is not visible as a process title in later versions. Starting the server is the act of creating these processes, and stopping the server is the act of terminating those processes.

Each postmaster listens for client connection requests on a defined port number. Multiple concurrently running postmasters cannot share that port number. The port number is often used to uniquely identify a particular postmaster and hence also the database server that it leads.

When we start a database server, we refer to a data directory, which contains the heart and soul – or at least the data – of our database. Subsidiary tablespaces may contain some data outside the main data directory, so the data directory is just the main central location and not the only place where data for that...

Starting the database server manually

Typically, the PostgreSQL server will start automatically when the system boots. You may opt to stop and start the server manually, or you may need to start it or shut it down for various operational reasons.

Getting ready

First, you need to understand the difference between the service and the server. The word server refers to the database server and its processes. The word service refers to the operating system wrapper that the server gets called by. The server works in essentially the same way on every platform, whereas each operating system and distribution has its own concept of a service.

Moreover, the way services are managed has changed recently: for instance, at the time of writing, most Linux distributions have adopted the systemd service manager. This means that you need to know which distribution and release you are using to find the correct variant of this recipe...

Stopping the server safely and quickly

There are several modes you can use to stop the server, depending on the level of urgency. We'll compare the effects in each mode.

How to do it…

There are two variants: with and without systemd. This is similar to the previous recipe, Starting the database server manually, which we'll refer to for further information. For example, what is the exact name of the systemd service unit for a given database server on a given GNU/Linux distribution?

When using systemd, you can stop PostgreSQL using fast mode by issuing the following after replacing SERVICEUNIT with the appropriate systemd service unit name:

sudo systemctl stop SERVICEUNIT

If systemd is not available and you are using Debian or Ubuntu, the command is as follows, which applies to the default version 14 instance:

pg_ctlcluster 14 main stop -m fast

Fast mode is the default since...

Stopping the server in an emergency

If nothing else is working, we may need to stop the server quickly, without caring about disconnecting the clients gently.

Break the glass in case of emergency!

How to do it…

Follow these steps to stop the server:

1. The basic command to perform an emergency stop on the server is as follows:

   pg_ctl -D datadir stop -m immediate

2. On Debian/Ubuntu, you can also use the following command:

   pg_ctlcluster 14 main stop -m immediate

As we mentioned in the previous recipe, this is just a wrapper around pg_ctl. From this example, we can see that it can pass through the -m immediate option.

In the previous recipe, we saw examples where the systemctl command was used to stop a server safely; however, this command cannot be used to perform an emergency stop.

How it works…

When you do an immediate stop, all the users have their transactions aborted and all their connections are...

Reloading the server configuration files

Some PostgreSQL configuration parameters can only be changed by reloading the entire configuration files. Note that in some cloud-based database services, this occurs automatically when parameters are changed, so this is not relevant.

How to do it…

There are two variants of this recipe, depending on whether you are using systemd. This is similar to the previous recipes in this chapter, especially the Starting the database server manually recipe. More details are provided there, such as the exact names of the systemd service units, depending on which database server you want to reload, and which GNU/Linux distribution you are working on.

With systemd, configuration files can be reloaded with the following syntax:

sudo systemctl reload SERVICEUNIT

Here, SERVICEUNIT must be replaced with the exact name of the systemd service unit for the server(s) that you want...

Restarting the server quickly

Some of the database server parameters require you to stop and start the server again fully. Doing this as quickly as possible can be very important in some cases. The best time to do this is usually a quiet time, with lots of planning, testing, and forethought. Sometimes, not everything goes according to plan.

How to do it…

Many of the recipes in this chapter are presented in two forms: one with systemd and one without. This may look repetitive or boring, but it's unavoidable because introducing a new system does not automatically eliminate all existing alternatives or migrate old installations to new ones.

As we mentioned previously, you can find further systemd details, including details on service unit names, in the previous recipe, Starting the database server manually.

A PostgreSQL server that's managed by systemd can be restarted in fast mode by issuing the following...

Preventing new connections

In certain emergencies, you may need to lock down the server completely, or just prevent specific users from accessing the database. It's hard to foresee all the situations where you may need to do this, so we will present a range of options.

How to do it…

Connections can be prevented in several ways, as follows:

1. Pause and resume the session pool. See the Setting up a connection pool recipe, later in this chapter, on controlling connection pools.
2. Stop the server! See the Stopping the server safely and quickly and the Stopping the server in an emergency recipes, though this is not recommended.
3. Restrict the connections for a specific database to zero by setting the connection limit to 0:

   ALTER DATABASE foo_db CONNECTION LIMIT 0;

This will limit normal users from connecting to that database, though it will still allow superuser connections.

4. Restrict the connections for a...

Restricting users to only one session each

If resources need to be closely controlled, you may wish to restrict users so that they can only connect to the server once, at most. The same technique can be used to prevent connections entirely for that user.

How to do it…

We can restrict users to only one connection using the following command:

postgres=# ALTER ROLE fred CONNECTION LIMIT 1;
ALTER ROLE

This will then cause any additional connections to receive the following error message:

FATAL: too many connections for role "fred"

You can eliminate this restriction by setting the value to -1.

It's possible to set the limit to zero or any positive integer. You can set this to a number other than max_connections, though it is up to you to make sense of that if you do.

Setting the value to zero will completely restrict normal connections. Note that even if you set the connection limit to zero for superusers, they will...

Pushing users off the system

Sometimes, we may need to remove groups of users from the database server for various operational reasons. Let's learn how to do this.

How to do it…

You can terminate a user's session with the pg_terminate_backend() function, which is included with PostgreSQL. This function takes the PID, or the process ID, of the user's session on the server. This process is known as the backend, and it is a different system process from the program that runs the client.

To find the PID of a user, we can look at the pg_stat_activity view. We can use it in a query, like this:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE ...

There are a couple of things to note if you run this query. If the WHERE clause doesn't match any sessions, then you won't get any output from the query. Similarly, if it matches multiple rows, you will get a fairly useless result – that is, a list...

Deciding on a design for multitenancy

There are many reasons why we may want to split groups of tables or applications: security, resource control, convenience, and so on. Whatever the reason, we often need to separate groups of tables (I avoid saying the word database, just to avoid various kinds of confusion).

This topic is frequently referred to as multitenancy, though this is not a fully accepted term yet.

The purpose of this recipe is to discuss the options we have so that we can move on to other, more detailed recipes.

How to do it…

If you want to run multiple physical databases on one server, then you have four main options, which are as follows:

• Option 0 (default): Run separate PostgreSQL instances in separate virtual machines on the same physical server. This is the default option in cloud systems such as EDB BigAnimal, as well as in on-premise deployments such as VMware or Kubernetes-based services.
• Option 1: Run...

Using multiple schemas

We can separate groups of tables into namespaces, referred to as schemas by PostgreSQL. In many ways, they can be thought of as being similar to directories, though that is not a precise description, and schemas are not arranged in a hierarchy.

Getting ready

Make sure you've read the Deciding on a design for multitenancy recipe so that you're certain that this is the route you wish to take. Other options exist, and they may be preferable in some cases.

How to do it…

Follow these steps:

1. Schemas can easily be created using the following commands:

   CREATE SCHEMA finance; 
   CREATE SCHEMA sales;

2. Then, we can create objects directly within those schemas using fully qualified names, like this:

   CREATE TABLE finance.month_end_snapshot (.....)

The default schema where an object is created is known as current_schema. We can find out what our current schema is by using the following...

Giving users their own private databases

Separating data and users is a key part of administration. There will always be a need to give users a private, secure, or simply risk-free area (sandbox) to use the database. Here's how.

Getting ready

Again, make sure you've read the Deciding on a design for multitenancy recipe so that you're certain this is the route you wish to take. Other options exist, and they may be preferable in some cases.

How to do it…

Follow these steps to create a database with restricted access for a specific user:

1. We can create a database for a specific user with some ease. From the command line, as a superuser, we can do the following:

   postgres=# create user fred;
   CREATE ROLE
   postgres=# create database fred owner fred;
   CREATE DATABASE

2. As database owners, users have login privileges, so they can connect to any database by default. There is a command named ALTER DEFAULT PRIVILEGES for...

Running multiple servers on one system

Running multiple PostgreSQL servers on one physical system is possible if it is convenient for your needs.

Getting ready

Once again, make sure that you've read the Deciding on a design for multitenancy recipe so that you're certain this is the route you wish to take. Other options exist, and they may be preferable in some cases.

How to do it…

The core version of PostgreSQL easily allows multiple servers to run on the same system, but there are a few wrinkles to be aware of.

Some installer versions create a PostgreSQL data directory named data. When this happens, it gets a little difficult to have more than one data directory without using different directory structures and names.

Debian/Ubuntu packagers chose a layout specifically designed to allow multiple servers potentially running with different software release levels. You may remember this from the Locating the database...

Setting up a connection pool

A connection pool is a term that's used for a collection of already-connected sessions that can be used to reduce the overhead of connection and reconnection.

There are various ways by which connection pools can be provided, depending on the software stack in use. The best option is to look at the server-side connection pool software because that works for all connection types, not just within a single software stack.

In this recipe, we're going to look at PgBouncer, which is designed as a very lightweight connection pool. Its name comes from the idea that the pool can be paused and resumed to allow the server to be restarted or bounced.

Getting ready

First of all, decide where you're going to store the PgBouncer parameter files, log files, and PID files. PgBouncer can manage more than one database server's connections at the same time, though that probably isn't wise for simple...

Accessing multiple servers using the same host and port

We will now show you one simple, yet important, application of the previous recipe, Setting up a connection pool. In that recipe, you learned how to reuse connections with PgBouncer, and thus reduce the cost of disconnecting and reconnecting.

Here, we will demonstrate another way to use PgBouncer – one instance can connect to databases hosted by different database servers at the same time. These databases can be on separate hosts and can even have different major versions of PostgreSQL!

Getting ready

Suppose we have three database servers, each one hosting one database. All you need to know beforehand is the connection string for each database server.

More complex arrangements are possible, but those are left to you as an exercise.

Before you try this recipe, you should have already gone through the previous recipe. These two recipes have many steps in common, but we've kept them separate because...