Published on: 01-Jun-2021 Table of Contents – Page 1 of 11
Table of Contents
1 PostgreSQL installation in Windows...2
1.1 PostgreSQL configuration ...2
1.2 Astraia configuration ...4
2 How to change the default PostgreSQL DATA directory on Windows...5
2.1 Step 1: Stop The PostgreSQL Service...5
2.2 Step 2: Change Registry Values ...5
2.3 Step 3: Move the data folder to a new the location...6
2.4 Step 4: Restart The PostgreSQL Service ...6
3 Backup the astraia database with PostgreSQL ...8
4 Restoring the astraia PostgreSQL database from backup...11
Published on: 01-Jun-2021 PostgreSQL installation in Windows - Page 2 of 11 1.
2.
a.
b.
c.
d.
e.
f.
g.
h.
1 PostgreSQL installation in Windows
1.1 PostgreSQL configuration
Go to astraia website, login and on partner download section look for the PostgreSQL installer.
Run the installation wizard and follow the instructions:Run pgAdmin, the database manager. It can be found searching for pgAdmin on Windows Start.
Leave the Installation Directory as it is, unless you have special needs and cannot install on the C:
drive.
All components are ticked by default, it's fine to install all the components since they could be useful later.
Be careful to change the DATA directory, enter "C:\astraia_databases\postgres" or change the "C:\"
with any other drive which is suitable.
The DATA directory will contain all the databases files.
Next you'll have to enter the password for the superuser "postgres" (use password: Sc_w4b_ng0l8).
Leave the default port 5432
On the next page it will ask to select a locale. The encoding will be UTF8 + the locale selected here, make sense to select the customer country locale or the English United States if you are testing.
Click next another couple of time to start installing PostgreSQL on the system.
When the installation is completed, untick the checkbox for auto-running the Stack tool and close the installer.
PostgreSQL can only be used with astraia on production from astraia version 1.27.2
Do NOT use special characters like "@" or "!" into the "postgres" user password, otherwise the backup script will not work!
Is possible to use a dedicated user for the backup process, however this is not explained on this manual, anyway suitable information
on how to create a dedicated backup user is available on internet.
Published on: 01-Jun-2021 PostgreSQL installation in Windows - Page 3 of 11 3.
4.
5.
6.
Once the pgAdmin is running it ask for a master password, it's the "postgres" user password. After successfully log in click on the server ">".
When requested type again the "postgres" password that was introduced during the installation and continue clicking on PostgreSQLxx ">" as well.
Right click Databases > Create > Database and enter db name 'astraia'. On the "Definition" tab you can change the encoding if ever will be necessary.
The default UTF8 is perfectly fine for astraia. The Collation will be defaulted on what you have chosen when installing PostgreSQL. Leave all the rest as it is and click on "Save".
You may have to change the following line on the "pg_hba.conf" file in your database directory, enabling the access from network (edit the file with notepad or text editor of your choice):
# TYPE DATABASE USER ADDRESS METHOD
...
host all all all scram-sha-256
# IPv6 local connections:
...
Notice the "all" instead of 127.0.0.1/32
Please read the file comments to get more info on the possibilities and capabilities for the connections to the datatabase.
Published on: 01-Jun-2021 PostgreSQL installation in Windows - Page 4 of 11 1.
2.
3.
4.
5.
6.
7.
1.2 Astraia configuration
Install astraia, select ORACLE database type during the astraia setup in order to enable the Connection Pool in astraia. Check this link for more info https://confluence.astraia.com/x/CgAP.
Edit the "...astraia\.config\sartup.properties" and make sure that the Connection Pool values are set as follow:
System.DB.UseConnectionPool=true
System.DB.ConnectionPool.MaxConnections=20 System.DB.ConnectionPool.ConnectionsAtRest=3
System.DB.RelinquishConnectionOnStatementClose=true
From astraia 1.28.x the installer will include the database type PostgreSQL as available option.
Start astraia service manager
Go to Options menu > Databases > Select Type = PostgreSQL
If not already present, enter the URL: jdbc:postgresql://localhost:5432/astraia(see page 2)
Username= postgres Password = Sc_w4b_ng0l8 Test Connection and press OK.
Start astraia license server if necessary and then astraia main application.
The jdbc driver (postgresql-42.2.5.jar) is by default in the installation folder under the "lib" folder.
If you have an astraia NW you will have to change "localhost" with the hostname or IP of the server.
"astraia" is the database name created in PostgreSQL, if you have chosen a different name for the database then change it accordingly.
Published on: 01-Jun-2021 How to change the default PostgreSQL DATA directory on Windows - Page 5 of 11
2 How to change the default PostgreSQL DATA directory on Windows
If you installed PostgreSQL DATA directory on his default location on C:\ drive, or you simply need to move it from where it is, on this section you will find how to change the default PGDATA directory to another location.
2.1 Step 1: Stop The PostgreSQL Service
Close all application that are currently connected to your database (astraia, astraia services), then go to Windows Services Management and stop the PostgreSQL service:
Start->Settings->Control Panel->Administrative Tools->Services
You should check the task manager to see if any postgresql.exe instances are still running. If so, DO NOT TERMINATE them, instead close all applications that are still connected to the database (astraia, astraia services). Sometimes services keep persistent connections, check if any java or javaservice instance is still running, probably those are astraia services or main application still in use. In this case you also need to stop the remaining services and/or close astraia instances.
2.2 Step 2: Change Registry Values
Start the Windows Registry Editor (regedit.exe) and navigate to:
"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-some version".
Double click on "ImagePath" and change the directory after the "–D" option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.
Published on: 01-Jun-2021 How to change the default PostgreSQL DATA directory on Windows - Page 6 of 11
2.3 Step 3: Move the data folder to a new the location
Move your current PGDATA directory to the directory you just specified in Step 2. Please note that you should do this as Administrator on server machines. You should also check user permissions in the new directory to ensure the username under which your PostgreSQL instance runs has permissions to do so in the new directory, otherwise PostgreSQL will not start.
2.4 Step 4: Restart The PostgreSQL Service
Before you can start your database you must close and re-open the Windows Services Management window. This refreshes the path you just changed in the registry without the need to restart your system, in case it does not work restart the system and it should be fine.
After closing and re-opening the Services Management window or having restarted the system, you should check the "path to executable" information of the PostgreSQL Database service. The path after "–D" option should be pointing to your new directory now.
Published on: 01-Jun-2021 How to change the default PostgreSQL DATA directory on Windows - Page 7 of 11 Now that the PGDATA directory has been re-located and the service startup parameter in the registry has been modified, you can go ahead and start the service.
Published on: 01-Jun-2021 Backup the astraia database with PostgreSQL - Page 8 of 11 1.
2.
3.
3 Backup the astraia database with PostgreSQL
There are many ways to backup but for the purpose of this manual I will explaing how to backup the astraia database on a schedule base using a script.
Create a file named "astraia_backup_pgsql.bat" in the astraia main folder.
Edit the file with Notepad or any preferred editor.
Copy paste the following script into the file:
Published on: 01-Jun-2021 Backup the astraia database with PostgreSQL - Page 9 of 11
@echo off 1
setlocal ENABLEDELAYEDEXPANSION 2
for /f "tokens=1-5 delims=/ " %%i in ("!date!") do ( 3
set dow=%%l 4
set month=%%j 5
set day=%%i 6
set year=%%k 7
set time=%%m 8
9 ) 10
REM ****the above variables might need to be changed depending on the date-time format of your locale settings****
11
REM ****beware that if you do not check the variables the file name and folder name might not be correct, or the script might not work at all****
12
REM ****check the printed FOLDER NAME and FILE NAME when executing the script to check that it works as expected****
13 14
set hour=!time:~0,2!
15
if "!hour:~0,1!" == " " set hour=0!hour:~1,1!
16
echo hour=!hour!
17
set min=!time:~3,2!
18
if "!min:~0,1!" == " " set min=0!min:~1,1!
19
echo min=!min!
20
set secs=!time:~6,2!
21
if "!secs:~0,1!" == " " set secs=0!secs:~1,1!
22
echo secs=!secs!
23 24
set datestrf=<PathTo>\!day!_!month!_!year!
25
echo The FOLDER NAME is !datestrf!
26
if not exist "!datestrf!" mkdir !datestrf!
27 28
set datestr=!hour!_!min!_!secs!_!day!-!month!-!year!
29
echo The FILE NAME TimeStamp is !datestr!
30
set bcklog=!datestrf!\!datestr!_backup.log 31
echo LOG file name is !bcklog!
32
set BACKUP_FILE=!datestrf!\<NameOfTheFile>_!datestr!.backup 33
echo backup file name is !BACKUP_FILE!
34
SET PGPASSWORD=<PassWord>
35
echo ASTRAIA POSTGRESQL BACKUP LOG - !time!:!date! >!bcklog!
36
echo ---
>>%bcklog%
37
"<postgreSQL_InstallationFolder>\<PostgreSQL_VersionNumber>\bin\pg_d ump" 2>>!bcklog! -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>
38
echo ---
>>%bcklog%
39
Published on: 01-Jun-2021 Backup the astraia database with PostgreSQL - Page 10 of 11 4.
5.
6.
7.
Change the <xyz> tags in the script with the desired string: e.g. <Password> into Sc_w4b_ng0l8, <PathTo>
into D:\astraia_backup\, <NameOfTheFile> into astraia, <DATABASENAME> into astraia, etc.
The script, once executed will create a backup of the astraia database in a sub folder of the <PathTo>, named with the current day_month_year, if not already existent. The file will have a date and time stamp in the name, so the script can be theoretically run down to every seconds, therefore it can be used with a schedule task to produce backups of any given frequency: daily, hourly, etc. The script also delete files and folders older then 7 days in the <PathTo> folder if the files are not protected from writing or need elevated rights to be deleted, in which case you need to run the script with admin right in order to make it work. You can change on the last lines where the "forfiles" command is, the number 7 to any amount of days needed to wait before the old files get deleted by the script. A log file with a time stamp is produced on every run of the script into the same folder of the backup files.
Save the file and try to run it to check that it works as expected.
Once the script correctly creates backups of the astraia database where you want it, you need to create a Task in the Windows Task Scheduler to enable the batch file to be run on the time and dates desired.
echo Checking and deleting old files if any: !time!:!date! >>!
bcklog!
40
forfiles /p "<PathTo>" /s /d -7 /c >>!bcklog! 2>>&1 "cmd /c del / S /Q /F @file && if @isdir==TRUE rd @path >>!bcklog!"
41
echo End of old files check and delete: !time!:!date! >>!bcklog!
42
echo END OF BACKUP OPERATION - !time!:!date! >>!bcklog!
43
exit /b 1 44
The script date-time variables might need to be changed depending on the date-time format of your locale settings !!
Beware that if you do not check the variables the file name and folder name might not be correct, or the script might not work at all.
Check the printed FOLDER NAME and FILE NAME when executing the script to check that it works as expected !!
Make sure the script effectively deletes files and folders older then the specified amount of days. If the script do not delete the files you could end up with a full drive and backup will not work anymore. If you wish to keep the backups archived somewhere else you will have to move them periodically with another script or manually before the backup script deletes them.
If your database folder or your destination folder needs elevated rights to read/write on them, you will have to execute the script with admin rights.
If you have a large database to backup or free space constraints, consider to apply compression either directly on the script or manually after the files are created.
Published on: 01-Jun-2021 Restoring the astraia PostgreSQL database from backup - Page 11 of 11
4 Restoring the astraia PostgreSQL database from backup
If you need to restore the backup of your astraia database, the following command will restore it in a new database, without touching any existent PostgreSQL database.
"<postgreSQL_InstallationFolder>\<PostgreSQL_VersionNumber>\bin\pg_restore" -U postgres -Ft -C -d <NameOfNewDatabase> < <PathToAndNameOfTheBackupFile>
Change the tag between <...> with the path to PostgreSQL bin folder, the name of your new database and the path+name of the backup file.
If you compressed (zipped) your backup, you will need to decompress it before restoring.
