Upsize to an external RDBMS

Upsizing to an external RDBMSA Relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS's use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use. is a simple process that should take approximately 15-30 minutes. The high-level steps to upsize are:

1. Backup the existing data.

2. Create and initialize the new database.

3. Load the backed up data into the new database.

4. Restart the application.

These steps are discussed in detail below.

This section also includes some database specific configuration information. For more information, see Configuring a database .

Step 1 - Stop PaperCut NG

Stop the Application Server. This allows the data to be backed up, guaranteeing that all data is saved and ready to load into the new database.

For more information, see Stopping and starting the Application Server.

Step 2 - Perform a backup of the existing data

Perform a backup of the database. This data is loaded into the application in a later step. For more information, see Managing system backups.

To back up the database:

1. On the server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

   Mac: sudo su - papercut

   Linux: su - papercut

3. Change (cd) to the server binaries directory. e.g.

   Windows: cd "C:\Program Files\PaperCut NG\server\bin\win"

   Mac: cd "/Applications/PaperCut NG/server/bin/mac"

   Linux: cd ~papercut/server/bin/linux-*

4. Run the following command:

   db-tools export-db

   The output of the above command shows the name of the backup file.

5. Take note of the backup file name because it is required in a later step.

Step 3 - Create a new database in the external RDBMS

This step differs depending on on the external database you are using. It is assumed that the administrator knows how to create a new database. No matter what database is used the following steps must be performed:

1. Create a new empty database for dedicated use by PaperCut NG. When creating the database make sure to select the correct character encoding for your language.

   Important:

   Create the database with a character encoding suitable for your language. For SQLStructured Query Language (SQL) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). Server, the character encoding is set in Collation on the new database screen. For other databases, such as, PostgreSQL or MySQL, select a unicode character set (UNICODE or UTF8) that allows all possible characters to be stored.

2. Create a new database user (and password) for PaperCut NG to use to connect to the database.

3. Assign the appropriate permissions to the new user to give them full access to the new database (e.g. permission to create/drop tables, and select/insert/update/delete in all tables).

Step 4 - Change the PaperCut NG connection details

The next step is to configure PaperCut NG to connect to the new external database. To do this:

1. On the server, open the server config file in a text editor (e.g. Notepad):

   [app-path]/server/server.properties

2. Comment out the line:

   database.type=Internal

   by adding a # (hash) character to the beginning of the line.

3. Find the database connection details for the database type you require (e.g. SQL Server or PostgreSQL), and uncomment the lines by removing the # (hash) characters.

4. Set the username and password to connect to the database

   database.username=[your-db-user]

   database.password=[your-db-password]

5. Set the database URL, which describes the location and connection details of the external database. See below for details of the format of the database URLs for different database types.

SQL Server database connection URL format

The SQL Server URL format is:

jdbc:jtds:sqlserver://[server]/[database]

The [server] parameter is the name of the server running the SQL Server database, and must be resolvable from the PaperCut NG server. If the SQL Server instance is running on the same machine, then you can use localhost.

The [database] parameter is the name of the SQL Server database you created in Step 3 above.

The default port is 1433. You can specify a different port in the URL if required.

jdbc:jtds:sqlserver://[server][:port]/[database]

When using SQL Server instances, the instance name is specified in the connection URL as follows:

jdbc:jtds:sqlserver://[server]/[database];instance=[instancename]

SQL Server Express database connection URL format

The SQL Server Express format is:

jdbc:jtds:sqlserver://[server]:[port]/[database]

The [server] parameter is the name of the server running the SQL Server database, and must be resolvable from the PaperCut NG server. If the SQL Server instance is running on the same machine, then you can use localhost.

The [port] parameter is the port the SQL Server Express edition is configured to listen on. For more information on configuring SQL Express, see Configuring Microsoft SQL Express.

The [database] parameter is the name of the SQL Server database you created in Step 3 above.

PostgreSQL database connection URL format

The Postgres URL format is:

jdbc:postgresql://[server]/[database]

The [server] parameter is the name of the server running the PostgreSQL database, and must be resolvable from the PaperCut NG server. If the PostgreSQL instance is running on the same machine, then you can use localhost.

The [database] parameter is the name of the PostgreSQL database you created in Step 3 above.

MySQL database connection URL format

The MySQL URL format is:

jdbc:mysql://[server]/[database]

The [server] parameter is the name of the server running the MySQL database, and must be resolvable from the PaperCut NG server. If the MySQL instance is running on the same machine, then you can use localhost.

The [database] parameter is the name of the MySQL database you created in Step 3 above.

Important:

See Configuring MySQL for specific instructions to configure MySQL.

Oracle database connection URL format

The Oracle URL format is:

jdbc:oracle:thin:@[server]:[port]/[ServiceName]

The [server] parameter is the name of the server running the Oracle database, and must be resolvable from the PaperCut server. If the Oracle instance is running on the same machine, then you can use localhost.

The [port] specifies the port number that the Oracle services are listening on. By default this is 1521.

The [ServiceName] specifies the Oracle service name or identifier used to identify the database. For Oracle Express edition this is XE.

E.g. for an Oracle Express Edition installation on the same server as PaperCut, running on the default port and SID, use:

jdbc:oracle:thin:@localhost:1521/SE

Important:

See Configuring Oracle (and Oracle Express Edition) for specific instructions to configure Oracle.

Step 5 - Initialize the new database

The next step is to initialize the new database, creating the required database tables and initial data. To initialize the database:

1. On the server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

   Mac: sudo su - papercut

   Linux: su - papercut

3. Change (cd) to the server binaries directory. e.g.

   Windows: cd "C:\Program Files\PaperCut NG\server\bin\win"

   Mac: cd "/Applications/PaperCut NG/server/bin/mac"

   Linux (32bit): cd ~papercut/server/bin/linux-i686

   Linux (64bit): cd ~papercut/server/bin/linux-x64

4. Run the following command: db-tools init-db

A message is displayed to indicate that the connection details are correct and the database was initialized correctly.

Step 6 - Load the data into the new database

This step loads the data (that was exported in Step 2) into the database. To import the data:

1. On the server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

   Mac: sudo su - papercut

   Linux: su - papercut

3. Change (cd) to the server binaries directory. e.g.

   Windows: cd "C:\Program Files\PaperCut NG\server\bin\win"

   Mac: cd "/Applications/PaperCut NG/server/bin/mac"

   Linux (32bit): cd ~papercut/server/bin/linux-i686

   Linux (64bit): cd ~papercut/server/bin/linux-x64

4. Run the following command: db-tools import-db "backup file name".

   This command shows the data import progress.

If no errors occur, the application is ready to restart.

Step 7 - Restart PaperCut NG

The data is now in the new database and you can restart the server.

For more information, see Stopping and starting the Application Server.

Wait 30 seconds for the server to start, then log in to the Admin web interface. If you can log in successfully, then the upsizing process worked successfully.