How do I run a(n) SQL script?

xTuple's Support, Professional Services, and Development departments often provide SQL scripts to patch databases. This might be a data fix, the addition of an index to the database, a new version of a stored procedure, or other change.

There are at least three ways to do this:

  • command line tool such as psql
  • graphical user interface tool such as pgAdmin
  • the xTuple ERP desktop client's MetaSQL editor

First save the SQL script file to your local machine. You don't have to save the file to the computer running the database server as long as your local machine can connect to the server over the network.

Then start the program of your choice and log in to the database. As always, we strongly recommend that you first try any script on a backup copy of your database, not your production database.

psql

Open a terminal or command window and type this:

psql -h db-server-hostname -p db-server-port -U admin -d database-name

pgAdmin

  • Start pgAdmin
  • Connect to the server
  • Navigate to the Query tool

MetaSQL editor

  • Start the xTuple ERP desktop client
  • Log in to the database
  • System > Design > MetaSQL
  • Click NEW

The third step is to run the script. If the script is small, just copy it and paste it into the window you just opened. Otherwise, load it from the file:

psql

\f relative-or-full-path-to-the-file

This runs the script in its entirety

pgAdmin

  • File > Open... or click on the OPEN button in the toolbar
  • Query > Run or click the RUN button

MetaSQL editor

  • File > Open... or click on the OPEN button in the toolbar
  • Tools > Execute Query
  • If the script appears to run successfully, turn off 'Test Mode' and run it again:
    • Tools > Test Mode (make sure there is no check mark next to Test Mode)
    • Tools > Execute Query