Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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 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 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.
--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)


Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

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

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL