Spire Systems

These are setting for a sever with a minimum of 16GB of RAM

Make sure no one is using the system before making any changes.

For pgAdmin 3 (Spire 3.1.x and earlier)

- In pgAdmin select Tools > Server Configuration > postgresql.conf

- Review the following 5 settings and make sure each of them has a tick in the box as illustrated below (some of them won't have a tick yet):

shared_buffers 512MB

work_mem 10MB

effective_cache_size 8GB

checkpoint_segments 32

checkpoint_completion_target 0.9

Note:  you can review other settings and options here:  https://www.postgresql.org/docs/10/index.html
Important changes from Spire 2 to Spire 3:  https://github.com/gregs1104/pgtune/issues/21

- After inputting the new values then click on the green Play button (triangle) to update the configuration changes to the config file.

- Open Services on the Server and stop the Spire API Service.

- Restart the PostgreSQL (Spire-PostgreSQL-10 for Spire 3+) service.

- Start the Spire API Service again.

- Test Spire Desktop behaviour.

For pgAdmin 4 (Spire 3.2 and later)

- Browse to your PostgreSQL data folder, most likely C:\ProgramData\Spire\PostgreSQL\10.

- Open the file postgresql.conf in a text editor (Notepad is fine).

- Review the following 5 settings and make sure each of them doesn't have a leading hashtag # (a leading # denotes a commented line of text):

shared_buffers = 512MB

work_mem = 10MB

effective_cache_size = 8GB

max_wal_size = 1536MB

checkpoint_completion_target = 0.9

If you cannot find a setting as already present, please add it as a new line of text under the illustrated appropriate section.

Note:  you can review other settings and options here:  https://www.postgresql.org/docs/10/index.html

Important changes from Spire 2 to Spire 3:  https://github.com/gregs1104/pgtune/issues/21

- After inputting the new values then save the file and close the text editor.

- Open Services on the Server and stop the Spire API Service.

- Restart the Spire-PostgreSQL-10 service.

- Start the Spire API Service again.

- Test Spire Desktop behaviour.

Restoring Settings if PostgreSQL no longer Starts

It's possible you have made changes that have syntax errors and the database no longer runs.  You can restore to the last configuration backup by following these instructions:

1. Spire 2.x and PostgreSQL 9.x

- Browse to the (default) folder C:\ProgramData\PostgreSQL\9.3\data

- With Windows 10, go to the File Explorer's "View" tab and enable "File name extensions" (other versions of Windows may differ, for example this option may be under File Explorer Options)

- Rename the file postgresql.conf to something else, such as postgresql.conf.bad

- Rename the file postgresql.conf.bak to postgresql.conf

- Run the Services app and stop the PostgreSQL service (called "postgresql-x64-9.3...")

- Restart the PostgreSQL service

- Restart pgAdmin and verify the databases are accessible

2. Spire 3.x and PostgreSQL 10.x

- Browse to the (default) folder C:\ProgramData\Spire\PosgreSQL\10

- With Windows 10, go to the File Explorer's "View" tab and enable "File name extensions" (other versions of Windows may differ, for example this option may be under File Explorer Options)

- Rename the file postgresql.conf to something else, such as postgresql.conf.bad

- Rename the file postgresql.conf.bak to postgresql.conf

- Run the Services app and stop the PostgreSQL service (called "spire-postgresql-10")

- Restart the PostgreSQL service

- Restart pgAdmin and verify the databases are accessible