Apply a recommendation

A finding that has a safe action carries the exact statement for it. You run that statement yourself, on your own schedule. Guardioz changes nothing in your database: the agent only reads, and it never runs the statements in a finding. This page says where each kind of statement runs, which PostgreSQL rights it needs, and when the finding changes afterwards.

Before you start

The finding open in Guardioz. See Read your findings.
An SQL client that connects to the monitored server: psql, or the client you normally use.
A PostgreSQL role that can run the statement. Each section names the rights it needs.

The monitoring role guardioz_monitor, which the installer creates, reads data for monitoring only and cannot run any of these statements. On the server that runs PostgreSQL, one way to open a session with the rights these statements need is to run psql as the operating-system user that owns PostgreSQL:

sudo -u postgres psql

Replace postgres with the operating-system user that owns PostgreSQL on your server.

Copy the statement

You copy the statement from one of three places:

The list on a finding page: the Apply cell for a setting, and the Fix column for an index, a table or a replication slot. The button Copy puts the statement on the clipboard and its label changes to Copied, or to Copy failed when the browser refuses the clipboard. A row whose Fix cell reads Review carries no statement.
The page of the object: the card Suggested change for a setting, and the section Recommended next step for an index, a table or a slot.
The checkbox at the start of a row that carries a statement: the flagged settings rows and the index rows. Select one or more rows, and a bar appears with the button Copy selected, which takes every selected statement at once. The passed settings rows, the table rows and the replication slot rows have no checkbox.

For settings, Copy selected puts the statements one after another, separated by a blank line. For indexes it groups them per database. Each block starts with a comment that names the database and with a \connect line. psql runs that line and connects to that database with the same user, host and port. For a name made only of the letters a to z and A to Z, digits, _ and ., the line is \connect "orders". For any other name it is \connect -reuse-previous=on "dbname='sales db'", the form pg_dump uses for such names, so that a space, a quote or an = in the name cannot turn into another argument. Another SQL client does not run the line: remove the \connect lines, connect yourself to the database named in the comment, and then run the statements of that block one at a time. An index in a database whose name contains a line break has no checkbox, because the comment and the \connect line of a block cannot carry a line break; copy its statement from its own row.

When a \connect line fails, for example because the database no longer exists, psql in an interactive session keeps the previous connection, and the statements after the line run in the database psql was connected to before the line. psql that reads the script from a file closes the previous connection instead (psql, \connect). To get that behaviour, save the copied script to a file and run it with psql -f and the file name.

Only the rows you can see can be selected. When the buttons Issues, All and Passed hide a row, its selection is cleared.

Read the statement before you run it, and confirm three things: that the finding is the current one and not an earlier observation, that the object it names is still the one you mean, and that the database in the statement is the database you are connected to. The fact Last checked on the group page says how long ago Guardioz last analysed that group; a page of an earlier analysis says so and links to the current finding.

Change a setting

The statement runs in an SQL session on the server you want to change. ALTER SYSTEM changes the parameter for the whole PostgreSQL instance, so the database you connect to does not matter. It writes the value to the postgresql.auto.conf file, which PostgreSQL reads in addition to postgresql.conf.

Rights: only superusers, and users granted the ALTER SYSTEM privilege on that parameter, can change it this way (ALTER SYSTEM). The function pg_reload_conf is restricted to superusers by default, and other users can be granted EXECUTE on it (system administration functions). When the setting allow_alter_system is off on your server, ALTER SYSTEM fails for every role, a superuser included. Change the value where the configuration of your server is managed, for example in postgresql.conf.

The statement has two lines:

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

The first line writes the value, and the second one makes the running server read it. The setting and the value in this example show the form of the statement. The statement in your finding carries the value that Guardioz computed for your server. If PostgreSQL answers with a permission error, the role you connected as does not have the rights named here.

To confirm the running value, read the setting in an SQL session on that server:

SHOW work_mem;

That is the value PostgreSQL is running with.

What Guardioz shows next: the agent reads that value in every collection, and the settings checks run on every snapshot that carries it. After the next snapshot is analysed, a setting that Guardioz no longer recommends changing moves to the passed checks with OK and No change needed. Its own page then shows Current setting is adequate, and the new value appears in the table Setting change history. The agent collects once an hour; to see the result sooner, run a collection by hand (Manage the agent service).

A setting that needs a restart

Two of the settings that Guardioz checks take effect only after PostgreSQL restarts: shared_buffers and max_worker_processes. Their rows carry the badge Requires restart, and the statement carries a comment in place of the reload:

ALTER SYSTEM SET shared_buffers = '8GB';
-- Requires a PostgreSQL restart to take effect (static parameter).

The value in this example shows the form of the statement, not a recommendation. Run the ALTER SYSTEM line the same way as any other setting. The new value starts working only after the restart.

