PostgreSQL Database Maintenance

All databases require routine maintenance to keep them running efficiently. This includes the SQL database that Artisan version 4 uses which is PostgreSQL.

This is pronounced:

  1. say ‘post’ as you would the word ‘post’ in English;
  2. in ‘gres’ the ‘e’ sounds like it does in ‘best’;
  3. QL is pronounced like the letters of the alphabet.

 

CerTek chose this database for many important reasons and at this point in time we don’t have any plans on supporting any additional engines such as Microsoft SQL. The work involved in such a project would be extensive on an order of magnitude that far exceeds any possible benefits.

We use PostgreSQL because:

  1. It is portable across many platforms such as Windows, Mac O/S, and Linux.
  2. It is free and is supported by a large developer community.
  3. It is an extremely reliable, powerful, and efficient database engine.

 

Maintenance:

The maintenance tasks that need to be performed are easy and fairly quick. At the moment these tasks can only be performed by login into the PG Admin tool.

WARNING! – This is a powerful tool and you could accidentialy delete your Artisan database if you are not careful!

Because of this, CerTek guards this password, but since it is your data on your computer, we will provide it upon request from the owner. Just please be advised on a few conditions.

  1. With this tool you can perform all sorts of behind the scenes tasks which could included deleting, copying, altering, or stealing information without all the Artisan security controls. We do not recommend giving this password out to your staff. However Artisan does check for and detect certain security and configuration setting changes that were made from behind the scenes.
  2. Please don’t change this password because we may need it for maintenance and repair and other technical support issues.
  3. If you are looking for read only access to the data through SQL commands, there are several alternatives including the built-in custom report tool that allows for user tables to directly enter SQL select commands.
  4. You should make an Artisan backup on a regular basis and certainly right before you use the PG Admin tool.

 

Opening up PG Admin

  1. Launch the PG Admin app from your Start Menu on the computer that we may call The Server. This could be Register 1. PostgreSQL is normally on just one computer. If you are looking at the very top of the Artisan window, it should say something like Default Database on This computer.
    I
    t is important to be on the main computer because it will be faster.
  2. Click on the plus sign next to the PostgreSQL 9.6 or 10 or whatever version you have. If you have more than one, it is probably the latest.  
  3. You will be prompted for your password. It is case sensitive.
  4. You will need to get all the computers to Exit Artisan before running the tool.
  5. Now find your database. This is also at the top bar on the Artisan screen. Default means that it is POS.
  6. Right click over it and choose Maintenance… from the menu.
  7. Change the Vacuum to Full and Analyze to Yes
  8. Make sure that Artisan is closed everywhere and then click OK.
  9. This will take a few minutes to run depending on the size of your database.