Watchdoc - PostgreSQL database - Create a database

Prerequisites

Before creating the PostgreSQL database, perform the following steps:

1. install Postgre SQL on the database server ;

2. install a PostgreSQL administration tool (such as pgadmin);

3. on the database server, open port 5432 ;

4. ensure that all servers have the right to access the database server via port 5432.

Procedure

Copy table creation files

The table creation files must be copied to the PostgreSQL database server.

1. As administrator on the server hosting Watchdoc, go to the Watchdoc folder;

2. go to the C:\Program Files\Doxense\Watchdoc\SQL folder and copy the following files corresponding to your Watchdoc version:

   • pgsql_awdstats[version#].sql = Watchdoc statistics database

   • pgsql_cards45.sql = Base cards

   • pgsql_cluster[n°version].sql = interserver database

   • pgsql_pmv40.sql = Base Quota
     
     
     

      

3. on the server on which you wish to create your database, under c:, create a SQL folder :

4. in this folder, paste the previously copied files :
   

 

Accessing the server

As administrator, access the server on which PostgreSQL is hosted;

1. launch SQL Shell :
   
   

    

2. connect to the server on which you wish to create the database, entering the information you entered when installing PostgreSQL:
   
   

Create a user

1. Create a user with the following command: CREATE USER user_nameGR WITH PASSWORD 'Password';
   CREATE USER user_name WITH PASSWORD 'Password';
   

Create a database

1. Create the database by entering the following command:
   CREATE DATABASE watchdocstats WITH OWNER watchdoc_rw ENCODING = 'UTF8'
   
   Please note: for snacks, please refer to the dedicated documentation (depending on Windows or Linux environment : https://www.postgresql.org/docs/current/multibyte.html

    

2. Give Watchdoc access using the following commands:
   GRANT CONNECT ON DATABASE "watchdocstats" TO watchdoc_rw;
GRANT USAGE ON SCHEMA public TO watchdoc_rw;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO watchdoc_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO watchdoc_rw;

Create the tables

1. Log out and log back in with the newly created Watchdoc account to check that it has been registered;
   
   \\i
   

    

2. Run the following command:

   \i 'c:/SQL/pgsql_awdstats60.sql'

    

   Depending on the type of base you need, run the following scripts:

   pgsql_awdstats60.sql = Watchdoc statistics database

   pgsql_cards45.sql = cards database

   pgsql_cluster60.sql = interserver database

   pgsql_pmv60.sql = quota database

   
   

 

Modifying a configuration file

In the case of a new database, you need to modify the configuration file (hba.conf) to authorize remote connections with the newly created user:

• for Windows: as administrator, edit the file C:\Program Files\PostgreSQL\17\data\hba.conf ;

• for Linux, as administrator, edite the file Linux /pgsql/data/hba.conf

Do this on each server hosting a PostgreSQL database.

 

At the end of the file, add a line in the #IPv4 local connections category, indicating:

• the IP address of the database server: validate that the CIDR (in bold below) includes all WATCHDOC servers or enter 0.0.0.0/0 (no blocking).

  • host all watchdoc_rw 0.0.0.0/0 trust

  • host all watchdoc_rw ::1/128 trust

• the name of the user created :check the account, (in bold below): which must match the account configured in Watchdoc :

  • host all watchdoc_rw 0.0.0.0/0 trust

  • host all watchdoc_rw ::1/128 trust
    

 

Do this on each server hosting a PostgreSQL database.

(cf. more information in https://docs.postgresql.fr/12/auth-pg-hba-conf.html)