Steps to enable archive log mode in Postgres database

 

Step-1: Validate the archive mode status 


postgres=# show archive_mode;

 archive_mode

--------------

 off

(1 row)


postgres=# show archive_command;

 archive_command

-----------------

 (disabled)

(1 row)



Step-2: Create the archive location 

mkdir -p /var/lib/pgsql/14/archive


Step3: Enable archive mode 

postgres=# ALTER SYSTEM SET archive_mode to 'ON';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET archive_command TO 'cp %p /var/lib/pgsql/14/archive/archive%f';
ALTER SYSTEM
postgres=# show archive_mode;
 archive_mode
--------------
 off
(1 row)

postgres=# show archive_command;
 archive_command
-----------------
 (disabled)
(1 row)


Step4: Set the standard parameter for better performance

postgres=# alter system set wal_keep_size='500MB';
ALTER SYSTEM
postgres=# alter system set archive_timeout='900s';
ALTER SYSTEM
postgres=# alter system set max_wal_senders=10;


archive_timeout:

Forces a WAL switch after a predetermined period of time and archives it even if the WAL file is not completely filled because transactions are lower during non-peak hours. This is important because, even if there are few transactions and the WAL file hasn't been used in a while, it still includes information about some transactions that, if not saved, will be lost in the event of a crash.


max_wal_senders:
This setting is optional for archiving but required for online backup. The maximum number of wal sender processes that the PostgreSQL server can start for Online Backup and Streaming Replication can be specified.

Step-5: Bounce the database to take effect the above settings

-bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-09-17 14:34:11.192 EDT [6361] LOG:  redirecting log output to logging collector process
2023-09-17 14:34:11.192 EDT [6361] HINT:  Future log output will appear in directory "log".
 done
server started


Step-6: Validate the archive mod settings

postgres=# show wal_keep_size;
 wal_keep_size
---------------
 500MB
(1 row)

postgres=# show archive_timeout;
 archive_timeout
-----------------
 15min
(1 row)

postgres=# show max_wal_senders;
 max_wal_senders
-----------------
 10
(1 row)

postgres=# show archive_mode;
 archive_mode
--------------
 on
(1 row)

postgres=# select name,setting from pg_settings where name like 'archive%';
          name           |                  setting
-------------------------+-------------------------------------------
 archive_cleanup_command |
 archive_command         | cp %p /var/lib/pgsql/14/archive/archive%f
 archive_mode            | on
 archive_timeout         | 900
(4 rows)


Step-7: Force log switch to validate the archive generation

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/40000F0
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/5000078
(1 row)

postgres=# \q
-bash-4.2$ ls -rlt
total 32768
-rw-------. 1 postgres postgres 16777216 Sep 17 14:39 archive000000010000000000000004
-rw-------. 1 postgres postgres 16777216 Sep 17 14:40 archive000000010000000000000005
-bash-4.2$ pwd
/var/lib/pgsql/14/archive



Steps to disable the archive mode


Step-1: Set the archive mode to off and bounce the database

postgres=# ALTER SYSTEM SET archive_mode to 'OFF';
ALTER SYSTEM

-bash-4.2$ /usr/pgsql-14/bin/pg_ctl -D /var/lib/pgsql/14/data/ restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-09-17 14:44:23.424 EDT [6548] LOG:  redirecting log output to logging collector process
2023-09-17 14:44:23.424 EDT [6548] HINT:  Future log output will appear in directory "log".
 done
server started

-bash-4.2$ psql
psql (14.9)
Type "help" for help.

postgres=# show archive_mode;
 archive_mode
--------------
 off
(1 row)







Comments

Post a Comment

Popular posts from this blog

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

rs.stepDown() in MongoDB replication

It instructs the primary of the replica set to become a secondary. After the primary steps down,the eligible secondaries will hold an election for primary. Generally, the rs.stepDown() does not immediately step down the primary. If no electable secondaries are up to date with the primary, the primary waits up to secondaryCatchUpPeriodSecs (by default 10 seconds)  for a secondary to catch up. Once an electable secondary is available, the method steps down the priamry.  Once stepped down, the original primary becomes a secondary and is ineligible from becoming primary again for the remainder of time specified by stepDownSecs. The rs.stepDown() method attempts to terminate long-running user's operations that block the primary from stepping down, such as an index build, a write operation, or a map-reduce job. Syntax: rs.stepDown(stepDownSecs, secondaryCatchUpPeriodSecs)
Read more