.. SPDX-FileCopyrightText: 2021 Dalibo .. .. SPDX-License-Identifier: GPL-3.0-or-later Using the Command Line Interface ================================ .. highlight:: console 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 :ref:`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 :ref:`detailed chapter ` on the topic. For the purpose of this tutorial, we'll simply define so-called :ref:`site-settings ` to declare the use of two satellite components *pgBackRest* for physical backup and *Prometheus postgres_exporter*: .. code-block:: yaml :caption: File ~/.config/pglift/settings.yaml 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=`` option to ``instance create`` command, see :doc:`/howto/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 ----------------- .. code-block:: none $ 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 :ref:`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 .. code-block:: none $ 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