Frequently Asked Questions - PostgreSQL

 


How do we boot a user from the database?

In order to forcefully disconnect a user from the database, you have to find the Process ID (pid) for the backend connection of that user. Each user connection to the database spawns a backend process for their connection. Once you know the pid for the process, you can kill the process. On unix the command would be "kill [pid]" while other OSes may have different ways to kill a process. In order to find the pid you can look at the pg_stat_activity table and the column procid should contain the number you need.

How do I figure out what version of PostgreSQL I'm running?

If you don't remember which version of PostgreSQL you're running, you can always find out with the following SQL statement:

select version();

This is easy to do if you're familiar with pgAdmin. Simply connect to your server with pgAdmin. Select one of your databases. Open the SQL editor tool. Paste in the above command. Then execute the query. Your PostgreSQL version will be displayed in the results window.

How do I stop the PostgreSQL service on a Mac?

When using the PostBooks Installer to install xTuple ERP on a Mac, you may run into an issue if you have a previous instance of PostgreSQL running. If you get an error message during the installation that says:

"There has been an error. There is not enough shared memory. PostgreSQL component requires a minimum shared memory segment of 32MB. Please increase "shmmax" kernel parameter (in /etc/sysctl.conf) or close any other PostgreSQL instances before restarting installation."

You can use the Terminal application on Mac OS X to run commands to stop the database service. You'll give yourself Superuser privileges, then switch yourself to the postgres user, then stop the service.

  1. Make sure you've closed all connections to the postgres database, such as PGAdmin or xTuple.
  2. Go to Terminal (It's in Applications/Utilities)
  3. Type: sudo su -
  4. Enter the password you use to login to your Mac
  5. Type: cd /Applications/xTuple/postgresql/bin/ (assuming you installed xTuple in the default directory. If not, you'll need to change the path to where you installed it).
  6. Type: sudo -u postgres ./pg_ctl -D /Applications/xTuple/postgresql/data stop
  7. You can start up the database in the same way, using the start command: sudo -u postgres ./pg_ctl -D /Applications/xTuple/postgresql/data start
Error: 'connection to specified xtuple erp server cannot be made'

This issue was raised in our forum, and we thought we'd post a possible answer here. We have spoken with various users who encountered this error and discovered something in common with both. Both had prior installs of other Open Source solutions on their computer, prior to installing PostBooks. In both cases the the other product also installed an instance of PostgreSQL. It appears the two installations of Postgres are conflicting, and preventing the PostBooks installer from finishing the install. If you are done for the moment evaluating the other Postgres-powered application, try uninstalling it, and then running the PostBooks installer again.

How do I install pgcrypto?

If you are going to use credit card processing or if you're getting this message ERROR:  function decrypt(bytea, bytea, unknown) does not exist then you need to install pgcrypto. Pgcrypto is a library used for encrypting sensitive data. The pgcrypto software is a popular add-on package included with the PostgreSQL source code distribution. The pgcrypto module is used by our credit card encryption functionality.  Even if you are not processing credit cards, we still recommend that pgcrypto be installed. 

First, we need to locate the pgcrypto.sql file.  One typical path to this is:  /usr/local/pgsql/share/contrib/pgcrypto.sql

If it isn't there you can always search for the file.  You may need to perform the search as root:  find / | grep pgcrypto.sql (be patient, this takes awhile)  Or if you're on Windows just use the Windows file search.

Once you've located the file the next step is to install it.  The command to install pgcrypto onto your database is: psql -U mfgadmin NameOfDB < /wherever/the/file/is/pgcrypto.sql  Or if you prefer using pgAdmin, connect to the database you want to install pgcrypto and open/execute pgcrypto.sql through the SQL Editor.  It is also a good idea to install pgcrypto.sql on the template1 database.