Postgres pg_ctl Tips and Tricks

pg_ctl is an very useful but underrated utility that can make the lives of development teams easier. Read on to learn more about pg_ctl and how it can improve your development and test workflows.

What is pg_ctl?

pg_ctl is a command-line tool included in the standard Postgres distribution. It is available everywhere that Postgres itself is, similar to the other included tools like psql and pg_dump .

The executable will be in the same directory as the other Postgres binaries. The exact location varies with the Linux distro and Postgres version:

# debain, ubuntu, ...
/usr/lib/postgresql/11/bin

# rhel, centos, ...
/usr/pgsql-11/bin

You may want to add this directory to your PATH, or alias pg_ctl to the full path.

Create a Database Cluster

Unlike other RDBMSes, a single Postgres database server process (historically called the postmaster ), manages a database cluster . The usage of the term cluster is not modern, and does not refer to a group of networked nodes. A database cluster hosts a set of databases, with some features (roles, physical replication, WAL files, etc.) common to all of them. The Postgres systemd service that is installed by your Linux distro serves a single database cluster.

You can use pg_ctl to create a database cluster. At creation, the cluster lives entirely within a single directory. It contains all necessary configuration files ( postgres.conf , pg_hba.conf , etc.) and data files. It is self-contained, and can be moved to another reasonably similar machine if file permissions are handled correctly. You can even place log files inside the directory, so that you have all the related files (configuration, data, logs) in one place.

To create a database cluster, use:

$ pg_ctl -D myclus initdb

This creates a directory called myclus under the current directory, and populates it with all the files necessary to start a server from it.

Here is a sample session:

$ pg_ctl -D myclus initdb
The files belonging to this database system will be owned by user "alice".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory myclus ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/11/bin/pg_ctl -D myclus -l logfile start

Start a Database Server

A “Postgres server” is basically a postmaster process that is started with the location of a database cluster directory. This postmaster process in turn spawns multiple process that do various background activities as well as handle incoming connections. You can see this process model in action by viewing the system process tree using a tool like htop, for example.

To start a postmaster process for your new database cluster, use:

$ pg_ctl -D myclus -l myclus/log start

The -l option specifies the location of the Postgres log file, which in this case is within the cluster directory itself. It’s not uncommon to place the log file within the cluster directory.

You should see an output like this:

waiting for server to start.... done
server started

Reloading, restarting and stopping happen as you’d expect:

$ pg_ctl -D myclus -l myclus/log reload
server signaled
$ pg_ctl -D myclus -l myclus/log restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
$ pg_ctl -D myclus -l myclus/log stop
waiting for server to shut down.... done
server stopped

You should now be able to connect to this new cluster using clients like psql and pgAdmin .

Setting Port And Other Options

Practically though, if you already have Postgres installed on your machine, you’ll probably need to edit myclus/postgres.conf and change the values for port , unix_socket_directories and also maybe listen_address before the cluster starts up cleanly. This is because the system-installed Postgres service is already running on port 5432 and the directories in unix_socket_directories cannot be written to by a regular user. The default listen_address is localhost, meaning that you won’t be able to connect to the cluster from outside localhost.

If you’re using pg_ctl to create and teardown clusters in your automated test scripts, it is easier if you can specify these options directly from the command-line rathen than programmatically editing myclus/postgres.conf . You can specify the options like this:

$ pg_ctl -D myclus -l myclus/log -o "-p 6000 -k /tmp -i" start

This starts the server on port 6000, with the unix socket created in the directory /tmp and listening on all interfaces.

You need to specify these options only for “start”, you can omit them for other commands, including even “restart”.

Other Useful Start Options

There are a couple of other options that you can use inside “-o” that might be useful:

• -F disables fsync, useful to faster completion of test scripts
• -B shared_bufffers set value of shared_buffers , example -B 100MB
• -c conf_var=value set any configuration value, example -c wal_level=logical

Here’s an example with some of these set:

$ pg_ctl -D myclus -l myclus/log -o "-p 6000 -k /tmp -i -B 100MB -c wal_level=logical" start

These options are acually the command line options of the postgres process, the full list of which are documented here .

Run Database of a Different Postgres Version

EnterpriseDB hosts pre-built binaries for various Postgres versions for various platforms. These are tarballs without any installer.

Grab the tarball you want, unpack it, locate the pg_ctl binary with it, and use that to create a cluster. pg_ctl will automatically find the associated initdb/postgres/other binaries that it needs to create/start the cluster.

You can use this regardless, and independent of, any existing PostgreSQL installation on the machine.

Create Services on Windows

pg_ctl is available on all platforms, including MacOS and Windows. In particular, you can use it to easily create a service that can be started and stopped via the Service Control Manager (SCM). To create a service, use:

pg_ctl -D myclus -N myclus_service register

This creates an auto-start service called “myclus_service”.

This feature is available only in Postgres v10 and above.

About pgDash

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics .

pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here orsignup today for a free trial.