Skip to content

Commit 7781f4e

Browse files
adunstanadunstan
committed
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 e1c95e5 commit 7781f4e

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();

0 commit comments

Comments
 (0)