These are the essential steps required to configure a PostgreSQL sever running on Ubuntu. We use Synaptic to to install PostgreSQL.
Updating up the postgres user account
Step 1 is to update the postgres user account. Open a Terminal (Ctrl+Alt+T) and type the following:
sudo -u postgres psql postgres
At the #postgres=# prompt type the following and press Enter:
\password
Type a new password, press Enter, confirm the password and press Enter again.
Now type \q and press Enter.
Location of configuration files
When the server is running on a Linux machine, the configuration files are normally the /etc/postgresql/{version}/main folder.
For example, for version 14, configuration files are located in the following folder:
/etc/postgresql/14/main
Allowing network access to the server
You must edit postgresql.conf configuration file in order to allow network (remote) access to the server.
The most reliable way to determine the location of the postgresql.conf is to open a Terminal (Ctrl+Alt+T) and use psql to execute the following query:
sudo -u postgres psql -c 'SHOW config_file;'
Open postgresql.conf with a text editor (vi for example). The line with the listen_addresses parameter must be uncommented (remove the #) and then modified to read as follows:
listen_addresses = '*'
Once the configuration file has been modified and saved, the configuration
Configuring client access to the server
You must add the IP address of client computers that are allowed to access a PostgreSQL server to the pg_hba.conf configuration file.
The most reliable way to determine the location of the file is to open a Terminal and use psql execute the following query:
sudo -u postgres psql -c 'SHOW hba_file;'
Please refer to the official documentation, especially the examples section, for more information.
Example
Allow all clients on the 192.168.7 network to access any database on a server:
host all all 192.168.7.0/24 trust
Reloading the configuration files
Use the following command to reload the configuration files:
sudo -u postgres psql -c 'SELECT pg_reload_conf();'
Reboot
Once you have made the appropriate changes to pg_hba.conf and postgresql.conf, reboot the operating system.
Firewall configuration
Port 5432 must be open on the machine that is running the PostgreSQL server.
You can test client connection to the server by temporarily turning off the firewall on the server.
Extensions
Dispatch requires the uuid-ossp extension.
Each time Dispatch connects to a PostgreSQL database, it will automatically try to create the extension by executing the following statement:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
Server timeout
show tcp_keepalives_idle;
show tcp_keepalives_interval;
show tcp_keepalives_count;