Overview
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:
- Minimal
- Archive
- Replica
- Logical
If you depend on WAL for recovery, REPLICA is good option.
Enabling
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.
Verify
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.