Using the Command Line Interface

This tutorial illustrates how to use the command-line interface (CLI) of pglift. pglift provides a CLI that can be used as follows:

$ pglift --help
Usage: pglift [OPTIONS] COMMAND [ARGS]...

Deploy production-ready instances of PostgreSQL

                                  Set log threshold (default to INFO when
                                  logging to stderr or WARNING when logging
                                  to a file).
  -l, --log-file LOGFILE          Write logs to LOGFILE, instead of stderr.
  --interactive / --non-interactive
                                  Interactively prompt for confirmation when
                                  needed (the default), or automatically pick
                                  the default option for all choices.
  --version                       Show program version.
  --help                          Show this message and exit.

  instance  Manage instances.
  pgconf    Manage configuration of a PostgreSQL instance.
  role      Manage roles.
  database  Manage databases.

There are several entry points (“commands”) corresponding to main objects handled by pglift: instances, roles, databases, pgconf, etc. Each entry point has its own help:

$ pglift instance create --help
Usage: pglift instance create [OPTIONS] NAME

  Initialize a PostgreSQL instance

  --version VERSION            Postgresql version.
  --port PORT                  Tcp port the postgresql instance will be
                               listening to.
  --help                       Show this message and exit.

Most top-level commands like database or role operate on a particular instance which needs to be specified through -i/--instance option; the option is required unless there is only one existing instance.

For more details about logging, see the detailed explanation.

Site configuration

Before actually using pglift to manage PostgreSQL instances, it is often needed to configure the site (i.e. the target machine). The main documentation includes a detailed chapter on the topic. For the purpose of this tutorial, we’ll simply define so-called site-settings to declare the use of two satellite components pgBackRest for physical backup and Prometheus postgres_exporter:

File ~/.config/pglift/settings.yaml
 prefix: /srv/pglift
     mode: path
     path: /srv/pglift/backups
   execpath: /usr/bin/prometheus-postgres-exporter

Also note the prefix field, which defines a global prefix under which all configuration and data files will be installed (unless specified otherwise).

With that in place, the site can be configured by running:

$ pglift site-configure install
INFO     installing base pgbackrest configuration
INFO     creating pgbackrest include directory
INFO     creating pgbackrest repository path
INFO     creating common pgbackrest directories
INFO     creating postgresql log directory

Creating an instance

$ pglift instance create app --pgbackrest-stanza=app
INFO     initializing PostgreSQL
INFO     configuring PostgreSQL authentication
INFO     configuring PostgreSQL
INFO     starting PostgreSQL 16-app
INFO     creating role 'prometheus'
INFO     creating role 'backup'
INFO     altering role 'backup'
INFO     configuring Prometheus postgres_exporter 16-app
INFO     configuring pgBackRest stanza 'app' for pg1-path=...
INFO     creating pgBackRest stanza app
INFO     starting Prometheus postgres_exporter 16-app

There are many more options to instance create commands, some built in and some depending on the activation of satellite components. For example, a standby instance can also be created by passing the --standby-for=<primary dsn> option to instance create command, see Standby setup for dedicated documentation.

The instance actually consists of a PostgreSQL instance with a backup service (pgbackrest) and a monitoring service (Prometheus postgres_exporter) set up.

Listing instances

$ pglift instance list
┃ name ┃ version ┃ port ┃ datadir                            ┃ status      ┃
│ app  │ 16      │ 5432 │ /srv/pglift/srv/pgsql/16/app/data  │ running     │
│ test │ 16      │ 5433 │ /srv/pglift/srv/pgsql/16/test/data │ not_running │

Altering an instance

$ pglift instance alter app --port=5456
INFO     configuring PostgreSQL
INFO     reconfiguring Prometheus postgres_exporter 16-app
INFO     restarting Prometheus postgres_exporter 16-app
INFO     configuring pgBackRest stanza 'app' for pg1-path=...
> PostgreSQL needs to be restarted; restart now? [y/n] (n): y
INFO     restarting PostgreSQL
INFO     stopping PostgreSQL 16-app
INFO     starting PostgreSQL 16-app
INFO     starting Prometheus postgres_exporter 16-app

