cli Archive

Reload PostgreSQL Configuration

PostgreSQL allows for many settings in their postgresql.conf file. Depending on your database you may need to tweak any of these settings to find the optimal performance or to work the way you want it in your environment. The easy way to apply any changes is just by restarting the database. But that would be counter productive, if your blog or application runs on PostgreSQL, then that would leave your users seeing an “Unable to connect to database.” error until the database starts up.

Luckily PostgreSQL has two options for applying changes made to the postgresql.conf without restarting.

1. With an SQL command

If you have a superuser credentials you can just execute the pg_reload_conf() function. This will apply any changes that have been made to the postgresql.conf.

2. With the terminal

It is possible to load changes done to the postgresql.conf file via the terminal. You will need to login as the postgres user or su into it if you have root access. Then execute the pg_ctl command with the reload parameter.

If your data folder is not in the default location, pg_ctl will complain

In that case you will need to give it the location. That is done by passing the -D flag followed b the location of the data folder

The current version of PostgreSQL I am using is 9.3.5. Not all versions of PostgreSQL offer the same settings, they may change over version numbers. Also not all settings are available to be changed on the fly. Some require that PostgreSQL be restarted to take effect. There are not many of these settings, but they are settings that you usually only set once. For example the ip and port number.

Below are all the settings available for PostgreSQL v9.3.5 on Centos 6.5 as returned by the SHOW ALL command. I have marked the ones that require a restart to take effect. All other can modified and applied either via the pg_reload_conf() function or the pg_ctl command.

 

The Time Command

Often while executing commands in the command line interface I find myself nervous or anxious. Particularly if I am working in a production machine because I don’t know if and when a command I issued is going to finish. Knowing how long a command is going to take is very useful as I would be able to anticipate it’s completion instead of waiting and looking at a black and white terminal with no progress for hours. This is where the time command is very useful.

The time command is essentially a wrapper for any command executed in a terminal. The time command must precede any other command to be executed, for example:

The above command will copy big_file.tar.gz to big_file_copy.tar.gz and will return how long it took.

The time command will output to standard error three pieces of information. The elapsed real time, this is how long it took from the start of the call to it’s termination. The user CPU time, which is the CPU time spent executing the instructions from the command issued. The last bit of information is the system CPU time. The system CPU contains the time it took to execute the tasks from the command issued.

If we view the output of the copy command we issued earlier

We can see that it took 23 minutes to run through the entire command execution, from me hitting enter until the file was copied to it’s new location. Roughly 360ms for the CPU to execute all command related to cp , and about 1 minute and 25 seconds for the CPU to execute the tasks needed by cp to copy a file.

As you can see the time command is very useful as it allows you to time how long a command takes to execute. You can use it to benchmark scripts that you created, find out how long something is going to take before actually implementing it on a production machine, or you just want to know for curiosity sake.