Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Enabling PostgreSQL Archivelog

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

postgres=# show archive_command;;
(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
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;
(1 row)

postgres=# show archive_command;
 cp %p /Archive/Location/%f
(1 row)

postgres=# show wal_level; 
(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@'


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger