Support

Hi Nicholas,

We run Akeeba Backup Pro on two Joomla 6 sites that use PostgreSQL. A restore test showed our PostgreSQL backups could not be restored intact, while every run had reported success. I traced it to four separate defects, all in engine/Dump/Native/Postgresql.php in 10.4.0. A patch for all four is attached.

The issue

1. --no-policies requires pg_dump 18. getCreateStatement() always passes it (line 960). pg_dump 17 and older reject it with unrecognized option '--no-policies' and exit 1, so no table gets a CREATE statement. The option is absent from the pg_dump 17 documentation and present in 18.
2. That failure is swallowed, and its reason is discarded. getCreateStatement() throws with pg_dump's own error text (line 996), but the caller catches Exception (line 390), throws $e away, and logs only Cannot get the CREATE statement for table #__x -- skipping. The backup then finishes as "complete with warnings" and uploads normally.
3. CREATE UNIQUE INDEX is rewritten into invalid SQL. The DDL post-processor adds IF NOT EXISTS and exempts lines where str_contains($line, 'CREATE INDEX') (line 1052). A unique index does not contain that substring, and the greedy (.*) then places IF NOT EXISTS before the table name: CREATE UNIQUE INDEX "x" ON IF NOT EXISTS "#__t" USING "btree" (...), which is a syntax error on restore.
4. jsonb data is written as a hex literal. JSONB shares the quoteHex() branch with BYTEA and the spatial types (line 715). '\x7b22...' is valid bytea input, but for a jsonb column PostgreSQL parses that string itself as JSON and rejects it (invalid input syntax for type json, Token "\" is invalid), failing the whole multi-row INSERT. Plain JSON already goes through quote() and restores fine.

Real-world scenario

Our servers run PostgreSQL 16, whose pg_dump rejects --no-policies. We had worked around defect 1 by deleting that line locally. Updating to 10.4.0 replaced the file, and the next two scheduled backups each logged 124 "Cannot get the CREATE statement" warnings, uploaded off-site, and showed as successful. Reading the SQL back out of those archives found 0 CREATE TABLE statements for 124 tables. Nothing in the log said why; running pg_dump by hand with the same flags showed the unrecognized option.

After restoring the workaround, we replayed a fresh archive into a scratch PostgreSQL 16 database. All 124 tables came back, but 7 statements failed: every standalone unique index (defect 3, 147 unique indexes live against 140 restored). The only table with jsonb data restored 0 of its 6 rows (defect 4). Both of those appear identically in archives made with pg_dump 16 and pg_dump 18, so they are independent of defect 1.

Environment where reproduced

• Akeeba Backup Pro: 10.4.0 (manifest XML and #__extensions.manifest_cache agree). Defects 1 to 4 are also present in 10.3.7.
• Joomla: 6.1.3
• PHP: 8.4.25 (CLI and FPM)
• PostgreSQL server: 16.15 on Debian 12 (PGDG packages)
• pg_dump: 16.15, and 18.6 for comparison

Proposed fix

1. A small private getPgDumpMajorVersion() runs pg_dump --version once per backup through ektelese(), caches the major version in volatile configuration, and --no-policies is added only when it is 18 or later. If the version cannot be read it returns 0, so the flag is omitted.
2. The caught exception's message is appended to the existing warning, e.g. ... -- skipping. Failed to run pg_dump. Return code 1, error: .... Behaviour is otherwise unchanged.
3. The exemption becomes preg_match('/^CREATE\s+(UNIQUE\s+)?INDEX\b/i', $line).
4. case 'JSONB': is removed, so jsonb is quoted like json and text.

--- a/engine/Dump/Native/Postgresql.php
+++ b/engine/Dump/Native/Postgresql.php
@@ -381,6 +381,8 @@
 		// If it is the first run, find number of rows and get the DDL.
 		if ($this->nextRange == 0)
 		{
+			$createError = '';
+
 			try
 			{
 				$outCreate = $this->getCreateStatement(
@@ -389,15 +391,17 @@
 			}
 			catch (Exception $e)
 			{
-				$outCreate = '';
+				$outCreate   = '';
+				$createError = $e->getMessage();
 			}
 
 			if (empty($outCreate))
 			{
 				Factory::getLog()->warning(
 					sprintf(
-						"Cannot get the CREATE statement for %s %s -- skipping", $this->nextTable->type,
-						$this->nextTable->abstractName
+						"Cannot get the CREATE statement for %s %s -- skipping%s", $this->nextTable->type,
+						$this->nextTable->abstractName,
+						($createError === '') ? '' : ('. ' . $createError)
 					)
 				);
 
@@ -712,7 +716,6 @@
 						{
 							// Hex encode spatial data and special types
 							case 'BYTEA':
-							case 'JSONB':
 							case 'GEOMETRY':
 							case 'GEOGRAPHY':
 							case 'POINT':
@@ -924,6 +927,39 @@
 	}
 
 	/**
+	 * Returns the major version of a pg_dump binary, or 0 if it cannot be determined.
+	 *
+	 * @param   string  $pgDumpPath  The path to pg_dump
+	 *
+	 * @return  int
+	 */
+	private function getPgDumpMajorVersion(string $pgDumpPath): int
+	{
+		$configuration = Factory::getConfiguration();
+		$cached        = $configuration->get('volatile.database.postgres.pgdump_major', null);
+
+		if ($cached !== null)
+		{
+			return (int) $cached;
+		}
+
+		$output = [];
+		$major  = 0;
+
+		if (
+			$this->ektelese(escapeshellarg($pgDumpPath) . ' --version', $output) === 0
+			&& preg_match('/\(PostgreSQL\)\s+(\d+)/', implode(' ', $output), $matches)
+		)
+		{
+			$major = (int) $matches[1];
+		}
+
+		$configuration->set('volatile.database.postgres.pgdump_major', $major);
+
+		return $major;
+	}
+
+	/**
 	 * Gets the DDL for an entity using pg_dump.
 	 */
 	protected function getCreateStatement(string $abstractName, string $tableName, string $type, bool $withDrop = false): string
@@ -957,7 +993,12 @@
 		$command .= ' -s'; // Schema only
 		$command .= ' -O'; // Disable owner information
 		$command .= ' --no-comments'; // Disable comments
-		$command .= ' --no-policies'; // Disable dumping security policies
+		// --no-policies was added in pg_dump 18. Older versions reject it with "unrecognized option".
+		if ($this->getPgDumpMajorVersion($pgDumpPath) >= 18)
+		{
+			$command .= ' --no-policies'; // Disable dumping security policies
+		}
+
 		$command .= ' --no-security-labels'; // Disable dumping security labels
 		$command .= ' --quote-all-identifiers';
 
@@ -1048,8 +1089,8 @@
 		});
 		// -- Change all `CREATE ... "#__` to `CREATE ... IF NOT EXISTS "#__`
 		$lines = array_map(function ($line) {
-			// CREATE INDEX... must NOT be converted!!!
-			if (str_contains($line, 'CREATE INDEX'))
+			// CREATE INDEX and CREATE UNIQUE INDEX must NOT be converted!!!
+			if (preg_match('/^CREATE\s+(UNIQUE\s+)?INDEX\b/i', $line))
 			{
 				return $line;
 			}

Alternative shapes

• For defect 2, a table whose DDL cannot be obtained might reasonably fail the backup rather than finish as complete-with-warnings, since a dump with no CREATE statements cannot be restored. I kept the patch to logging because that changes behaviour, and it is your call.
• For defect 1, testing for the option (e.g. parsing pg_dump --help) would work as well as a version check if you prefer that.

Tested against

Each change was tested on its own; I have not run the patched engine end to end.

• Defect 1: the version check against real --version output from pg_dump 16.15 (flag omitted) and 18.6 (flag added). Separately, Akeeba's exact pg_dump flags against the 16 server: pg_dump 16 exits 1, pg_dump 18 exits 0 and emits the CREATE statement.
• Defect 3: the post-processor closure, old and new rule, applied to real pg_dump output lines (unique index, index, table, sequence). The old rule breaks both unique index forms; the new one leaves them intact and still converts tables and sequences.
• Defect 4: 12 real jsonb values plus one containing quotes, backslashes, unicode, a newline and a tab, each quoted exactly as Driver\Postgresql::quote() does and as quoteHex() does, inserted into a scratch PostgreSQL 16 table. quote(): 13 of 13 inserted and compared equal to the source as jsonb. quoteHex(): 13 of 13 rejected.

Also noticed (not in the patch)

• Views, possibly. The same post-processor rule would turn CREATE VIEW "#__v" AS into CREATE VIEW IF NOT EXISTS "#__v" AS, which PostgreSQL 16 rejects as a syntax error (CREATE SEQUENCE IF NOT EXISTS is fine). Our sites have no views, so I have not reproduced this through a backup. It may be worth a look.
• akeeba:option:set rejects engine.dump.postgres.pgdump_path with "Invalid key" on a PostgreSQL site. A mysql-only option (engine.dump.native.nobtree) reads fine on a MySQL site, so it seems specific to the postgres-gated option. I did not find the cause. It matters here because pointing that option at a pg_dump 18 binary is the natural workaround for defect 1; we ended up setting it directly in the profile's configuration JSON.

Thanks for taking a look. Happy to rework any of this, split it into separate tickets, or take a different approach on any of the four.

Brian

Hi Nicholas,

No worries. I appreciate you reintroducing support for PG. I am learning a lot lately about its benefits (e.g. pgvector, pgjson) and am looking to use it much more extensively moving forward.

LMK if there is anything I can do to help with testing or providing additional use cases.

Brian