108
Chapter 23. Routine Database Maintenance Tasks
is that the normal XID space is circular with no endpoint. Therefore, once a row version has been
created with a particular normal XID, the row version will appear to be “in the past” for the next two
billion transactions, no matter which normal XID we are talking about. If the row version still exists
after more than two billion transactions, it will suddenly appear to be in the future. To prevent this,
frozen row versions are treated as if the inserting XID were
FrozenTransactionId
,so that they
will appear to be “inthe past” to all normal transactions regardless of wraparound issues, and so such
row versions will be valid until deleted, no matter how long that is.
vacuum_freeze_min_age controls how old an XID value has to be before its row version will be
frozen. Increasing this settingmay avoid unnecessary work if the rows thatwouldotherwise be frozen
will soon be modified again, but decreasing this setting increases the number of transactions that can
elapse before the table must be vacuumedagain.
VACUUM
normally skips pages that don’t have any dead row versions, but those pages might still
have row versions with old XID values. To ensure all old row versions have been frozen, a scan
of the whole table is needed. vacuum_freeze_table_age controls when
VACUUM
does that: a whole
table sweep is forced if the table hasn’t been fully scanned for
vacuum_freeze_table_age
minus
vacuum_freeze_min_age
transactions. Setting it to 0 forces
VACUUM
to always scan all pages,
effectively ignoring the visibility map.
The maximum time that a table can go unvacuumed is two billion transactions minus the
vacuum_freeze_min_age
value at the time
VACUUM
last scanned the whole table. If it were to
go unvacuumed for longer than that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain unfrozen rows with XIDs older than the age
specified by the configuration parameter autovacuum_freeze_max_age. (This will happen even if
autovacuum is disabled.)
This implies that if a table is not otherwise vacuumed, autovacuum will be invoked on it approxi-
matelyonce every
autovacuum_freeze_max_age
minus
vacuum_freeze_min_age
transactions.
For tables that are regularly vacuumed for space reclamation purposes, this is of little importance.
However, for static tables (including tables that receive inserts, but no updates or deletes), there is
no need to vacuum for space reclamation, so it can be useful to try to maximize the interval be-
tween forced autovacuums on very large static tables. Obviously one can do this either by increasing
autovacuum_freeze_max_age
or decreasing
vacuum_freeze_min_age
.
The
effective
maximum
for
vacuum_freeze_table_age
is
0.95
*
autovacuum_freeze_max_age
; a setting higher than that will be capped to the maximum.
A
value higher than
autovacuum_freeze_max_age
wouldn’t make sense because
an anti-wraparound autovacuum would be triggered at that point anyway, and the 0.95
multiplier leaves some breathing room to run a manual
VACUUM
before that happens. As
a rule of thumb,
vacuum_freeze_table_age
should be set to a value somewhat below
autovacuum_freeze_max_age
,leaving enough gap so that a regularly scheduled
VACUUM
or an
autovacuum triggered by normal delete and update activity is run in that window. Setting it too close
could leadto anti-wraparound autovacuums, even though the table was recently vacuumed to reclaim
space, whereas lower values lead to more frequent whole-table scans.
The
sole
disadvantage
of
increasing
autovacuum_freeze_max_age
(and
vacuum_freeze_table_age
along with it) is that the
pg_clog
subdirectory of the database
cluster will take more space, because it must store the commit status of all transactions back to the
autovacuum_freeze_max_age
horizon. The commit status uses two bits per transaction, so if
autovacuum_freeze_max_age
is set to its maximum allowed value of two billion,
pg_clog
can
be expected to grow to about half a gigabyte. If this is trivial compared to your total database size,
setting
autovacuum_freeze_max_age
to its maximum allowed value is recommended. Otherwise,
set it depending on what you are willing to allow for
pg_clog
storage. (The default, 200 million
transactions, translates to about 50MB of
pg_clog
storage.)
560
102
Chapter 23. Routine Database Maintenance Tasks
One disadvantage of decreasing
vacuum_freeze_min_age
is that it might cause
VACUUM
to do
useless work: freezing a row version is a waste of time if the rowis modified soon thereafter (causing
it to acquire a new XID). So the setting should be large enough that rows are not frozen until they are
unlikely to change any more.
To track the age of the oldest unfrozen XIDs in a database,
VACUUM
stores XID statistics in the
system tables
pg_class
and
pg_database
.In particular, the
relfrozenxid
column of a table’s
pg_class
row contains the freeze cutoff XID that was used by the last whole-table
VACUUM
for that
table. All rows inserted by transactions with XIDs older than this cutoff XID are guaranteed to have
been frozen. Similarly, the
datfrozenxid
column of a database’s
pg_database
row is a lower
bound on the unfrozen XIDs appearing in that database — it is just the minimum of the per-table
relfrozenxid
values within the database. A convenient way to examine this information is to exe-
cute queries such as:
SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN (’r’, ’m’);
SELECT datname, age(datfrozenxid) FROM pg_database;
The
age
columnmeasures the number of transactions from the cutoff XID tothe current transaction’s
XID.
VACUUM
normally only scans pages that have been modified since the last vacuum, but
relfrozenxid
can only be advanced when the whole table is scanned. The whole table is scanned
when
relfrozenxid
is more than
vacuum_freeze_table_age
transactions old, when
VACUUM
’s
FREEZE
option is used, or when allpages happento require vacuuming to remove dead row versions.
When
VACUUM
scans the whole table, after it’s finished
age(relfrozenxid)
should be a little
more than the
vacuum_freeze_min_age
setting that was used (more by the number of transactions
started since the
VACUUM
started). If no whole-table-scanning
VACUUM
is issued on the table until
autovacuum_freeze_max_age
is reached, an autovacuum will soon be forced for the table.
If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit
warning messages like this when the database’s oldest XIDs reach ten million transactions from the
wraparound point:
WARNING:
database "mydb" must be vacuumed within 177009986 transactions
HINT:
To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
(Amanual
VACUUM
shouldfixtheproblem, as suggestedbythehint; butnote that the
VACUUM
must be
performed by a superuser, else it will fail to process system catalogs and thus not be able to advance
the database’s
datfrozenxid
.) If these warnings are ignored, the system will shut down and refuse
to start any new transactions once there are fewer than 1 million transactions left until wraparound:
ERROR:
database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:
Stop the postmaster and vacuum that database in single-user mode.
The 1-million-transaction safety margin exists to let the administrator recover without data loss, by
manually executing the required
VACUUM
commands. However, since the system will not execute
commands once it has gone intothe safety shutdown mode, theonly waytodothis is tostop the server
and start the server in single-user mode to execute
VACUUM
.The shutdown mode is not enforced in
single-user mode. See the postgres reference page for details about using single-user mode.
561
79
Chapter 23. Routine Database Maintenance Tasks
23.1.5.1. Multixacts and Wraparound
Multixact IDs are used to support row locking by multiple transactions. Since there is only limited
space in a tuple header to store lock information, that information is encoded as a “multiple transac-
tion ID”, or multixact ID for short, whenever there is more than one transaction concurrently locking
arow. Information about which transaction IDs are included in any particular multixact ID is stored
separatelyin the
pg_multixact
subdirectory, and only the multixactID appears in the
xmax
field in
the tuple header. Like transaction IDs, multixact IDs are implemented as a 32-bit counter and corre-
sponding storage, all of which requires careful aging management, storage cleanup, and wraparound
handling. There is a separate storage area which holds the list of members in each multixact, which
also uses a 32-bit counter and which must also be managed.
During a
VACUUM
table scan, either partial or of the whole table, any multixact ID older than vac-
uum_multixact_freeze_min_age is replaced by a different value, which can be the zero value, a
single transaction ID, or a newer multixact ID. For each table,
pg_class
.
relminmxid
stores the
oldest possible multixact ID still appearing in any tuple of that table. If this value is older than
vacuum_multixact_freeze_table_age, a whole-table scan is forced. Whole-table
VACUUM
scans, re-
gardless of what causes them, enable advancing the value for that table. Eventually, as all tables in
all databases are scanned and their oldest multixact values are advanced, on-disk storage for older
multixacts can be removed.
As a safety device, a whole-table vacuum scan willoccur for anytable whose multixact-age is greater
than autovacuum_multixact_freeze_max_age. Whole-table vacuum scans will also occur progres-
sively for all tables, starting with those that have the oldest multixact-age, if the amount of used
member storage space exceeds the amount 50% of the addressible storage space. Both of these kinds
of whole-table scans will occur even if autovacuum is nominally disabled.
23.1.6. The Autovacuum Daemon
PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is
to automate the execution of
VACUUM
and
ANALYZE
commands. When enabled, autovacuum checks
for tables that have had a large number of inserted, updated or deleted tuples. These checks use the
statistics collection facility; therefore, autovacuum cannot be used unless track_counts is set to
true
.
In the default configuration, autovacuuming is enabled and the related configuration parameters are
appropriately set.
The “autovacuum daemon” actually consists of multiple processes. There is a persistent daemon pro-
cess, calledtheautovacuumlauncher, which is in charge of starting autovacuumworker processes for
all databases. The launcher will distribute the workacross time, attempting tostart one worker within
each database every autovacuum_naptime seconds. (Therefore, if the installation has
N
databases,
anew worker will be launched every
autovacuum_naptime
/
N
seconds.) A maximum of autovac-
uum_max_workers worker processes are allowed to run at the same time. If there are more than
autovacuum_max_workers
databases to be processed, the next database will be processed as soon
as the first worker finishes. Each worker process will check each table within its database and exe-
cute
VACUUM
and/or
ANALYZE
as needed.
log_autovacuum_min_duration
can be used to monitor
autovacuum activity.
If several large tables all become eligible for vacuuming in a short amount of time, all autovacuum
workers might become occupied with vacuuming those tables for a long period. This would result in
other tables and databases not being vacuumed until a worker became available. There is no limit on
how many workers might be in a single database, but workers do try to avoid repeating work that has
already been done by other workers. Note that the number of running workers does not count towards
max_connections or superuser_reserved_connections limits.
562
C# Word - Paragraph Processing in C#.NET Add references: C# users can set paragraph properties and create content such as run, footnote, endnote and picture in a paragraph.
adding image to pdf form; how to add picture to pdf
84
Chapter 23. Routine Database Maintenance Tasks
Tables whose
relfrozenxid
value is more than autovacuum_freeze_max_age transactions old are
always vacuumed (this also applies to those tables whose freeze max age has been modified via
storage parameters; see below). Otherwise, if the number of tuples obsoleted since the last
VACUUM
exceeds the “vacuum threshold”, the table is vacuumed. The vacuum threshold is defined as:
vacuum threshold = vacuum base threshold + vacuum scale factor
*
number of tuples
where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is au-
tovacuum_vacuum_scale_factor, and the number of tuples is
pg_class
.
reltuples
.The number of
obsolete tuples is obtained from the statistics collector; it is a semi-accurate count updated by each
UPDATE
and
DELETE
operation. (It is only semi-accurate because some information might be lost un-
der heavy load.) If the
relfrozenxid
value of the table is more than
vacuum_freeze_table_age
transactions old, the whole table is scanned to freeze old tuples and advance
relfrozenxid
,other-
wise only pages that have been modified since the last vacuum are scanned.
For analyze, a similar condition is used: the threshold, defined as:
analyze threshold = analyze base threshold + analyze scale factor
*
number of tuples
is compared to the total number of tuples inserted, updated, or deleted since the last
ANALYZE
.
Temporary tables cannot be accessed by autovacuum. Therefore, appropriate vacuum and analyze
operations should be performed via session SQL commands.
The default thresholds and scale factors are taken from
postgresql.conf
,but it is possible to
override them on a table-by-table basis; see Storage Parameters for more information. If a settinghas
been changed via storage parameters, that value is used; otherwise the global settings are used. See
Section 18.10 for more details on the global settings.
Besides the base threshold values and scale factors, there are six more autovacuum parameters that
can be set for each table via storage parameters. The first parameter,
autovacuum_enabled
,can be
set to
false
to instruct the autovacuum daemon to skip that particular table entirely. In this case
autovacuum will only touch the table if it must do so to prevent transaction ID wraparound. Another
two parameters,
autovacuum_vacuum_cost_delay
and
autovacuum_vacuum_cost_limit
,
are used to set table-specific values for the cost-based vacuum delay feature (see
Section
18.4.4).
autovacuum_freeze_min_age
,
autovacuum_freeze_max_age
and
autovacuum_freeze_table_age
are used to set values for vacuum_freeze_min_age,
autovacuum_freeze_max_age and vacuum_freeze_table_age respectively.
When multiple workers are running, the cost delay parameters are “balanced” among all the running
workers, so that the totalI/O impact on the system is the same regardless of the number of workers ac-
tually running. However, any workers processing tables whose
autovacuum_vacuum_cost_delay
or
autovacuum_vacuum_cost_limit
have been set are notconsidered in the balancing algorithm.
23.2. Routine Reindexing
In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command or a
series of individual rebuilding steps.
B-tree index pages that have become completely empty are reclaimed for re-use. However, there is
still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted,
the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range
are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is
recommended.
563
65
Chapter 23. Routine Database Maintenance Tasks
The potential for bloat in non-B-tree indexes has not been well researched. It is a good idea to peri-
odically monitor the index’s physical size when using any non-B-tree indextype.
Also, for B-tree indexes, a freshly-constructed index is slightly faster to access than one that has been
updated many times because logically adjacent pages are usually also physically adjacent in a newly
built index. (This consideration does not apply to non-B-tree indexes.) It might be worthwhile to
reindex periodically just to improve access speed.
REINDEX can be used safely and easily in all cases. But since the command requires an exclusive
table lock, it is often preferable to execute an index rebuild with a sequence of creation and replace-
ment steps. Index types that supportCREATE INDEX withthe
CONCURRENTLY
option can instead be
recreated that way. If that is successful and the resulting index is valid, the original index can then be
replacedby the newly built one using a combinationof ALTER INDEXandDROP INDEX. When an
index is used to enforce uniqueness or other constraints, ALTER TABLE might be necessary to swap
the existing constraint with one enforced by the new index. Review this alternate multi-step rebuild
approach carefullybefore using it as therearelimitations onwhich indexes canbe reindexed this way,
and errors must be handled.
23.3. Log File Maintenance
It is a good idea to save the database server’s log output somewhere, rather than just discarding it via
/dev/null
.The log output is invaluable when diagnosing problems. However, the log output tends
to be voluminous (especially at higher debug levels) so you won’t want to save it indefinitely. You
need to rotate the log files so that new log files are started and old ones removed after a reasonable
period of time.
If you simply direct the stderr of
postgres
into a file, you will have log output, but the only way
to truncate the log file is to stop and restart the server. This might be acceptable if you are using
PostgreSQL in a development environment, but few production servers would find this behavior ac-
ceptable.
A better approach is to send the server’s stderr output to some type of log rotation program.
There is a built-in log rotation facility, which you can use by setting the configuration parameter
logging_collector
to
true
in
postgresql.conf
.The control parameters for this program are
described in Section 18.8.1. You can also use this approach to capture the log data in machine
readable CSV (comma-separated values) format.
Alternatively, you might prefer to use an external log rotation program if you have one that you
are already using with other server software. For example, the rotatelogs tool included in the Apache
distributioncan be usedwith PostgreSQL. To do this, justpipe theserver’s stderr outputto the desired
program. If you start the server with
pg_ctl
,then stderr is already redirected to stdout, so you just
need a pipe command, for example:
pg_ctl start | rotatelogs /var/log/pgsql_log 86400
Another production-grade approach to managing log output is to send it to syslog and let syslog deal
with file rotation. To do this, set the configuration parameter
log_destination
to
syslog
(to log
to syslog only) in
postgresql.conf
.Then you can send a
SIGHUP
signal to the syslog daemon
whenever you want to force it to start writing a new log file. If you want to automate log rotation, the
logrotate program can be configured to work with log files from syslog.
On many systems, however, syslog is not very reliable, particularly with large log messages; it might
truncate or drop messages just when you need them the most. Also, on Linux, syslog will flush each
564
19
Chapter 23. Routine Database Maintenance Tasks
message to disk, yielding poor performance. (You can use a “
-
”at the start of the file name in the
syslog configuration file to disable syncing.)
Note thatall the solutions described above take care of starting new log files at configurable intervals,
but they do not handle deletion of old, no-longer-useful log files. You will probably want to set up a
batch job to periodically delete old log files. Another possibility is to configure the rotation program
so that old log files are overwritten cyclically.
pgBadger
2
is an external project that does sophisticated log file analysis. check_postgres
3
provides
Nagios alerts when important messages appear in the log files, as well as detection of many other
extraordinary conditions.
2. http://dalibo.github.io/pgbadger/
3. http://bucardo.org/wiki/Check_postgres
565
68
Chapter 24. Backup and Restore
As with everything that contains valuable data, PostgreSQL databases should be backed up regu-
larly. While the procedure is essentially simple, it is important to have a clear understanding of the
underlying techniques and assumptions.
There are three fundamentally different approaches to backing up PostgreSQL data:
•
SQL dump
•
File system level backup
•
Continuous archiving
Each has its own strengths and weaknesses; eachis discussed in turn in the following sections.
24.1. SQL Dump
The idea behind this dump method is to generate a file with SQL commands that, when fed back to
the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL
provides the utility program pg_dump for this purpose. The basic usage of this command is:
pg_dump
dbname
>
outfile
As you see, pg_dumpwrites its resulttothe standard output. We willsee below howthis canbeuseful.
While the above command creates a text file, pg_dump can create files in other formats that allow for
parallism and more fine-grained control of object restoration.
pg_dump is aregular PostgreSQL clientapplication(albeita particularly clever one). This means that
you can perform this backup procedure from any remote host that has access to the database. But
remember that pg_dump does not operate with special permissions. In particular, it must have read
access to all tables that you want to back up, so in order to back up the entire database you almost
always have to run it as a database superuser. (If you do not have sufficient privileges to back up the
entire database, you can still back up portions of the database to which you do have access using
options such as
-n
schema
or
-t
table
.)
To specify which database server pg_dump should contact, use the command line options
-h
host
and
-p
port
.The default host is the local host or whatever your
PGHOST
environment variable spec-
ifies. Similarly, the default port is indicated by the
PGPORT
environment variable or, failing that, by
the compiled-indefault. (Conveniently, the server will normally have the same compiled-in default.)
Like any other PostgreSQL client application, pg_dump will by default connect with the database
user name that is equal to the current operating system user name. To override this, either specify the
-U
option or set the environment variable
PGUSER
.Remember that pg_dump connections are subject
to the normal client authentication mechanisms (which are described in Chapter 19).
An important advantage of pg_dump over the other backupmethods described later is that pg_dump’s
output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and
continuous archiving are both extremely server-version-specific. pg_dump is also the only method
that will work when transferring a database to a different machine architecture, such as going from a
32-bit to a 64-bit server.
Dumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of
the database at the time pg_dump began running. pg_dump does not block other operations on the
database while it is working. (Exceptions are those operations that need to operate with an exclusive
lock, such as most forms of
ALTER TABLE
.)
566
68
Chapter 24. Backup and Restore
24.1.1. Restoring the Dump
Text files created by pg_dump are intended to be read in by the psql program. The general command
form to restore a dumpis
psql
dbname
<
infile
where
infile
is the file output by the pg_dump command. The database
dbname
will not be cre-
ated by this command, so you must create it yourself from
template0
before executing psql (e.g.,
with
createdb -T template0
dbname
). psql supports options similar to pg_dump for specifying
the database server to connect to and the user name to use. See the psql reference page for more
information. Non-text file dumps are restored using the pg_restore utility.
Before restoring an SQL dump, all the users who own objects or were grantedpermissions onobjects
in the dumped database must already exist. If they do not, the restore will fail to recreate the objects
with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is
not.)
By default, the psql scriptwill continue to execute after an SQL error is encountered. You might wish
to runpsql with the
ON_ERROR_STOP
variable set to alter thatbehavior and havepsql exitwith anexit
status of 3 if an SQL error occurs:
psql --set ON_ERROR_STOP=on dbname < infile
Either way, you will only have a partially restored database. Alternatively, you can specify that the
whole dumpshould be restored as a single transaction, so the restore is either fullycompleted or fully
rolled back. This mode can be specified by passing the
-1
or
--single-transaction
command-
line options to psql. When using this mode, be aware that even a minor error can rollback a restore
that has already run for many hours. However, that might still be preferable to manually cleaning up
acomplexdatabase after a partially restored dump.
The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database
directly from one server to another, for example:
pg_dump -h
host1 dbname
| psql -h
host2 dbname
Important: The dumps produced by pg_dump are relative to
template0
.This means that any
languages, procedures, etc. added via
template1
will also be dumped by pg_dump. As a result,
when restoring, if you are using a customized
template1
,you must create the empty database
from
template0
,as in the example above.
After restoring a backup, it is wise to run ANALYZE on each database so the query optimizer has
useful statistics; see Section 23.1.3 and Section 23.1.6for more information. For more advice on how
to load large amounts of data into PostgreSQL efficiently, refer to Section 14.4.
24.1.2. Using pg_dumpall
pg_dump dumps only a single database at a time, and it does not dump information about roles or
tablespaces (because those arecluster-wide rather thanper-database). To supportconvenient dumping
of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs
567
Documents you may be interested
Documents you may be interested
