Backup of Internal and External PostgreSQL Database


Before the backup

It is recommended to stop YSoft SafeQ services before database backup is performed.

The backup of databases shall be done at the same time to prevent inconsistency among databases.

  1. Stop all YSoft SafeQ services in the whole environment (Management Servers, Site Servers) except the following (leave the services listed below running):

    1. YSoft SafeQ Bundled PostgreSQL (available if an embedded PostgreSQL DB is used)

      You can use the following PowerShell script to perform the task:

      Get-Service *YSoft* | Where-Object {$_.Name -ne 'YSoftPGSQL' -and $_.Name -ne 'YYSoftPGSQL' } | Stop-Service

    2. pgAdmin4

PostgreSQL Backup

Important information

All backup procedures should be consulted first with Database Administrator/Owner in case an external PostgreSQL database is used.

Please note that this is just a quick guide for pgAdmin tool. Latest documentation is always available on PostgreSQL web pages.

Backup using pgAdmin administration tool

  1. On the database server, run the pgAdmin administration tool

  2. On pgAdmin tool click on Add New server

    images/download/attachments/195304094/Create_1.png

  3. Fill SQDB6 in Name section and continue to Connection tab

    images/download/thumbnails/195304094/create_2.png

  4. Fill the connection information of the database

    images/download/thumbnails/195304094/create_3.png

  5. In browser window, double-click the newly added item to connect to the server

  6. Right-click in the database to backup and click the Backup... option

    images/download/attachments/195304094/backup.png

  7. Set a Filename of your choice

  8. Keep Format set as Custom

  9. Set the Role name as postgres

  10. Press the Backup button

    images/download/attachments/195304094/backup_2.png

  11. If everything is correct, a popup should appear with information that the procedure ended successfully

    images/download/attachments/195304094/backup_succesfull.png

    1. You can check the log if you click on More details

    Additional information

    The backup process is executed by the tools using Python (embedded with PG Admin). The installer adds a path to Python libraries into the PATH system variable.

    If the backup process fails and displays the Cannot locate Python36.dll error message, make sure that the "PATH" system variable contains the path to the "bin" folder of pgAdmin (f or Embedded PostgreSQL, this path is " %SAFE_HOME%\PGSQL\pgAdmin 4\bin")

  12. Repeat steps 1. - 11. for SQDB6_DWH database on the appropriate server

  13. Repeat steps 1. - 11. for SQDB6_IMS database on the appropriate server

  14. Repeat steps 1. - 11. for SQDB6_YPS database on the appropriate server

Backup using command line

If you prefer to use command line and YSoft SafeQ was installed with the embedded PostgreSQL database, use the following commands:

"%SAFEQ_HOME%\PGSQL\bin\pg_dump.exe" --host localhost --port 5433 --username "postgres" --role "postgres" --format custom --blobs --verbose --file "SQDB6.backup" "SQDB6"
"%SAFEQ_HOME%\PGSQL\bin\pg_dump.exe" --host localhost --port 5433 --username "postgres" --role "postgres" --format custom --blobs --verbose --file "SQDB6_IMS.backup" "SQDB6_IMS"
<# Use the command below only when Data Warehouse deployment is used #>
"%SAFEQ_HOME%\PGSQL\bin\pg_dump.exe" --host localhost --port 5433 --username "postgres" --role "postgres" --format custom --blobs --verbose --file "SQDB6_DWH.backup" "SQDB6_DWH"
<# Use the command below only when using Payment System #>
"%SAFEQ_HOME%\PGSQL\bin\pg_dump.exe" --host localhost --port 5433 --username "postgres" --role "postgres" --format custom --blobs --verbose --file "SQDB6_YPS.backup" "SQDB6_YPS"

You might need to enter password for the "postgres" user after executing the command.

Backup will be created in the folder from which the command is run.


For running these commands in PowerShell, replace %SAFEQ_HOME%\PGSQL\bin\pg_dump.exe with &$env:SAFEQ_HOME\PGSQL\bin\pg_dump.exe
%SAFEQ_HOME%\PGSQL is the default location of the PostgreSQL that comes embedded in SafeQ.

When using an external PostgreSQL server, you need to locate the executable "pg_dump.exe" which comes as part of PostgreSQL installation and adjust the script accordingly:

  • replace the "%SAFEQ_HOME%\PGSQL\bin\pg_dump.exe" with path to "pg_dump.exe"

  • replace also --host and --port values if needed

After the backup

Start YSoft SafeQ services with Automatic startup type on Management Servers again

Do not start YSoft SafeQ Spooler Controller Group Service. It will be started by YSoft SafeQ Spooler Controller service when proper configuration is ready.

  1. Start YSoft SafeQ Management Service first

  2. Start remaining YSoft SafeQ services in no particular order

    1. You can use the following PowerShell script to perform the task

      Get-Service *YSoft* | Where-Object {$_.Name -ne 'YSoftSQ-SPOCGS'} | Start-Service

Start YSoft SafeQ services with Automatic startup type on Site Servers again

Do not start YSoft SafeQ Spooler Controller Group Service. It will be started by YSoft SafeQ Spooler Controller service when proper configuration is ready.

  1. Start YSoft SafeQ Spooler Controller first

  2. Start remaining YSoft SafeQ services in no particular order

    1. You can use the following PowerShell script to perform the task

      Get-Service *YSoft* | Where-Object {$_.Name -ne 'YSoftSQ-SPOCGS'} | Start-Service