In general production environments, databases need to enable archive mode, so how to enable archive mode in PG?
The archive configuration in pg involves several parameters as follows:
# - Archiving -
Whether to enable archives
#archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
Archive command，Notice %p %f %% The meaning of formatting。
%p It's archivedredoFile path，
%f It's archivedredoDocument file name
%% Yes a percent sign
#archive_command = '' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# . 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
Timeout forced archive，example：if10No activity in the database，oneredoThe file has not been finished，Will not archive，
But we want the database to be at least10分钟要切换one日志，Then it can be usedarchive_timeout
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
– The archive configuration method is:
1. Create an archive directory
pg12@oracle-> mkdir -p $PGDATA/archive/
2. Edit the archive script
The script can also delete archived logs within 7 days.
pg12@oracle-> vi $PGDATA/
test ! -f $PGDATA/arch/$1 && cp --preserve=timestamps $2 $PGDATA/arch/$1 ; find /arch/ -type f -mtime +7 -exec rm -f {} \;
3. Configure archive-related parameters
wal_level = replica archive_mode = on archive_command = ' %f %p'
After the configuration is complete, just restart the database service.
pg12@oracle-> ps -ef|grep archiver pg12 21338 21331 0 20:20 ? 00:00:00 postgres: archiver
Supplement: Postgresql archive failed and an error was reported in the log file
An archive failure occurred during PG operation, and an error occurred in the log file.
The error is reported as follows:
cp: writing `/arch/20171204/000000010000000000000002': No space left on device LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: DATE=`date +%Y%m%d`;DIR="/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp pg_xlog/000000010000000000000002 $DIR/000000010000000000000002 cp: writing `/arch/20171204/000000010000000000000002': No space left on device LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: DATE=`date +%Y%m%d`;DIR="/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp pg_xlog/000000010000000000000002 $DIR/000000010000000000000002 cp: writing `/arch/20171204/000000010000000000000002': No space left on device LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: DATE=`date +%Y%m%d`;DIR="/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp pg_xlog/000000010000000000000002 $DIR/000000010000000000000002 WARNING: archiving transaction log file "000000010000000000000002" failed too many times, will try again later
The reason is that the file system/arch space where the archive log is located is insufficient.
By cleaning out expiring temporary files under this file system, or storing archive logs into larger system space.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.