Creating a Database Dump

Liferay’s backup service allows you to upload a backup using a snapshot comprised of two compressed files, containing the document library and database dump, respectively. To upload data from a local Liferay instance’s database, you must be able to create a dump to upload. This is required for migrating to Liferay Cloud and Liferay upgrades.

Note

The backup service only supports backing up and restoring one database (the database set via the lcp-secret-database-name secret, usually lportal). Compressing and uploading multiple databases is not supported.

Ensuring Correct Table Capitalization

Database table and column names are case sensitive in Liferay Cloud, and they must follow a convention that differs by type of database (PostgreSQL or MySQL). Errors with table name capitalization can occur with certain operating systems or conversions between database types. For example, MySQL is case-sensitive in Linux by default, but not in Windows or MacOS (where Liferay may generate tables with all lower case names).

Before you create a database dump, ensure that your database’s tables have the appropriate capitalization. If your database’s tables don’t have the correct capitalization pattern, you must convert them to the correct format manually or with a script. You must also ensure that any of your own code that references the table names reflects the updated capitalization. Please submit a Support ticket if you need assistance adjusting your table names.

PostgreSQL Capitalization (Lower Case)

For PostgreSQL, table names must be in lower case to avoid errors. If the table names were created with upper case characters (like PascalCase), they’re unrecognizable by Liferay Cloud. If you converted your database to PostgreSQL with a tool like pgloader, the names are converted to lower case by default.

For example, convert these Pascal case table names:

• AccountEntry → accountentry

• CPDefinition → cpdefinition

• TrashEntry → trashentry

You can use the ALTER TABLE command in PostgreSQL to change a table’s name.

MySQL Capitalization (Pascal Case)

For MySQL, table names must be in Pascal case. If the table names were created in lower case, they’re unrecognizable by Liferay Cloud.

For example, convert these lower case table names:

• accountentry → AccountEntry

• cpdefinition → CPDefinition

• trashentry → TrashEntry

You can use the RENAME TABLE command in MySQL to change a table’s name.

Creating and Compressing a PostgreSQL Database Dump

The commands to create and compress a database dump for PostgreSQL depend on your operating system. The commands shown here compress the database dump into a database.gz file, which is the required format for database uploads to Liferay PaaS.

Note

If you are using Windows (OS), you must install file compression software to execute commands to pack/unpack compressed files. Install 7-zip or similar file compression software to do this.

For Linux/MacOS:

Run these commands to perform the dump and compress it (replace the variable values with your own username, host, and database name):

USERNAME=dxpcloud
HOST=localhost
DATABASE=lportal

pg_dump -U ${USERNAME} --format=plain --no-owner --no-acl -h ${HOST} -d ${DATABASE} | gzip > database.gz

For Windows:

Run these commands to perform the dump and compress it (replace the variable values with your own username, host, and database name):

SET USERNAME=dxpcloud
SET HOST=localhost
SET DATABASE=lportal

pg_dump -U %USERNAME% --format=plain --no-owner --no-acl -h %HOST% -d %DATABASE% -f database.sql

7za a -tgzip database.gz database.sql

You can also download a database dump from the backup service to see how the backup service creates its PostgreSQL dump file.

The resulting dump file should not contain any statements to drop or create the database. Here is an example:

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: accountentry; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.accountentry (

Creating and Compressing a MySQL Database Dump

The steps shown here compress the MySQL database dump into a database.gz file, which is the required format for database uploads to Liferay PaaS.

Note

If you are using Windows, these steps assume you have 7-Zip installed to compress the database dump. You must also have direct access to the MySQL database.

1. Perform the database dump and compress it into a .gz archive.

   For Linux and MacOS:

   mysqldump -uroot -ppassword --databases --add-drop-database lportal | gzip -c | cat > database.gz
   

   For Windows:

   mysqldump -u##### -p##### --databases --add-drop-database lportal > database.sql
   

   7za a -tgzip database.gz database.sql
   

   The --databases and --add-drop-database flags are necessary for backup restoration to work correctly. You can also use the /backup/download API to see how the backup service creates its MySQL dump file.

The resulting database dump contains the following code just before the create table statements.

--
-- Current Database: `lportal`
--

/*!40000 DROP DATABASE IF EXISTS `lportal`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `lportal` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `lportal`;

Related Topics

• Downloading and Uploading Backups