After the restart, read the setting in an SQL session on that server to confirm the running value:

SHOW shared_buffers;

Until that restart, PostgreSQL runs the old value, the agent reports the old value, and the finding stays open. Guardioz shows no state that says a restart is waiting.

When one change comes before another

Several recommended values are computed from the current values of other settings. Guardioz names an order of changes in one case: when work_mem and shared_buffers both have an open recommendation, the card Why this value? on the work_mem page tells you to apply the shared_buffers change and restart first, and then to come back to work_mem. If you cannot restart now, the work_mem value is still safe to apply today. Look at work_mem again after the restart.

Change an index

An index statement runs in an SQL session connected to the database that holds the index. The index page names that database in the sentence it puts in front of the statement. When you use Copy selected, the comment at the top of each block names the database of that block.

A statement with CONCURRENTLY cannot run inside a transaction, so run one statement at a time, with the client set to commit each statement as it runs. psql does that by default; a graphical SQL client often opens a transaction for you. Such a statement can also run for several minutes on a large index, so keep the session open until it returns.

Statement	What it does	Rights it needs
`DROP INDEX CONCURRENTLY`	Drops the index without blocking selects, inserts, updates and deletes on its table.	You must be the owner of the index.
`ALTER TABLE ... DROP CONSTRAINT`	Drops the constraint together with the index under it. An index that a named constraint owns cannot be removed with `DROP INDEX`. It takes an ACCESS EXCLUSIVE lock on the table: it waits for the queries that use the table, and it blocks reads and writes of the table until it finishes. The drop itself is short.	You must own the table.
`REINDEX INDEX CONCURRENTLY`	Rebuilds the index in place, without blocking inserts, updates or deletes on its table.	The `MAINTAIN` privilege on the table.

Guardioz never gives a statement that drops a primary key; it rebuilds the key instead. When a broken primary key also has a healthy duplicate, the statement rebuilds the primary key first and then drops the duplicate, in one block.

A statement with CONCURRENTLY can fail or be cancelled, for example when the session closes. PostgreSQL then leaves an index behind that it marks unusable: queries do not use it, and depending on how far the statement got, it can still take disk space. A rebuild with REINDEX INDEX CONCURRENTLY leaves an index whose name ends in _ccnew or _ccold. The next analysis that carries the index catalog reports it under Invalid indexes, with the statement for it; for a _ccnew or _ccold index that statement is DROP INDEX CONCURRENTLY IF EXISTS. That catalog arrives every hour during the first 24 hours after the registration and once a day after that; a collection run by hand brings the report forward (Manage the agent service).

A rebuild of a unique index fails while the table holds rows with the same key, so run it again only after you have found and resolved those rows. Otherwise it fails again for the same reason and leaves one more unusable copy.

Put an index back

When Guardioz can state the way back exactly, the index page carries it under If you need it back, with its own Copy button. It is a CREATE INDEX CONCURRENTLY statement, or CREATE UNIQUE INDEX CONCURRENTLY for a unique index, built from the definition that PostgreSQL reported for the index that the recommended statement removes. There is no such statement when the recommended statement removes nothing (a rebuild), when it removes a copy that a rebuild left behind (a name that ends in _ccnew or _ccold), when a named constraint owns the index, and when the definition of the index was not collected or is not a plain CREATE INDEX statement.

What Guardioz shows next: the index catalog is collected every hour during the first 24 hours after the registration and once a day after that. After the next collection that carries it is analysed, the index leaves the finding, and when it was the last one the group turns to OK. A collection run by hand brings that forward (Manage the agent service).

Start the cleanup of a table earlier

A table in Tables behind on cleanup that has a safe value carries one statement. It sets a storage option on that table only, and it changes no setting of the server:

ALTER TABLE public.orders SET (autovacuum_vacuum_max_threshold = 2000000);

The table name and the number in this example show the form of the statement, not a recommendation. The statement in your finding carries the table and the value that Guardioz computed for your server.

The statement runs in an SQL session connected to the database that holds the table. The table page names that database in the sentence it puts in front of the statement. A table list can hold tables from several databases, and its Copy button copies the statement without the database name, so read the Database column of the row before you run it.

Rights: you must own the table to use ALTER TABLE (ALTER TABLE).

What the statement does: PostgreSQL starts automatic cleanup of a table after a number of its rows have become dead, and autovacuum_vacuum_max_threshold is the upper limit of that number (the setting). Set on a table, it replaces the server-wide limit for that table. The statement needs no restart. For an autovacuum storage parameter, ALTER TABLE takes a SHARE UPDATE EXCLUSIVE lock on the table. That lock mode does not conflict with the locks that SELECT, INSERT, UPDATE and DELETE take, so reads and writes of the table continue. It conflicts with another lock of the same mode and with the stronger ones, so the statement waits while a manual VACUUM, an ANALYZE, a CREATE INDEX CONCURRENTLY or another schema change runs on that table (table-level locks).

