PostgreSQL configuration file parameters are very important when managing a PostgreSQL database, and this blog post will discuss the importance of backing those files up. The following are the primary configuration files of the PostgreSQL database:
postgresql.conf: One of the most important configuration files for the PostgreSQL database is postgresql.conf file. This file contains a range of parameters that determine the behavior of the database when it is started. When the PostgreSQL database is launched, it refers to the parameter values present in this file to determine how it will operate.
For instance, the port parameter present in this file specifies the port on which the PostgreSQL database will listen for incoming connections. This is just one example of how the parameters in the postgresql.conf file can have a significant impact on the way the database operates. Overall, it is crucial to have a good understanding of these parameters to optimize the performance and functionality of your PostgreSQL database.
postgresql.auto.conf: The postgresql.auto.conf file has the same structure as the postgresql.conf file. When we change the parameter values using the “alter system” command, those values will be stored in the postgresql.auto.conf file. This file is automatically updated; we don’t need to update it manually. Parameters in the postgresql.auto.conf file takes precedence over those in the postgresql.conf file. For instance, if the parameter “archive_mode” is set to “off” in the postgresql.conf file, but it is set to “on” in the postgresql.auto.conf file, the “archive_mode” parameter will be set to “on” when the database starts.
Include directives: PostgreSQL provides the below three directives/parameters for managing the postgresql.conf and pg_hba.conf effectively. The following can be used to divide the configuration files into different parts:
1 2 3 | include file include_if_exists file include_dir directory |
In the postgresql.conf file, we can specify another configuration file to read by using the include or include_if_exists parameter. The difference between the two is that include_if_exists will not throw an error when the referenced file is not present.
In the pg_hba.conf file, we can specify another set of authentication rules into different authentication files using the include parameter. The authentication records from the other authentication file will be placed where the included parameter is present in the pg_hba.conf file.
The include_dir parameter can be used to specify multiple configuration/authentication files. You can find more information regarding these directives by visiting the links provided at the bottom.
pg_hba.conf: The pg_hba.conf file is used for client authentication in PostgreSQL. This file consists of authentication records. In this file, you can find the authentication-related information, i.e., database name, database user name, etc.
pg_ident.conf: The pg_ident.conf file is an essential component in PostgreSQL’s ident authentication process. It maps the operating system user to the corresponding database user. In this file, you can find information related to ident authentication, such as map-name, system-username, and database-username. To enable access to the database, you must add the map-name in the pg_hba.conf file. In Postgres 15/16, the user mapping-related details can be accessed in the pg_ident_file_mappings view.
Backing up configuration files can be helpful in the following situations
- Reverting changes – For instance, if you make changes to a parameter value and the output is not as expected, you can refer to the old values in the configuration files backup or use the “show all” backup to revert back.
- Comparing parameter values over time – If you need to compare parameter values over a period of time, you can refer to both the current and old parameter values in the backup of the configuration files.
- Incorrect parameter value – In case a parameter value is updated incorrectly, you can use the configuration files backup to update the correct value.
How to backup configuration files
First, use the given commands to locate all four files and copy them to the backup location. Additionally, If you have any files mentioned in the include directives, copy them to the backup location.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | postgres=# show config_file; config_file ----------------------------------------- /etc/postgresql/16/main/postgresql.conf (1 row) postgres=# show hba_file; hba_file ------------------------------------- /etc/postgresql/16/main/pg_hba.conf (1 row) postgres=# show ident_file; ident_file --------------------------------------- /etc/postgresql/16/main/pg_ident.conf (1 row) postgres=# show data_directory; data_directory ----------------------------- /var/lib/postgresql/16/main (1 row) In the data directory, you will find the postgresql.auto.conf file. postgres=# postgres@ip-172-31-90-30:~$ cd /var/lib/postgresql/16/main postgres@ip-172-31-90-30:~/16/main$ ls -ld postgresql.auto.conf -rw------- 1 postgres postgres 88 Mar 12 14:40 postgresql.auto.conf postgres@ip-172-31-90-30:~/16/main$ |
We can manually copy the single file using the cp command or schedule the backup in the cron.
In the example below, all four files will be backed up every night at 12:30 AM. I’m using the /tmp location as a backup location for the demonstration.
1 2 3 4 5 6 7 8 9 | crontab -e 30 0 * * * cp /etc/postgresql/16/main/postgresql.conf /tmp/postgresql_$(date +%Y_%m_%d).conf 30 0 * * * cp /var/lib/postgresql/16/main/postgresql.auto.conf /tmp/postgresql.auto_$(date +%Y_%m_%d).conf 30 0 * * * cp /etc/postgresql/16/main/pg_hba.conf /tmp/pg_hba_$(date +%Y_%m_%d).conf 30 0 * * * cp /etc/postgresql/16/main/pg_ident.conf /tmp/pg_ident_$(date +%Y_%m_%d).conf |
Instead of copying the postgresql.conf and postgresql.auto.conf files, we can also backup current parameter values using the show all; command.
The show all; command is used to show the settings of run-time parameters.
1 2 3 4 5 6 7 | postgres@ip-172-31-90-30:/tmp$ psql psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1)) Type "help" for help. postgres=# show all; |
We can schedule the show all; in the cron so that it runs automatically. The command will run automatically at night and copy the current values in the backup location.
1 | 30 0 * * * psql -c "show all;" >> /tmp/show_all_$(date +%Y_%m_%d).txt |
In this blog, we have seen various PostgreSQL configuration files, their locations, and methods for backing these configuration files. By understanding these concepts, database administrators can ensure that the backup of their PostgreSQL configuration files runs effectively. You can get more information about the configuration files at the links below.
- https://www.postgresql.org/docs/current/runtime-config-file-locations.html
- https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
- https://www.postgresql.org/docs/current/config-setting.html
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together. Run PostgreSQL in your production and mission-critical environments and easily deploy and orchestrate reliable PostgreSQL in Kubernetes.
Download Percona Distribution for PostgreSQL Today!
