Skip to content
This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Commit 81bfd14

Browse files
adunstanpull[bot]
adunstan
authored and
pull[bot]
committedFeb 12, 2023
Add --schema and --exclude-schema options to vacuumdb.
These two new options can be used to either process all tables in specific schemas or to skip processing all tables in specific schemas. This change also refactors the handling of invalid combinations of command-line options to a new helper function. Author: Gilles Darold Reviewed-by: Justin Pryzby, Nathan Bossart and Michael Paquier. Discussion: https://postgr.es/m/929fbf3c-24b8-d454-811f-1d5898ab3e91%40migops.com
1 parent 45a6d94 commit 81bfd14

File tree

3 files changed

+239
-41
lines changed

3 files changed

+239
-41
lines changed
 

‎doc/src/sgml/ref/vacuumdb.sgml

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,40 @@ PostgreSQL documentation
3939
<arg choice="opt"><replaceable>dbname</replaceable></arg>
4040
</cmdsynopsis>
4141

42+
<cmdsynopsis>
43+
<command>vacuumdb</command>
44+
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
45+
<arg rep="repeat"><replaceable>option</replaceable></arg>
46+
47+
<arg choice="plain" rep="repeat">
48+
<arg choice="opt">
49+
<group choice="plain">
50+
<arg choice="plain">
51+
<arg choice="opt">
52+
<group choice="plain">
53+
<arg choice="plain"><option>-n</option></arg>
54+
<arg choice="plain"><option>--schema</option></arg>
55+
</group>
56+
<replaceable>schema</replaceable>
57+
</arg>
58+
</arg>
59+
60+
<arg choice="plain">
61+
<arg choice="opt">
62+
<group choice="plain">
63+
<arg choice="plain"><option>-N</option></arg>
64+
<arg choice="plain"><option>--exclude-schema</option></arg>
65+
</group>
66+
<replaceable>schema</replaceable>
67+
</arg>
68+
</arg>
69+
</group>
70+
</arg>
71+
</arg>
72+
73+
<arg choice="opt"><replaceable>dbname</replaceable></arg>
74+
</cmdsynopsis>
75+
4276
<cmdsynopsis>
4377
<command>vacuumdb</command>
4478
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
@@ -244,6 +278,30 @@ PostgreSQL documentation
244278
</listitem>
245279
</varlistentry>
246280

281+
<varlistentry>
282+
<term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
283+
<term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
284+
<listitem>
285+
<para>
286+
Clean or analyze all tables in
287+
<replaceable class="parameter">schema</replaceable> only. Multiple
288+
schemas can be vacuumed by writing multiple <option>-n</option> switches.
289+
</para>
290+
</listitem>
291+
</varlistentry>
292+
293+
<varlistentry>
294+
<term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
295+
<term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
296+
<listitem>
297+
<para>
298+
Do not clean or analyze any tables in
299+
<replaceable class="parameter">schema</replaceable>. Multiple schemas
300+
can be excluded by writing multiple <option>-N</option> switches.
301+
</para>
302+
</listitem>
303+
</varlistentry>
304+
247305
<varlistentry>
248306
<term><option>--no-index-cleanup</option></term>
249307
<listitem>
@@ -619,6 +677,14 @@ PostgreSQL documentation
619677
<prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</userinput>
620678
</screen></para>
621679

680+
<para>
681+
To clean all tables in the <literal>foo</literal> and <literal>bar</literal> schemas
682+
in a database named <literal>xyzzy</literal>:
683+
<screen>
684+
<prompt>$ </prompt><userinput>vacuumdb --schema='foo' --schema='bar' xyzzy</userinput>
685+
</screen></para>
686+
687+
622688
</refsect1>
623689

624690
<refsect1>

‎src/bin/scripts/t/100_vacuumdb.pl

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,8 @@
103103
CREATE TABLE funcidx (x int);
104104
INSERT INTO funcidx VALUES (0),(1),(2),(3);
105105
CREATE INDEX i0 ON funcidx ((f1(x)));
106+
CREATE SCHEMA "Foo";
107+
CREATE TABLE "Foo".bar(id int);
106108
|);
107109
$node->command_ok([qw|vacuumdb -Z --table="need""q(uot"(")x") postgres|],
108110
'column list');
@@ -146,5 +148,45 @@
146148
[ 'vacuumdb', '--min-xid-age', '2147483001', 'postgres' ],
147149
qr/GREATEST.*relfrozenxid.*2147483001/,
148150
'vacuumdb --table --min-xid-age');
151+
$node->issues_sql_like(
152+
[ 'vacuumdb', '--schema', '"Foo"', 'postgres' ],
153+
qr/VACUUM "Foo".bar/,
154+
'vacuumdb --schema');
155+
$node->issues_sql_like(
156+
[ 'vacuumdb', '--exclude-schema', '"Foo"', 'postgres' ],
157+
qr/(?:(?!VACUUM "Foo".bar).)*/,
158+
'vacuumdb --exclude-schema');
159+
$node->command_fails_like(
160+
[ 'vacuumdb', '-N', 'pg_catalog', '-t', 'pg_class', 'postgres', ],
161+
qr/cannot vacuum specific table\(s\) and exclude schema\(s\) at the same time/,
162+
'cannot use options -N and -t at the same time');
163+
$node->command_fails_like(
164+
[ 'vacuumdb', '-n', 'pg_catalog', '-t', 'pg_class', 'postgres' ],
165+
qr/cannot vacuum all tables in schema\(s\) and specific table\(s\) at the same time/,
166+
'cannot use options -n and -t at the same time');
167+
$node->command_fails_like(
168+
[ 'vacuumdb', '-n', 'pg_catalog', '-N', '"Foo"', 'postgres' ],
169+
qr/cannot vacuum all tables in schema\(s\) and exclude schema\(s\) at the same time/,
170+
'cannot use options -n and -N at the same time');
171+
$node->command_fails_like(
172+
[ 'vacuumdb', '-a', '-N', '"Foo"' ],
173+
qr/cannot exclude specific schema\(s\) in all databases/,
174+
'cannot use options -a and -N at the same time');
175+
$node->command_fails_like(
176+
[ 'vacuumdb', '-a', '-n', '"Foo"' ],
177+
qr/cannot vacuum specific schema\(s\) in all databases/,
178+
'cannot use options -a and -n at the same time');
179+
$node->command_fails_like(
180+
[ 'vacuumdb', '-a', '-t', '"Foo".bar' ],
181+
qr/cannot vacuum specific table\(s\) in all databases/,
182+
'cannot use options -a and -t at the same time');
183+
$node->command_fails_like(
184+
[ 'vacuumdb', '-a', '-d', 'postgres' ],
185+
qr/cannot vacuum all databases and a specific one at the same time/,
186+
'cannot use options -a and -d at the same time');
187+
$node->command_fails_like(
188+
[ 'vacuumdb', '-a', 'postgres' ],
189+
qr/cannot vacuum all databases and a specific one at the same time/,
190+
'cannot use option -a and a dbname as argument at the same time');
149191

150192
done_testing();
There was a problem loading the remainder of the diff.

0 commit comments

Comments
 (0)
Failed to load comments.