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
Options:
-L, --log-level [debug|info|warning|error|critical]
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.
--completion [bash|fish|zsh] Output completion for specified shell and
exit.
--help Show this message and exit.
Commands:
instance Manage instances.
pgconf Manage configuration of a PostgreSQL instance.
role Manage roles.
database Manage databases.
pghba Manage entries in HBA configuration of a PostgreSQL instance.
wal Manage WAL replay of a PostgreSQL instance.
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
Options:
--version [18|17|16|15|14] PostgreSQL version; if unspecified,
determined from site settings or most recent
PostgreSQL installation available on site.
--standby-for DSN DSN of primary for streaming replication.
--standby-password PASSWORD Password for the replication user.
--standby-slot SLOT Replication slot name. Must exist on
primary.
--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:
prefix: /srv/pglift
pgbackrest:
repository:
mode: path
path: /srv/pglift/backups
prometheus:
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 creating base pgBackRest configuration directory: /srv/pglift/etc/pgbackrest
INFO installing base pgBackRest configuration
INFO creating pgBackRest include directory: /srv/pglift/etc/pgbackrest/conf.d
INFO creating pgBackRest repository backups and archive directory: /srv/pglift/backups
INFO creating pgBackRest log directory: /srv/pglift/log/pgbackrest
INFO creating pgBackRest spool directory: /srv/pglift/srv/pgbackrest/spool
INFO creating PostgreSQL log directory: /srv/pglift/log/postgresql
Creating an instance¶
$ pglift instance create app --pgbackrest-stanza=app
INFO initializing PostgreSQL
INFO configuring PostgreSQL authentication
INFO configuring PostgreSQL
INFO starting PostgreSQL 18/app
INFO creating role 'prometheus'
INFO creating role 'backup'
INFO configuring Prometheus postgres_exporter 18-app
INFO configuring pgBackRest stanza 'app' for pg1-path=/srv/pglift/srv/pgsql/18/app/data
INFO creating pgBackRest stanza 'app'
INFO checking pgBackRest configuration for stanza 'app'
INFO starting Prometheus postgres_exporter 18-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 │ 18 │ 5432 │ /srv/pglift/srv/pgsql/18/app/data │ running │
│ test │ 18 │ 5433 │ /srv/pglift/srv/pgsql/18/test/data │ not_running │
└──────┴─────────┴──────┴────────────────────────────────────┴─────────────┘
Altering an instance¶
$ pglift instance alter app --port=5456
INFO configuring PostgreSQL
INFO reconfiguring Prometheus postgres_exporter 18-app
INFO restarting Prometheus postgres_exporter 18-app
INFO configuring pgBackRest stanza 'app' for pg1-path=/srv/pglift/srv/pgsql/18/app/data
> PostgreSQL needs to be restarted; restart now? [y/n] (n): y
INFO restarting PostgreSQL
INFO stopping PostgreSQL 18/app
INFO starting PostgreSQL 18/app
INFO starting Prometheus postgres_exporter 18-app
Getting instance information¶
$ pglift instance get app
name version port data_checksums locale encoding pending_restart replication_slots prometheus pgbackrest
app 18 5456 True C UTF8 False port: 9187 stanza: app
Note
The default output is quite terse but, using the -o json option to
instance get, one can get a lot more information.
Note
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 18/app needs reload due to parameter changes: log_connections
INFO reloading PostgreSQL configuration for 18/app
log_connections: None -> True
Adding and manipulating instance objects¶
$ pglift role -i app create dba --password --login
Password:
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,
"valid_until": null,
"memberships": [],
"hba_records": [],
"pgpass": false
}
$ pglift role -i app alter dba --connection-limit=10 --createdb
INFO altering role 'dba'
$ pglift role -i app get dba
name has_pas… inherit login superu… createdb create… replica… connec… valid_u… member… 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 schemas extensions locale publications subscriptions tablespace
myapp dba public C pg_default
$ pglift database -i app 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 schemas extensions locale publications subscriptions tablespace
myapp postgres public C pg_default