You are here

Things to look at on the Server

Subscribe to Syndicate

Virus Software on the Postgresql Server

If postgres is implemented on a stand alone machine (per PCI requirement 2.2.1), we recommend that you DO NOT install virus software on the PostgreSQL Server. It is the single biggest factor that can affects performance.

If file sharing or user access is permitted to the database server and you install virus software, never allow virus scanner to scan the actual postgres database directories because virus scanners severely affect performance when many files are changed rapidly.

Refer to this link for disabling windows 10 defender

If you must install Virus Software, specifically exclude:

  • any TCP/IP traffic on ports 5432
  • anything contained in the PostGres database folders. This is generally:
    • C:\Program Files\Postgresql -or-
    • C:\Program Files (x86)\Postgresql -or-
    • \Library\Postgresql
  • Under OSX and Linux, the need for virus checking on the Postgres server is generally not required as the database server is often a stand-alone machines
  • If you really must scan the database folder, it is acceptable to run a once per day scan on the database folder - but only if scheduled late at night.

Other Factors Affecting Database Performance

  • Make sure auto update (Microsoft) or software update (OS-X) is completely turned off. Those features should never be run automatically. Microsoft is famous for delivering them often and restarting servers. Its just bad news. Plan your service outages and do them all - say - once a month or every second Monday. Downloads of large patches in the background might be a problem, or restarts after an upgrade may have affected Postgres's buffer pools so it needs to run a bit to get faster and determine commonly used database pages.

    Windows Automatic Updates is now found in Services in Windows 10. Read the following to see how to completely disable the update process.

  • Look to any service that you might have added or changed recently. For example, if you installed another program on the server, does that take resources that affect the original installation. If so, then you may need to re-configure that application, remove the application or reconfigure the postgres server. For example, Microsoft Exchange server is known to suck the resources out of a machine and it is always recommended (even by Microsoft), that it be placed on its own server.
  • Check for available disk space on the server. If you have less than 20% disk space left, then you might want to get rid of some old data or backups. Postgres uses the operating system to do page swapping and if there is no space left, you will see performance degradation because you filled up the dad drive. Old database backups might simply be accumulating and you may wish to save older ones and delete them from the server
  • Check the performance monitor (Windows or OSX) to see where your resources are going. If CPU is being consumed, see who is using it. If disk I/O is going crazy, that could be a clue
  • It is possible that a parameter was set up incorrectly for the Macintosh or Windows server. If other items are not found to cause the problem, please contact support and we can look at the postgres database parameters for you.
  • There some platform specific considerations to look at for:
    • OSX such as airport, spotlight, disable power saving, and time machine
    • Windows such as indexing and shadow copies