Getting instance information

$ pglift instance get app
 name  version  port  data_checksums  locale  encoding  pending_restart  pgbackrest   prometheus
 app   16       5456  False           C       UTF8      False            stanza: app  port: 9187


The default output is quite terse but, using the -o json option to instance get, one can get a lot more information.


PostgreSQL instance configuration can be managed using the pgconf command, as described in more details in the dedicated section. A few quick examples:

$ pglift pgconf -i app show log_connections
# log_connections = off
$ pglift pgconf -i app set log_connections=on
INFO     configuring PostgreSQL
INFO     instance 16/app needs reload due to parameter changes: log_connections
INFO     reloading PostgreSQL configuration for 16-app
log_connections: None -> True

Adding and manipulating instance objects

$ pglift role -i 16/app create dba --password --login
Repeat for confirmation:
INFO     creating role 'dba'
$ pglift role -i app get dba -o json
  "name": "dba",
  "has_password": true,
  "inherit": true,
  "login": true,
  "superuser": false,
  "createdb": false,
  "createrole": false,
  "replication": false,
  "connection_limit": null,
  "validity": null,
  "in_roles": [],
  "pgpass": false
$ pglift role -i 16/app alter dba --connection-limit=10 --createdb
INFO     altering role 'dba'
$ pglift role -i app get dba
 name  has_pas…  inherit  login  superus…  createdb  create…  replica…  connec…  validity  in_rol…  pgpass
 dba   True      True     True   False     True      False    False     10                          False
$ pglift database -i app create myapp
$ pglift database -i app alter myapp --owner dba
$ pglift database -i app get myapp
 name   owner  settings  publications  subscriptions  tablespace
 myapp  dba                                           pg_default
$ pglift database -i 13/main list
┃ name      ┃ owner    ┃ encoding ┃ collation ┃ ctype ┃ acls        ┃ size   ┃ description  ┃ tablespace  ┃
│ myapp     │ dba      │ UTF8     │ C         │ C     │             │ 7.9 MB │              │ name:       │
│           │          │          │           │       │             │        │              │ pg_default  │
│           │          │          │           │       │             │        │              │ location:   │
│           │          │          │           │       │             │        │              │ size: 31.4  │
│           │          │          │           │       │             │        │              │ MB          │
│ postgres  │ postgres │ UTF8     │ C         │ C     │             │ 7.8 MB │ default      │ name:       │
│           │          │          │           │       │             │        │ administrat… │ pg_default  │
│           │          │          │           │       │             │        │ connection   │ location:   │
│           │          │          │           │       │             │        │ database     │ size: 31.4  │
│           │          │          │           │       │             │        │              │ MB          │
│ template1 │ postgres │ UTF8     │ C         │ C     │ =c/postgre… │ 7.9 MB │ default      │ name:       │
│           │          │          │           │       │ postgres=C… │        │ template for │ pg_default  │
│           │          │          │           │       │             │        │ new          │ location:   │
│           │          │          │           │       │             │        │ databases    │ size: 31.4  │
│           │          │          │           │       │             │        │              │ MB          │
$ pglift database -i app drop myapp
$ pglift role -i app drop dba
INFO     dropping role 'dba'

Dropping a role

If role is the owner of PostgreSQL objects (e.g. databases, tables, functions, …) you will get an error:

$ pglift role -i app drop dba
INFO     dropping role 'dba'
Error: role "dba" cannot be dropped because some objects depend on it (detail: owner of database myapp)

You now have two options, delete the owned items:

$ pglift role -i app drop dba --drop-owned
INFO     dropping role 'dba'

$ pglift database -i app get myapp
Error: database 'myapp' not found

or reassign them to a new user:

$ pglift role -i app drop dba --reassign-owned postgres
INFO     dropping role 'dba'

$ pglift database -i app get myapp
 name   owner     settings  publications  subscriptions  tablespace
 myapp  postgres                                         pg_default