postgresql_enablewriteeaheadlog

Write ahead log or WAL is analog of many other databases transactional logs:

  • Oracle - Archive log
  • DB2 - Transactional Log
  • Microsoft - Transactional Log
  • and so on.

The basic idea is to keep little files with information about our changes. In postgreSQL we have 3 levels of WAL settings:

  1. Minimal
  2. Archive
  3. Replica
  4. Logical

If you depend on WAL for recovery, REPLICA is good option.

In order to enable WAL, you have edit the postgresql.conf file and change the following settings:

  • wal_level (replica or logical)
  • wal_max_senders (should be >= 1)
  • archive_mode (should be on)
  • archive_command (depends on your environment but here is an example: 'test ! -f /var/lib/pgsql/9.6/archive/%f && cp %p /var/lib/pgsql/9.6/archive/%f'

Once all is done, we have to restart the service as follows:

[root@localhost data]# service postgresql-9.6 restart
Redirecting to /bin/systemctl restart postgresql-9.6.service
[root@localhost data]# service postgresql-9.6 status
Redirecting to /bin/systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2019-05-20 03:44:44 EDT; 9s ago
     Docs: https://www.postgresql.org/docs/9.6/static/
  Process: 3748 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 3753 (postmaster)
   CGroup: /system.slice/postgresql-9.6.service
           ├─3753 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
           ├─3755 postgres: logger process
           ├─3757 postgres: checkpointer process
           ├─3758 postgres: writer process
           ├─3759 postgres: wal writer process
           ├─3760 postgres: autovacuum launcher process
           ├─3761 postgres: archiver process
           └─3762 postgres: stats collector process

May 20 03:44:43 localhost.localdomain systemd[1]: Starting PostgreSQL 9.6 database server...
May 20 03:44:44 localhost.localdomain postmaster[3753]: < 2019-05-20 03:44:44.132 EDT > LOG:  redirecting log output to logging collector process
May 20 03:44:44 localhost.localdomain postmaster[3753]: < 2019-05-20 03:44:44.132 EDT > HINT:  Future log output will appear in directory "pg_log".
May 20 03:44:44 localhost.localdomain systemd[1]: Started PostgreSQL 9.6 database server.

Write Ahead Log (WAL) Enables us to make online base backup, so let's verify if the WAL works firstly:

[root@localhost archive]# ls -lart
total 81924
drwx------. 5 postgres postgres       66 May 19 08:35 ..
-rw-------. 1 postgres postgres 16777216 May 19 08:43 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 May 19 08:48 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 May 19 08:48 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 May 19 08:48 000000010000000000000004
-rw-------. 1 postgres postgres      302 May 19 08:48 000000010000000000000004.00000028.backup
drwxr-xr-x. 2 postgres postgres      214 May 19 08:50 .
-rw-------. 1 postgres postgres 16777216 May 19 08:50 000000010000000000000005

Seems all is fine, now we can make a base backup, explained further in the wiki.

  • postgresql_enablewriteeaheadlog.txt
  • Last modified: 2019/11/13 16:51
  • by 127.0.0.1