Cleanup makes space available for reuse inside the table. Smaller files on disk are not guaranteed. The lower number decides after how many dead rows cleanup of the table can start. It does not decide when autovacuum reaches the table.

Put the previous threshold back

The table page carries the way back under If you need it back, with its own Copy button. For a table that had no value of its own, the statement removes the storage option, and the server-wide setting applies to the table again:

ALTER TABLE public.orders RESET (autovacuum_vacuum_max_threshold);

For a table that already had its own value, the statement sets that value again. Copy the statement from the page of the table, not from this example.

To read the storage options a table has now, run this in an SQL session connected to the database that holds the table. Replace public.orders with the name of your table:

SELECT reloptions FROM pg_class WHERE oid = 'public.orders'::regclass;

What Guardioz shows next: the storage options of a table travel with the catalog, which is collected every hour during the first 24 hours after the registration and once a day after that. After the next collection that carries it is analysed, a table that Guardioz no longer reports leaves the finding, and when it was the last one the group turns to OK. The page of the table then says Resolved, or, when that analysis could not yet cover every table of the database, that a newer analysis of the server exists. A collection run by hand brings that forward (Manage the agent service).

Drop a replication slot

The statement runs in an SQL session on the server that holds the slot, with psql or another SQL client. The slot page names that server in the sentence it puts in front of the statement.

Rights: use of the functions for replication slots is restricted to superusers and users having the REPLICATION privilege (replication management functions).

Before you drop a slot, confirm what uses it. In an SQL session on the server that holds the slot, read what PostgreSQL reports about its slots:

SELECT slot_name, slot_type, database, active, active_pid FROM pg_replication_slots;

active is t while something is connected to the slot. A slot that is not active can still belong to a standby server that is switched off, to a logical subscriber, to a change data capture tool, or to a cluster manager that maintains slots, for example Patroni. Confirm with the people who run them before you drop it.

The statement that drops the slot:

SELECT pg_drop_replication_slot('SLOT_NAME');

Replace SLOT_NAME with the name of the slot. The statement on the slot page already carries the name.

A slot that belongs to the initial table synchronization of a logical subscription carries no drop statement. The subscription worker manages such a slot, and a long first copy of a table can keep it inactive for hours. Its page tells you to look at the table synchronization state of that subscription, in pg_subscription_rel.srsubstate, before you touch the slot. When the slot is invalidated or about to be invalidated, the page also tells you to read the log of the subscriber instead of dropping the slot yourself.

What Guardioz shows next: replication slots are part of every collection, so the slot leaves the finding after the next snapshot is analysed. The page of the slot keeps working and states that the slot is no longer flagged.

Findings without a statement

Not every finding carries one:

Open transactions and cleanup gives advice as text only. Guardioz gives no statement that ends a session. The advice on a row is to finish the transaction or to roll it back, or to let a maintenance operation finish rather than cancelling it.
Transaction log archiving gives its next step as text only. It is a diagnosis that you run yourself on the server: the lines to look for in the PostgreSQL server log, running the archive command by hand for the oldest waiting file, and pgbackrest check when pgBackRest does the archiving. When archive_mode is on and neither archive_command nor archive_library is set, the next step is to set one of them, or to set archive_mode to off, which needs a restart. Guardioz gives no statement that removes transaction log files or changes the archive command.
A table in Tables behind on cleanup carries a statement only where Guardioz can give a safe value. Without one, the Fix cell of the row reads Review, the page of the table has no Recommended next step, and its card Why it matters names the reason, for example that the size of the table or of one of its indexes is not measured yet. Every reason is listed under Tables behind on cleanup.
When raising max_wal_size would help but the data disk has no safe room for it, the Apply cell holds a note in place of a statement. There is a Copy button next to it, and nothing to run:
```
-- Add disk space on the data volume first; this setting cannot be raised safely until then.
```

If the finding stays open

A setting that needs a restart stays open until PostgreSQL restarts.
An index finding and a table finding wait for the next collection that carries the catalog: one per hour during the first 24 hours after the registration, and one per day after that.
When the setting still does not match what Guardioz recommends for your server, the finding stays open and carries the new recommendation. The page of the setting shows both the current value and the recommended one.
When nothing arrives at all, the agent is what to look at first: Agent and data problems.

Next steps

Read your findingsWhat a row says, what Age counts, and where a row leads.

What Guardioz checksOne entry per group of checks: what it looks at, and what you get.

When the agent collectsThe hourly, daily and weekly parts of a collection.