v10.17 (build: May 28 2024)

PostgreSQL

If PostgreSQL server was not installed before


It can be installed on Windows as well as on Unix.

The setup, as usual, is necessary to perform either on a separate server workstation or on administator's workstation (if separate server room is not available).

Attention! PostgreSQL versions below 11 are not supported!

During installation, you need to create a password for the superuser "postgres", with its account you can further perform complex setup.



Setup on Windows:
The installation is intuitive and does not require comments, after which you may need to open the port 5432 in Firewall.
Configuration files postgresql.conf and pg_hba.conf are located in folder data\ of the main installation folder.



Setup on Linux (Ubuntu example):
sudo apt update
sudo apt upgrade
sudo apt install postgresql
sudo -i -u postgres
psql
\password postgres
Enter new postgres password: *****
Repeat postgres password: *****
\q
exit
Configuration files postgresql.conf and pg_hba.conf are located in:
/etc/postgresql/14/main/postgresql.conf
/etc/postgresql/14/main/pg_hba.conf
(where instead of 14 there may be another version number of PostgreSQL)
It is convenient to use to edit next command: sudo nano



If you need remote (not localhost) access to the SQL server, opening port in the Firewall will not be enough.
In the file postgresql.conf you need to make sure that the parameter listen_addresses is set to '*'
In the file pg_hba.conf you need to add the IP address(es) or ranges from which access will be allowed.
For example, change 192.168.0.1/24 to 0.0.0.0/0 (for all IPv4) and ::/0 (for all IPv6)
After the changes it is need to restart the SQL-server service!
On Linux:
sudo systemctl stop postgresql
sudo systemctl start postgresql

Information about SSL-encryption is here.

If you need to be able to login with Active Directory accounts, then you need to make a number of settings (here).




If PostgreSQL is already installed


If PostgreSQL is already installed, then you only need to check the possibility of remote access to it (if required). See previous section "If PostgreSQL server was not installed before".

Also, if you manually changed the standard_conforming_strings parameter in the postgresql.conf file of the SQL server installation folder by setting it to off, then you need to change the value to on and restart the SQL Server service.

If you need to be able to login with Active Directory accounts, then you need to make a number of settings (here).




PostgreSQL-server re-installation


Removing the SQL server should be done in a standard way through the "Control Panel" -> "Add or Remove Programs" (information for Windows).
In this case, after deleting the server, the Scopd database files will remain.
It is recommended to delete database files before reinstalling the SQL-server again.


© Scopd