How to enable archive log in postgres database ?
In this tutorial i will explained about how to enable archivelog in postgresql server
- WAL Archive log In PostgreSQL database system, the actual database ‘writes’ to an addition file called write-ahead log (WAL) to disk.
- It contains a record of writes that made in the database system. In the case of Crash, database can be repaired/recovered from these records.
- Normally, the write-ahead log logs at regular intervals (called Checkpoints) matched against the database and then deleted because it no longer is required. You can also use the WAL as a backup because,there is a record of all writes made to the database.
WAL Archiving Concept :
- In pg_xlog/pg_wal write ahead logs are stored. It is the log file, where all the logs are stored of committed and uncommitted transaction. It contains max 6 logs, and last one overwrites. If archiver is on, it moves there.
- The write-ahead log is composed of each 16 MB large, which are called segments.Â
- The WALs reside under pg_xlog/pg_wal directory and it is the subdirectory of ‘data directory’. The filenames will have numerical(0-9) and character(a-z) named in ascending order by PostgreSQL Instance. To perform a backup on the basis of WAL, one needs a basic backup that is, a complete backup of the data directory, and the WAL Segments between the base backup and the current date.
Type 1 :
–First check the postgresql server whether archive log  already enable or not using psql terminal.
postgres=# show archive_mode; archive_mode -------------- off (1 row) postgres=# show archive_command;; archive_command ----------------- (disabled) (1 row)
–Let’s create directories for backups and archives (login as root USER):
mkdir -p /Archive/Location mkdir /backups chown postgres:postgres -R Archive chown postgres:postgres /backups
— check the folder permission
[root@p1 /]# ls -lrt drwxr-xr-x 2 postgres postgres 4096 Apr 10 07:54 backups drwxr-xr-x 3 postgres postgres 4096 Apr 10 07:54 Archive
— switch to postgres user and modify postgresql.conf. Making the cluster into Archive-log mode requires RESTART.
[root@p1 /]# su postgres cd $PGDATA echo 'max_wal_senders=1' >> postgresql.conf echo 'wal_level=hot_standby' >> postgresql.conf echo 'archive_mode=on' >> postgresql.conf echo "archive_command='cp %p /Archive/Location/%f'" >> postgresql.conf
— Restart the server and check the server whether running or not
bash-3.2$ pg_ctl restart -D $PGDATA waiting for server to shut down.... done server stopped server starting bash-3.2$ 2017-04-10 08:12:07 EDT LOG: redirecting log output to logging collector process 2017-04-10 08:12:07 EDT HINT: Future log output will appear in directory "pg_log". bash-3.2$ pg_ctl status -D $PGDATA pg_ctl: server is running (PID: 9825) /opt/PostgreSQL/9.3/bin/postgres "-D" "/opt/PostgreSQL/9.3/data"
— After the server start check the archive whether properly enable or not using psql terminal.
postgres=# show archive_mode;
archive_mode
--------------
on
(1 row)
postgres=# show archive_command;
archive_command
----------------------------
cp %p /Archive/Location/%f
(1 row)
postgres=# show wal_level;
wal_level
-------------
hot_standby
(1 row)
Type II :
If you want gzip format archives means you can give GZIP instead of CP then restart the server .
archive_mode =on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'gzip < %p /archivelog/archive_9.6/%f'
Note : Archiver won’t enable when wal_level is “minimal” , wal_level should be hot_standby or replica if above 9.6.
Type III :
Shipping archive to remote location .
wal_level = replica # minimal, replica, or logical archive_mode = on archive_command = 'rsync -av %p /home/postgres/archive/%f && rsync -av %p postgres@192.168.2.3:/home/postgres/archive/%f'
Login with Google