PostgreSQL Archive Log Directory

According to PosgreSQL’s documentation : “Write-Ahead Logging (WAL) is a standard method for ensuring data integrity.”. These files are created in a directory at a local or remote server and if you desire to copy them you have to tape library. However, PostgreSQL database isn’t created with the WAL option enabled and to be able to use it you have to configure some essential parameters in the postgresql.conf file. Therefore , these parameters are:

archive_command: Here is the directory that you want your WAL files be stored in. You can configure it as below, taken from the PosgreSQL documentation :

archive_command = 'test ! -f /postgresql/pgarchives/%f && cp %p /postgresql/pgarchives//%f'

archive_mode: Change to “on” to enable WAL archiving. This parameter has started in the version 8.3.

wal_level : This is the parameter that determines how much information is written to the WAL. There are some slight differences in the values to this parameter in different versions. For instance, in the version 9.5 the option are ‘minimal’,’archive’,’hot_standby’ and ‘logical’ while in the version 9.6 is ‘minimal’, ‘replica’ and ‘logical’ and from version 10 onward ‘replica’ becomes the default. You can see the different values for different versions at the documentation : https://www.postgresql.org/docs/12/runtime-config-wal.html

max_wal_senders: This parameter is important when you are using pg_basebackup or have the intention to create a standby database. The default value for this parameter is 10 and according to the documentation “specified the maximum number of concurrent connections from standby servers or streaming base backup clients”.

You have to perform the modification of those parameters within the file postgresql.conf located in your $PGDATA directory and perform a stop following by a start of your cluster. By default, as it is in some other database such as Oracle, PostgreSQL database comes with the archiving option disabled and you can check this running the command “show archive_mode;” inside of a instance as below:

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

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

After the configuration mentioned above is done and the bounce of the database, the archiving will be enabled and the results of the query will be “on” . Therefore you can run the command “Select pg_start_backup(‘Testing’);” following by “Select pg_stop_backup();” to test your archiving configuration and if you see some backup files among wal files in the directory that you have chosen in the archive_command parameter your configurations are ok.

brunors

Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)